PostgreSQL - Backup and Restore

PostgreSQL-BackupAndRestore.md

PostgreSQL Backup and Restore

Backup

Backup to .sql file

pg_dump -U username -d db_name -h host_name > out_filename.sql

# If using docker
docker container exec -it container_name pg_dump -U username -d db_name -h host_name > out_filename.sql

options

• -h: host
• -d: database
• -U: username

Backup with compression

pg_dump -U username -d db_name -h host_name -F c -b -v -f out_filename.tar

options

• -F: Specifies the format of the backup. c stands for custom format, which is compressed and allows for more flexible restores.
• -b: Includes large objects in the dump.
• -v: Verbose mode. This provides detailed information about the dump process as it happens.
• -f: Specifies the output file name for the backup.

Restore

Restore backup without compression

psql -U username -d db_name -h host_name < out_file.sql

Restore backup with compression

pg_restore -U username -d db_name -h host_name -c -v backup_file.tar

pg_restore -U username -d db_name -h host_name -c -v --no-acl --no-owner backup_file.tar

options

• -c: Drops the database objects before recreating them. This ensures that any existing objects in the target database are removed before the restore, preventing conflicts.
• --no-acl: Prevents restoring access privileges (grant/revoke commands) from the dump. This is useful if you want to avoid overwriting existing access permissions in the target database.
• --no-owner: Prevents restoring ownership of objects. This is useful if you are restoring to a different database or server where the original user does not exist or should not own the objects.