Thread: 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 apg_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 willtry to restore those functions as well and fails. Maybe "create or replace function" can be used here? I'm willing todo 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" Youmust 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 itagain (will report a lot of errors, but a few inserts as well) will make this work again. However it requires a lot ofDBA assistance to restore. All other problems are solvable by using "pg_restore -L" and using a modified archiv index, but IMO this should work outof the box. Best regards,Mario Weilguni
This is known problem with pg_dump.
Don't remember if it's solved in 7.3
Regards,
Oleg
On Fri, 16 Aug 2002, Mario Weilguni wrote:
> Hi,
>
> I'm using the ltree module and located several problems, I think not all problems are really ltree related but might
bea 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
willtry 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
accessmethod "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
runningit 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
outof the box.
>
> Best regards,
> Mario Weilguni
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Mario Weilguni <mweilguni@sime.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?
> * several functions are already defined in template1, so "create database " will restore these functions. pg_restore
willtry 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
accessmethod "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
Am Freitag, 16. August 2002 15:51 schrieben Sie:
> Mario Weilguni <mweilguni@sime.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
databasenamed with -d is used only to issue the initial CREATE DATABASE command. All data is restored
intothe 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