Combining storage snapshots with continuous archiving can provide point-in-time recovery options. This requires WAL archiving to be enabled at the time that the storage snapshot is taken. In the event that a recovery is performed the storage snapshot can be restored and then the WAL archive is used to apply the changes between the storage snapshot and the latest write-ahead log archive location.
Continuous archiving can be sent to a FlashBlade NFS share or a specific, sperate volume on FlashArray. In either scenarios storage snapshots can be taken of the archive location to preserve the recovery point in line with the storage snapshot of the PostgreSQL cluster.
A possible recovery model could be to stagger the creation of a storage snapshot and archive location to achieve better protection options , for example:
1. Create PostgreSQL cluster storage snapshot.
2. Time elapses , changes on primary system occur and then the write-ahead-log is continuously archived.
3. Create a snapshot of the archive location.
4. Time elapses , create a storage snapshot of the PostgreSQL cluster.
....
Repeat 1-4.
This could be useful in scenarios where the PostgreSQL cluster is located on a different FlashArray to the archive location.
To enable WAL archiving edit the postgres configuration file (postgres.conf) with the following:
wal_level = replica
archive_mode = on
In order for continuous archiving to take place there needs to be an archive command that specifies the destination and copy commands.If running PostgreSQL will need to be restarted.
Note that in this example a volume has been mounted to the backup directory, provided with the correct permissions and new archive files are created on the root of the directory.
Linux
archive_command = 'test ! -f /postgres/backups/%f && cp %p /postgres/backups/%f'
Microsoft Windows
archive_command = 'copy "%p" "C:\\PGSQL\\backups\\%f"'
At this point the WAL archive logs will be created and can be used for point-in-time recovery operations.