Backup and Restore Fundamentals

Backing up and restoring databases in PostgreSQL is crucial for data security and recovery. This lesson covers the fundamental methods for backing up and restoring PostgreSQL databases, including the tools and commands used.

2.1. SQL Dump

The SQL dump method exports the database to a file in SQL format.

pg_dump dbname > dbname.sql

To restore from an SQL dump, use:

psql dbname < dbname.sql

2.2. File System Level Backup

This method involves copying the data directory directly. Ensure the database is not running during this operation.

cp -r /var/lib/postgresql/data /backup/location

To restore, stop the PostgreSQL server, copy the backup back, and start the server.

3.1. Restoring from SQL Dump

Use the following command:

psql dbname < dbname.sql

3.2. Restoring from File System Backup

Ensure PostgreSQL is stopped, then copy the data back:

cp -r /backup/location/data /var/lib/postgresql/data

Finally, restart the PostgreSQL server.

4.1. Regular Backups

Schedule regular backups to avoid data loss.

4.2. Test Restores

Periodically test your backup restores to ensure they work correctly.

4.3. Secure Backups

Store backups in a secure location to prevent unauthorized access.