Header RSS Feed
 
If you only want to see the articles of a certain category, please click on the desired category below:
ALL Android Backup BSD Database Hacks Hardware Internet Linux Mail MySQL Monitoring Network Personal PHP Proxy Shell Solaris Unix Virtualization VMware Windows Wyse

How to dump and restore a Postgres database
Friday - Jun 24th 2011 - by - (0 comments)

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

Show form to leave a comment

Comments (newest first):

No comments yet.

Go to Homepage home
Linux Howtos how to's
Monitoring Plugins monitoring plugins
Links links

Valid HTML 4.01 Transitional
Valid CSS!
[Valid RSS]

7362 Days
until Death of Computers
Why?