Forum OpenACS Q&A: pg_dump and pg_restoring from production to dev

I have a production instance (safe4all) and a dev instance (safe4all-dev).

I'd like to periodically copy the database from production to dev, so that I have accurate data to play around with.

How do you all do this?

It seems like you'd do a

pg_dump safe4all > safe4all.dmp
dropdb safe4all-dev
createdb safe4all-dev
pg_restore -d safe4all-dev safe4all.dmp

Is that accurate? I'm pretty new to Postgres.

Collapse
Posted by Stan Kaufman on
Jade, does that work? It usually isn't subtle; either it does or it doesn't.

For what it's worth, this is what I do:

Edit all the \connect statements out of the dump file. I don't fundamentally understand what purpose they serve, but depending on how you set your permissions, they can result in only partial recreation of the db. And it will fail silently without showing in the error log.

Use this syntax -- see http://www.postgresql.org/docs/view.php?version=7.2&idoc=1&file=backup.html#BACKUP-DUMP-RESTORE

psql safe4all-dev < safe4all.dmp 2> errors.txt

This works fine for me anyway.

Collapse
Posted by Don Baccus on
Connect essentially logs in as the given user.  This ensures that if you're restoring a database that all items are recreated owned by the right user - important for [PG not OpenACS] permission checking.

The big gotcha is that pg_dump doesn't guarantee all function definitions are dumped earlier than they're used - a minor and annoying but not dangerous bug.  In OpenACS-land this is usually manifested by references to postgresql.sql-defined functions being in the dump before they're defined.

So I always do:

psql my_db -f acs-kernel/sql/postgresql/postgresql.sql

before attempting a restore then ignore the dup function definitions that result.

Collapse
Posted by Ola Hansson on
Jade,

Roberto has it all in writing in the following doc:

https://openacs.org/doc/misc/openacs-pg-migration.txt

Look at point 6 and 7.

/Ola

Collapse
Posted by Jade Rubick on
Here's what I do:

svc -d /service/safe4all-dev
restart-aolserver safe4all-dev
dropdb safe4all-dev
createdb safe4all-dev
createlang plpgsql safe4all-dev
psql -d safe4all-def -f /web/safe4all/packages/acs-kernel/sql/postgresql/postgresql.sql
psql -d safe4all-def -f /web/safe4all/packages/acs-kernel/sql/postgresql/rel-segments-create.sql
zcat safeall.dmp.gz | psql -d safe4all-dev -f -
rsync -aC --delete /web/safe4all/content-repository-content-files safe4all-dev
svc -u /service/safe4all-dev

Thanks to Michael Steigman for helping me with this. I'm going to link this in on the documentation.