Re: pg_restore and user defined types, several other pg_restore problems

Am Freitag, 16. August 2002 15:51 schrieben Sie:
> Mario Weilguni <mweilguni(at)sime(dot)com> writes:
> > Here are the problems I've encountered:
> > * pg_restore tries to create a table with ltree and ltree[] datatypes
> > before the type itself is created, so it fails.
>
> Odd; what are the OIDs of the table and the datatypes?

The table has 20517267, and the datatype has 85286596. The type was introduced later on, and added with
"alter table add". Maybe this is the problem?

>
> > * several functions are already defined in template1, so "create database
> > " will restore these functions. pg_restore will try to restore those
> > functions as well and fails. Maybe "create or replace function" can be
> > used here?
>
> No. Use pg_restore per the documentation: make an empty database for it
> to restore into (by cloning template0 instead of template1).

Oops, I did not know this. What happens if I use the -C switch of pg_restore?
The man page says:
-C

--create
Create the database before restoring into it. (When this switch
appears, the database named with -d is used only to issue the
initial CREATE DATABASE command. All data is restored into the
database name that appears in the archive.)

But does pg_restore use template0 or template1?

>
> > pg_restore: [archiver (db)] could not execute query: ERROR: data type
> > ltree[] has no default operator class for access method "gist" You must
> > specify an operator class for the index or define a default operator
> > class for the data type
>
> Are you using recent sources? As of two weeks or so ago, pg_dump should
> know how to dump operator classes.

No, I do not use 7.3cvs, this is version 7.2.1. But if this is fixed, it's not a problem for me,
now I know how to restore the database, and 7.3 should not be too far away :-)

Thanks!

Best regards,
Mario Weilguni