pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Streaming Replication Hooks
2+ messages / 2 participants
[nested] [flat]

* Streaming Replication Hooks
@ 2012-03-14 17:47 David Fennell <david.fennell@hospedia.co.uk>
  2012-03-15 12:12 ` Re: Streaming Replication Hooks Sergey Konoplev <gray.ru@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: David Fennell @ 2012-03-14 17:47 UTC (permalink / raw)
  To: pgsql-admin

Hi All,

I hope this is the correct mailing list to ask this question.

We are setting up a system using postgresql 9.1 and streaming
replication to copy reference data from a single central location to
several remote sites.  The database changes will come in clumps with
long periods of no activity in between.  I would like to add some kind
of programmatic hook to tell us that new changes have been received from
central so that we can trigger messages to be sent out to devices on the
site that may require updates of their data so that I don't have to rely
on polling the database(s).

Is there any way to hook at this point? Even if it didn't tell exactly
what had changed, just that a particular database or table had been
modified that would be perfect.  I don't mind what language is required
either.  

I found reference to wal_sender and wal_receiver hooks which sounds very
close to what I need, but I suspect that wal_receiver is processed
before the data is applied to the database and I would like a hook after
the database has been updated.

I am assuming that normal triggers cannot be used on a replicated
database?  Can someone confirm or deny this?

Thanks in advance.

Dave Fennell

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Streaming Replication Hooks
  2012-03-14 17:47 Streaming Replication Hooks David Fennell <david.fennell@hospedia.co.uk>
@ 2012-03-15 12:12 ` Sergey Konoplev <gray.ru@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Sergey Konoplev @ 2012-03-15 12:12 UTC (permalink / raw)
  To: David Fennell <david.fennell@hospedia.co.uk>; +Cc: pgsql-admin

Probably you will find pg_last_xact_replay_timestamp() interesting. It
returns the timestamp of the last transaction (when the transaction
was commited) that was replayed on slave.

http://www.postgresql.org/docs/9.1/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE

On Wed, Mar 14, 2012 at 9:47 PM, David Fennell
<david.fennell@hospedia.co.uk> wrote:
> Hi All,
>
> I hope this is the correct mailing list to ask this question.
>
> We are setting up a system using postgresql 9.1 and streaming replication to
> copy reference data from a single central location to several remote sites.
> The database changes will come in clumps with long periods of no activity in
> between.  I would like to add some kind of programmatic hook to tell us that
> new changes have been received from central so that we can trigger messages
> to be sent out to devices on the site that may require updates of their data
> so that I don't have to rely on polling the database(s).
>
> Is there any way to hook at this point? Even if it didn't tell exactly what
> had changed, just that a particular database or table had been modified that
> would be perfect.  I don't mind what language is required either.
>
> I found reference to wal_sender and wal_receiver hooks which sounds very
> close to what I need, but I suspect that wal_receiver is processed before
> the data is applied to the database and I would like a hook after the
> database has been updated.
>
> I am assuming that normal triggers cannot be used on a replicated database?
> Can someone confirm or deny this?
>
> Thanks in advance.
>
> Dave Fennell



-- 
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com
LinkedIn: http://ru.linkedin.com/in/grayhemp
JID/GTalk: gray.ru@gmail.com Skype: gray-hemp



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2012-03-15 12:12 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-14 17:47 Streaming Replication Hooks David Fennell <david.fennell@hospedia.co.uk>
2012-03-15 12:12 ` Sergey Konoplev <gray.ru@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox