Re: pg_receivelwal vs synchronous

On Wed, Jun 26, 2019 at 5:06 PM Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com>
wrote:

> Hi,
>
> PostgreSQL 11.4
>
> Given,
>
> postgresql.conf:
> ----------------
> wal_level = replica
> synchronous_commit = remote_apply
> synchronous_standby_names = '*'
>
>
> CREATE ROLE repluser WITH LOGIN REPLICATION PASSWORD 'mypwd';
> SELECT pg_create_physical_replication_slot('replica1');
>
>
> Execute
>
> pg_receivewal -D /tmp/wal/ -S replica1 --synchronous -p 5432 -h
> localhost -U repluser -W --dbname="application_name=replica1"
>
> gives
>
> LOG: standby "replica1" is now a synchronous standby with priority 1
>
> Then
>
> psql -c 'CREATE DATABASE test' postgres
>
> which hangs (committed locally).
>
> postgres=# SELECT * FROM pg_replication_slots ;
> slot_name | plugin | slot_type | datoid | database | temporary |
> active | active_pid | xmin | catalog_xmin | restart_lsn |
> confirmed_flush_lsn
>
> -----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
> replica1 | | physical | | | f | t
> | 12502 | | | 0/1655508 |
> (1 row)
>
> postgres=# SELECT * FROM pg_stat_replication;
> pid | usesysid | usename | application_name | client_addr |
> client_hostname | client_port | backend_start |
> backend_xmin | state | sen
> t_lsn | write_lsn | flush_lsn | replay_lsn | write_lag |
> flush_lag | replay_lag | sync_priority | sync_state
>
> -------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+----
>
> -------+-----------+-----------+------------+-----------------+-----------------+-----------------+---------------+------------
> 12502 | 16384 | repluser | replica1 | ::1 |
> | 45816 | 2019-06-26 11:00:45.098276-04 | |
> streaming | 0/1
> 655508 | 0/1655508 | 0/1655508 | | 00:00:00.000267 |
> 00:00:00.000267 | 00:02:14.938836 | 1 | sync
> (1 row)
>
>
> So, replica1 is active and in sync mode, but replay_lsn is never
> updated, and replay_lag keeps increasing.
>
>
> What am I missing here ?
>

I believe your problem is remote_apply.

pg_receivewal never *applies* any WAL, so it just updates the write and
flush locations. Notice how the replay_lsn remains NULL.

So you need synchronous_commit to be 'on' or 'remote_write', not
'remote_apply'.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>