Skip to main content
Pure Technical Services

Creating Volume Snapshots of PostgreSQL on FlashArray

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

KP_Ext_Announcement.png

FlashArray volume snapshots are a very fast way in which a consistent recovery point can be created for PostgreSQL clusters. it is possible for a PostgreSQL cluster to be deployed with a number of different volume and file system architectural layouts which will affect some aspects of the storage snapshot creation process. 

Regardless of volume and file system architectural layout there are two methods of creating a PostgreSQL recovery point using storage snapshots:

  • Application Consistent  - An application consistent volume snapshot is typically performed by coordinating with the application and operating system to ensure everything is in a state that recovery is assured and possible to a point in time. 
  • Crash Consistent - A crash consistent volume snapshot ignores the presence or possibility of an application. No effort is made to ensure that the application is in a specific state and recovery to a point in time cannot be guaranteed. 

It is not possible to backup isolated tables, databases or table spaces. In order for recovery to be possible the storage snapshot(s) must include all storage volumes in the PostgreSQL cluster.  

Matching Storage Locations to FlashArray Volumes  

Finding PostgreSQL Storage Locations 

The two storage locations which matter the most when creating a PostgreSQL cluster storage snapshot are the data directory and any tablespaces associated with the cluster. A default layout will have only a single storage location while a layout separating database storage locations using tablespaces could have multiple other volumes to be considered. 

Data Directory 

PostgreSQL keeps track of the physical location of its data directory in the configuration parameters. To retrieve the data directory location use the show command:

show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/13/data
(1 row)

Tablespaces 

To identify any tablespaces apart of the cluster query the pg_tablespace view for the relevant information:

SELECT *, pg_tablespace_location(oid) FROM pg_tablespace;

A PostgreSQL cluster with the default tablespace will not show any values other than pg_default or pg_global.

postgres=# SELECT *, pg_tablespace_location(oid) FROM pg_tablespace;
 oid  |  spcname   | spcowner | spcacl | spcoptions | pg_tablespace_location
------+------------+----------+--------+------------+------------------------
 1663 | pg_default |       10 |        |            |
 1664 | pg_global  |       10 |        |            |
(2 rows)

If there are additional tablespaces present then the output should show them and their location in the pg_tablespace_location column:

  oid   |     spcname     | spcowner | spcacl | spcoptions | pg_tablespace_location
--------+-----------------+----------+--------+------------+------------------------
   1663 | pg_default      |       10 |        |            |
   1664 | pg_global       |       10 |        |            |
 780355 | database02_tbps |       10 |        |            | /postgres/database02
 780356 | database03_tbps |       10 |        |            | /postgres/database03
 780357 | database04_tbps |       10 |        |            | /postgres/database04
 780358 | database01_tbps |       10 |        |            | /postgres/database01

If required the tablespace to database mapping can be seen when using the output of /l+ in psql:

postgres=# \l+
                                                                       List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   |   Tablespace    |                Description

------------+----------+----------+-------------+-------------+-----------------------+---------+-----------------+------------------------------------------
--
 database01 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +| 7753 kB | database01_tbps |
            |          |          |             |             | postgres=CTc/postgres+|         |                 |
            |          |          |             |             | doesuser=CTc/postgres |         |                 |
 database02 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +| 7753 kB | database02_tbps |
            |          |          |             |             | postgres=CTc/postgres+|         |                 |
            |          |          |             |             | doesuser=CTc/postgres |         |                 |
 database03 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +| 7753 kB | database03_tbps |
            |          |          |             |             | postgres=CTc/postgres+|         |                 |
            |          |          |             |             | doesuser=CTc/postgres |         |                 |
 database04 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +| 7753 kB | database04_tbps |
            |          |          |             |             | postgres=CTc/postgres+|         |                 |
            |          |          |             |             | doesuser=CTc/postgres |         |                 |
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7917 kB | pg_default      | default administrative connection database
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7753 kB | pg_default      | unmodifiable empty database
            |          |          |             |             | postgres=CTc/postgres |         |                 |
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7753 kB | pg_default      | default template for new databases
            |          |          |             |             | postgres=CTc/postgres |         |                 |
(7 rows)

Matching PostgreSQL Storage Location to Mounted File System(s)

Once the directory locations are known they can be inspected to identify the FlashArray volume(s) which each corresponds to. 

For Linux/Unix systems the "df " command is useful to get an overview of what file systems are mounted and the volumes they correspond to. 

For Microsoft Windows systems the diskpart and wmic utilities will be used to identify file system(s) and corresponding volume(s). 

Linux/Unix  

When using the "df -h" command all mounted file systems and the corresponding volumes are listed.  One the volumes for PostgreSQL have been identified take note of the /dev/mapper/<volume> path as this will be used to identify the volume serial number. 

Using the default layout with a FlashArray volume there should be a volume mounted to "/var/lib/pgsql" or "/var/lib/pgsql/<version>/data". 

