Re: Streaming Replication Hooks

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(dot)fennell(at)hospedia(dot)co(dot)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(dot)ru(at)gmail(dot)com Skype: gray-hemp