Home > Storage > PowerFlex > White Papers > Deploying PostgreSQL on Dell PowerFlex > Use Case 1: Manual and policy-based point-in-time database snapshots
Creating a quiesced point-in-time database snapshot results in a database copy that includes all committed transactions up to the time of the snapshot creation. An administrator can perform this task on demand or automate it by job scheduling or by creating a snapshot policy. These database snapshots can help to:
PowerFlex snapshot operations can be performed from PowerFlex Manager UI, PowerFlex CLI, or by using PowerFlex REST APIs. Note that this section refers to database point-in-time consistent copies. For creation of quiesced database backups, a coordination with PostgreSQL database APIs is required.
Manual snapshots offer flexibility and control for creating on-demand copies, allowing users to capture specific database states as needed, such as before implementing updates, changes, or for ad-hoc data protection.
The following steps detail how to create a PowerFlex snapshot manually of PostgreSQL database volumes:
/usr/pgsql-16/bin/pgbench -U postgres -c 50 -j 30 -T 6000 -N -S mydb1 -p 5436
\c mydb1
CREATE TABLE authors(
author_id SERIAL PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL
);
INSERT INTO authors (author_id, first_name, last_name)
VALUES (1, 'Tony', 'Jackson'), (2, 'John', 'Lee'), (3, 'Annie', 'Frank');
To create a snapshot manually, in PowerFlex UI, under Block > Volumes, select all the volumes that the database resides in, including data, and log volumes, and click create snapshot. The following figure shows how to create a snapshot manually:
Figure 13. Creating a snapshot manually
The PowerFlex volumes included automatically construct a snapshot consistency group. The following dialog is displayed, and the snapshots are created with the names and suffixes specified. For snapshots that are only required for recovery purposes, Dell Technologies recommends that you keep the Read Only option (default) selected as shown in the following figure:
Figure 14. Snapshot name and values
Snapshot volumes ‘Logs-snap-1’ and ‘data-snap-1’ are created. These snapshots capture a quiesced image of the PostgreSQL database and can be used to start the database with data valid to the time the snapshot was created. To use a snapshot, the snapshot volumes are mapped to a host, the XFS filesystems on the snapshot volumes are mounted, the PostgreSQL service is pointed to these filesystems, and started, allowing the user to connect to the database and access the data from that snapshot. The following figure shows how to map a snapshot:
Figure 15. Mapping a snapshot
The following commands show an example of mounting the XFS filesystems and starting the PostgreSQL service:
# sudo mount /dev/scinid /mnt/xfs-pgdatadb1
# sudo mount /dev/scinie /mnt/logs
# sudo systemctl start postgresql-db1.service
Where /dev/scinid and /dev/scinie are newly discovered snapshot volumes.
Policy-based snapshots automate snapshot creation, retention, and deletion based on predefined schedules or conditions, ensuring consistency and reliability in snapshot management. This approach streamlines the snapshot creation and deletion processes, improves data protection, and reduces administrative overhead compared to manual snapshot management.
The following steps detail how to create a policy based PowerFlex snapshot of PostgreSQL database volumes:
Figure 16. Snapshot policy creation
When using the snapshot policy, a policy rule is created that defines the time interval between snapshot creation and the retention period (or in other words, how many snapshots to keep).
To create the policy described above, use the following CLI command:
# scli --login --management_system_ip 10.10.10.10 --username admin --password *****
# scli --add_snapshot_policy --snapshot_policy_name snap_policy_hourly --snapshot_creation_cadence 60 -- number_of_snapshots_per_retention_level 12,7,4
In the above example, the name of the policy is DB-SNAPSHOT. It creates a snapshot every 2 hours and keeps 12 of them. Making sure that snapshots of each of the two hour intervals for the past 24 hours are available. The policy rule can include a multilevel retention structure to retain the snapshots in minutes/hours/days or weeks. The above policy also describes another level of retention to keep one daily copy of the snapshot for a week.
After the policy is created, to assign it to the database volumes, select the policy, then click More Actions and Assign volumes. Select the volumes on which the snapshot policy is to be applied (in this case, the data and log volumes of the PostgreSQL database). Users can view the volumes created by the snapshot policy in the PowerFlex UI > Block > volumes tab. The following figure shows a snapshot policy to volumes window:
Figure 17. Snapshot policy to volumes
2. Snapshots are automatically created once the defined rule condition is met. To use a snapshot, the snapshot volumes are mapped to a host, the XFS filesystems on the snapshot volumes are mounted, the PostgreSQL service is pointed to these filesystems and started, and then the user can connect to the database to access the data from that snapshot.
Note: PowerFlex snapshots in a consistency group are guaranteed to be from precisely the same point in time. Hence, it is important to take the snapshot of the data and log volume together to restore and recover the database in a consistent state. In the event of a database issue, administrators should manually stop snapshot policies to prevent data loss. It can be enabled once the database is restored.