Filesystem                                       Size  Used Avail Use% Mounted on
devtmpfs                                         252G     0  252G   0% /dev
tmpfs                                            252G  1.2M  252G   1% /dev/shm
tmpfs                                            252G   11M  252G   1% /run
tmpfs                                            252G     0  252G   0% /sys/fs/cgroup
/dev/mapper/rhel-root                            630G  7.6G  623G   2% /
/dev/mapper/rhel-home                             73G  549M   72G   1% /home
/dev/dm-3                                       1014M  245M  770M  25% /boot
/dev/mapper/3624a93708488b6dac70f42a200021650p1  599M  6.9M  592M   2% /boot/efi
tmpfs                                             51G     0   51G   0% /run/user/0
/dev/mapper/3624a9370668f1ab9b15f4bc400014275    1.0T   47G  977G   5% /var/lib/pgsql

If separating database storage locations using tablespaces  then the directory locations returned from the pg_tablespace view should be shown as well in the "df -h" output:

Filesystem                                       Size  Used Avail Use% Mounted on
devtmpfs                                         252G     0  252G   0% /dev
tmpfs                                            252G  1.2M  252G   1% /dev/shm
tmpfs                                            252G   11M  252G   1% /run
tmpfs                                            252G     0  252G   0% /sys/fs/cgroup
/dev/mapper/rhel-root                            630G  7.6G  623G   2% /
/dev/mapper/rhel-home                             73G  549M   72G   1% /home
/dev/dm-3                                       1014M  245M  770M  25% /boot
/dev/mapper/3624a93708488b6dac70f42a200021650p1  599M  6.9M  592M   2% /boot/efi
tmpfs                                             51G     0   51G   0% /run/user/0
/dev/mapper/3624a9370668f1ab9b15f4bc400014275    1.0T   47G  977G   5% /var/lib/pgsql
/dev/mapper/3624a9370668f1ab9b15f4bc400014273    2.0T   15G  2.0T   1% /postgres/database01
/dev/mapper/3624a9370668f1ab9b15f4bc400014274    2.0T   15G  2.0T   1% /postgres/database02
/dev/mapper/3624a9370668f1ab9b15f4bc400014271    2.0T   15G  2.0T   1% /postgres/database03
/dev/mapper/3624a9370668f1ab9b15f4bc400014272    2.0T   15G  2.0T   1% /postgres/database04

Microsoft Windows  

Open a PowerShell or Command Prompt and enter the diskpart utility:

diskpart 

Once in the diskpart utility use the following to list the volumes:

list volume
  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  Volume 0     D                       DVD-ROM         0 B  No Media
  Volume 1     C                NTFS   Partition    255 GB  Healthy    Boot
  Volume 2         Recovery     NTFS   Partition    499 MB  Healthy    Hidden
  Volume 3                      FAT32  Partition     99 MB  Healthy    System
  Volume 4         PG_Base      NTFS   Partition   1023 GB  Healthy

Take note of the Volume ### and Ltr. Using this information the volumes can then be matched to a serial number using wmic.

Match the File System(s) to FlashArray Volume(s) 

Linux/Unix 

The operating system will be queried for the device serial number of the PostgreSQL volume(s) and then matched to a block storage device on FlashArray. 

This example will use the default layout for a PostgreSQL cluster thus only one volume needs to be identified. The process is the same for any additional volumes used in a different layout.

Using the "df" command we query the mount point/directory on which the volume is mounted. 

df -h | grep /var/lib/pgsql
[root@DB-04 data]# df -h | grep /var/lib/pgsql
/dev/mapper/3624a9370668f1ab9b15f4bc400014275    1.0T   47G  977G   5% /var/lib/pgsql

Using the device name (/dev/mapper/<volume identifier>) udev is queried for the DM_SERIAL property.  

udevadm info --query=all --name=<device name> | grep DM_SERIAL
[root@DB-04 data]# udevadm info --query=all --name /dev/mapper/3624a9370668f1ab9b15f4bc400014275 | grep DM_SERIAL
E: DM_SERIAL=3624a9370668f1ab9b15f4bc400014275

Using the serial number returned for the device, it is possible to match it up to the block storage volume on a FlashArray. Note that the block volume serial number will be all of the characters after “3624a9370”. 

Microsoft Windows  

Execute the following command in PowerShell or Command Prompt to query all devices for the Name , SerialNumber and Size:

wmic diskdrive get name,size,SerialNumber
PS C:\Users\DBTest> wmic diskdrive get name,size,SerialNumber
Name                SerialNumber                      Size
\\.\PHYSICALDRIVE2  6000c2994e8572da6bfb90202edbed11  824633671680
\\.\PHYSICALDRIVE1  6000c29047b4551b50408c9b57e99e5a  824633671680

The \\.\PHYSICALDRIVE<Number> corresponds to the Volume number.

Once the serial numbers have been identified 

To see all of the volumes and their serial numbers using the FlashArray CLI execute the following:

