Thread

1. Fail to restore index tables by pg_dumpall

   Jessica Ord <jo@uk.xo.com> — 2000-11-14T17:46:59Z

   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
   
   
   

2. Re: Fail to restore index tables by pg_dumpall

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-14T19:03:20Z

   "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
   
   

3. RE: Fail to restore index tables by pg_dumpall

   Jessica Ord <jo@uk.xo.com> — 2000-11-15T10:48:10Z

   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
   >
   
   
   

4. Re: Fail to restore index tables by pg_dumpall

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-15T15:40:01Z

   "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