Recovering PostgreSQL from FlashArray Volume Snapshots
This guide can be used to recover from one or more volume snapshots created on FlashArray for a PostgreSQL cluster. Recovery can be done to the original system from where the volume snapshot was taken or to a different system (cloning).
This guide assumes that a storage snapshot of a PostgreSQL cluster has been taken as per Creating Volume Snapshots of PostgreSQL on FlashArray.
The method for recovering a PostgreSQL cluster from volume snapshots on FlashArray will vary under the following scenarios:
- The volume snapshots are created on a system using the default file system layout or the cluster is initialized at a different location.
- The cluster components are located on separate volumes.
- There are multiple tablespaces in the cluster deployed on multiple volumes.
The recovery method for application consistency is the same as crash consistency. The difference between the two approaches is how consistency is guaranteed.
Recovery Process
Step 1. Stop the PostgreSQL Cluster
Microsoft Windows - Run the following command in a PowerShell terminal (the typical service name is postgresql-<architecture>-<version> but could change if being deployed manually):
net stop postgresql-x64-13
Linux - Run the following command in a terminal(the service name in linux is typically postgresql-<version>):
systemctl stop postgresql-13
Step 2. Unmount PostgreSQL cluster Volumes
To find the volumes and volume locations in PostgreSQL the steps provided in Finding PostgreSQL Storage Locations can be used.
Microsoft Windows - Press the Win + R keys to open Run, type diskmgmt.msc into Run, and click/tap on OK to open Disk Management. Right click or press and hold on the drive (ex: "PG_Base") you want to unmount, and click/tap on Change Drive Letter and Paths
Select the mount point and click on the Remove button and then the OK button.
Linux - In a terminal execute the umount command:
umount /var/lib/pgsql
Step 3. Recover the Volume Snapshot
The recommended process when using Creating Volume Snapshots of PostgreSQL on FlashArray is to use Protection Groups to create storage snapshots of PostgreSQL clusters. Recovering volume snapshots will showcase how to recover snapshots from a protection group.
In the FlashArray graphical user interface navigate to the Protection Groups view by selecting Protection and then Protection Groups.
Identify and select the Source Protection Group containing the relevant PostgreSQL cluster to restore.
In the protection group select the Protection Group Snapshot to restore.
Repeat this for each volume in the PostgreSQL cluster. Protection Group Snapshot.
For each volume snapshot in the protection group snapshot select the recover button next to the volume to recover.
Confirm that the volume to recover is correct and select Restore.
Step 4. Mount the PostgreSQL cluster volume(s)
Repeat this for each volume used for the PostgreSQL Cluster.
If the databases are separated using tablespaces on multiple volumes then each tablespace volume must be mounted to the same location as the original database cluster.
Assuming the volume is already connected to the host and has been discovered proceed by mounting the volume.
If a new volume has been created there could be a need to rescan for new devices. To discover new devices do the following:
Microsoft Windows
In Disk Management select Action and then Rescan Disks.
Linux
To scan for new devices use the rescan-scsi-bus.sh script with the -a qualifier.
rescan-scsi-bus.sh
Microsoft Windows - Press the Win + R keys to open Run, type diskmgmt.msc into Run, and click/tap on OK to open Disk Management. Right click or press and hold on the drive (ex: "MySQL_Base") you want to mount, and click/tap on Change Drive Letter and Paths.
In Change Drive Letters and Paths for <Volume> select the Add... button. Choose either a drive letter or an empty NTFS folder to mount to volume to and select OK when done.
The new drive letter or path should be displayed in the dialog. Select OK when complete to mount the volume.
Linux - In a terminal execute the mount command for the device:
mount /dev/mapper/<device> /var/lib/pgsql
If there is an entry in fstab and the original volume was overwritten with a snapshot then the mount command for the directory can be used without the device:
mount /var/lib/pgsql
Step 5. Start the PostgreSQL Cluster
Do not start the cluster if point in time recovery is required. Instead skip to Recovering PostgreSQL from FlashArray Volume Snapshots
Microsoft Windows - Run the following command in a PowerShell terminal (the typical service name is postgresql-<architecture>-<version> but could change if being deployed manually):
net start postgresql-x64-13
Linux - Run the following command in a terminal(the service name in linux is typically postgresql-<version>):
systemctl start postgresql-13
Point In Time Recovery with Continuous Archiving
This recovery process follows the steps provided in the Point In Time Recovery section of Creating Volume Snapshots of PostgreSQL on FlashArray.
To ensure that PostgreSQL recovers the write-ahead-log as apart of the snapshot recovery process ensure the following is in place prior to starting the cluster:
- All of the volumes for the PostgreSQL cluster are mounted at the correct locations in the operating system.
- The location where archived write-ahead logs are located are readable by the system where the PostgreSQL cluster is being recovered to.
- The restore_command entiry in the PostgreSQL configuration file (postgresql.conf) are edited to ensure the files from the write-ahead-log archive can be copied to the PostgreSQL cluster WAL and roll forward the operations which have occurred after the storage snapshot was taken.
-
restore_command = 'cp /postgres/backups/%f "%p"'
-
-
As the PostgreSQL user (In Windows this is the user who owns the PostgreSQL service and in Linux this is postgres) create a recovery.signal file in the data directory. This file informs the cluster that it needs to start in recovery mode.
-
touch /var/lib/pgsql/13/data/recovery.signal
-
-
Remove all WAL files in the pg_wal directory.
-
rm -rf /var/lib/pgsql/13/data/pg_wal/*
-
At this point the PostgreSQL cluster can be started with will roll forward the write-ahead-log from the archive location.
More information on continuous archiving and recovery methods can be found in the PostgreSQL documentation.