In Postgres, I want to stop the replication process on the primary. Secondary database is no more working and primary wal folder is growing in size. Can someone help me with what are the settings I should do in the postgresql.conf so that primary stop creating wal files for replication? Below are the current settings and doubts.

wal_level = logical -> Can I change to minimal? any impact?

max_wal_senders = 3 -> can I set it to 0? will it stop the whole replication work on primary

wal_keep_size = 400 -> can I reduce to 40 (MB)? Will it reduce the Wal folder size?)

max_wal_size = 1GB -> Is this the size for each replication? (or it is independant of replications)

min_wal_size = 80MB -> what is implication of this minimum value? Wal is written only if the size is 80 MB? What will be the impact if I reduce to 8 MB

Any other setting that will stop primary consuming resources for replication purpose?

And finally, what happens to current WAL files when I change these settings? Will it reduce size or just stop increasing further?

Any pointers to a suitable article/documentation will also help. Thank you

In Postgres, I want to stop the replication process on the primary. Secondary database is no more working and primary wal folder is growing in size. Can someone help me with what are the settings I should do in the postgresql.conf so that primary stop creating wal files for replication? Below are the current settings and doubts.

wal_level = logical -> Can I change to minimal? any impact?

max_wal_senders = 3 -> can I set it to 0? will it stop the whole replication work on primary

wal_keep_size = 400 -> can I reduce to 40 (MB)? Will it reduce the Wal folder size?)

max_wal_size = 1GB -> Is this the size for each replication? (or it is independant of replications)

min_wal_size = 80MB -> what is implication of this minimum value? Wal is written only if the size is 80 MB? What will be the impact if I reduce to 8 MB

Any other setting that will stop primary consuming resources for replication purpose?

And finally, what happens to current WAL files when I change these settings? Will it reduce size or just stop increasing further?

Any pointers to a suitable article/documentation will also help. Thank you

• postgresql
• database-replication

2 Answers 2

To stop replication when the standby (the subscriber) is not available, you have to drop the replication slot that belongs to the subscription:

SELECT pg_drop_replication_slot('subscription_name');

By default, the name of the replication slot is the same as the name of the subscription, but you might have chosen a different name (look at the view pg_replication_slots).

Once the replication slot is gone, the publisher “fets” the subscriber an will no longer retain the WAL for that subscriber.

From the PostgreSQL documentation, you can set the values, wal_level to minimal, archive_mode to off (if you have wal archiving enabled), and max_wal_senders to zero. The wal size settings may not have much importance when you make the wal_level as minimal (of course you can reduce it).

14.4.7. Disable WAL Archival and Streaming Replication

"When loading large amounts of data into an installation that uses WAL archiving or streaming replication, it might be faster to take a new base backup after the load has completed than to process a large amount of incremental WAL data. To prevent incremental WAL logging while loading, disable archiving and streaming replication, by setting wal_level to minimal, archive_mode to off, and max_wal_senders to zero. But note that changing these settings requires a server restart, and makes any base backups taken before unavailable for archive recovery and standby server, which may lead to data loss.

If the soul purpose is to reduce the resource usage for your primary then you should look for your sql statements and queries and fine turn them. WAL is 'write ahead log' for mainly for PITR or replication. Reducing wal_level is not recommended for production environments.

You may consider,if you think archiving WAL files will help for space related constraints as well:-> https://www.postgresql./docs/current/continuous-archiving.html#BACKUP-ARCHIVING-WAL