Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)
| От | Bala M |
|---|---|
| Тема | Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication) |
| Дата | |
| Msg-id | CAJ4rSwvRHM=oKb_Ri-PpK=4fS_ciZFCoMSgF1P=OdJv2aU5XJg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication) (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Ответы |
Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)
|
| Список | pgsql-general |
1) Show how you have set up the publishers and subscribers.
We have configured logical replication between PostgreSQL 11 (publisher) and PostgreSQL 16 (subscriber).
Publisher: PostgreSQL 11 with wal_level=logical, max_replication_slots=20, and publication created for all tables (FOR ALL TABLES).
Subscriber: PostgreSQL 16 with max_logical_replication_workers=16, max_sync_workers_per_subscription=8, connected through a subscription to replicate all tables.
Publisher: PostgreSQL 11 with wal_level=logical, max_replication_slots=20, and publication created for all tables (FOR ALL TABLES).
Subscriber: PostgreSQL 16 with max_logical_replication_workers=16, max_sync_workers_per_subscription=8, connected through a subscription to replicate all tables.
select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
-----------+----------+--------------+-----------+-----------+-----------+-------------
test_v_pub | 10 | t | t | t | t | t
(1 row)
select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-------------------------------------------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+----------------+---------------------
vahan_sub | pgoutput | logical | 16468 | test_v | f | t | 3722767 | | 3146687395 | 20538/7D039948 | 20538/7D045790
pg_120870_sync_94301_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/71C4FD28 | 20538/71C4FD60
pg_120870_sync_93499_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7D039948 | 20538/7D039980
pg_120870_sync_99121_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/6F290C88 | 20538/6F290CC0
pg_120870_sync_108854_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7C6B5FD0 | 20538/7C6B6020
pg_120870_sync_93451_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7D0359A8 | 20538/7D0359E0
pg_120870_sync_94589_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/4C0001E8 | 20538/4C000220
pg_120870_sync_98225_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/6F290DA0 | 20538/6F290DD8
pg_120870_sync_96438_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/75CE4120 | 20538/75CE4158
pg_120870_sync_93469_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7D00E0B0 | 20538/7D00E0E8
pg_120870_sync_94277_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/4C000178 | 20538/4C0001B0
pg_120870_sync_109085_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7B72CFE8 | 20538/7B72D020
pg_120870_sync_109110_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7AE3B078 | 20538/7AE3B0B0
pg_120870_sync_99247_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/4C0001B0 | 20538/4C0001E8
pg_120870_sync_103454_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7D0002F0 | 20538/7D000328
pg_120870_sync_96317_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/71C59D20 | 20538/71C59D58
pg_120870_sync_107790_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7A4102F0 | 20538/7A410328
pg_120870_sync_98730_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/6F00CE40 | 20538/6F00CE78
pg_120870_sync_93488_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7CF2C4F0 | 20538/7CF2C528
pg_120870_sync_103457_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/6F127188 | 20538/6F1271C0
(20 rows)
postgres=# select * from pg_subscription;
-[ RECORD 1 ]-------+---------------------------------------------------------------------
oid | 120870
subdbid | 91683
subskiplsn | 0/0
subname | test_v_sub
subowner | 10
subenabled | t
subbinary | f
substream | f
subtwophasestate | d
subdisableonerr | f
subpasswordrequired | t
subrunasowner | f
subconninfo | host=xyz port=5432 user=postgres password=test dbname=test_v
subslotname | test_v_sub
subsynccommit | off
subpublications | {test_v_pub}
suborigin | any
2) Are you trying to replicate everything at once or a table or subset
of tables at a time?
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
-----------+----------+--------------+-----------+-----------+-----------+-------------
test_v_pub | 10 | t | t | t | t | t
(1 row)
select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-------------------------------------------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+----------------+---------------------
vahan_sub | pgoutput | logical | 16468 | test_v | f | t | 3722767 | | 3146687395 | 20538/7D039948 | 20538/7D045790
pg_120870_sync_94301_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/71C4FD28 | 20538/71C4FD60
pg_120870_sync_93499_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7D039948 | 20538/7D039980
pg_120870_sync_99121_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/6F290C88 | 20538/6F290CC0
pg_120870_sync_108854_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7C6B5FD0 | 20538/7C6B6020
pg_120870_sync_93451_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7D0359A8 | 20538/7D0359E0
pg_120870_sync_94589_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/4C0001E8 | 20538/4C000220
pg_120870_sync_98225_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/6F290DA0 | 20538/6F290DD8
pg_120870_sync_96438_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/75CE4120 | 20538/75CE4158
pg_120870_sync_93469_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7D00E0B0 | 20538/7D00E0E8
pg_120870_sync_94277_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/4C000178 | 20538/4C0001B0
pg_120870_sync_109085_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7B72CFE8 | 20538/7B72D020
pg_120870_sync_109110_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7AE3B078 | 20538/7AE3B0B0
pg_120870_sync_99247_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/4C0001B0 | 20538/4C0001E8
pg_120870_sync_103454_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7D0002F0 | 20538/7D000328
pg_120870_sync_96317_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/71C59D20 | 20538/71C59D58
pg_120870_sync_107790_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7A4102F0 | 20538/7A410328
pg_120870_sync_98730_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/6F00CE40 | 20538/6F00CE78
pg_120870_sync_93488_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/7CF2C4F0 | 20538/7CF2C528
pg_120870_sync_103457_7567666078127625550 | pgoutput | logical | 16468 | test_v | f | f | | | 3146687395 | 20538/6F127188 | 20538/6F1271C0
(20 rows)
postgres=# select * from pg_subscription;
-[ RECORD 1 ]-------+---------------------------------------------------------------------
oid | 120870
subdbid | 91683
subskiplsn | 0/0
subname | test_v_sub
subowner | 10
subenabled | t
subbinary | f
substream | f
subtwophasestate | d
subdisableonerr | f
subpasswordrequired | t
subrunasowner | f
subconninfo | host=xyz port=5432 user=postgres password=test dbname=test_v
subslotname | test_v_sub
subsynccommit | off
subpublications | {test_v_pub}
suborigin | any
2) Are you trying to replicate everything at once or a table or subset
of tables at a time?
We are replicating all tables at once (full database-level replication), not table-by-table.
3) How active is the source database?
The source database is highly active, with continuous transactions and heavy write activity across large tables.
Thanks & Regards
Krishna.
On Thu, 6 Nov 2025 at 23:53, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/6/25 09:04, Bala M wrote:
>
> Hi Adrian, Thank you for your response. Please find the requested
> details below:
>
> Since you have already started is that not already to late for this?
>
> Yes We are currently in the *testing phase* and validating with the
> above parameters. However, the replication process has been *extremely
> slow — it’s been running for the past 5 days* with limited progress.
1) Show how you have set up the publishers and subscribers.
2) Are you trying to replicate everything at once or a table or subset
of tables at a time?
3) How active is the source database?
>
> Any specific tuning recommendations or best practices to improve
> performance at this stage would be greatly appreciated.
>
>
> Thanks & Regards
> Krishna.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления: