Thread: Fwd: Postgresql Data corruption
Hi team
Could you help us to sort this out please?
We had some hardware problems with Datacenter and could not using postgresql from restored snapshots it showing "Structure needs to be cleaning " during startup
, on centos7 did xfs_repair and postgresql started successfully. But could not select datas from it . showing below message .
This is when want to login to db with postgres user .
psql: PANIC: could not open critical system index 2662
but can logged in with another user and trying to select from some tables , showing this message :
ERROR: catalog is missing 11 attribute(s) for relid 113971
Could you help us to solve this problem? or could you give contact who can help with this?
Thanks in advance.
On Tue, Jan 14, 2020 at 2:55 PM Tulqin Navruzov <tulqin.navruzov@finnetlimited.com> wrote:
Hi teamCould you help us to sort this out please?We had some hardware problems with Datacenter and could not using postgresql from restored snapshots it showing "Structure needs to be cleaning " during startup, on centos7 did xfs_repair and postgresql started successfully. But could not select datas from it . showing below message .This is when want to login to db with postgres user .psql: PANIC: could not open critical system index 2662but can logged in with another user and trying to select from some tables , showing this message :ERROR: catalog is missing 11 attribute(s) for relid 113971Could you help us to solve this problem? or could you give contact who can help with this?Thanks in advance.
First of all, please attend to these instructions:
from what you describe looks like you need to restore from backups or promote a slave to master if you have such a setup and the slave(s) were not affected by these hardware problems.
Can't help you personally right now due to work, but try these guys:
On Tue, Jan 14, 2020 at 4:08 PM Rene Romero Benavides <rene.romero.b@gmail.com> wrote:
On Tue, Jan 14, 2020 at 2:55 PM Tulqin Navruzov <tulqin.navruzov@finnetlimited.com> wrote:Hi teamCould you help us to sort this out please?We had some hardware problems with Datacenter and could not using postgresql from restored snapshots it showing "Structure needs to be cleaning " during startup, on centos7 did xfs_repair and postgresql started successfully. But could not select datas from it . showing below message .This is when want to login to db with postgres user .psql: PANIC: could not open critical system index 2662but can logged in with another user and trying to select from some tables , showing this message :ERROR: catalog is missing 11 attribute(s) for relid 113971Could you help us to solve this problem? or could you give contact who can help with this?Thanks in advance.First of all, please attend to these instructions:from what you describe looks like you need to restore from backups or promote a slave to master if you have such a setup and the slave(s) were not affected by these hardware problems.--
On 1/14/20 12:44 PM, Tulqin Navruzov wrote: > > > Hi team > > Could you help us to sort this out please? > > We had some hardware problems with Datacenter and could not using > postgresql from restored snapshots it showing "Structure needs to be > cleaning " during startup > , on centos7 did xfs_repair and postgresql started successfully. But > could not select datas from it . showing below message . > > This is when want to login to db with postgres user . > psql: PANIC: could not open critical system index 2662 > > but can logged in with another user and trying to select from some > tables , showing this message : > > ERROR: catalog is missing 11 attribute(s) for relid 113971 > > Could you help us to solve this problem? or could you give contact who > can help with this? Do you have space to back up the $DATADIR or do you have a recent backup? Asking because that would be helpful before you try the below. I would take a look at: https://www.postgresql.org/docs/12/sql-reindex.html In section: Notes ... Things are more difficult if you need to recover from corruption of an index on a system table. ... For more information on single-user mode: https://www.postgresql.org/docs/12/app-postgres.html#APP-POSTGRES-SINGLE-USER > > Thanks in advance. -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, Jan 14, 2020 at 02:34:04PM -0800, Adrian Klaver wrote: > On 1/14/20 12:44 PM, Tulqin Navruzov wrote: >> but can logged in with another user and trying to select from some >> tables , showing this message : >> >> ERROR: catalog is missing 11 attribute(s) for relid 113971 >> >> Could you help us to solve this problem? or could you give contact who >> can help with this? > > Do you have space to back up the $DATADIR or do you have a recent backup? > > Asking because that would be helpful before you try the below. > > I would take a look at: > https://www.postgresql.org/docs/12/sql-reindex.html That's a bad data corruption pattern, and rolling in a backup is recommended (make sure that they are actually safe to use!). A REINDEX should help you to retrieve data and move it somewhere else if you don't have a backup, but nothing more as there is no guarantee that other parts of the system are not broken. As others have already pointed out, take the time necessary to read that: https://wiki.postgresql.org/wiki/Corruption But first, you should take three, slow, deep breaths. Rushing can only make things worse. -- Michael