Backup and Restore Database in PostgreSQL

ที่มา : https://tecadmin.net/backup-and-restore-database-in-postgresql/

ทดสอบบน

centos 7

postgres …

1. Backup and Restore Single Database

Backup: single database in PostgreSQL. Replace your actual database name with mydb.

$ pg_dump -U postgres -d mydb > mydb.pgsql

Restore: single database backup in PostgreSQL.

$ psql -U postgres -d mydb < mydb.pgsql

2. Backup and Restore All Databases

Backup: all databases in PostgreSQL using pg_dumpall utility.

$ pg_dumpall -U postgres > alldbs.pgsql

Restore: all database backup using following command.

$ psql -U postgres < alldbs.pgsql

3. Backup and Restore Single Table

Backup: a single table named mytable from mydb database.

$ pg_dump -U postgres -d mydb -t mytable > mydb-mytable.pgsql

Restore: single table backup to database. Make sure your backup file contains only single table backup which you want to restore.

$ psql -U postgres -d mydb < mydb-mytable.pgsql

4. Compressed Backup and Restore Database

Backup: PostgreSQL database in compressed format.

$ pg_dump -U postgres -d mydb | gzip > mydb.pgsql.gz

Restore: database from compressed backup file directly.

$ gunzip -c mydb.pgsql.gz | psql -U postgres -d mydb

5. Split Backup in Multiple Files and Restore

Backup: PostgreSQL database and split backup in multiple files of specified size. It helps us to backup a large database and transfer to other host easily. As per below example it will split backup files of 100mb in size.

$ pg_dump -U postgres -d mydb | split -b 100m – mydb.pgsql

Restore: database backup from multiple splited backup files.

$ cat mydb.pgsql* | psql -U postgres -d mydb

Backup: database in compressed splited files of specified size.

$ pg_dump -U postgres -d mydb | gzip | split -b 100m – mydb.pgsql.gz

Restore: database from multiple files of compressed files.

$ cat mydb.pgsql.gz* | gunzip | psql -U postgres -d mydb

หากต้องทำ crontab backup database ใช้คำสั่งนี้
PGPASSWORD="password" pg_dump -h 127.0.0.1 -U postgres db_backup > /home/backup/db_backup.sql