Backup and Restore PostgreSQL Databases using Pg_dump

PostgreSQL is a popular open-source relational database management system used for web applications, business intelligence, and other data-intensive applications. A critical aspect of managing a PostgreSQL database is ensuring data protection by having a backup and restore strategy in place.

In this article, we will provide a step-by-step guide on how to backup and restore a PostgreSQL database.

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.