From: | Sbob <sbob(at)quadratum-braccas(dot)com> |
---|---|
To: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Logical Replication Setup using one replication slot per table? |
Date: | 2023-03-22 23:57:16 |
Message-ID: | 24800418-6f52-067a-9adc-cd89fccda8cc@quadratum-braccas.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi all;
I setup logical replication by specifying 402 specific tables like shown
below:
On the Publisher:
CREATE PUBLICATION testpub FOR TABLE
my_test_schema.core_tab_00,
my_test_schema.core_tab_01,
-- and more tables
my_test_schema.core_tab_401,
my_test_schema.core_tab_402
;
On the Subscriber:
CREATE SUBSCRIPTION testsub
connection 'host=10.10.10.7 dbname=my_test_db port=5432' PUBLICATION
testpub;
Once I started replication I was seeing errors in the log on the
subscriber about not enough replication slots, it seems the db wants to
create a replication slot for each table, I grabbed all the rows with
"could not create replication slot" and parsed the set to a unique list
and came up with 385 rows like these:
ERROR: could not create replication slot
"pg_92884_sync_85377_7213515424408371294": ERROR: all replication slots
are in use
As I continue checking the number of unique "could not create
replication slot" entries in the log is growing
I have max_replication_slots on both servers set to 10
If i run this query on the publisher I see 10 rows:
# select * from pg_stat_replication_slots ;
slot_name | spill_txns | spill_count |
spill_bytes | stream_txns | stream_count | stream_bytes | total_txns |
total_bytes | stats_reset
-----------------------------------------+------------+-------------+-------------+-------------+--------------+--------------+------------+-------------+-------------
testsub2 | 0 | 0
| 0 | 0 | 0 | 0 | 0
| 0 |
pg_92884_sync_81493_7213515424408371294 | 0 | 0
| 0 | 0 | 0 | 0 | 0
| 0 |
pg_92884_sync_71976_7213515424408371294 | 0 | 0
| 0 | 0 | 0 | 0 | 0
| 0 |
pg_92884_sync_72306_7213515424408371294 | 0 | 0
| 0 | 0 | 0 | 0 | 0
| 0 |
pg_92884_sync_71760_7213515424408371294 | 0 | 0
| 0 | 0 | 0 | 0 | 0
| 0 |
pg_92884_sync_74101_7213515424408371294 | 0 | 0
| 0 | 0 | 0 | 0 | 0
| 0 |
pg_92884_sync_74661_7213515424408371294 | 0 | 0
| 0 | 0 | 0 | 0 | 0
| 0 |
pg_92884_sync_71286_7213515424408371294 | 0 | 0
| 0 | 0 | 0 | 0 | 0
| 0 |
pg_92884_sync_82949_7213515424408371294 | 0 | 0
| 0 | 0 | 0 | 0 | 0
| 0 |
pg_92884_sync_81829_7213515424408371294 | 0 | 0
| 0 | 0 | 0 | 0 | 0
| 0 |
(10 rows)
It seems that PostgreSQL wants to create a replication slot for each
table, I did a test on a separate set of VMs before this with 50 tables
and it only used one replication slot, what is going on? Am I doing
something wrong?
From | Date | Subject | |
---|---|---|---|
Next Message | Sbob | 2023-03-23 00:10:08 | Re: Logical Replication Setup using one replication slot per table? SOLVED |
Previous Message | Keith | 2023-03-21 13:58:08 | Re: PostgreSQL equivalents to dbms_metadata.get_ddl & dbms_metadata.get_dependent_ddl |