Fwd: Postgresql Data corruption

On Tue, Jan 14, 2020 at 2:55 PM Tulqin Navruzov <
tulqin.navruzov@finnetlimited.com> 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?

Thanks in advance.

First of all, please attend to these instructions:
https://wiki.postgresql.org/wiki/Corruption

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.

--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Can't help you personally right now due to work, but try these guys:
https://pgexperts.com/services/emergency_help/

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 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.

First of all, please attend to these instructions:
https://wiki.postgresql.org/wiki/Corruption

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.

--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

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