-
-
Save facilita-tecnologia/e49b233d6c4d75cf7953a1113676e830 to your computer and use it in GitHub Desktop.
PG Dump/Restore
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment