pg_restore - problems restoring the data

From: Arindam <arindam(dot)mukerjee(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_restore - problems restoring the data
Date: 2007-02-16 12:38:55
Message-ID: d85a51ff0702160438w4aba2423rb913ac64b9339be5@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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'

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2007-02-16 13:02:30 Re: ROLE INHERIT
Previous Message Clodoaldo 2007-02-16 12:13:08 Re: Setting up functions in psql.