Hi- Thanks for the helpful suggestions on this problem last Wednesday morning- I spent the rest of the day in a meeting, and I'm now returning to the problem post-holiday. I apologize for the slow response to your ideas.
Tom- You were correct, I was restoring the wrong database in my example with template0. When I corrected this problem, I got a new error message! (progress of a sort <grin>). Apparently my first problem is the result of plpgsql already being defined in template1. I haven't touched template1 since my install, but it may be that template1 comes with this already defined, or it may be that template1 is set up this way only in the Debian package, which I use for installation. Perhaps Oliver can shed some light on this. At any rate, using template0 as suggested solved my original problem, and now I've got a new one. Take a look at the example below: nickf@morgai:/data1/db-backup$ pg_dump -Ft alpha > inhoward.02_12_02_02_00_01.dump.tar nickf@morgai:/data1/db-backup$ dropdb alpha DROP DATABASE nickf@morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha CREATE DATABASE nickf@morgai:/data1/db-backup$ pg_restore -d alpha inhoward.02_12_02_02_00_01.dump.tar pg_restore: [archiver (db)] could not execute query: ERROR: Relation "actor" does not exist nickf@morgai:/data1/db-backup$ When I do a "\d" in psql after receiving this message, it looks like some of my schema has been restored, but "actor" is indeed missing. I spot-checked a few tables, and found that none of the data has been restored yet. It appears that in the process of creating the schema, pg_restore attempted to create an object that required the existence of actor, which wasn't restored yet. My conjecture is that the objects are just being created in the wrong order. To test this I tried a couple of commands after wiping the database clean again: pg_restore -s -d alpha inhoward.02_12_02_02_00_01.dump.tar - resulted in exactly the same error, suggesting again that the error message is probably being generated while restoring the schema. pg_restore -t actor -d alpha inhoward.02_12_02_02_00_01.dump.tar - completed successfully, indicating that the schema & data for "actor" is present in the dump file. I consulted the pg_restore documentation, and found several switches that affected the order of object creation, which I tried with the results shown below: nickf@morgai:/data1/db-backup$ dropdb alpha DROP DATABASE nickf@morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha CREATE DATABASE nickf@morgai:/data1/db-backup$ pg_restore -o -d alpha inhoward.02_12_02_02_00_01.dump.tar pg_restore: [archiver (db)] could not execute query: ERROR: relation "rule" not found nickf@morgai:/data1/db-backup$ dropdb alpha DROP DATABASE nickf@morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha CREATE DATABASE nickf@morgai:/data1/db-backup$ pg_restore -N -d alpha inhoward.02_12_02_02_00_01.dump.tar pg_restore: [archiver (db)] could not execute query: ERROR: Relation "actor" does not exist nickf@morgai:/data1/db-backup$ dropdb alpha DROP DATABASE nickf@morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha CREATE DATABASE nickf@morgai:/data1/db-backup$ pg_restore -r -d alpha inhoward.02_12_02_02_00_01.dump.tar pg_restore: [archiver (db)] could not execute query: ERROR: Relation "actor" does not exist My goal in working with pg_restore using the tar format is to be able to either restore an individual table or recreate the entire database conveniently should the need arise. I've used the text dump & frequently done a full restore by piping it into to psql without problems before, so I can confirm that for our database, the objects get created in the correct order when using that format. So... That's the whole story- Any thoughts on what I should try next? Thanks, -Nick -------------------------------------------------------------------------- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]