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
Hi,
I am new to this postgreSQL, currently I am doing the back up and purging the data from one GP server to another new GP server. For offloading the data we are using the pg_dump utitlity pg_dump -t schema.tablename flashone > /tmp/dump/schema.tablename.sql;. For my understanding we are dumping all the data from the table till today to the path mentioned. We are asked to schedule either weekly or monthly incremental data to the new server. I am not sure how to proceed, if we use the pg_dump utility we can back up all the data till that date. Please advise me for the incremental backup.
Thanks,
Venkat
Hi,
Im using this comand but i have a password to my server acess and when i run the pg_dump cmd prompt a password needed.
Can i set this automatically? i dont want to put my password everytime i run my script.
Can you give me an exemple how can i?
– automatically answer the prompt password
– or, remove my acess server password
im using a windows server 2013 with postgreeSQL 9.3.
Thank you so much
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
after almost 1 hour of struggle found the right solution..thank you Sir !!
thanks 🙂
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
Thank you for your nice article..
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 ??
you need to dropdb dbname first, then createdb dbname and only then pg_restore dbname
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!
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 I restore.
3. I then to the restore and all is fine
So my question is with #2 above. The files are getting large and to change the schema takes a long time and soon I may not have enought disk space to do this. Any ideas on how to overcome this?
Recently, i wrote an article regarding incremental backup in PostgreSQL, may it help to you?
https://mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/