Re: Notification or action when WAL archives fully restored and streaming replication started
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Hi,
I've got master/slave replication setup between a few hosts. At any
point a slave could become a master. I've got appropriate locking in
place using an external system so that only one master can exist at a
time. I'm having trouble determining when my slaves are in sync with the
current master so that they can be a candidate for promotion. I want
some form of indicator _without reading a log_ when streaming
replication has started.
My recovery.conf for slaves:
standby_mode = on
restore_command = 'gunzip < /archives/wal/%f > %p'
recovery_target_timeline = 'latest'
primary_conninfo = 'host=PGSQL_FRONTEND_NAME port=5432
user=PGSQL_RECOVERY_USER password=PGSQL_RECOVERY_PASS'
Appropriate postgresql.conf:
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archives/wal/%f && gzip < %p >
/archives/wal/%f'
archive_timeout = 15min
Regards,
Michael Cassaniti
-----BEGIN PGP SIGNATURE-----
iG0EAREIAB0WIQT0DIHSqEo48gI0VT9pF1oDt4Q+5wUCXInRqgAKCRBpF1oDt4Q+
5661AN4nRJPXF/M0ZoLg3JVH8f0UsO1WlouHruIRMnsnAN4q9x4G6S4RcobUm5Kh
qTNOD2F3v6A8ng4ABFpm
=5qCA
-----END PGP SIGNATURE-----
Import Notes
Reply to msg id not found: 9b97926f-ba7f-2fb1-bb4f-74ed33e49c2b@cassaniti.id.auReference msg id not found: 9b97926f-ba7f-2fb1-bb4f-74ed33e49c2b@cassaniti.id.au
On Thu, Mar 14, 2019 at 02:59:38PM +1100, Michael Cassaniti wrote:
I've got master/slave replication setup between a few hosts. At any
point a slave could become a master. I've got appropriate locking in
place using an external system so that only one master can exist at a
time. I'm having trouble determining when my slaves are in sync with the
current master so that they can be a candidate for promotion. I want
some form of indicator _without reading a log_ when streaming
replication has started.
pg_stat_replication on the primary, no? Here is its documentation:
https://www.postgresql.org/docs/11/monitoring-stats.html#MONITORING-STATS-VIEWS
--
Michael
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
On 14/3/19 3:10 pm, Michael Paquier wrote:
On Thu, Mar 14, 2019 at 02:59:38PM +1100, Michael Cassaniti wrote: >> I've got master/slave replication setup between a few hosts. At any
point a slave could become a master. I've got appropriate locking in
place using an external system so that only one master can exist at a
time. I'm having trouble determining when my slaves are in sync with
the >> current master so that they can be a candidate for promotion. I
want >> some form of indicator _without reading a log_ when streaming >>
replication has started. > > pg_stat_replication on the primary, no?
Here is its documentation: >
https://www.postgresql.org/docs/11/monitoring-stats.html#MONITORING-STATS-VIEWS
-- > Michael This at least points me in the right direction. I need
something on the receiver side, so the state column in
pg_stat_wal_receiver might cover. I can check for state = streaming, but
the DB won't accept connections in standby mode.
Thanks for the quick reply.
-----BEGIN PGP SIGNATURE-----
iG0EAREIAB0WIQT0DIHSqEo48gI0VT9pF1oDt4Q+5wUCXInxjAAKCRBpF1oDt4Q+
562uAOCX3SfCi4ppOd0hBhzsdRWh/3yPeMm8F7c7BgrWAN4tGe+7RnRMrLeE6pOU
/5kdwISbxH6fmBEZ1CZM
=qCpv
-----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
On 14/3/19 5:15 pm, Michael Cassaniti wrote:
On 14/3/19 3:10 pm, Michael Paquier wrote: > > On Thu, Mar 14, 2019
at 02:59:38PM +1100, Michael Cassaniti wrote: >> I've got master/slave
replication setup between a few hosts. At any > >> point a slave could
become a master. I've got appropriate locking in > >> place using an
external system so that only one master can exist at a > >> time. I'm
having trouble determining when my slaves are in sync with > the >>
current master so that they can be a candidate for promotion. I > want
some form of indicator _without reading a log_ when streaming >> >
replication has started. > > pg_stat_replication on the primary, no? >
Here is its documentation: > >
https://www.postgresql.org/docs/11/monitoring-stats.html#MONITORING-STATS-VIEWS
-- > Michael This at least points me in the right direction. I need
something on the receiver side, so the state column in >
pg_stat_wal_receiver might cover. I can check for state = streaming, but
the DB won't accept connections in standby mode. > > Thanks for the
quick reply. Actually I was wrong. For some reason my testing initially
didn't want to allow read-only connections. Problem solved.
-----BEGIN PGP SIGNATURE-----
iG0EAREIAB0WIQT0DIHSqEo48gI0VT9pF1oDt4Q+5wUCXIoJgAAKCRBpF1oDt4Q+
53AnAOCQSbaOZy+K1qR8C3+EjBWshY0qwwVmHIG+Khy0AOCmAS+TgqE+mngBzpx5
jgMbyjaDn2RXvS14xW68
=sk4C
-----END PGP SIGNATURE-----