Re: URGENT: restoring a database - Mailing list pgsql-sql

On Friday, October 26, 2001, at 11:22  AM, Oleg Lebedev wrote:

> Hi,
> I think I got a problem here.
> I tried to restore my database from dump.bac file, which was created
> with
> pg_dumpall -o > dump.bac
> This is what I did:
>> createdb replica
>> psql -d replica -f dump.bac
>

Sounds like you didn't read up on pg_dump to closely.

What you probaly should have doe was not use pg_dumpall, but
pg_dump -o -F t livedb > livedb.dump.tar

The use pg_restore

My distribution doesnt have pg_dumpall, but I imagine that unlike pg_dump
where it does
 \connect - someuser

at the top.

It does
\connect origdb someuser

So you probably want to edit a copy of the dump file,
remove the db nmae from the connect statement, then drop the old db
then

You should be able to execute that section of the dump pertaining to the 
db you want to restore on the replacement original, and the new database.

EG:

Your edited snippet of the dump (Checcking all \connect statements to 
ensure they are eiter removed, or refer to currently connected 
database (-) is in dbdump.sql

psql -U username> CREATE DATABASE live;> \connect live> \i dbdump.sql> CREATE DATABASE replica;> \connect replica> \i
dbdump.sql>\q
 



> Notice that I have two different databases stored in this file.
> This is what I got:
>
> You are now connected to database template1.
> DELETE 3
> psql:db_10_22_01.bac:7: ERROR:  CREATE USER: permission denied
> psql:db_10_22_01.bac:8: ERROR:  CREATE USER: permission denied
> psql:db_10_22_01.bac:9: ERROR:  CREATE USER: permission denied
> psql:db_10_22_01.bac:11: ERROR:  pg_aclcheck: invalid user id 503
> You are now connected to database template1 as user postgres.
> psql:db_10_22_01.bac:18: ERROR:  CREATE DATABASE: database "webspectest"
>
> already exists
> You are now connected to database webspectest as user postgres.
> CREATE
> DROP
> You are now connected as new user postgres.
> psql:db_10_22_01.bac:48: NOTICE:  CREATE TABLE/PRIMARY KEY will create
> implicit index 'activitytype_pkey' for table 'activitytype'
> psql:db_10_22_01.bac:48: ERROR:  Relation 'activitytype' already exists
> psql:db_10_22_01.bac:65: NOTICE:  CREATE TABLE/PRIMARY KEY will create
> implicit index 'dcr_pkey' for table 'dcr'
>
> Obviously, no database was created. Moreover, I can not access my
> neither of my existing databases anymore.
> When I try:
>> psql webspectest
> I get an error:
> psql: FATAL 1: user "olebedev" does not exist
>
> At this point I am completely stuck.
> Please help.
> thanks,
>
> Oleg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
--
Best Regards
David Stanaway
================================
Technology Manager
Australia's Premier Internet Broadcasters
Phone: +612 9357 1699
Fax: +612 9357 1169
Web: http://www.netventures.com.au
Support: support@netventures.com.au
================================
The Inspire Foundation is proudly supported by Net Ventures through the 
provision of streaming solutions for it's national centres.  The Inspire 
Foundation is an Internet-based foundation that inspires young people to 
help themselves, get involved and get online. Please visit Inspire at 
http://www.inspire.org.au