Re: [HACKERS] pg_restore and user defined types, several other pg_restore problems
Am Freitag, 16. August 2002 15:51 schrieben Sie: Mario Weilguni [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] pg_restore and user defined types, several other pg_restore problems
Hi, I'm using the ltree module and located several problems, I think not all problems are really ltree related but might be a pg_dump/pg_restore problem. 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. * 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? I'm willing to do this if it's ok. * I've gist indices on ltree[] columns, and when pg_restore tries to restore it it will report: 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 It seems those extra functions for index support are not restored. Removing the commit/end from ltree.sql and running it again (will report a lot of errors, but a few inserts as well) will make this work again. However it requires a lot of DBA assistance to restore. All other problems are solvable by using pg_restore -L and using a modified archiv index, but IMO this should work out of the box. Best regards, Mario Weilguni ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_restore and user defined types, several other pg_restore problems
Mario Weilguni [EMAIL PROTECTED] 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? * 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). 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. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
