RE: Fail to restore index tables by pg_dumpall

Dear Tom,

Thank you for your reply. It is strangely there was no error message at all
when I restore the backup as if everything ran smoothly.

I am not sure if the 'initdb --template' command has also cleared away all
the system index tables as well. This is because I find out all the system
index tables are no longer exists when I run the '\dS' command to list
system tables and indexes. I forgot to run this command before I execute
the 'initdb --template' command so I am not sure what is the cause. I read
from other user's comment from the Internet that one should run this
'initdb' to clear the database. I wonder is that true?

I am not sure if I should just drop all the old testing databases in the new
machine except template1 database before restoring all the on-line databases
onto the new machine. Please advise if possible.

Many many thanks.

regards,
Jessica

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 14 November 2000 19:03
> To: Jessica Ord
> Cc: pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] Fail to restore index tables by pg_dumpall
>
>
> "Jessica Ord" <jo(at)uk(dot)xo(dot)com> writes:
> > After that, I restored this file onto a newer version of
> PostgreSQL 7.0.2 by
> > the below command.
>
> > # psql -e template1 < db.all
>
> > However, I login to the database and I noticed that all the tables are
> > restored properly except all my index tables are missing.
>
> > I then scrutinized the file generated by the pg_dumpall. For
> example, the
> > following statement is found in the file but it didn't seem to
> create the
> > 'status_index' table.
>
> > CREATE INDEX "status_index" on "ribs_log" using btree ( "status"
> > "int4_ops" );
>
> The only theory I can come up with is that the restore script failed
> before it got as far as the CREATE INDEX commands. Did you look for
> any error messages that might've come out of the psql run?
>
> regards, tom lane
>