Обсуждение: streaming replication sent location not advancing - feature request?

Поиск
Список
Период
Сортировка

streaming replication sent location not advancing - feature request?

От
pgorg_aav
Дата:
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

Re: streaming replication sent location not advancing - feature request?

От
Jerry Sievers
Дата:
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

Re: streaming replication sent location not advancing - feature request?

От
pgorg_aav
Дата:

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 Consulting

e:
postgres.consulting@comcast.net
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