Home > Storage > PowerFlex > White Papers > Deploying PostgreSQL on Dell PowerFlex > Use Case 3: Full database recovery
The exact process for a full database recovery may vary from database to database. Database recovery encompasses restoring the desired snapshot and applying subsequent archived logs to restore the database to its desired state. This method ensures data integrity and minimizes downtime in case of database corruption.
The following steps describe the complete database recovery use case of PostgreSQL database volumes using a PowerFlex snapshot.
1. To create a database recovery scenario, we are running additional database transactions by 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
row)
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 unmounted prior to restoring the snapshot volume.
Note: The transaction log filesystem is left as it is, assuming it was not corrupted, which is the only way to perform a full database recovery. If the transaction logs are not available, Use Case 4 should be followed instead of 3.
The latest snapshot of the data 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
Note: Only the data volume is restored and mapped to the host and not the transaction log volume. The assumption in this use case is that the transaction logs have survived the disaster and can be used to perform a full database recovery. If you restore the log volume, you overwrite these latest transactions with old data from the time of the snapshot creation.
3. Log in to the mounted directory and verify the content of the postgres.conf file has the following configurations:
# wal_level = replica
# archive_mode = on
# archive_command = 'cp %p /mnt/archive/%f'
# restore_command = 'cp /mnt/archive/%f %p'
# Log_directory=/mnt/logs/
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. 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.
6. Verify successful connection to the newly recovered database from the snapshot volume and verify the presence of manually added data and tables, ensuring they contain the recovered content.
For more information about the command line options, see continuous archive Postgres backup.