Thread: pg_restore blues

OK, I'm probably doing something very stupid, but can somebody explain how I'm supposed to restore a custom archive
dump?

$ createdb foo
CREATE DATABASE
$ psql foo
foo=# create table users (id serial);
NOTICE:  CREATE TABLE will create implicit sequence 'users_id_seq' for SERIAL column 'users.id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'users_id_key' for table 'users'
CREATE
foo=# \q
$ pg_dump --blobs --clean --create --file=/tmp/foo.dump --format=c --compress=9 foo
$ dropdb foo
DROP DATABASE
$ pg_restore --create -d test /tmp/foo.dump
Archiver(db): Could not execute query. Code = 7. Explanation from backend: 'ERROR:  ProcedureCreate: procedure
pltcl_call_handleralready exists with same arguments 
'.
$ pg_restore --create --clean -d test /tmp/foo.dump
Archiver(db): Could not execute query. Code = 7. Explanation from backend: 'ERROR:  index "users_id_key" does not exist
'.

What's the magic command that I am looking for??


Allan

Oh, no!  Please don't tell me I also can't use BLOB in PostgreSQL.
Surely there is a way to backup and restore the *whole* database?

I've tried creating the database manually and then
    $ droplang  plpgsql foo
before the restore, but that didn't work either.  The language is, of
course, created in template1.


I'm stumped.  :-(  I can backup my db, but I can't restore it.  Not much
use, is it...?


Allan.

Lee Harr wrote:

> On Tue, 16 Oct 2001 18:42:19 +0100, Allan Engelhardt <allane@cybaea.com> wrote:
>
>>OK, I'm probably doing something very stupid, but can somebody explain how I'm supposed to restore a custom archive
dump?
>>
>>$ createdb foo
>>CREATE DATABASE
>>$ psql foo
>>foo=# create table users (id serial);
>>NOTICE:  CREATE TABLE will create implicit sequence 'users_id_seq' for SERIAL column 'users.id'
>>NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'users_id_key' for table 'users'
>>CREATE
>>foo=# \q
>>$ pg_dump --blobs --clean --create --file=/tmp/foo.dump --format=c --compress=9 foo
>>$ dropdb foo
>>DROP DATABASE
>>$ pg_restore --create -d test /tmp/foo.dump
>>
>
>
> I never use pg_restore... Actually did not know it was there.
>
> I always use psql:
>
> psql
> create database restordbname;
> \c restordbname
> \i foo.dump
> \q
>
> Of course I also do not use blobs....
>
>
>

On Tue, 16 Oct 2001 18:42:19 +0100, Allan Engelhardt <allane@cybaea.com> wrote:
> OK, I'm probably doing something very stupid, but can somebody explain how I'm supposed to restore a custom archive
dump?
>
> $ createdb foo
> CREATE DATABASE
> $ psql foo
> foo=# create table users (id serial);
> NOTICE:  CREATE TABLE will create implicit sequence 'users_id_seq' for SERIAL column 'users.id'
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'users_id_key' for table 'users'
> CREATE
> foo=# \q
> $ pg_dump --blobs --clean --create --file=/tmp/foo.dump --format=c --compress=9 foo
> $ dropdb foo
> DROP DATABASE
> $ pg_restore --create -d test /tmp/foo.dump


I never use pg_restore... Actually did not know it was there.

I always use psql:

psql
create database restordbname;
\c restordbname
\i foo.dump
\q

Of course I also do not use blobs....