From: Chris Hoover <chrish@aweber.com>
To: pgsql-admin@lists.postgresql.org
Subject: Help with logical replication: pg_replication_slots.active_pid is null????
Date: Sun, 16 Jul 2023 20:30:59 -0400
Message-ID: <3DF8573D-9E47-4770-94A1-C0B873BD1D7C@aweber.com> (raw)
Hey Everyone,
Need some help with logical replication. I’m trying to move my mail 2.5TB database to a new postgres server and upgrade from 14-15 at the same time. I’m running into some strange issues.
1. my pg_replication_slots are filling up with entries that have no active pid. I can’t figure out what is going on here.
```
15> select * from pg_replication_slots
pg_67341299_sync_92244_7249511065961178394 │ pgoutput │ logical │ 16387 │ xxx │ f │ f │ (null) │ (null) │ 698512637 │ 1270/84298C10 │ 1270/843169A8 │ reserved │ (null) │ f
pg_67341299_sync_89883_7249511065961178394 │ pgoutput │ logical │ 16387 │ xxx │ f │ f │ (null) │ (null) │ 698171095 │ 126E/20D13A50 │ 126E/20D13A88 │ extended │ (null) │ f
pg_67341299_sync_90126_7249511065961178394 │ pgoutput │ logical │ 16387 │ xxx │ f │ f │ (null) │ (null) │ 698167442 │ 126E/1F66FFC8 │ 126E/1F670000 │ extended │ (null) │ f
pg_67341299_sync_92637_7249511065961178394 │ pgoutput │ logical │ 16387 │ xxx │ f │ f │ (null) │ (null) │ 698153629 │ 126E/1A732E08 │ 126E/1A732EE0 │ extended │ (null) │ f
pg_67341299_sync_89526_7249511065961178394 │ pgoutput │ logical │ 16387 │ xxx │ f │ f │ (null) │ (null) │ 698041660 │ 126D/E5A0E4C0 │ 126D/E5A10CC8 │ extended │ (null) │ f
pg_67341299_sync_89604_7249511065961178394 │ pgoutput │ logical │ 16387 │ xxx │ f │ f │ (null) │ (null) │ 698177047 │ 126E/231CBD58 │ 126E/231CBE30 │ extended │ (null) │ f
pg_67341299_sync_92447_7249511065961178394 │ pgoutput │ logical │ 16387 │ xxx │ f │ f │ (null) │ (null) │ 698233353 │ 126E/53D730B0 │ 126E/53D730E8 │ extended │ (null) │ f
pg_67341299_sync_93707_7249511065961178394 │ pgoutput │ logical │ 16387 │ xxx │ f │ f │ (null) │ (null) │ 698171095 │ 126E/20D1C608 │ 126E/20D1ECB0 │ extended │ (null) │ f
```
2. I’m getting way more tables marked as copying than should be allowed. Says I have 619 tables copying. However, I’m limited to 10 replication slots on the master and have max_sync_workers_per_subscription set to 8. That should mean at any given time I have 8 tables copying during the initial data load. (Other 2 slots are for the main logical replication slot and our patroni replication). I don’t understand what is happening. I would love to bump my replication slots on the current primary, but that requires an outage.
```
15> BEGIN;
select current_timestamp,
case srsubstate
when 'i' then 'Initialized'
when 'd' then 'Data Copying'
when 'f' then 'Finished'
when 's' then 'Synchronized'
when 'r' then 'Ready'
end as table_state,
count(*)
from pg_subscription_rel group by srsubstate order by srsubstate;
select pg_size_pretty(pg_database_size(current_database()));
rollback;
BEGIN
current_timestamp | table_state | count
-------------------------------+--------------+-------
2023-07-16 20:23:13.780121-04 | Data Copying | 619
2023-07-16 20:23:13.780121-04 | Initialized | 721
2023-07-16 20:23:13.780121-04 | Ready | 152
2023-07-16 20:23:13.780121-04 | Synchronized | 19
(4 rows)
```
Any advice would be welcome. I need to get this database replicated and ready to failover as soon as possible. Initially we are scheduled for this Thursday (7/20) for the failover, but of course replication has to be current before then.
Thanks,
Chris Hoover
Senior DBA
AWeber.com
Cell: (803) 528-2269
Email: chrish@aweber.com
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-admin@postgresql.org
Cc: chrish@aweber.com, pgsql-admin@lists.postgresql.org
Subject: Re: Help with logical replication: pg_replication_slots.active_pid is null????
In-Reply-To: <3DF8573D-9E47-4770-94A1-C0B873BD1D7C@aweber.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