Skip to main content
Pure Technical Services

Recovering PostgreSQL from FlashArray Volume Snapshots

Currently viewing public documentation. Please login to access the full scope of documentation.

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 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

clipboard_e13832335191289ba96e1c1fe4aab0e40.png

Select the mount point and click on the Remove button and then the OK button.

clipboard_e9d9775a27caaefed4da263f9a69bfdb0.png

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. 

clipboard_e0106bc29b212e8c91f5f9812dbe96d12.png

In the protection group select the Protection Group Snapshot to restore.

clipboard_e2387265cf717286953c144bcf5234636.png

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. 

clipboard_e5f1e401400b51f365e6e8cd5ddbb6514.png

Confirm that the volume to recover is correct and select Restore

clipboard_ed3c71e5a373232183b31b4c72c07fdb2.png

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

clipboard_eff8e51123a8a87b70dde2f9720523b0a.png

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. 

clipboard_e5a8b994a73606bdfbee69222a390ee2f.png

The new drive letter or path should be displayed in the dialog. Select OK when complete to mount the volume. 

clipboard_e27c836cbdce2ea294c454adf442b14ff.png

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:

  1.  All of the volumes for the PostgreSQL cluster are mounted at the correct locations in the operating system. 
  2. The location where archived write-ahead logs are located are readable by the system where the PostgreSQL cluster is being recovered to. 
  3.  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.
    1. restore_command = 'cp /postgres/backups/%f "%p"' 
      
  4. 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. 

    1. touch /var/lib/pgsql/13/data/recovery.signal
      
  5. Remove all WAL files in the pg_wal directory.

    1. 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