PostgreSQL: Backup and Restore Database

In the world of database management, ensuring the safety and integrity of your data is paramount. PostgreSQL, with its robust set of tools, offers powerful methods for backing up and restoring databases. This comprehensive guide aims to equip database administrators and developers with the knowledge needed to effectively dump and restore PostgreSQL databases, ensuring that data is securely backed up and can be restored when needed.

Introduction

PostgreSQL, an advanced open-source relational database management system, offers various methods for data backup and recovery. Among these, the pg_dump and pg_restore commands are widely used for their versatility and effectiveness in managing database backups and restoration processes.

Understanding pg_dump and pg_restore

• pg_dump is a utility for backing up a PostgreSQL database. It creates a file containing SQL commands that can recreate the database to its original state.
• pg_restore is used to restore databases from a backup file created by pg_dump. It can handle custom, tar, and directory formats produced by pg_dump.

1. Backup Single Database

• Enter the following command to create a backup file:

  pg_dump my_database > my_database.sql 
  

  Replace my_database with the name of the database to be backed up.

• Restore a single database from backup in PostgreSQL. Just use “psql” command to restore PostgreSQL database.

  psql my_database < my_database.sql 
  

2. Backup All Databases

• Backup all databases in PostgreSQL using pg_dumpall utility.

  pg_dumpall > alldbs.sql 
  

• Restore: all database backup using following command.

  psql  < alldbs.sql 
  

3. Backup Single Table

• Backup: a single table named mytable from mydb database.

  pg_dump -d mydb -t table_1 > mydb-table_1.sql 
  

• Restore: single table backup to database. Make sure your backup file contains only single table backup which you want to restore.

  psql mydb < mydb-table_1.sql 
  

4. Compressed Backup

• Backup PostgreSQL database in compressed format. Use gzip command line utility to accept piped backup data and make archive file.

  pg_dump mydb | gzip > mydb.sql.gz 
  

• Restore database from compressed backup file directly. Here gunzip will extract backup file and send data to psql command via pipe interface.

  gunzip -c mydb.sql.gz | psql mydb 
  

5. Split Backup in Multiple Files and Restore

• Backup: PostgreSQL database and split backup in multiple files of specified size. It helps us to backup a large database and transfer to other host easily. As per below example it will split backup files of 100mb in size.

  pg_dump  mydb | split -b 100m – mydb.pql 
  

• Restore: database backup from multiple splited backup files.

  cat mydb.sql* | psql  mydb 
  

• Backup: database in compressed splited files of specified size.

  pg_dump  mydb | gzip | split -b 100m – mydb.sql.gz 
  

• Restore: database from multiple files of compressed files.

  cat mydb.sql.gz* | gunzip | psql  mydb 
  

Conclusion

In this article, we provided a step-by-step guide on how to backup and restore a PostgreSQL database using the pg_dump and psql command-line utilities. It is important to have a backup and restore strategy in place to ensure data protection and minimize the risk of data loss. By following this guide, you can ensure that your PostgreSQL database is backed up regularly and can be restored quickly and efficiently in the event of data loss.