PostgreSQL database server provides pg_dump
and psql
utilities for backup and restore databases. This article will describe various ways to use of pg_dump command to backup database. Also you will learn how to restore datbase backup.
Backup and Restore Database in PostgreSQL
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. Backup and Restore Single Database
Backup: single database in PostgreSQL. Replace your actual database name with
$ 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
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