Re: pg_receivelwal vs synchronous - Mailing list pgsql-general

From Magnus Hagander
Subject Re: pg_receivelwal vs synchronous
Date
Msg-id CABUevEwgi7d1y8qcsRYCvASUY7ny+duW+-w_u_5uQFcb8eSfmA@mail.gmail.com
Whole thread Raw
In response to pg_receivelwal vs synchronous  (Jesper Pedersen <jesper.pedersen@redhat.com>)
Responses Re: pg_receivelwal vs synchronous
List pgsql-general
On Wed, Jun 26, 2019 at 5:06 PM Jesper Pedersen <jesper.pedersen@redhat.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'.

--

pgsql-general by date:

Previous
From: Prakash Ramakrishnan
Date:
Subject: patch 11.2 to 11.4
Next
From: Jesper Pedersen
Date:
Subject: Re: pg_receivelwal vs synchronous