How to dump and restore a Postgres database

Written by Claudio Kuenzler - 0 comments

Published on June 24th 2011 - Listed in Linux Database Postgres

First make sure, that the local postgres user has full rights on the Postgres server (trust):

cat pg_hba.conf
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust

DUMP
Now we create the full dump (structure and data) of the database DB1:

pg_dump -p 5432 -h localhost -U postgres -Fc -f /tmp/pgdump.sql DB1

And this is for a single table 'table1' in DB1:

pg_dump -Fc -t table1 DB1 > /tmp/table1.dmp

RESTORE
To restore the dump several steps need to be done:

1. Create the database DB1 manually if database doesn't exist yet (e.g. if you want to restore the sql-dump on another server)

2. Don't forget to create a role if a special user (not postgres) is the owner of the database.

3. Launch the restore command:

pg_restore -p 5432 -h localhost -U postgres -d DB1 -Fc /tmp/pgdump.sql

Add a comment

Comments (newest first)

No comments yet.