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:
1 |
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:
1 |
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:
1 |
diskpart |
Once in the diskpart utility use the following to list the volumes:
1 |
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.
1 |
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.
1 |
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:
1 |
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:
1 |
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:
1 |
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.
Navigate to the Protection view and select Protection groups.
Identify the section for Source Protection Groups and select the "+" in the top right hand corner:
In the Create Protection Group dialog provide a unique name and select Create.
Once the Protection Group has been created it will show up in the Source Protection Groups section.
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.
In the Add Members dialog identify the relevant objects to add. Once complete select Add.
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.
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.
Once the Protection Group Snapshot has been created it will be shown in the Protection Group Snapshots for the Protection Group.
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.
1 |
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.
Comments
0 comments
Please sign in to leave a comment.