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 | CAJ4rSwuMDcsvXNfxBefWfDknoJMkdZoDmOJ_8pmo8ut_h_V57g@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>) |
| Список | pgsql-general |
Hi Adrian, Thank you for your response. Please find the requested details below:
PostgreSQL Version:
Source: PostgreSQL 11.15
Target: PostgreSQL 16.9
Operating System:
Source: RHEL 7.9
Target: RHEL 9.6
Network Distance:
Both servers are in the same data center, connected through a high-speed internal network (low latency).
Logical Replication Settings:
Source - Postgres 11.15.
-- ==== WAL & Replication Settings ====
wal_level = 'logical'
max_wal_senders = '30'
max_replication_slots = '20'
wal_keep_segments = '800'
wal_sender_timeout = '300s'
max_worker_processes = '32'
max_logical_replication_workers = '16'
max_sync_workers_per_subscription = '8'
==== WAL & Checkpoint ====
max_wal_size = '40GB'
min_wal_size = '4GB'
checkpoint_timeout = '45min'
checkpoint_completion_target = '0.9'
==== Memory ====
shared_buffers = '18GB'
work_mem = '128MB'
maintenance_work_mem = 4GB'
effective_cache_size = '275GB'
Target DB Postgres 16.10
==== Logical Replication Settings ====
max_worker_processes = '32'
max_logical_replication_workers = '16'
max_sync_workers_per_subscription = '8'
wal_receiver_timeout = '300s'
==== WAL & Checkpoint ====
checkpoint_timeout = '45min'
checkpoint_completion_target = '0.9'
max_wal_size = '40GB'
min_wal_size = '4GB'
==== Memory ====
shared_buffers = '18GB'
work_mem = '128MB'
maintenance_work_mem = '4GB'
effective_cache_size = '275GB'
synchronous_commit = 'off'
PostgreSQL Version:
Source: PostgreSQL 11.15
Target: PostgreSQL 16.9
Operating System:
Source: RHEL 7.9
Target: RHEL 9.6
Network Distance:
Both servers are in the same data center, connected through a high-speed internal network (low latency).
Logical Replication Settings:
Source - Postgres 11.15.
-- ==== WAL & Replication Settings ====
wal_level = 'logical'
max_wal_senders = '30'
max_replication_slots = '20'
wal_keep_segments = '800'
wal_sender_timeout = '300s'
max_worker_processes = '32'
max_logical_replication_workers = '16'
max_sync_workers_per_subscription = '8'
==== WAL & Checkpoint ====
max_wal_size = '40GB'
min_wal_size = '4GB'
checkpoint_timeout = '45min'
checkpoint_completion_target = '0.9'
==== Memory ====
shared_buffers = '18GB'
work_mem = '128MB'
maintenance_work_mem = 4GB'
effective_cache_size = '275GB'
Target DB Postgres 16.10
==== Logical Replication Settings ====
max_worker_processes = '32'
max_logical_replication_workers = '16'
max_sync_workers_per_subscription = '8'
wal_receiver_timeout = '300s'
==== WAL & Checkpoint ====
checkpoint_timeout = '45min'
checkpoint_completion_target = '0.9'
max_wal_size = '40GB'
min_wal_size = '4GB'
==== Memory ====
shared_buffers = '18GB'
work_mem = '128MB'
maintenance_work_mem = '4GB'
effective_cache_size = '275GB'
synchronous_commit = 'off'
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.
Any specific tuning recommendations or best practices to improve performance at this stage would be greatly appreciated.
Thanks & Regards
Krishna.
On Wed, 5 Nov 2025 at 21:07, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/4/25 22:27, Bala M wrote:
> Thank you all for your suggestions,
>
> Thanks for your quick response and for sharing the details.
> After reviewing the options, the logical replication approach seems to
> be the most feasible one with minimal downtime.
>
> However, we currently have 7 streaming replication setups running from
> production, with a total database size of around 15 TB. Out of this,
> there are about 10 large tables ranging from 1 TB (max) to 50 GB (min)
> each, along with approximately 150+ sequences.
>
> Could you please confirm if there are any successful case studies or
> benchmarks available for a similar setup?
Since you have given minimal information in this post, I doubt there is
really a way to compare to other situations. Collect the details you
provided earlier in the thread for those folks getting to it just now.
That would be:
1) Postgres versions on both ends
2) OS and versions on both ends.
3) Network distance between 'machines'.
4) The logical replication settings.
> Additionally, please share any recommended parameter tuning or best
> practices for handling logical replication at this scale.
Since you have already started is that not already to late for this?
>
> Current server configuration:
>
> CPU: 144 cores
>
> RAM: 512 GB
>
>
> Thanks & Regards
> Krishna.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления: