Forum OpenACS Q&A: pg_restore with blobs problem

Posted by Claudio Pasolini on 05/04/03 11:52 AM

I'm posting here instead of pg mailing lists because this problem seems OpenACS related.

I'm saving file-storage files in the database and I use this command in my daily backup:

pg_dump -b -F c -o mydb_prod >mydb_prod.dmp.gz

To restore a development instance of the DB I first did this sequence;

createdb mydb_dev
pg_restore -d mydb_dev -F c -o mydb_prod.dmp.gz
but I got this error:
pg_restore: [archiver (db)] could not execute query: ERROR:  function plpgsql_call_handler a\
lready exists with same argument types

I searched around and followed Tom Lane's suggestion, dropping mydb_dev and recreating a really empty database with:

create database mydb_dev with template = template0;

but then the same pg_restore gives this known error:

pg_restore: [archiver (db)] could not execute query: ERROR:  Function 'tree_ancestor_keys(va\
rbit, int4)' does not exist

I tried to load the postgres.sql from the acs-kernel:

psql -f packages/acs-kernel/sql/po\
stgresql/postgresql.sql mydb_dev

as I usually do before restoring plain text databases, but then I get again the first error

pg_restore: [archiver (db)] could not execute query: ERROR:  function plpgsql_call_handler a\
lready exists with same argument types

It seems that pg_restore, contrary to psql, stops when trying to create a function and this already exists.

Now I am in loop and don't know how to proceed.

Hope that someone has already encountered and solved this problem.

2: Re: pg_restore with blobs problem (response to 1)

Posted by Don Baccus on 05/04/03 09:42 PM

Ouch, this is why I've always done text dumps and have reloaded via psql. At the very worst you can ignore dupe errors of this sort in psql or hand-edit the text dump. We don't use PG's built-in binary large objects but rather store binary data after first uuencoding it, which makes it possible to use text dumps.

This problem isn't really OpenACS related - postgres flat-out is unable to guarantee it will make a dump that will load without tweaking of this sort. It will dump functions that call other functions before they're restored. An obvious workaround is to patch pg_restore so it doesn't die on a dupe function restore.

I suggest you take this to the PG mailing lists to see if Tom or anyone else has any bright ideas.

3: Re: pg_restore with blobs problem (response to 2)

Posted by Claudio Pasolini on 05/05/03 09:15 PM

I never investigated the way used by OpenACS to store blobs, and I don't really understand how uuencoding binary data makes it possible to use plain text dumps, but simply removing all the switches from pg_dump and restoring with psql has worked!

Thank You Don!

4: Re: pg_restore with blobs problem (response to 1)

Posted by Don Baccus on 05/06/03 12:02 AM

uuencoding is a text encoding of binary data that's safe to send via e-mail etc. You can't just put binary data into a text string and get it back out in PostgreSQL because a byte value of zero is used to flag the end of a string. So any string of binary bytes that contain a zero would be corrupted (prematurely terminated, actually) when read back out of the database.

uuencoded binary data, on the other hand, only contains printable characters that don't cause such problems. The penalty is modest, it takes 4 uuencoded bytes to store 3 arbitrary 8-bit bytes.