Обсуждение: Logical Replication Setup using one replication slot per table?

Поиск
Список
Период
Сортировка

Logical Replication Setup using one replication slot per table?

От
Sbob
Дата:
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?






Re: Logical Replication Setup using one replication slot per table? SOLVED

От
Sbob
Дата:

I found the answer in the Docs:

Each subscription will receive changes via one replication slot (see Section 27.2.6). Additional replication slots may be required for the initial data synchronization of pre-existing table data and those will be dropped at the end of data synchronization.


Which is exactly the behavior I saw




On 3/22/23 17:57, Sbob wrote:
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?