Home > Storage > PowerFlex > White Papers > Deploying PostgreSQL on Dell PowerFlex > Use Case 2: Database backup process
Effective backup procedures are vital for data protection and business resilience. PostgreSQL's pg_backup_start and pg_backup_stop options facilitate streamlined backup processes, maintaining data integrity throughout. Also, it is important to ensure that WAL archiving is enabled and working correctly. Additionally, that snapshots are taken periodically.
The following steps describe the database backup process of PostgreSQL database volumes using PowerFlex snapshots.
1. Map a PowerFlex volume for WAL archive logs and mount it with the required permissions.
# sudo mkfs.xfs /dev/scinic
# sudo mount /dev/scinic /mnt/archive
# sudo chown postgres:postgres /mnt/archive
# sudo chmod 0700 /mnt/archive
Enable WAL archive logging and restore command options as a part of recovery preparations in the postgres.conf file of the database.
# wal_level = replica
# archive_mode = on
# archive_command = 'cp %p /mnt/archive/%f'
# restore_command = 'cp /mnt/archive/%f %p'
# log_directory=/mnt/logs/
2. Run the following commands and restart the database service for the changes to be effective.
# /usr/pgsql-16/bin/psql -c "ALTER SYSTEM SET archive_command TO 'cp %p /mnt/archive/%f'" -p 5436
# sudo systemctl start postgresql-db1.service
3. Verify that the configured WAL archive logging is working, any data that is written to db creates the WAL logs in the mounted directory here > /mnt/archive.
[postgres@conode1 mnt]$ ls -l /mnt/archive
total 103514120
-rw-------. 1 postgres postgres 16777216 Feb 13 06:36 000000010000001B00000002
-rw-------. 1 postgres postgres 16777216 Feb 13 06:36 000000010000001B00000003
-rw-------. 1 postgres postgres 16777216 Feb 13 06:38 000000010000001B00000004
-rw-------. 1 postgres postgres 16777216 Feb 13 06:38 000000010000001B00000005
4. To create a point-in-time snapshot for consistent database backup, it is necessary to initiate a checkpoint in snapshot mode using the pg_start_backup() function. This ensures that the backup maintains consistency and recoverability up to the starting point of the backup. Below are the commands to be run after logging into the database.
mydb1=# SELECT pg_backup_start(label => 'backup', fast => true;
pg_backup_start
-----------------
1B/3000028
(1 row)
5. Take a snapshot of data and log volumes from the PowerFlex UI. Once the snapshot is taken, run the below with function pg_backup_stop. This function terminates the backup mode of the database and performs an automatic switch to the next WAL segment.
mydb1=# SELECT * FROM pg_backup_stop(wait_for_archive => true);
NOTICE: all required WAL segments have been archived
lsn | labelfile | spcmapfile
------------+----------------------------------------------------------------+------------
1B/3000138 | START WAL LOCATION: 1B/3000028 (file 000000010000001B00000003)+|
| CHECKPOINT LOCATION: 1B/3000060 +|
| BACKUP METHOD: streamed +|
| BACKUP FROM: primary +|
| START TIME: 2024-02-13 01:46:28 CST +|
| LABEL: backup +|
| START TIMELINE: 1 +|
(1 row)
The above protection process creates valid database snapshots leveraging PowerFlex snapshot technology in coordination with the database APIs. If a database recovery is required, it can be accomplished through two distinct methods: full recovery or partial recovery up to a specified recovery point. Use cases 2 and 3 outline the comprehensive steps for both scenarios, ensuring a seamless restoration process.