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

ใส่ความเห็น

อีเมลของคุณจะไม่แสดงให้คนอื่นเห็น ช่องที่ต้องการถูกทำเครื่องหมาย *