Skip site navigation (1) Skip section navigation (2)

Re: Streaming Replication Hooks

From: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
To: David Fennell <david(dot)fennell(at)hospedia(dot)co(dot)uk>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Streaming Replication Hooks
Date: 2012-03-15 12:12:09
Message-ID: CAL_0b1sWrJbQG_7WCbp87KBxLVfiJiwrH2aNP1tC7Gof2myYgA@mail.gmail.com (view raw or flat)
Thread:
Lists: 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(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

In response to

pgsql-admin by date

Next:From: Christian UllrichDate: 2012-03-16 08:35:43
Subject: Re: Enabling Windows Authentication
Previous:From: Khangelani GamaDate: 2012-03-15 04:33:45
Subject: Re: Update actions (with user name) inside PostgreSQL DB - any version on postgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group