Backup strategy using 'wal_keep_segments'

From: Rhhh Lin <ruanlinehan(at)hotmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Backup strategy using 'wal_keep_segments'
Date: 2017-10-22 20:57:46
Message-ID: DB6PR1001MB1141ACC9D4BA85F44399E933AE410@DB6PR1001MB1141.EURPRD10.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Version 9.4...

Per the PG docs, to facilitate continuous WAL archiving and PITR recovery...
"To enable WAL archiving, set the wal_level configuration parameter to archive (or hot_standby), archive_mode to on, and specify the shell command to use in the archive_command configuration parameter."

This instruction is fine and I have a solid understanding of the implementation of these parameters, what they do and the why behind them, i.e. in order to maintain a continuous chain of database changes in the WAL stream.

My question however is this...
A colleague recently suggested that instead of implementing an 'archive_command' to push archivable WALs to a secondary location (for further backup to tape for example), we could instead persist the WAL files in their current location by setting the "wal_keep_segments" parameter to an extreme value e.g. 1000 and have the 'archive_command' do nothing.

So, something like...
wal_keep_segments=1000
archive_command='cd .'

And then periodically copy the archived WAL_files from the pg_xlog directory out to tape, removing as we go?

Is this approach feasible? Assuming obviously, we have sufficient disk space to facilitate 1000 WAL files etc.
But from a point-in-time recovery, and backup perspective - are we missing anything if we were to adopt this non-standard approach?

Regards

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Paquier 2017-10-22 22:17:01 Re: Backup strategy using 'wal_keep_segments'
Previous Message Tom Lane 2017-10-22 20:48:19 Re: parray_gin and \d errors in PG10