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 13.34.232.23 -p 5432 your_db_name > backup_file_name.bak
# or
$ pg_dump -h 13.34.232.23 -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 1.2.3.4 -p 5432 -a -f /path/to/sql
## LOCAL DB EXAMPLE
$ 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:

DROP DATABASE IF EXISTS databasename;
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