Backup & Restore Database in PostgreSQL (pg_dump,pg_restore)

Here i demostrate how to backup and restore dabatase in PostgreSQL

1)Backup data with pg_dump


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

To list all of the available options of pg_dump , please issue following command.

pg_dump -?

-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode
-F, –format=c|t|p output file format (custom, tar, plain text)
-c, –clean clean (drop) schema prior to create
-b, –blobs include large objects in dump
-v, –verbose verbose mode
-f, –file=FILENAME output file name

2) Restore data with pg_restore


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

To list all of the available options of pg_restore , please issue following command.

pg_restore -?

-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Abhishek Yadav
Guest
Abhishek Yadav

after almost 1 hour of struggle found the right solution..thank you Sir !!

Akash Kumar
Guest
Akash Kumar

thanks :)

Virendra Rajput
Guest
Virendra Rajput

Thanks, for the examples. I used it to create a automated backup script that, runs every night and uploads the DB backup, to one of my Dropbox folders.

https://github.com/bkvirendra/Dropbox_db_backup

ketua rw
Guest
ketua rw

Thank you for your nice article..

Rafael
Guest
Rafael

Hi, there!
I tried this on Windows 7. I created some testDB, with one table and lines, after did pg_dump like above, then I deletd testDB, did pg_restore like the example,

no warning or error messages on cmd when executing both the commands, but the database does not get restored, nothing happens

what’s going on ??

Max
Guest
Max

you need to dropdb dbname first, then createdb dbname and only then pg_restore dbname

Stanley Plattsmier
Guest
Stanley Plattsmier

I found your blog in the “Related Blogs” section of another blog. You write excellent content. I have your blog bookmarked so I’ll definitely come back later. Keep up the amazing work!

trackback
Solution Hacker - Common DBA jobs

[…] Backup and restore database in postgresql […]

Matt
Guest
Matt
How would you do large databased. Lets say your database is 5 gig. Here is what I do and it takes a long time and I worry some day I may not have enough space: 1. Backup the database using pretty much the same as you do in step 1 2. I want to change the schema in the backup so I vi this file which is about 5.5 gigs. This is done because I comment out a few things in the file and also put a commit at the bottom of the backup or it will not save when… Read more »
Ayoub
Guest
Ayoub

Hi,
Thank you very much for this useful post.

I am new to postgresql and I plan to do daily backup:

I use this command for dump:

pg_dump –host=myhost –port=myport –username=super –format=c –file=myfile.dump mydbname

This seems to work

But For restore, I had to drop the database, recreate it and then apply the command:

pg_restore –host=myhost –port=myport –username=super -d mydbname myfile.dump

==> Is there a way to do it without having to drop it and recreate it manually?

Best regards,
Ayoub