Skip to main content

Backup and recovery

Welcome to this tutorial on Backup and Recovery in PostgreSQL! As an administrator dealing with databases, it is essential to know how to safeguard data, and in extreme cases, recover lost data. In this tutorial, we'll walk you through the basics of backup and recovery in PostgreSQL.

Understanding Backups

In PostgreSQL, backups are snapshots of your database at a particular point in time. They are useful for safeguarding data against potential data loss due to accidental deletions, hardware failures, or any other unforeseen event. There are two types of backups in PostgreSQL:

  • Physical Backups: These are byte-by-byte copies of the database, its files, and directories. They are easy to understand, but the recovery process can be a bit lengthy.

  • Logical Backups: These are backups of table data and schema in SQL format. They are slower and more space-consuming than physical backups, but they provide a lot of flexibility.

Backup Methods

Physical Backups​

Physical backups in PostgreSQL are handled by a utility called pg_basebackup. This utility makes a binary copy of the database files and directories. Here's how you can create a physical backup:

pg_basebackup -h localhost -D /path/to/backup/directory -U myuser -P -v -R

In this command, -h specifies the host, -D specifies the backup directory, -U specifies the user, -P shows progress, -v enables verbose mode, and -R includes configuration files for easier recovery.

Logical Backups​

Logical backups in PostgreSQL are handled by two utilities pg_dump and pg_dumpall. pg_dump is used to backup a single database, whereas pg_dumpall is used to backup all databases.

Here's how you can create a logical backup of a single database using pg_dump:

pg_dump -h localhost -U myuser -d mydatabase -F t > /path/to/backup/directory/mydatabase.tar

Here's how you can create a backup of all databases using pg_dumpall:

pg_dumpall -h localhost -U myuser > /path/to/backup/directory/alldb.sql

Recovery Methods

Physical Recovery​

To recover from a physical backup, you just need to replace the current database directory with the backup directory. But before you do that, make sure to stop the PostgreSQL service. Here's how you can do this:

# stop postgresql service
sudo service postgresql stop

# replace current directory with backup directory
sudo rm -r /var/lib/postgresql/9.3/main
sudo cp -r /path/to/backup/directory /var/lib/postgresql/9.3/main

# start postgresql service
sudo service postgresql start

Logical Recovery​

To recover from a logical backup, you need to use the psql or pg_restore utility depending on the format of your backup. If your backup is in plain SQL format, you can use the psql utility:

psql -h localhost -U myuser -d mydatabase < /path/to/backup/directory/mydatabase.sql

If your backup is in tar format, you can use the pg_restore utility:

pg_restore -h localhost -U myuser -d mydatabase -v /path/to/backup/directory/mydatabase.tar

Conclusion

And that's a wrap! You now know how to create backups and recover your data in PostgreSQL. Remember, regular backups are crucial for safeguarding your data. So, make sure to incorporate them into your routine as a database administrator. Happy learning!