Thread: streaming replication sent location not advancing - feature request?
Hi,
With wal shipping it is possible to pull the WAL files in advance from the master.
Is it possible to do something similar for streaming replication?
This morning due to VM hardware issues, a couple of standbys lagged (A LOT) behind master
# select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
-------+----------+----------+------------------+---------------+-----------------+-------------+-------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
...=x8 snipped
1758 | 10 | postgres | walreceiver | 172.26.149.47 | | 34798 | 2014-02-04 14:48:51.318344-05 | streaming | 1D0/C49A9BE0 | 1D0/C49A9BE0 | 1D0/C49A9BE0 | 1D0/C49A9B38 | 0 | async
16072 | 10 | postgres | walreceiver | 172.26.115.48 | | 57152 | 2013-12-07 09:25:21.713723-05 | streaming | 1C8/11B40000 | 1C8/119A0000 | 1C8/11900000 | 1C8/117EDAB8 | 0 | async
(9 rows)
Thanks much in advance,
Amit
pgorg_aav <kahitarich-postgresorg@yahoo.com> writes: > Hi, > > With wal shipping it is possible to pull the WAL files in advance from the master. > Is it possible to do something similar for streaming replication? wal_keep_segments <something higher> Otherwise just use the already supported hybrid of WAL archiving and streaming. > This morning due to VM hardware issues, a couple of standbys lagged (A LOT) behind master > > # select * from pg_stat_replication ; > pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | > write_location | flush_location | replay_location | sync_priority | sync_state > -------+----------+----------+------------------+---------------+-----------------+-------------+-------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------ > ...=x8 snipped > 1758 | 10 | postgres | walreceiver | 172.26.149.47 | | 34798 | 2014-02-04 14:48:51.318344-05| streaming | 1D0/C49A9BE0 | 1D0/ > C49A9BE0 | 1D0/C49A9BE0 | 1D0/C49A9B38 | 0 | async > 16072 | 10 | postgres | walreceiver | 172.26.115.48 | | 57152 | 2013-12-07 09:25:21.713723-05| streaming | 1C8/11B40000 | 1C8/ > 119A0000 | 1C8/11900000 | 1C8/117EDAB8 | 0 | async > (9 rows) > > Thanks much in advance, > Amit > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
Thank you for your response.
I'm following the hybrid model - one thing I've noticed is when the standbys fall back to WAL shipping, they stop taking read requests (in this case we would've lose almost half the capacity), is there something that I can do to prevent that?
The wal_keep_segments was slowly increased to 8000 during this issue.
Hence my "feature request?" comment...
Like mysql (advance apologies for mentioning it :) ) has a slave_io_thread, can one be implemented natively in PostgreSQL?
Or is that left to the individual to implement in the restore_command.
I'm going to try to play around more with the restore command.
Thanks,
Amit
On Friday, March 7, 2014 12:12 PM, Jerry Sievers <gsievers19@comcast.net> wrote:
pgorg_aav <kahitarich-postgresorg@yahoo.com> writes:
> Hi,
>
> With wal shipping it is possible to pull the WAL files in advance from the master.
> Is it possible to do something similar for streaming replication?
wal_keep_segments <something higher>
Otherwise just use the already supported hybrid of WAL archiving and
streaming.
> This morning due to VM hardware issues, a couple of standbys lagged (A LOT) behind master
>
> # select * from pg_stat_replication ;
> pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location |
> write_location | flush_location | replay_location | sync_priority | sync_state
> -------+----------+----------+------------------+---------------+-----------------+-------------+-------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
> ...=x8 snipped
> 1758 | 10 | postgres | walreceiver | 172.26.149.47 | | 34798 | 2014-02-04 14:48:51.318344-05 | streaming | 1D0/C49A9BE0 | 1D0/
> C49A9BE0 | 1D0/C49A9BE0 | 1D0/C49A9B38 | 0 | async
> 16072 | 10 | postgres | walreceiver | 172.26.115.48 | | 57152 | 2013-12-07 09:25:21.713723-05 | streaming | 1C8/11B40000 | 1C8/
> 119A0000 | 1C8/11900000 | 1C8/117EDAB8 | 0 | async
> (9 rows)
>
> Thanks much in advance,
> Amit
>
--
Jerry Sievers
Postgres DBA/Development Consultingpostgres.consulting@comcast.net
e:
p: 312.241.7800
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin