Thread: Fail to restore index tables by pg_dumpall

Dear Psql-bugs Group,

I am not sure if this is a bug or not but I really hope if any of you can
help me.  My problem is I logged as postgres superuser. I then backed up my
PostgreSQL database with version 6.4.2 by using pg_dumpall.

# pg_dumpall -o > db.all

Then, I initialize the template1 by running,
# initdb --template

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" );

I would be grateful if any of you could tell me how to backup and restore
back the index tables into a newer version of database in case I have done
it incorrectly.  I only test the restoration on a test machine with version
7.0.2 but in actual fact I will have to restore the database onto Postgres
6.5.3 which is bundled with the Linux Redhat 6.2.  I just want to make sure
that I know all the steps before I tried it on the machine with PostgreSQL
6.5.3.  I have presumed that there shouldn't have much difference, am I
right?

I hope to hear from any of you soon as I am really desperate to migrate all
the old data into the new machine.

Thank you.


regards,
Jessica Ord

"Jessica Ord" <jo@uk.xo.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

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@sss.pgh.pa.us]
> Sent: 14 November 2000 19:03
> To: Jessica Ord
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] Fail to restore index tables by pg_dumpall
>
>
> "Jessica Ord" <jo@uk.xo.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
>

"Jessica Ord" <jo@uk.xo.com> writes:
> I am not sure if the 'initdb --template' command has also cleared away all
> the system index tables as well.

initdb --template?  Why are you using that?  I think that's a poorly
debugged (to put it kindly) feature.  I'd recommend staying away from
it.  Assuming that you are careful not to put any user data into
template1, there is no reason ever to reinitialize template1 after the
initial initdb.

At this point I think you may have to do a full initdb to get back into
a good state.  If you have backups of everything, that seems like the
way to go rather than trying to dig your way out of the hole bit by bit.

            regards, tom lane