pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with logical replication: pg_replication_slots.active_pid is null????
2+ messages / 2 participants
[nested] [flat]
* Help with logical replication: pg_replication_slots.active_pid is null????
@ 2023-07-17 00:30 Chris Hoover <chrish@aweber.com>
2023-07-18 10:42 ` Re: Help with logical replication: pg_replication_slots.active_pid is null???? hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 2+ messages in thread
From: Chris Hoover @ 2023-07-17 00:30 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help with logical replication: pg_replication_slots.active_pid is null????
2023-07-17 00:30 Help with logical replication: pg_replication_slots.active_pid is null???? Chris Hoover <chrish@aweber.com>
@ 2023-07-18 10:42 ` hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 2+ messages in thread
From: hubert depesz lubaczewski @ 2023-07-18 10:42 UTC (permalink / raw)
To: Chris Hoover <chrish@aweber.com>; +Cc: pgsql-admin@lists.postgresql.org
On Sun, Jul 16, 2023 at 08:30:59PM -0400, Chris Hoover wrote:
> 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.
Please consider reading
https://www.depesz.com/2023/06/04/a-tale-about-incomplete-upgrade-from-postgresql-12-to-14/
Your problem is one of problems I encountered, and the post contains
information how to work around it.
Best regards,
depesz
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-07-18 10:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-17 00:30 Help with logical replication: pg_replication_slots.active_pid is null???? Chris Hoover <chrish@aweber.com>
2023-07-18 10:42 ` hubert depesz lubaczewski <depesz@depesz.com>
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