|
How can I use the command line to dump my postgresql database to a file and later restore it in-place (as in overwrite the existing data)? This is for either backup purposes or for copying between my production and staging servers for testing. I can't use the django management tools, such as dumpdata/loaddata because its difficult to clear out the database completely. (flush calls the post-syncdb routines which add data into the otherwise empty db) Also copying over table relationships can be problematic. When I attempt to issue standard pg_dump / pg_restore commands I can't just drop the database and re-create it from the command line. I run into permissions issues since the database and public schema are owned by a superuser, not my db user. asked 15 Sep '11, 01:53 Cole |
|
Our docs on this are here, Personally I use PHPpgadmin unless its a very large DB. answered 15 Sep '11, 02:04 johns Thanks. This gets me 90% of the way there. Its a bit of a challenge though to overwrite existing data. I clarified my post to reflect this aspect.
(15 Sep '11, 02:30)
Cole
|
|
After some trial and error I found the following commands worked for me. Please let me know if there's anything wrong with this approach.
pg_dump -U db_username -Fc db_name > dump.db pg_restore -U db_username -n public -c -1 -d db_name dump.dbPretty standard use of pg_dump. The trick is reading the archive back in correctly with pg_restore. The combination of "-n public" and "-c" clears all existing tables in the public schema before restoring the tables and data. Without "-n public," pg_restore will attempt to clear the whole db, including the public namespace which is owned by the superuser, not me. "-1" ensures that the restore takes place as a single transaction, and any error will cancel the whole operation.
pg_dump -U db_username -Fc db_name | pg_restore -U staging_db_username -n public -c -1 -O -d staging_db_nameBasically its the previous two commands piped together using different databases. The one difference is the use of the "-O" command. Without it pg_restore will try restore the ownership of each table to the user that created it. In this case, since the tables are being copied to a different database, you don't want to copy over the ownership. -O strips that out.
answered 15 Sep '11, 02:17 Cole There is nothing wrong with that method. Thank you for sharing the results with the community.
(15 Sep '11, 02:27)
johns
|
|
@Cole - Thanks for the great post! In my Django website, I installed the app " I believe it was the hint you gave about adding the flags I did have an unrelated hiccup in my procedure. I discovered that my WebFaction postgres database didn't have all in indexes and contraints as my MacBook database. I had to manually add the missing indexes and constraints before the answered 19 May '14, 18:37 stefan0 |
