pg_restore - problems restoring the data

[Arindam <arindam.mukerjee@xxxxxxxxx>]

I am writing a script to carry out updates from Postgres 7.2.1 to
8.1.5. The strategy is as follows:

1. Take the dump of the 7.2.1 database using pg_dump (of 8.1.5)
2. Stop the database
3. Uninstall the database
4. Install the database and start the database
5. Restore the dump taken in step 1 using pg_restore

Steps and 1 and 5 take care of maintaining the same data in the
upgraded database as had been in the older database.

The step 4 install code is legacy - I am calling it and did not write
it. I do know that it is doing the following things:
a. Installing Postgres 8.1.5
b. Starting the service
c. Executing some additional DDL commands. (details given at the end
of this email)

My problem is that in step 1, I take the dump like this:

<8.1.5 dir>/bin/pg_dump -d dbname -U dbuser -F t -f /data/data.dmp

I also tried this with the -c option.

And in step 5, I do this:

<8.1.5 dir>/bin/pg_restore -d dbname -U dbuser -F t /data/data.dmp

I also tried this with the -c option.

In all the above cases, this is the error I get:

pg_restore: [archiver (db)] could not execute query: ERROR: cannot
drop schema public because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
   Command was: DROP SCHEMA public;

pg_restore: [archiver (db)] could not execute query: ERROR: schema
public already exists

Please help.

-- Arindam


PS: The scripts that we execute after the install is complete are the following:

-- sql_file.sql
CREATE LANGUAGE plpgsql;

CREATE FUNCTION sym_bitand(numeric, int) returns int $$ begin return
($1::int) & $2; end; $$ language 'plpgsql';

CREATE OPERATOR & (leftarg = numeric, rightarg = int, procedure =
sym_bitand, commutator = & );
-- sql_file.sql ends


and run it like this:

psql -U dbuser -d template1 -f sql_file.sql


d. Create the following database:

psql -U dbuser -d template1 -c 'CREATE DATABASE some_db_name'