Postgresql. Dump and restore with pg_dump and pg_restore

Short note for not forget.

1. Backup your data with pg_dump

pg_dump -U postgres -h localhost -p 5432 -Fc -b -c -f ./backupname.dump databasename

all options are described in pg_dump help

-U username used to connect to the database
-h database server host (or socket dir)
-p database server port number (5432 - default)
-F output format (c - custom - with that format pg_restore works)
-b include BLOB in dump
-c clean (for all schema element in the dump before creating clean it)

additional -i ignore-version proceed even when server version mismatches (typical when connecting to a remote host)

2. Restore with pg_restore

pg_restore -U postgres -h localhost -p 5432 -d newdatabase ./backupname.dump

Note: can handle dump, created by pg_dump with -Fc option

-d database name to connect

all parameters are described in pg_restore help

Leave a Reply

Your email address will not be published. Required fields are marked *