Forum OpenACS Q&A: Backup and Restore of Postgresql

Folks

After a bit of trial and error, I now have a pretty solid method for backing-up and restoring/replicating Postgres OpenACS databases.It avoids the problems of restoring functions and pgplsql and I've been using on several live sites for the last 6 months or more including automatically replicating the database to a developement site.

So I thought I'd share it in case its of use to anyone else.

Backup

Split the backup into two distinct files - one for the schema and one for the data. The following little script assumes an installation called service1 with a database called service1;


# Dump the data
/usr/local/pgsql/bin/pg_dump -U nsadmin --no-owner --disable-triggers --data-only \
-f /var/lib/aolserver/service1/database-backup/service1_data.dmp service1

# Dump the schema
/usr/local/pgsql/bin/pg_dump -U nsadmin --schema-only \
-f /var/lib/aolserver/service1/database-backup/service1_schema.dmp service1


Restore

Restoring with this method is now straightforward as the schema is applied before the data. So to replicate or restore the database saved above;


# Drop the existing DB
/usr/bin/dropdb service1

# Create a new one from template0 (avoids the pgpsql problems)
/usr/bin/createdb --template=template0 --encoding=UNICODE service1

# Restore the schema
/usr/bin/psql service1 < /var/lib/aolserver/service1/database-backup/service1_schema.dmp

# Restore the data
/usr/bin/psql service1 < /var/lib/aolserver/service1/database-backup/service1_data.dmp

Enjoy!

Steve

Dave

Its been a while but IIRC there where two problems the first is that creating a default db gave errors with restoring pgplsql as it alreay exists in template1 and the other more fundamental problem is that a plain dump of schema and data together didn't restore in the correct sequence which gave errors if functions weren't present for triggers et al. This is referenced in the docs https://openacs.org/doc/openacs-5-1/snapshot-backup.html where it suggests runnning the object init script first:

Because of a bug in Postgres backup-recovery, database objects are not guaranteed to be created in the right order. In practice, running the OpenACS initialization script is always sufficient to create any out-of-order database objects.

Are you saying that you can use pg_dump and then suck the file back into a newly created db without error?

- Steve

It's probably also worth pointing out that plpgsql doesn't exist in template1 unless you (or whoever is the DB admin) has put it there - doing that is a convenience so you don't have to do it whenever you create a new DB that will use plpgsql, but isn't part of the default PG configuration...