Request a Demo
Home   >   mailing lists

Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication) - Mailing list pgsql-general

From Bala M
Subject Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)
Date
Msg-id CAJ4rSwvRHM=oKb_Ri-PpK=4fS_ciZFCoMSgF1P=OdJv2aU5XJg@mail.gmail.com
Whole thread Raw
In response to Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)
List pgsql-general
Tree view

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.

 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?

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 by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)
Next
From: Ashish Mukherjee
Date:
Subject: Enquiry about long-running queries