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

1: pg_dump and pg_restoring from production to dev

Posted by Jade Rubick on 02/15/03 07:55 PM

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.

2: Re: pg_dump and pg_restoring from production to dev (response to 1)

Posted by Stan Kaufman on 02/15/03 08:36 PM

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.

3: Re: pg_dump and pg_restoring from production to dev (response to 1)

Posted by Don Baccus on 02/15/03 08:59 PM

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.

4: Re: pg_dump and pg_restoring from production to dev (response to 1)

Posted by Ola Hansson on 02/15/03 09:03 PM

Jade,

Roberto has it all in writing in the following doc:

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

Look at point 6 and 7.

/Ola

5: Re: pg_dump and pg_restoring from production to dev (response to 1)

Posted by Jade Rubick on 02/16/03 12:23 AM

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.