Re: BUG #13443: master will remove dead rows when hot standby(use slot) disconnect

At 2015-06-16 08:52:48, "Michael Paquier" <michael.paquier@gmail.com> wrote:
>On Tue, Jun 16, 2015 at 4:30 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> On Mon, Jun 15, 2015 at 2:05 AM, <digoal@126.com> wrote:
>>> http://www.postgresql.org/docs/devel/static/warm-standby.html#STREAMING-REPLICATION
>>>
>>> 25.2.6. Replication Slots
>>> Replication slots provide an automated way to ensure that the master does
>>> not remove WAL segments until they have been received by all standbys, and
>>> that the master does not remove rows which could cause a recovery conflict
>>> even when the standby is disconnected.
>>>
>>> In my test, master will remove rows when standby disconnect.
>>
>>
>> I can't reproduce this.  In my hands when the standby crashes, tuples on the
>> master are not removed until either that slot is destroyed on the master, or
>> until the standby reconnects.
>
>Yep.
>
>> Can you show us all the settings changes you have made to postgresql.conf
>> and recovery.conf, and to the replication slots table?
>
>Yes, perhaps the standby has already acknowledged the dead tuples
>before you shut it down.
>
>> One potential doc bug I see is that the it seems to imply that replication
>> slots replaces the need for hot_standby_feedback, when it fact it must be
>> used in conjunction with it.  Do you have hot_standby_feedback turned on in
>> the standby?
yes, i'm not open the hot_standby_feedback, thanks. When open it, no problem now.

>
>As far as I recall, using replication slots implies that the
>RecentGlobalXmin horizon is updated to guarantee the presence of
>tuples on the standby once it reconnects. Perhaps I am missing
>something?
>-- 
>Michael

Example:
///////////////////////// Primary:
postgres=# select * from pg_file_settings;                  sourcefile                   | sourceline | seqno |            name            |      setting       
-----------------------------------------------+------------+-------+----------------------------+-------------------- /data03/pgdata95/pg_root/postgresql.conf      |         63 |     1 | port                       | 1922 /data03/pgdata95/pg_root/postgresql.conf      |         64 |     2 | max_connections            | 100 /data03/pgdata95/pg_root/postgresql.conf      |         68 |     3 | unix_socket_directories    | . /data03/pgdata95/pg_root/postgresql.conf      |         71 |     4 | unix_socket_permissions    | 0700 /data03/pgdata95/pg_root/postgresql.conf      |        102 |     5 | tcp_keepalives_idle        | 60 /data03/pgdata95/pg_root/postgresql.conf      |        104 |     6 | tcp_keepalives_interval    | 10 /data03/pgdata95/pg_root/postgresql.conf      |        106 |     7 | tcp_keepalives_count       | 10 /data03/pgdata95/pg_root/postgresql.conf      |        116 |     8 | shared_buffers             | 4096MB /data03/pgdata95/pg_root/postgresql.conf      |        131 |     9 | dynamic_shared_memory_type | posix /data03/pgdata95/pg_root/postgresql.conf      |        160 |    10 | bgwriter_delay             | 10ms /data03/pgdata95/pg_root/postgresql.conf      |        176 |    11 | wal_level                  | logical /data03/pgdata95/pg_root/postgresql.conf      |        179 |    12 | synchronous_commit         | off /data03/pgdata95/pg_root/postgresql.conf      |        189 |    13 | wal_compression            | on /data03/pgdata95/pg_root/postgresql.conf      |        192 |    14 | wal_buffers                | 16MB /data03/pgdata95/pg_root/postgresql.conf      |        194 |    15 | wal_writer_delay           | 10ms /data03/pgdata95/pg_root/postgresql.conf      |        202 |    16 | max_wal_size               | 8GB /data03/pgdata95/pg_root/postgresql.conf      |        227 |    17 | max_wal_senders            | 32 /data03/pgdata95/pg_root/postgresql.conf      |        232 |    18 | max_replication_slots      | 32 /data03/pgdata95/pg_root/postgresql.conf      |        234 |    19 | track_commit_timestamp     | off /data03/pgdata95/pg_root/postgresql.conf      |        322 |    20 | log_destination            | csvlog /data03/pgdata95/pg_root/postgresql.conf      |        328 |    21 | logging_collector          | on /data03/pgdata95/pg_root/postgresql.conf      |        340 |    22 | log_truncate_on_rotation   | on /data03/pgdata95/pg_root/postgresql.conf      |        446 |    23 | log_timezone               | PRC /data03/pgdata95/pg_root/postgresql.conf      |        533 |    24 | datestyle                  | iso, mdy /data03/pgdata95/pg_root/postgresql.conf      |        535 |    25 | timezone                   | PRC /data03/pgdata95/pg_root/postgresql.conf      |        548 |    26 | lc_messages                | C /data03/pgdata95/pg_root/postgresql.conf      |        550 |    27 | lc_monetary                | C /data03/pgdata95/pg_root/postgresql.conf      |        551 |    28 | lc_numeric                 | C /data03/pgdata95/pg_root/postgresql.conf      |        552 |    29 | lc_time                    | C /data03/pgdata95/pg_root/postgresql.conf      |        555 |    30 | default_text_search_config | pg_catalog.english /data03/pgdata95/pg_root/postgresql.auto.conf |          3 |    31 | track_commit_timestamp     | on /data03/pgdata95/pg_root/postgresql.auto.conf |          4 |    32 | shared_buffers             | 8GB /data03/pgdata95/pg_root/postgresql.auto.conf |          5 |    33 | synchronous_commit         | on
(33 rows)

