Home > Storage > PowerFlex > White Papers > Deploying PostgreSQL on Dell PowerFlex > Use Case 4: Partial database recovery
Partial database recovery of a PostgreSQL database might be used in scenarios where a specific point-in-time view of the data is required for analysis or auditing purposes, even if it does not reflect the latest state of the database. However, unintended incomplete recovery could occur due to errors during the restoration process or incomplete application of transaction logs. In such cases, data inconsistencies or loss may occur, potentially impacting database integrity and usability. A database administrator has the capability to perform a partial recovery to a specific time as designated by the user. This is achieved by using the recovery_target_time function within the postgres.conf file, allowing verification of data at the specified moment. Then, the administrator can revert to the most recent state to transition to a complete recovery phase.
The following steps describe the partial recovery process for PostgreSQL database volumes using PowerFlex snapshot:
1. To create a partial database recovery scenario, we are running additional database transactions using pgbench and manual scripts to insert tables and data. The database size is augmented by an additional 10 GB of data since the last snapshot was captured.
postgres=# \c mydb1
You are now connected to database "mydb1" as user "postgres".
mydb1=# SELECT pg_size_pretty(pg_database_size('mydb1'));
pg_size_pretty
----------------
145 GB
(1 row)
In the case of partial database recovery, we will utilize the recovery_target_time parameter, which will restore only a portion of the data, approximately 7 gigabytes, up to the specified timestamp instead of full 10gigabytes of data written.
2. A database corruption is simulated by deleting some of the data files, an event that justifies a database restoration and recovery. To start the recovery process, the PostgreSQL service is stopped to avoid any locks or open files. The existing data filesystem is dismounted prior to restoring the snapshot volume. If the transaction logs did not survive the disaster and the transaction log snapshot volume is to be restored as well, unmount the transaction log filesystem as well.
The latest snapshot of the data volume (and if required, also the transaction log volume) is mapped and mounted to the node for recovering the database. For example, the snapshot that is mounted on the target node gets discovered as scinid and scinie devices:
# sudo mount /dev/scinid /mnt/logs
# sudo mount /dev/scinie /mnt/xfs-pgdata
3. Log in to the mounted directory and verify the content of the postgres.conf file has the following configuration:
# wal_level = replica
# archive_mode = on
# archive_command = 'cp %p /mnt/archive/%f'
# restore_command = 'cp /mnt/archive/%f %p'
# Log_directory=/mnt/logs/
# recovery_target_time= ’2024-02-13 06:48:00’
Note that with the recovery_target_time parameter set to '2024-02-13 06:48:00', the database recovery process will extend only up to this specified time frame. Any data written to the disk after this timestamp will not be included in the recovery.
4. Create an empty recovery.signal file in the data directory. Start the PostgreSQL service for this mounted database.
# touch recovery.signal
# sudo systemctl start postgresql-db1.service
5. Start the postgresql service and monitor the log files, notice that the recovery of database from archive logs happen only till the specified time in recovery_target_time and the database system is ready to accept connections once it is recovered till that timestamp.
6. Once the postgresql service starts successfully monitor the log files. When the log displays the message indicating that the database system is prepared to accept connections and has finished writing all database files from its archive logs, the recovery process is completed.
tail -f /mnt/logsdb1/log/postgresql-Wed.log
2024-02-10 15:29:28.082 CST [3598396] LOG: restored log file "000000010000002800000028" from archive
2024-02-10 15:29:28.102 CST [3598396] LOG: restored log file "000000010000002800000029" from archive
2024-02-10 15:29:28.206 CST [3598396] LOG: restored log file "00000001000000280000002A" from archive
2024-02-10 15:29:41.120 CST [3598394] LOG: checkpoint complete: wrote 16274 buffers (99.3%); 0 WAL file(s) added, 0 removed, 55 recycled; write=0.060 s, sync=0.003 s, total=0.089 s; sync files=31, longest=0.002 s, average=0.001 s; distance=897002 kB, estimate=897002 kB; lsn=28/A2000028, redo lsn=28/A2000028
2024-02-10 15:29:41.136 CST [3598392] LOG: database system is ready to accept connections.
7. Verify successful connection to the partially recovered database from the snapshot volume and verify the presence of manually added data and tables, ensuring they contain the recovered content.