How to Backup and Restore Postgres Database

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
pg_dump -U citizix -h 10.2.1.10 -d dbname > dbname.sql

Possible options:

  • -W: prompt for password
  • -F: Format
  • p: plain sql
  • c: custom-format archive
  • d: directory-format archive
  • t: tar-format archive
1
pg_dump -U db_user -W -F t db_name > /path/to/dump_name.tar

Take a compressed backup of PostgreSQL database

1
pg_dump -U db_user -d db_name | gzip > path/to/backup.sql.gz

You can also pass in a connection strong

1
pg_dump "postgres://db_user:db_pass@db_host:db_port/db_name?sslmode=disable" > db_name-2023-03-28.sql

DB restore

To restore

1
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.

1
pg_restore -d db_name /path/to/dump_name.tar -c -U db_user

Restoring from a compressed backup

1
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
Last updated on Oct 14, 2024 11:46 +0300
comments powered by Disqus
Citizix Ltd
Built with Hugo
Theme Stack designed by Jimmy