PostgreSQL database server (also known as Postgres) is an advance database management system. The Postgres server provides psql
, pg_dump
and pg_dumpall
command line utilities to backup and restore databases.
Advertisement
This article will describe various ways to use of pg_dump command to backup database. Also you will learn how to restore postgres database backup via command line.
Backup and Restore Database in Postgres
Below is some connections options which you can use for connecting remote server or authenticated server with all queries given in this article.
-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. PostgreSQL Backup Single Database
- Backup a single database in PostgreSQL server. Use “-d” comamnd line option to provide database name to pg_dump command. Make sure to replace your actual database name in place of
mydb .pg_dump -h localhost -U postgres -W -d mydb > mydb.sql
- Restore a single database from backup in PostgreSQL. Just use “psql” command to restore PostgreSQL database.
psql -h localhost -U postgres -W -d mydb < mydb.sql
2. PostgreSQL Backup All Databases
- Backup all databases in PostgreSQL using
pg_dumpall
utility.pg_dumpall -h localhost -U postgres -W > alldbs.sql
- Restore: all database backup using following command.
psql -h localhost -U postgres -W < alldbs.sql
3. PostgreSQL Backup Single Table
- Backup: a single table named mytable from mydb database.
pg_dump -h localhost -U postgres -d mydb -W -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 -h localhost -U postgres -W -d mydb < mydb-table_1.sql
4. Compressed Backup and Restore Database
- Backup PostgreSQL database in compressed format. Use gzip command line utility to accept piped backup data and make archive file.
pg_dump -h localhost -U postgres -W -d 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 -h localhost -U postgres -W -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 -h localhost -U postgres -W -d mydb | split -b 100m – mydb.pql
- Restore: database backup from multiple splited backup files.
cat mydb.sql* | psql -h localhost -U postgres -W -d mydb
- Backup: database in compressed splited files of specified size.
pg_dump -h localhost -U postgres -W -d mydb | gzip | split -b 100m – mydb.sql.gz
- Restore: database from multiple files of compressed files.
cat mydb.sql.gz* | gunzip | psql -h localhost -U postgres -W -d mydb
Conclusion
In this tutorial, you have learned about backup and restore of PostgreSQL server database, tables.
6 Comments
por que me sale (bash: jampy db.bk: permised denied
Great post. Articles that have meaningful and insightful comments are more enjoyable, at least to me. It’s interesting to read what other people thought and how it relates to them or their clients, as their perspective could possibly help you in the future.
Nice blog help me lot. There i found an interesting article, giving brief description on restoring a single table from the backup in sql server. You may also have a look:
http://www.sqlmvp.org/restore-single-table-from-backup/
Hope! you also like this article.
Nice tips but I prefer to make PostgreSQL backup with the help of 3rd party tools like Postgresql-Backup http://postgresql-backup.com/
Great tool! Thanks!
Compressed Backup and Restore Database process helped me a lot. Thanks