purevol list
pureuser@flasharray> purevol list
Name                          Size   Source                    Created                  Serial
postgres-database01           2T     -                         2021-09-06 09:36:57 PDT  668F1AB9B15F4BC400014273
postgres-database02           2T     -                         2021-09-06 09:36:57 PDT  668F1AB9B15F4BC400014274
postgres-database03           2T     -                         2021-09-06 09:36:57 PDT  668F1AB9B15F4BC400014271
postgres-database04           2T     -                         2021-09-06 09:36:57 PDT  668F1AB9B15F4BC400014272
postgres-pgdata               1T     -                         2021-09-06 09:40:38 PDT  668F1AB9B15F4BC400014275

Once the volume(s) have been identified then a storage snapshot can be created for it. 

Creating Storage Snapshots for PostgreSQL

To create a storage snapshot of the PostgreSQL cluster two system administration functions are used:

  • pg_start_backup - This prepares the cluster for an on-line backup. A label for the backup is provided during execution which can be used to identify it at a later date. Two modes of operation are possible when using this function , exclusive or non-exlusive mode. The exclusive mode the functional writes a backup label file and , if additional tablespaces are present , a tablespace map file to the cluster data directory. Once the files have been created a checkpoint is performed and then the backup's starting write-ahead-log location is returned. The non exclusive mode returns the contents of the files to the user only when using the pg_stop_backup function, which must then be manually copied to the backup area.  
  • pg_stop_backup - This the finishing function call for an exclusive or non-exclusive backup. If exclusive mode was specified when using pg_start_backup then this function removes the backup label and table space mapping file. 

Creating a storage snapshot requires that the backup mode is set to exclusive. 

Volume snapshots for PostgreSQL should always be created as apart of a Protection Group. This ensures that if there are multiple volumes for a single PostgreSQL instance that all volumes are created consistently with one another at the same point in time. 

A Protection Group allows for objects(volumes, hosts, etc) with similar characteristics to be grouped together for snapshot and protection manageability operations. 

Step 1. Prepare the system for a storage snapshot 

The pg_start_backup() function can be started with the following arguments pg_start_backup ( 'Cool Snapshot Name' [, fast boolean [, exclusive boolean ]] ). The exclusive value needs to always be set to true while the fast value is dependent on the PostgreSQL environment and usage frequency. Setting fast to true forces an immediate checkpoint which causes an IO spike , which could slow existing queries.  

Executing pg_start_backup() will respond as follows if successful and respond with the write-ahead-log location:

SELECT pg_start_backup('Snapshot01', true, true);
postgres=# SELECT pg_start_backup('Snapshot01', true, true);
 pg_start_backup
-----------------
 A/4F000028

Step 2. Take a volume snapshot on FlashArray 

Once the system has been prepared for a storage snapshot the volume(s) identified in Matching PostgreSQL storage locations to FlashArray volumes can have a storage snapshot created of them using the following process:

In the FlashArray graphical user interface find the volumes relevant to the PostgreSQL cluster. This can be done in the Storage view under Volumes.

clipboard_e105c29f80b209050529381aeb71dafac.png

Navigate to the Protection view and select Protection groups.

clipboard_e22773f8f446feab9e98610044a1c82d0.png

Identify the section for Source Protection Groups and select the "+" in the top right hand corner:

clipboard_eded0b537291baf6406c755f637d80cc1.png

In the Create Protection Group dialog provide a unique name and select Create.

clipboard_e843139a933b44c24038e30c9b0424888.png

Once the Protection Group has been created it will show up in the Source Protection Groups section.

clipboard_ef0ada2653acb4fe52003c3b692561072.png

Selecting the Protection Group will bring up the context view for it. Identify the section for Members and select the three ellipses in the top right hand corner. In the selection options that emerge select Add Volumes... to add volumes or Add Hosts.. to add the PostgreSQL host in question. 

clipboard_eb4c82149f45e2213d6ae8595e596dcec.png

In the Add Members dialog identify the relevant objects to add. Once complete select Add

clipboard_ef13ea43a8a91fefccd18b4cfc8ba111d.png

Once the relevant members have been added to the Protection Group identify the section for Protection Group Snapshots and select the "+" in the top right hand corner top create a protection group snapshot. 

clipboard_e1fc8c7029150e8b1773ba0d3fef296b5.png

In the Create Snapshot dialog provide the same same label used in preparing the PostgreSQL for a storage snapshot as the Optional Suffix. At this point retention and replication policies can be applied to this specific storage snapshot. Select Create when ready to create the protection group snapshot. 

clipboard_e18df38183e3d5298644c4335719379b7.png

Once the Protection Group Snapshot has been created it will be shown in the Protection Group Snapshots for the Protection Group. 

clipboard_e464b4be1e4b24d654742899ac206bae2.png

Step 3. Stop the Backup Operation 

After storage snapshot creation the backup operation needs to be stopped using the pg_stop_backup function. Exclusive mode must be set to true. 

SELECT pg_stop_backup(true);

The response should contain the write-ahead-log location if successful: 

postgres=# SELECT pg_stop_backup(true);
 pg_stop_backup
----------------
 (A/51000138,,)
(1 row)

If WAL archiving is not enabled the following error might occur:  

NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup

Point In Time Recovery 

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.