PostgreSQL backup and restore strategies

Post #276,450 by tuberculosis 12/22/06 4:01:52 PM Reply	PostgreSQL backup and restore strategies I've got a nice empty database with schema defined and reference data populated as a starting point for my app. I want to return to this state often during testing and have a good backup strategy for production data when I go into production next week. Should I prefer the text or archive formats with pg_dump? What are the advantages/disadvantages of each? [link\|http://www.blackbagops.net\|Black Bag Operations Log] [link\|http://www.objectiveclips.com\|Artificial Intelligence] [link\|http://www.badpage.info/seaside/html\|Scrutinizer]
Post #276,452 by Yendor 12/22/06 4:35:33 PM Reply	Re: PostgreSQL backup and restore strategies I've got a nice empty database with schema defined and reference data populated as a starting point for my app. I want to return to this state often during testing and have a good backup strategy for production data when I go into production next week. For Java coding, I create the entire DDL along with test data in my Ant scripts (code originally courtesy of Adminiscott), wherever possible. This allows a consistent frame of reference for JUnit tests as well. Should I prefer the text or archive formats with pg_dump? What are the advantages/disadvantages of each? For backing up PG databases, I have created a quickie little shell script: `#!/bin/sh # Command to backup a Postgresql database pg_dump -U $DB_USERNAME --create --inserts --file=$1.sql $1` If your database is named `foo`, then you would invoke this via `backup foo` and expect to find full DDL along with current data inside `./foo.sql`. HTH. -YendorMike "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." - Benjamin Franklin, 1759 Historical Review of Pennsylvania
Post #276,453 by admin 12/22/06 5:39:56 PM Reply	Ditto on Mike For development, I typically blow away the database, reload the schema, then reload the data from a known good state. This way all sequences are reset and I'm constantly testing my build scripts as well -- no surprises. As a side, this is easy with Django: python manage.py syncdb. :-) Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #276,490 by tuberculosis 12/23/06 12:11:18 AM Reply	ddl is easy Schema is generated from meta model and various utils exist to drop/create tables/constraints/sequences. OTOH, I have a metric assload of data that took a couple weeks to amass - this I've been keeping in a pg_dump generated file. What about deployment - is this still the way to go for production? It kind of looks like pg_dump to archive file with pg_restore gives quite a bit more flexibility - but I am suspicious of binary formats. [link\|http://www.blackbagops.net\|Black Bag Operations Log] [link\|http://www.objectiveclips.com\|Artificial Intelligence] [link\|http://www.badpage.info/seaside/html\|Scrutinizer]
Post #276,500 by Yendor 12/23/06 1:43:44 AM Reply	Re: ddl is easy OTOH, I have a metric assload of data that took a couple weeks to amass - this I've been keeping in a pg_dump generated file. What about deployment - is this still the way to go for production? It kind of looks like pg_dump to archive file with pg_restore gives quite a bit more flexibility - but I am suspicious of binary formats. As noted above, I use `pg_dump` to dump my output to a text file. The `--inserts` flag tells `pg_dump` to create `INSERT INTO ...` statements for each row instead of a tab-delimited format, or other format. I prefer to have SQL statements because I can read them alot easier than I can read tab-delimited files. Maybe I'm just set in my ways and I need to upgrade...But I'll take the performance degradation that goes along with INSERT statements in favor of something that I could read more easily if I had to. Importing the output of my `pg_dump` command into a new copy of my database is a simple `\\i filename.sql` at the Postgres CLI. -YendorMike "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." - Benjamin Franklin, 1759 Historical Review of Pennsylvania
Post #276,527 by crazy 12/23/06 11:14:21 AM Reply	If the issue is accumulating a large qty of data that need to be put back, then you could have a 2 stage build. Can you have your data external to the database to start off with, ie: you are going through a development phase, accumulating data in the database, but then have the data exported into flat file to be available for a rebuild? If so, then create your database without any data, and then use the copy command to quickly bring it back in. [link\|http://www.postgresql.org/docs/8.1/interactive/sql-copy.html\|http://www.postgresq...ive/sql-copy.html] You can have the data saved in non-binary or binary, depending on your speed requirement. You get much better control of what tables are done when, and you can place the data physically on the server before import (if you are doing client server, and the round trip hurts you).

Welcome to IWETHEY!