Thread: Urgent Help required
Hi,
We are running with postgres sql 7.3.2. We were trying to create an index on a big table. The create index command ran for nearly 5 hours at which point we decided to interrupt it. Since this was interrupted, any operations attempted on the table on which the index was being created gives following error in pgsql log:
LOG: all server processes terminated; reinitializing shared memory and semaphor
es
LOG: database system was interrupted at 2009-10-16 10:44:54 BST
LOG: checkpoint record is at 150/71A0C0CC
LOG: redo record is at 150/71A0C0CC; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 1757299460; next oid: 43508941
LOG: database system was not properly shut down; automatic recovery in progress
LOG: ReadRecord: record with zero length at 150/71A0C10C
LOG: redo is not required
LOG: database system is ready
Any idea what this means and what we need to do to resolve access to this table again? We can see the next oid number increases over time. Access to all other tables in the database is fine. Any help would be greatly appreciated.
Many Thanks
Neha.
Hi,
We are running with postgres sql 7.3.2. We were trying to create an index on a big table. The create index command ran for nearly 5 hours at which point we decided to interrupt it. Since this was interrupted, any operations attempted on the table on which the index was being created gives following error in pgsql log:
LOG: all server processes terminated; reinitializing shared memory and semaphor
es
LOG: database system was interrupted at 2009-10-16 10:44:54 BST
LOG: checkpoint record is at 150/71A0C0CC
LOG: redo record is at 150/71A0C0CC; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 1757299460; next oid: 43508941
LOG: database system was not properly shut down; automatic recovery in progress
LOG: ReadRecord: record with zero length at 150/71A0C10C
LOG: redo is not required
LOG: database system is ready
Any idea what this means and what we need to do to resolve access to this table again? We can see the next oid number increases over time. Access to all other tables in the database is fine. Any help would be greatly appreciated.
Many Thanks
Neha.
--
GJ
On Fri, Oct 16, 2009 at 11:26:40AM +0100, Neha Patel wrote: > Hi, > > We are running with postgres sql 7.3.2. Whatever you thought your most urgent priority was, it's actually getting your database off of a major version of PostgreSQL, 7.3, whose end-of-life was well over a year ago. Your second most urgent priority is creating an upgrade strategy and integrating it into your development and deployment processes. I hope you have good backups. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Hi David, Many thanks for your reply. After good 10 hours of work we managed to restore from a backup. Regards Neha> -----Original Message----- From: David Fetter [mailto:david@fetter.org] Sent: 16 October 2009 17:28 To: Neha Patel Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Urgent Help required On Fri, Oct 16, 2009 at 11:26:40AM +0100, Neha Patel wrote: > Hi, > > We are running with postgres sql 7.3.2. Whatever you thought your most urgent priority was, it's actually getting your database off of a major version of PostgreSQL, 7.3, whose end-of-life was well over a year ago. Your second most urgent priority is creating an upgrade strategy and integrating it into your development and deployment processes. I hope you have good backups. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Neha Patel wrote: > > Hi, > > > > We are running with postgres sql 7.3.2. We were trying to... > well, right off the bat, thats a -really- old version. Release notes say February 2003. 7.3 was updated to 7.3.21, and we're currently on 8.4 (while still supporting 8.3, 8.2, 8.1, and 7.4). There were a LOT of fixes between 7.3.2 and 7.3.21, see the release notes for each version in between... http://www.postgresql.org/docs/current/static/release.html
On Fri, 2009-10-16 at 11:26 +0100, Neha Patel wrote: > We are running with postgres sql 7.3.2. We were trying to create an > index on a big table. The create index command ran for nearly 5 hours > at which point we decided to interrupt it. Since this was interrupted, > any operations attempted on the table on which the index was being > created gives following error in pgsql log: > LOG: all server processes terminated; reinitializing shared memory and > semaphor 1. Shut down postgresql and make a full filesystem copy of the PGDATA directory. This will ensure that anything else you do won't leave you in a worse position. 2. Upgrade to the latest version of postgresql 7.3, which is 7.3.21 3. Start up again 4. Try to fix the problem: a. see if there are any indexes on the table b. if so, drop them c. try to get a good logical backup using pg_dump. You may want to disable index scans by using "SET enable_indexscan = f;". d. If that doesn't work, you may have catalog corruption. Examine the catalogs (documented here: http://www.postgresql.org/docs/7.3/static/catalogs.html ), and look entries related to your table in pg_class and pg_index, and see if anything looks wrong. e. start from a fresh install and restore using the logical backup 5. Upgrade all data to a recent version of postgresql. You're on a really ancient version that has fallen out of official support. This may require several upgrade steps, but you should get upgraded to 8.3.8 or 8.4.1. A lot of bugs have been fixed, and as long as you are on 7.3, you will still be at serious risk. Regards, Jeff Davis
Hmm would this be a bad time to ask for PostGres 1.0 support? On Fri, Oct 16, 2009 at 1:55 PM, Jeff Davis <pgsql@j-davis.com> wrote: > On Fri, 2009-10-16 at 11:26 +0100, Neha Patel wrote: >> We are running with postgres sql 7.3.2. We were trying to create an >> index on a big table. The create index command ran for nearly 5 hours >> at which point we decided to interrupt it. Since this was interrupted, >> any operations attempted on the table on which the index was being >> created gives following error in pgsql log: > >> LOG: all server processes terminated; reinitializing shared memory and >> semaphor > > 1. Shut down postgresql and make a full filesystem copy of the PGDATA > directory. This will ensure that anything else you do won't leave you in > a worse position. > > 2. Upgrade to the latest version of postgresql 7.3, which is 7.3.21 > > 3. Start up again > > 4. Try to fix the problem: > a. see if there are any indexes on the table > b. if so, drop them > c. try to get a good logical backup using pg_dump. You may want to > disable index scans by using "SET enable_indexscan = f;". > d. If that doesn't work, you may have catalog corruption. Examine the > catalogs (documented here: > http://www.postgresql.org/docs/7.3/static/catalogs.html ), and look > entries related to your table in pg_class and pg_index, and see if > anything looks wrong. > e. start from a fresh install and restore using the logical backup > > 5. Upgrade all data to a recent version of postgresql. You're on a > really ancient version that has fallen out of official support. This may > require several upgrade steps, but you should get upgraded to 8.3.8 or > 8.4.1. A lot of bugs have been fixed, and as long as you are on 7.3, you > will still be at serious risk. > > Regards, > Jeff Davis > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >