Trying to dump and restore a production database with no success.
pg_dump database using the following:
pg_dump -v -b -C -Fc -U postgres -f servdb.tar.gz servdb
pg_restore with with following:
pg_restore -v -C -D template1 -Fc -U postgres servdb.tar.gz
restore fails with:
pg_restore: [archiver (db)] could not execute query: ERROR: data type
integer 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
pg_restore: ***aborted because of error
From the documentation the following query illustrates that I have four
gist acc_methods available:
select am.amname as acc_method, opc.opcname as ops_name
from pg_am am, pg_opclass opc
where opc.opcamid = am.oid
order by acc_method, ops_name;
acc_method | ops_name
-----------------------------
gist | gist_box_ops
gist | gist_int4_ops
gist | gist_poly_ops
gist | gist_timestamp_ops
The create index statement that is failing the restore:
CREATE INDEX idx_disc_loc ON order_items USING gist (disc, loc);
Has anyone battled this one before? Is there a problem with
dumping/restoring with blobs?
Thanks,
Rob
