PostgreSQL comes with built-in utilities for creating and restoring backups
Note: All commands can take the login url in the format postgres://db_user:db_pass@db_host:db_port/db_name?sslmode=disable
Backup
1
2
3
4
5
6
7
8
9
10
11
12
13
|
pg_dump -U citizix -h 10.2.1.10 -d dbname > dbname.sql
# With options:
# -W: prompt for password
# -F: Format
# p - plain sql
# c – custom-format archive
# d – directory-format archive
# t – tar-format archive
pg_dump -U db_user -W -F t db_name > /path/to/dump_name.tar
# Take a compressed backup of PostgreSQL database
pg_dump -U db_user -d db_name | gzip > path/to/backup.sql.gz
|
restore
1
2
3
4
5
6
7
|
psql -U citizix -h 10.0.8.133 -d dbname -f dbname.sql
# The -C flag is for creating a database before restoring data into it.
pg_restore -d db_name /path/to/dump_name.tar -c -U db_user
# Restoring from a compressed backup
gunzip -c path/to/backup.sql.gz | psql -U db_user -d db_name
|
Backing up a specific table
1
|
pg_dump -U db_user -d db_name -t table_name > path/to/backup.sql
|
Restoring a table from SQL file
1
|
psql -U db_user -d db_name < path/to/backup.sql
|
Backing up all the databases at once
1
|
pg_dumpall -U db_user > path/to/backup.sql
|
Restoring all the databases from the backup file
1
|
psql -U db_user < path/to/backup.sql
|