Thread: pg_restore does not restore/recreate indices

pg_restore does not restore/recreate indices

From
davidh@anstat.com.au (David Hsu)
Date:
Hi All,

I've just started using postgres and I cannot seem to get a full
dump/restore happening with pg_restore's custom format.  It seems to
always complain that it cannot drop the indices (of course!) but
somehow stops at the error and refuse the continue.

To dump I used:
[root]#  pg_dump -Fc -Upostgres db_name -f /tmp/dump.pgc

To restore (to another server) I used:
[root]#  pg_restore -v -Fc -Upostgres -c -ddb_name dump.pgc
pg_restore: connecting to database for restore
Password:
pg_restore: dropping INDEX "ix_document_by_title"
pg_restore: [archiver (db)] could not execute query: ERROR:  index
"ix_document_by_title" does not exist
pg_restore: *** aborted because of error

It seems it tries to drop the non-existent index, and STOPs at the
error.  It's the same with or without the '-c' flag.

I cannot not seem to find anything relating to
"ignore-erros-continue".  The same problem seems to exist for the tar
format.  The only format seems to work is the default dump into
script.  However I cannot "easily" restore selective table this way.
:(

Any suggestion appreciated / welcome.

My system info:
RH 7.3
2.4.18-4smp #1 SMP Thu May 2 18:32:34 EDT 2002 i686 unknown
postgresql-server-7.2.1-2PGDG
postgresql-jdbc-7.2.1-5
postgresql-libs-7.2.1-5
postgresql-7.2.1-5

David Hsu


ps. the -i / --index= flags do not seem to work at all (and in fact
returns different messages ?!@#$@#$)

[root]# pg_restore -v -Upostgres -ddb_name -i ix_document_by_title
dump.pgc pg_restore: connecting to database for restore
Password:
pg_restore: creating TABLE actcategories
pg_restore: [archiver (db)] could not execute query: ERROR:  Relation
'actcategories' already exists
pg_restore: *** aborted because of error

[root]# pg_restore -v -Upostgres -ddb_name
--index=ix_document_by_title  dump.pgc
pg_restore: connecting to database for restore
Password:
pg_restore: implied data-only restore

[root]# pg_restore -v -Upostgres -ddb_name -iix_document_by_title
dump.pgc
pg_restore: connecting to database for restore
Password:
pg_restore: creating TABLE actcategories
pg_restore: [archiver (db)] could not execute query: ERROR:  Relation
'actcategories' already exists
pg_restore: *** aborted because of error