Postgres DB backup and restore approaches

Post Reply
Admin
Site Admin
Posts: 69
Joined: Thu Sep 24, 2020 5:40 pm

Postgres DB backup and restore approaches

Post by Admin »

Postgres DB backup and restore approaches

Get dump for Postgres DB:

Code: Select all

pg_dump -h host -p port  db_name > db_name.sql
To dump a database into a directory-format archive in parallel with 4 worker jobs:

Code: Select all

mkdir my_db_dumps
pg_dump -h host -p port -Fd db_name -j 4 -f my_db_dumps_dir
To dump a database schema only:

Code: Select all

pg_dump -h host -p port -s db_name > db_schema_only_mydb.sql
To restore database from a dump:

Code: Select all

psql -h host -p port -U my_user my_db < my_db_dump.sql   
Parallel backup:

Code: Select all

mkdir my_db_backups
pg_dump -F d -f my_db_backups -j 4 -h host -p port -U my_user -d my_db_backup_dir
Parallel restore:

Code: Select all

create database my_database;
pg_restore -F d -j 4 -h host -p port -U my_user -d my_db my_db_backups_dir
vacuumdb -h host -p port -U postgres -j 4 -z -v my_db
Post Reply