pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: hubert depesz lubaczewski <depesz@depesz.com>
To: pgsql-bugs mailing list <pgsql-bugs@postgresql.org>
Subject: Logical replication stops dropping used initial-sync replication slots
Date: Thu, 17 Mar 2022 15:24:26 +0100
Message-ID: <20220317142426.GA727@depesz.com> (raw )
Hi,
I have following case:
Primary: Ubuntu Bionic with Pg 12.9, and ~ 60k tables.
Replica: Ubuntu Focal with Pg 14.2.
Settings are:
max_wal_senders │ 50
wal_sender_timeout │ 60000
max_sync_workers_per_subscription │ 10
max_worker_processes │ 50
I made publication using:
create publication focal14;
select format('ALTER PUBLICATION focal14 ADD TABLE ONLY %I.%I;', n.nspname, c.relname) from pg_class c join pg_namespace n on c.relnamespace = n.oid where n.nspname ~ 'shard' and c.relkind in ('p', 'r') order by n.nspname, c.relname \gexec
Worked.
Then on replica I did:
create subscription focal14 connection 'host=${source_ip} port=5432 user=lrep_user dbname=${dbname}' publication focal14;
This started initial sync.
Happily it was working with 10 concurrent workers, but after ~ hour,
I started to get, in logs on replica:
2022-03-17 13:32:48.119 UTC,,,2923553,,62333880.2c9c21,2,,2022-03-17 13:32:48 UTC,8/447946,23877409,ERROR,53400,"could not find free replication state slot for replication origin with OID 51",,"Increase max_replication_slots and try again.",,,,,,,"","logical replication worker",,0
And sure enough, there are MANY replication slots on primary:
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
---------------------------------------------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+--------------+---------------------
focal14 | pgoutput | logical | 16606 | dbname | f | f | | | 160319873 | 8B7/89D938C0 | 8B7/93FD2380
pg_4962101_sync_4663369_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | t | 14139 | | 160322211 | 8BF/4D9EC3F0 | 8BF/5F79B708
pg_4962101_sync_4794836_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160320058 | 8B8/27FE6668 | 8B8/27FE66A0
pg_4962101_sync_4613771_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | t | 14129 | | 160322211 | 8BF/4D9EC3F0 | 8BF/5F79B708
pg_4962101_sync_4681288_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160320058 | 8B8/27FE6630 | 8B8/27FE6668
pg_4962101_sync_4596929_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160320058 | 8B8/27FE65F8 | 8B8/27FE6630
pg_4962101_sync_4394969_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | t | 14131 | | 160322211 | 8BF/4D9EC3F0 | 8BF/5F79B708
pg_4962101_sync_4402927_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | t | 14130 | | 160322211 | 8BF/4D9EC3F0 | 8BF/5EFFDE90
pg_4962101_sync_4458658_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160320058 | 8B8/27FE66A0 | 8B8/27FE66D8
pg_4962101_sync_4374836_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | t | 14132 | | 160322211 | 8BF/4D9EC3F0 | 8BF/5EFFDE90
pg_4962101_sync_4562514_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | t | 14137 | | 160322211 | 8BF/4D9EC3F0 | 8BF/5F79B708
pg_4962101_sync_4588090_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | t | 14138 | | 160322211 | 8BF/4D9EC3F0 | 8BF/5EFFDE90
pg_4962101_sync_4640031_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4188 | 8B7/940D41C0
pg_4962101_sync_4602213_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D41C0 | 8B7/940D41F8
pg_4962101_sync_4530451_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D41F8 | 8B7/940D4230
pg_4962101_sync_4667494_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4230 | 8B7/940D4268
pg_4962101_sync_4452706_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4268 | 8B7/940D42A0
pg_4962101_sync_4553748_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D42A0 | 8B7/940D42D8
pg_4962101_sync_4592679_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D42D8 | 8B7/940D4310
pg_4962101_sync_4600185_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4310 | 8B7/940D4348
pg_4962101_sync_4659756_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4348 | 8B7/940D4380
pg_4962101_sync_4389357_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4380 | 8B7/940D43B8
pg_4962101_sync_4773549_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D43B8 | 8B7/940D43F0
pg_4962101_sync_4784350_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D43F0 | 8B7/940D4428
pg_4962101_sync_4794832_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4428 | 8B7/940D4460
pg_4962101_sync_4678346_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4460 | 8B7/940D4498
pg_4962101_sync_4703039_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4498 | 8B7/940D44D0
pg_4962101_sync_4496803_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D44D0 | 8B7/940D4508
pg_4962101_sync_4612692_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4508 | 8B7/940D4540
pg_4962101_sync_4779832_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4540 | 8B7/940D4578
pg_4962101_sync_4569154_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4578 | 8B7/940D45B0
pg_4962101_sync_4561772_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D45B0 | 8B7/940D45E8
pg_4962101_sync_4452829_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D45E8 | 8B7/940D4620
pg_4962101_sync_4490381_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4620 | 8B7/940D4658
pg_4962101_sync_4585733_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4658 | 8B7/940D4690
pg_4962101_sync_4459745_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | t | 14140 | | 160322211 | 8BF/4D9EC3F0 | 8BF/5F79B708
pg_4962101_sync_4508354_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | t | 14141 | | 160322211 | 8BF/4D9EC3F0 | 8BF/5F79B708
pg_4962101_sync_4451993_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | t | 14142 | | 160322211 | 8BF/4D9EC3F0 | 8BF/5F79B708
pg_4962101_sync_4491715_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4770 | 8B7/940D47A8
pg_4962101_sync_4404697_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D47A8 | 8B7/940D47E0
pg_4962101_sync_4520525_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D47E0 | 8B7/940D4818
pg_4962101_sync_4508992_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4818 | 8B7/940D4850
pg_4962101_sync_4474811_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4850 | 8B7/940D4888
pg_4962101_sync_4514152_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4888 | 8B7/940D48C0
pg_4962101_sync_4379697_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D48C0 | 8B7/940D48F8
pg_4962101_sync_4677962_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D48F8 | 8B7/940D4930
pg_4962101_sync_4554683_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4930 | 8B7/940D4968
pg_4962101_sync_4450272_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D4968 | 8B7/940D49A0
pg_4962101_sync_4717161_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D49A0 | 8B7/940D49D8
pg_4962101_sync_4784928_7073527418723003846 | pgoutput | logical | 16606 | dbname | f | f | | | 160319884 | 8B7/940D49D8 | 8B7/940D4A10
(50 rows)
For some reason there are MANY inactive slots. But why?
I checked in logs for some slot that worked well, and found:
2022-03-17 12:59:58.069 UTC,"lrep_user","dbname",13435,"SOME_IP:50816",623330ce.347b,2,"authentication",2022-03-17 12:59:58 UTC,13/587955,0,LOG,00000,"replication connection authorized: user=lrep_user application_name=pg_4962101_sync_4512642_7073527418723003846 SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off)",,,,,,,,,""
2022-03-17 12:59:58.071 UTC,"lrep_user","dbname",13435,"SOME_IP:50816",623330ce.347b,3,"SELECT",2022-03-17 12:59:58 UTC,13/0,0,LOG,00000,"duration: 0.381 ms statement: SELECT pg_catalog.set_config('search_path', '', false);",,,,,,,,,"pg_4962101_sync_4512642_7073527418723003846"
2022-03-17 12:59:58.072 UTC,"lrep_user","dbname",13435,"SOME_IP:50816",623330ce.347b,4,"BEGIN",2022-03-17 12:59:58 UTC,13/587957,0,LOG,00000,"duration: 0.043 ms statement: BEGIN READ ONLY ISOLATION LEVEL REPEATABLE READ",,,,,,,,,"pg_4962101_sync_4512642_7073527418723003846"
2022-03-17 12:59:58.085 UTC,"lrep_user","dbname",13435,"SOME_IP:50816",623330ce.347b,5,"idle in transaction",2022-03-17 12:59:58 UTC,13/587957,0,LOG,00000,"logical decoding found consistent point at 8AF/6FDC8110","There are no running transactions.",,,,,"CREATE_REPLICATION_SLOT ""pg_4962101_sync_4512642_7073527418723003846"" LOGICAL pgoutput USE_SNAPSHOT",,,"pg_4962101_sync_4512642_7073527418723003846"
2022-03-17 13:00:00.126 UTC,"lrep_user","dbname",13435,"SOME_IP:50816",623330ce.347b,6,"SELECT",2022-03-17 12:59:58 UTC,13/587957,0,LOG,00000,"duration: 1.402 ms statement: SELECT c.oid, c.relreplident, c.relkind FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) WHERE n.nspname = 'aaa_shard_bbb' AND c.relname = 'some_table'",,,,,,,,,"pg_4962101_sync_4512642_7073527418723003846"
2022-03-17 13:00:00.130 UTC,"lrep_user","dbname",13435,"SOME_IP:50816",623330ce.347b,7,"SELECT",2022-03-17 12:59:58 UTC,13/587957,0,LOG,00000,"duration: 3.495 ms statement: SELECT a.attname, a.atttypid, a.attnum = ANY(i.indkey) FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_index i ON (i.indexrelid = pg_get_replica_identity_index(163890290)) WHERE a.attnum > 0::pg_catalog.int2 AND NOT a.attisdropped AND a.attgenerated = '' AND a.attrelid = 163890290 ORDER BY a.attnum",,,,,,,,,"pg_4962101_sync_4512642_7073527418723003846"
2022-03-17 13:00:00.134 UTC,"lrep_user","dbname",13435,"SOME_IP:50816",623330ce.347b,8,"COPY",2022-03-17 12:59:58 UTC,13/587957,0,LOG,00000,"duration: 3.525 ms statement: COPY aaa_shard_bbb.some_table TO STDOUT",,,,,,,,,"pg_4962101_sync_4512642_7073527418723003846"
2022-03-17 13:00:00.135 UTC,"lrep_user","dbname",13435,"SOME_IP:50816",623330ce.347b,9,"COMMIT",2022-03-17 12:59:58 UTC,13/0,0,LOG,00000,"duration: 0.039 ms statement: COMMIT",,,,,,,,,"pg_4962101_sync_4512642_7073527418723003846"
2022-03-17 13:00:01.162 UTC,"lrep_user","dbname",13435,"SOME_IP:50816",623330ce.347b,10,"idle",2022-03-17 12:59:58 UTC,13/0,0,LOG,00000,"starting logical decoding for slot ""pg_4962101_sync_4512642_7073527418723003846""","Streaming transactions committing after 8AF/6FDC8148, reading WAL from 8AF/6FDC8110.",,,,,"START_REPLICATION SLOT ""pg_4962101_sync_4512642_7073527418723003846"" LOGICAL 8AF/6FDC8148 (proto_version '1', publication_names '""focal14""')",,,"pg_4962101_sync_4512642_7073527418723003846"
2022-03-17 13:00:01.162 UTC,"lrep_user","dbname",13435,"SOME_IP:50816",623330ce.347b,11,"idle",2022-03-17 12:59:58 UTC,13/0,0,LOG,00000,"logical decoding found consistent point at 8AF/6FDC8110","There are no running transactions.",,,,,"START_REPLICATION SLOT ""pg_4962101_sync_4512642_7073527418723003846"" LOGICAL 8AF/6FDC8148 (proto_version '1', publication_names '""focal14""')",,,"pg_4962101_sync_4512642_7073527418723003846"
Not sure when this slot got removed, but it's no longer there.
But, a slot that currently exists and is inactive:
2022-03-17 13:32:48.401 UTC,"lrep_user","dbname",28146,"SOME_IP:37178",62333880.6df2,2,"authentication",2022-03-17 13:32:48 UTC,11/150184,0,LOG,00000,"replication connection authorized: user=lrep_user application_name=pg_4962101_sync_4474811_7073527418723003846 SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off)",,,,,,,,,""
2022-03-17 13:32:48.403 UTC,"lrep_user","dbname",28146,"SOME_IP:37178",62333880.6df2,3,"SELECT",2022-03-17 13:32:48 UTC,11/0,0,LOG,00000,"duration: 0.408 ms statement: SELECT pg_catalog.set_config('search_path', '', false);",,,,,,,,,"pg_4962101_sync_4474811_7073527418723003846"
2022-03-17 13:32:48.405 UTC,"lrep_user","dbname",28146,"SOME_IP:37178",62333880.6df2,4,"BEGIN",2022-03-17 13:32:48 UTC,11/150186,0,LOG,00000,"duration: 0.061 ms statement: BEGIN READ ONLY ISOLATION LEVEL REPEATABLE READ",,,,,,,,,"pg_4962101_sync_4474811_7073527418723003846"
2022-03-17 13:32:48.419 UTC,"lrep_user","dbname",28146,"SOME_IP:37178",62333880.6df2,5,"idle in transaction",2022-03-17 13:32:48 UTC,11/150186,0,LOG,00000,"logical decoding found consistent point at 8B7/940D4850","There are no running transactions.",,,,,"CREATE_REPLICATION_SLOT ""pg_4962101_sync_4474811_7073527418723003846"" LOGICAL pgoutput USE_SNAPSHOT",,,"pg_4962101_sync_4474811_7073527418723003846"
and some that is currently active:
2022-03-17 13:32:48.349 UTC,"lrep_user","dbname",28138,"10.2.210.180:37160",62333880.6dea,2,"authentication",2022-03-17 13:32:48 UTC,11/150181,0,LOG,00000,"replication connection authorized: user=lrep_user application_name=pg_4962101_sync_4663369_7073527418723003846 SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off)",,,,,,,,,""
2022-03-17 13:32:48.354 UTC,"lrep_user","dbname",28138,"10.2.210.180:37160",62333880.6dea,3,"SELECT",2022-03-17 13:32:48 UTC,11/0,0,LOG,00000,"duration: 0.397 ms statement: SELECT pg_catalog.set_config('search_path', '', false);",,,,,,,,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:32:48.356 UTC,"lrep_user","dbname",28138,"10.2.210.180:37160",62333880.6dea,4,"BEGIN",2022-03-17 13:32:48 UTC,11/150183,0,LOG,00000,"duration: 0.049 ms statement: BEGIN READ ONLY ISOLATION LEVEL REPEATABLE READ",,,,,,,,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:32:48.381 UTC,"lrep_user","dbname",28138,"10.2.210.180:37160",62333880.6dea,5,"idle in transaction",2022-03-17 13:32:48 UTC,11/150183,0,LOG,00000,"logical decoding found consistent point at 8B7/940D4690","There are no running transactions.",,,,,"CREATE_REPLICATION_SLOT ""pg_4962101_sync_4663369_7073527418723003846"" LOGICAL pgoutput USE_SNAPSHOT",,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:11.788 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,2,"authentication",2022-03-17 13:35:11 UTC,11/150814,0,LOG,00000,"replication connection authorized: user=lrep_user application_name=pg_4962101_sync_4663369_7073527418723003846 SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off)",,,,,,,,,""
2022-03-17 13:35:11.790 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,3,"SELECT",2022-03-17 13:35:11 UTC,11/0,0,LOG,00000,"duration: 0.414 ms statement: SELECT pg_catalog.set_config('search_path', '', false);",,,,,,,,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:11.793 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,4,"BEGIN",2022-03-17 13:35:11 UTC,11/150816,0,LOG,00000,"duration: 0.058 ms statement: BEGIN READ ONLY ISOLATION LEVEL REPEATABLE READ",,,,,,,,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:11.805 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,5,"idle in transaction",2022-03-17 13:35:11 UTC,11/150816,0,LOG,00000,"logical decoding found initial starting point at 8B8/2D0B3DB0","Waiting for transactions (approximately 1) older than 160320063 to end.",,,,,"CREATE_REPLICATION_SLOT ""pg_4962101_sync_4663369_7073527418723003846"" LOGICAL pgoutput USE_SNAPSHOT",,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:11.915 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,6,"idle in transaction",2022-03-17 13:35:11 UTC,11/150816,0,LOG,00000,"logical decoding found initial consistent point at 8B8/2D1859D0","Waiting for transactions (approximately 1) older than 160320064 to end.",,,,,"CREATE_REPLICATION_SLOT ""pg_4962101_sync_4663369_7073527418723003846"" LOGICAL pgoutput USE_SNAPSHOT",,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:12.035 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,7,"idle in transaction",2022-03-17 13:35:11 UTC,11/150816,0,LOG,00000,"logical decoding found consistent point at 8B8/2D1874C0","There are no running transactions.",,,,,"CREATE_REPLICATION_SLOT ""pg_4962101_sync_4663369_7073527418723003846"" LOGICAL pgoutput USE_SNAPSHOT",,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:12.050 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,8,"SELECT",2022-03-17 13:35:11 UTC,11/150816,0,LOG,00000,"duration: 1.056 ms statement: SELECT c.oid, c.relreplident, c.relkind FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) WHERE n.nspname = 'aaa_shard_ccc' AND c.relname = 'OTHER_TABLE'",,,,,,,,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:12.055 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,9,"SELECT",2022-03-17 13:35:11 UTC,11/150816,0,LOG,00000,"duration: 3.441 ms statement: SELECT a.attname, a.atttypid, a.attnum = ANY(i.indkey) FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_index i ON (i.indexrelid = pg_get_replica_identity_index(308468275)) WHERE a.attnum > 0::pg_catalog.int2 AND NOT a.attisdropped AND a.attgenerated = '' AND a.attrelid = 308468275 ORDER BY a.attnum",,,,,,,,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:12.063 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,10,"COPY",2022-03-17 13:35:11 UTC,11/150816,0,LOG,00000,"duration: 7.618 ms statement: COPY aaa_shard_ccc.OTHER_TABLE TO STDOUT",,,,,,,,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:12.069 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,11,"COMMIT",2022-03-17 13:35:11 UTC,11/0,0,LOG,00000,"duration: 0.051 ms statement: COMMIT",,,,,,,,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:12.069 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,12,"idle",2022-03-17 13:35:11 UTC,11/0,0,LOG,00000,"starting logical decoding for slot ""pg_4962101_sync_4663369_7073527418723003846""","Streaming transactions committing after 8B8/2D1874F8, reading WAL from 8B8/2D0AB818.",,,,,"START_REPLICATION SLOT ""pg_4962101_sync_4663369_7073527418723003846"" LOGICAL 8B8/2D1874F8 (proto_version '1', publication_names '""focal14""')",,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:12.069 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,13,"idle",2022-03-17 13:35:11 UTC,11/0,0,LOG,00000,"logical decoding found initial starting point at 8B8/2D0B3DB0","Waiting for transactions (approximately 1) older than 160320063 to end.",,,,,"START_REPLICATION SLOT ""pg_4962101_sync_4663369_7073527418723003846"" LOGICAL 8B8/2D1874F8 (proto_version '1', publication_names '""focal14""')",,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:12.083 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,14,"idle",2022-03-17 13:35:11 UTC,11/0,0,LOG,00000,"logical decoding found initial consistent point at 8B8/2D1859D0","Waiting for transactions (approximately 1) older than 160320064 to end.",,,,,"START_REPLICATION SLOT ""pg_4962101_sync_4663369_7073527418723003846"" LOGICAL 8B8/2D1874F8 (proto_version '1', publication_names '""focal14""')",,,"pg_4962101_sync_4663369_7073527418723003846"
2022-03-17 13:35:12.090 UTC,"lrep_user","dbname",14139,"10.2.210.180:43592",6233390f.373b,15,"idle",2022-03-17 13:35:11 UTC,11/0,0,LOG,00000,"logical decoding found consistent point at 8B8/2D1874C0","There are no running transactions.",,,,,"START_REPLICATION SLOT ""pg_4962101_sync_4663369_7073527418723003846"" LOGICAL 8B8/2D1874F8 (proto_version '1', publication_names '""focal14""')",,,"pg_4962101_sync_4663369_7073527418723003846"
Looks that, for some reason, it doesn't get data on next table to copy
data from, and is kinda stuck in process...
What could it be? How can I fix that?
We need to use this (logical replication) as we want to upgrade OS, and
there is locale problem, which prohibits usage of physical replication.
Best regards,
depesz
view thread (12+ messages) latest in thread
Message-ID: <20220317142426.GA727@depesz.com>
Permalink: ../20220317142426.GA727@depesz.com/
Also on: postgresql.org/message-id/20220317142426.GA727@depesz.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: depesz@depesz.com
Subject: Re: Logical replication stops dropping used initial-sync replication slots
In-Reply-To: <20220317142426.GA727@depesz.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox