Postgresql备份和恢复的方法及命令行操作步骤

更多详情参考 https://stackoverflow.com/questions/2732474/restore-a-postgres-backup-file-using-the-command-line

create backup

pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f
"/usr/local/backup/10.70.0.61.backup" old_db


restore from backup

pg_restore -i -h localhost -p 5432 -U postgres -d old_db -v
"/usr/local/backup/10.70.0.61.backup"


important to set -h localhost - option

---------------------------------------------------------

You might need to be logged in as postgres in order to have full privileges on databases.

su - postgres
psql -l # will list all databases on Postgres cluster


pg_dump/pg_restore

pg_dump -U username -f backup.dump database_name -Fc


switch -F specify format of backup file:

• c will use custom PostgreSQL format which is compressed and results in smallest backup file size
• d for directory where each file is one table
• t for TAR archive (bigger than custom format)

restore backup:

pg_restore -d database_name -U username -C backup.dump


Parameter -C should create database before importing data. If it doesn&＃39;t work you can always create database eg. with command (as user postgres or other account that has rights to create databases) createdb db_name -O owner

pg_dump/psql

In case that you didn&＃39;t specify the argument -F default plain text SQL format was used (or with -F p). Then you can&＃39;t use pg_restore. You can import data with psql.

backup:

pg_dump -U username -f backup.sql database_name


restore:

psql -d database_name -f backup.sql


----------------------------------------------------------------------------------

POSTGRESQL 9.1.12

DUMP:

pg_dump -U user db_name > archive_name.sql


put the user password and press enter.

RESTORE:

psql -U user db_name

put the user password and press enter.

-----------------------------------------------------------------------------------------

Below is my version of pg_dump which I use to restore the database:

pg_restore -h localhost -p 5432 -U postgres -d my_new_database my_old_database.backup


or use psql:

psql -h localhost -U postgres -p 5432 my_new_database

where -h host, -p port, -u login username, -d name of database

-------------------------------------------------------------------------------------------

Backup and restore with GZIP

For larger size database this is very good

backup

pg_dump -U user -d mydb | gzip > mydb.pgsql.gz


resore

gunzip -c mydb.pgsql.gz | psql dbname -U user


https://www.postgresql.org/docs/9.1/static/backup-dump.html

---------------------------------------------------------------------------------------------