///////////////////////// Standby:
pg95@db-172-16-3-150-> psql -h 127.0.0.1 -p 1923
psql (9.5devel)
Type "help" for help.
postgres=# select * from pg_file_settings;                   sourcefile                    | sourceline | seqno |             name             |      setting       
-------------------------------------------------+------------+-------+------------------------------+-------------------- /data03/pg95_stdby/pg_root/postgresql.conf      |         63 |     1 | port                         | 1923 /data03/pg95_stdby/pg_root/postgresql.conf      |         64 |     2 | max_connections              | 100 /data03/pg95_stdby/pg_root/postgresql.conf      |         68 |     3 | unix_socket_directories      | . /data03/pg95_stdby/pg_root/postgresql.conf      |         71 |     4 | unix_socket_permissions      | 0700 /data03/pg95_stdby/pg_root/postgresql.conf      |        102 |     5 | tcp_keepalives_idle          | 60 /data03/pg95_stdby/pg_root/postgresql.conf      |        104 |     6 | tcp_keepalives_interval      | 10 /data03/pg95_stdby/pg_root/postgresql.conf      |        106 |     7 | tcp_keepalives_count         | 10 /data03/pg95_stdby/pg_root/postgresql.conf      |        116 |     8 | shared_buffers               | 4096MB /data03/pg95_stdby/pg_root/postgresql.conf      |        131 |     9 | dynamic_shared_memory_type   | posix /data03/pg95_stdby/pg_root/postgresql.conf      |        160 |    10 | bgwriter_delay               | 10ms /data03/pg95_stdby/pg_root/postgresql.conf      |        176 |    11 | wal_level                    | logical /data03/pg95_stdby/pg_root/postgresql.conf      |        179 |    12 | synchronous_commit           | off /data03/pg95_stdby/pg_root/postgresql.conf      |        189 |    13 | wal_compression              | on /data03/pg95_stdby/pg_root/postgresql.conf      |        192 |    14 | wal_buffers                  | 16MB /data03/pg95_stdby/pg_root/postgresql.conf      |        194 |    15 | wal_writer_delay             | 10ms /data03/pg95_stdby/pg_root/postgresql.conf      |        202 |    16 | max_wal_size                 | 8GB /data03/pg95_stdby/pg_root/postgresql.conf      |        227 |    17 | max_wal_senders              | 32 /data03/pg95_stdby/pg_root/postgresql.conf      |        232 |    18 | max_replication_slots        | 32 /data03/pg95_stdby/pg_root/postgresql.conf      |        234 |    19 | track_commit_timestamp       | off /data03/pg95_stdby/pg_root/postgresql.conf      |        250 |    20 | hot_standby                  | on /data03/pg95_stdby/pg_root/postgresql.conf      |        258 |    21 | wal_receiver_status_interval | 1s /data03/pg95_stdby/pg_root/postgresql.conf      |        260 |    22 | hot_standby_feedback         | on /data03/pg95_stdby/pg_root/postgresql.conf      |        322 |    23 | log_destination              | csvlog /data03/pg95_stdby/pg_root/postgresql.conf      |        328 |    24 | logging_collector            | on /data03/pg95_stdby/pg_root/postgresql.conf      |        340 |    25 | log_truncate_on_rotation     | on /data03/pg95_stdby/pg_root/postgresql.conf      |        446 |    26 | log_timezone                 | PRC /data03/pg95_stdby/pg_root/postgresql.conf      |        533 |    27 | datestyle                    | iso, mdy /data03/pg95_stdby/pg_root/postgresql.conf      |        535 |    28 | timezone                     | PRC /data03/pg95_stdby/pg_root/postgresql.conf      |        548 |    29 | lc_messages                  | C /data03/pg95_stdby/pg_root/postgresql.conf      |        550 |    30 | lc_monetary                  | C /data03/pg95_stdby/pg_root/postgresql.conf      |        551 |    31 | lc_numeric                   | C /data03/pg95_stdby/pg_root/postgresql.conf      |        552 |    32 | lc_time                      | C /data03/pg95_stdby/pg_root/postgresql.conf      |        555 |    33 | default_text_search_config   | pg_catalog.english /data03/pg95_stdby/pg_root/postgresql.auto.conf |          3 |    34 | track_commit_timestamp       | on /data03/pg95_stdby/pg_root/postgresql.auto.conf |          4 |    35 | shared_buffers               | 8GB /data03/pg95_stdby/pg_root/postgresql.auto.conf |          5 |    36 | synchronous_commit           | on
(36 rows)

