Changing wal segment size on existing database cluster

From: James Lucas <jlucasdba(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Changing wal segment size on existing database cluster
Date: 2020-05-27 18:12:24
Message-ID: CAAFmbbPKkNYwf=kKBR4H3qGW4OJSJFcqkLEE5K2dLCLKZANafA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I have a high traffic database, where I'm interested in changing the
wal segment size to a larger value. I haven't found much
documentation about how to change the segment size of an existing
database. The obvious, safe solution would be to create a new
database cluster and dump/reload. This isn't ideal for a large
database though.

Pg_resetwal has a wal-segsize option, but the documentation doesn't
provide much guidance beyond that it's there. The pg_resetwal manpage
also has big warnings all over it about how the tool can corrupt your
database cluster. So my question is, is it safe to change wal-segsize
using pg_resetwal following a clean shutdown of the database? Just
reading the docs, it seems like the corruption issues are more around
non-graceful shutdowns or crash scenarios, with incomplete
transactions being wiped out by a wal reset. If the database was
shutdown cleanly this doesn't *seem* like it would be an issue.

Has anyone had experience doing this? I assume this would break any
physical replication standbys. Any other gotchas I should be looking
out for?

I've tested on a trivial (empty) database cluster, and everything
seems okay. But corruption might be difficult to detect until it's
too late.

Thanks,
James Lucas

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2020-05-27 19:08:28 Re: Can't remove default permissions entry
Previous Message zurich 2020-05-27 17:59:49 Re: Suggestion to Monitoring Tool