Backup and Restore PostgreSQL Databases

In this article we will show you the  first and most important task every database administrator / sysadmins must master “Backup and Restore your databases”. We will show you how to backup and restore a single database, backup and restore all existing databases, compress your backups and finally split your backups.

PostgreSQL database server provides pg_dump utility for backup databases. This article will describe various ways to use of pg_dump command to backup database. Also you will learn how to restore that backup.

-d, –dbname=DBNAME database name -h, –host=HOSTNAME database server hostname or ip -p, –port=PORT database server port number (default: 5432) -U, –username=NAME connect as specified database user -W, –password force password prompt –role=ROLENAME do SET ROLE before dump

1. Backup and Restore Single Database

• Backup: single database in PostgreSQL. Replace your actual database name with mydb.

$ pg_dump -U postgres -d mydb > mydb.pgsql

• Restore: single database backup in PostgreSQL.

$ psql -U postgres -d mydb < mydb.pgsql

2. Backup and Restore All Databases

• Backup: all databases in PostgreSQL using pg_dumpall utility.

$ pg_dumpall -U postgres > alldbs.pgsql

• Restore: all database backup using following command.

$ psql -U postgres < alldbs.pgsql

3. Backup and Restore Single Table

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

$ pg_dump -U postgres -d mydb -t mytable > mydb-mytable.pgsql

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

$ psql -U postgres -d mydb < mydb-mytable.pgsql

4. Compressed Backup and Restore Database

• Backup: PostgreSQL database in compressed format.

$ pg_dump -U postgres -d mydb | gzip > mydb.pgsql.gz

• Restore: database from compressed backup file directly.

$ gunzip -c mydb.pgsql.gz | psql -U postgres -d 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 -U postgres -d mydb | split -b 100m – mydb.pgsql

• Restore: database backup from multiple splited backup files.

$ cat mydb.pgsql* | psql -U postgres -d mydb

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

$ pg_dump -U postgres -d mydb | gzip | split -b 100m – mydb.pgsql.gz

• Restore: database from multiple files of compressed files.

$ cat mydb.pgsql.gz* | gunzip | psql -U postgres -d mydb

Summary

In this article we have explained the first and most important task every database administrator / sysadmins must master “Backup and Restore your databases”. Backup and restore process is too important in the IT world. It keeps you and your business save from any disaster that might happen. We showed you how to backup and restore a single database, backup and restore all existing databases, compress your backups and finally split your backups.

I hope this article is good enough for you.
See you in other articles.