BackUp PostgreSQL with pg_dump

  • |
  • 31 July 2021
Post image

In this post, we are going to find out how to backup postgresql database with pg_dump utility.

What is the pg_dump utility

PostgreSQL provides the pg_dump utility to simplify backing up a single database. This command must be run as a user with read permission to the database we intend to back up.

Backup File Types

3 types of backup files we have:

  • .bak: compressed binary format
  • .sql: plaintext dump
  • .tar: tarball

Steps to backup database

  • Log in as the postgres user:
$ su - postgres
  • Dump the contents of a database to a file. Replace dbname with the name of the database to be backed up:
$ pg_dump your_db_name > backup_file_name.bak
  • If we want to dump without log as the postgres user:
$ pg_dump -U postgres -W -h localhost your_db_name > backup_file_name.bak

Remote Database backup

  • pg_dump can be run from a client computer to back up data on a remote server. Use -h flag to specify the IP address and -p to identify the port:
$ pg_dump -h -p 5432 your_db_name > backup_file_name.bak
# or
$ pg_dump -h -p 5432 your_db_name > backup_file_name.sql

Backup Only the scheme definitions (create tables etc..)

$ pg_dump -U postgres -W -h localhost your_db_name --schema-only > schemeonly.sql

Backup Only the Data, not the schema

$ pg_dump -U postgres -W -h localhost your_db_name --data-only > dataOnly.sql

Backup Only One Table (Be Careful when using in the production !!)

$ pg_dump -U postgres -W -h localhost your_db_name -t your_table_name > tableBackup.sql

Note: When -t is specified, pg_dump makes no attempt to dump any other database objects that the selected table(s) might depend upon. Therefore, there is no guarantee that the results of a specific-table dump can be successfully restored by themselves into a clean database.

Backup dump data as INSERT commands rather than Copy

$ pg_dump -U postgres -W -h localhost local_Db --column-inserts --data- only > testAccount.sql

Dump data as INSERT commands (rather than COPY ). This will make restoration very slow

How to run .sql file in the postgresql

$ psql -U postgres -d your_db_name -h -p 5432 -a -f /path/to/sql
$ psql -U postgres -d local_Db -h localhost -p 5432 -a -f /path/to/sql
  • -U : username
  • -d: database name
  • -a: all echo
  • -f: path to SQL script

How to backup from your Docker/Podman Container to your host

If you want to backup your database when yu are running inside containers, you may use the following command:

$ podman exec -it {your_docker_name} pg_dump -U postgres -W -h localhost your_db_name > backup_file_name.bak

To Load Backup File

  • Drop the previous database and create the empty database again:
CREATE DATABASE databasename
  • Restore the database using psql:
$ psql databasename < backup_file_name.bak

To Load Backup/File when PostgreSQL running as container (Podman/Docker)

  • After dropping and re-creating database, copy the backup file inside the container:
$ podman cp /path/to/sql {containerId}:/path/to/container/sql
  • Then run the following psql command inside the container:
$ psql -U postgres -W -h localhost {db_name} -p 5432 -a -f /path/to/container/sql

You May Also Like