Query on WAL Optimization and Streaming Replication

From: "Shukla, Pranjal" <pshukla(at)akamai(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Query on WAL Optimization and Streaming Replication
Date: 2022-03-17 12:36:26
Message-ID: 69E6DB22-F46D-4798-B47E-A69C5B52FA56@akamai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

During upgrades of our application, we generally shutdown all Secondary servers which are getting stream replicated from Primary Servers. This is to maintain a copy of database on other servers should we wish to revert (of course we take DB Backups too before starting the activity). After the application upgrade is done, when we start the secondary, often the replication is broken, and we need to again setup using pg_basebackup. How do we ensure that secondary is able to resume the replication without the need of base back up again?

Below are some of the WAL related settings:

shared_buffers = 48GB

wal_level = replica

max_prepared_transactions = 200

max_wal_senders = 5

wal_keep_segments = 32

hot_standby = ON

effective_cache_size = 144GB

work_mem = 1GB

maintenance_work_mem = 2GB

wal_buffers = 16MB

min_wal_size = 1GB

max_wal_size = 2GB

Thanks & Regards
Pranjal Shukla

Responses

Browse pgsql-general by date

  From Date Subject
Next Message GoLang Developere 2022-03-17 12:58:50 High Postgres postmaster CPU when My Background Worker is loaded.
Previous Message Josef Šimánek 2022-03-17 11:32:16 Re: PSQL bug?