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'