pg95@db-172-16-3-150-> cd /data03/pg95_stdby/pg_root/
pg95@db-172-16-3-150-> cat recovery.conf |grep ^[a-z]
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'hostaddr=127.0.0.1 port=1922'               # e.g. 'host=localhost port=5432'
primary_slot_name = 'abc'

///////////////////////// Primary:
pg95@db-172-16-3-150-> psql
psql (9.5devel)
Type "help" for help.
postgres=# select pg_create_physical_replication_slot('abc');
postgres=# select * from pg_replication_slots ; slot_name | plugin | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn 
-----------+--------+-----------+--------+----------+--------+------------+------+--------------+------------- abc       |        | physical  |        |          | t      |       5093 | 1809 |              | 6/6E000000
(1 row)

postgres=# create table test(id int)
postgres-# ;
CREATE TABLE
postgres=# insert into test values (1),(2),(3);
INSERT 0 3

///////////////////////// Standby:
pg95@db-172-16-3-150-> psql -h 127.0.0.1 -p 1923
postgres=# begin transaction isolation level repeatable read;
BEGIN
postgres=# select * from test; id 
----  1  2  3
(3 rows)

///////////////////////// Primary:
postgres=# delete from test;
DELETE 3
postgres=# vacuum verbose test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 3 nonremovable row versions in 1 out of 1 pages
DETAIL:  3 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 5093
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 20694
backend_start    | 2015-06-16 08:50:58.779855+08
backend_xmin     | 
state            | streaming
sent_location    | 7/77B09918
write_location   | 7/77B09918
flush_location   | 7/77B09918
replay_location  | 7/77B09918
sync_priority    | 0
sync_state       | async

postgres=# checkpoint;
CHECKPOINT
postgres=# vacuum VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 3 nonremovable row versions in 1 out of 1 pages
DETAIL:  3 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

postgres=# select * from pg_replication_slots ;
-[ RECORD 1 ]+-----------
slot_name    | abc
plugin       | 
slot_type    | physical
datoid       | 
database     | 
active       | t
active_pid   | 5093
xmin         | 1815
catalog_xmin | 
restart_lsn  | 7/77B099F8

postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 5093
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 20694
backend_start    | 2015-06-16 08:50:58.779855+08
backend_xmin     | 
state            | streaming
sent_location    | 7/77B099F8
write_location   | 7/77B099F8
flush_location   | 7/77B099F8
replay_location  | 7/77B099F8
sync_priority    | 0
sync_state       | async

///////////////////////// HOST:
[root@db-172-16-3-150 backend]# ifdown lo

///////////////////////// Primary:
postgres=# select * from pg_stat_replication ; pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | 
sent_location | write_location | flush_location | replay_location | sync_priority | sync_state 
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+-
--------------+----------------+----------------+-----------------+---------------+------------
(0 rows)
postgres=# vacuum VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 3 nonremovable row versions in 1 out of 1 pages
DETAIL:  3 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

///////////////////////// HOST:
[root@db-172-16-3-150 backend]# ifup lo

///////////////////////// Primary:
postgres=# vacuum VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": removed 3 row versions in 1 pages
INFO:  "test": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": truncated 1 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM



///////////////////////// use shutdown immediate can also reflect it:
///////////////////////// Primary:
postgres=# insert into test values (1),(2),(3);
INSERT 0 3

///////////////////////// Standby:
postgres=# begin transaction isolation level repeatable read;
BEGIN
postgres=# select * from test; id 
----  1  2  3
(3 rows)

///////////////////////// Primary:
postgres=# delete from test;
DELETE 3
postgres=# vacuum VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 3 nonremovable row versions in 1 out of 1 pages
DETAIL:  3 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

///////////////////////// shutdown hot_standby:
pg95@db-172-16-3-150-> pg_ctl stop -m immediate -D /data03/pg95_stdby/pg_root
waiting for server to shut down.... done
server stopped

///////////////////////// Primary:
postgres=# select * from pg_stat_replication ;
(0 rows)

postgres=# select * from pg_replication_slots;
-[ RECORD 1 ]+-----------
slot_name    | abc
plugin       | 
slot_type    | physical
datoid       | 
database     | 
active       | f
active_pid   | 
xmin         | 1818
catalog_xmin | 
restart_lsn  | 7/77B0B690

postgres=# vacuum VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 3 nonremovable row versions in 1 out of 1 pages
DETAIL:  3 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

postgres=# select pg_drop_replication_slot('abc');
-[ RECORD 1 ]------------+-
pg_drop_replication_slot | 

postgres=# vacuum VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": removed 3 row versions in 1 pages
INFO:  "test": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": truncated 1 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM