Обсуждение: Catalog corruption
Hi,
I sent already an email about this topic to pgsql-admins but I think that it might be more relevant to this mailing list. I'm trying to investigate a corruption that happened on a machine of one of our clients.
A little background :
-os Centos 6.6
-PostgreSQL v9.2.5
-I was asked to understand why the corruption happened and how can we fix it. I copied the data dir to my machine on my local pc and started investigate it. They realized that there is a problem when they tried to backup the database via pg_dump. The error they got :
pg_dump: query returned 2 rows instead of one: SELECT tableoid, oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = datdba) AS dba, pg_encoding_to_char(encoding) AS encoding, datcollate, datctype, datfrozenxid, (SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, shobj_description(oid, 'pg_database') AS description FROM pg_database WHERE datname = 'db1'
So I checked and indeed there were duplicated rows in the pg_roles / pg_database tables. Moreover, there were multiple values in all the system catalogs !
The first think I did as Tom Lane suggested is to upgrade to v9.2.24. Afterwards I vacuumed all the databases but nothing helped. I tried to reindex the databases but I got the next error :
2018-08-28 21:50:03 +08 db2 24360 ERROR: could not access status of transaction 32212695
2018-08-28 21:50:03 +08 db2 24360 DETAIL: Could not open file "pg_subtrans/01EB": No such file or directory.
So I tried to analyze all the system catalogs manualy one by one :
select 'VACUUM ANALYZE pg_catalog.'||table_name from information_schema.tables where table_schema = 'pg_catalog' and table_type <> 'VIEW';
?column?
---------------------------------------------------
VACUUM ANALYZE pg_catalog.pg_statistic
VACUUM ANALYZE pg_catalog.pg_type
VACUUM ANALYZE pg_catalog.pg_authid
VACUUM ANALYZE pg_catalog.pg_proc
VACUUM ANALYZE pg_catalog.pg_class
VACUUM ANALYZE pg_catalog.pg_user_mapping
.....
and I got error only on the next system table :
afa=# ANALYZE pg_catalog.pg_shdepend;
ERROR: could not access status of transaction 32212695
DETAIL: Could not open file "pg_subtrans/01EB": No such file or directory.
When I try to reindex the table I'm getting the same error.
Solutions I tried :
-vacuum all the databases
-reindex system db_name - > raise the same error (could not access status...)
-upgrade to v9.2.24 (still got corruption).
-Use zero_damaged_pages=0
-generate an empty subtransaction file with dd
-Trying to reindex system indexes in single mode -> raise same error.
*Tried to delete the duplicated values in some of the system tables by the ctid value, but nothing is deleted.
Any idea how can I continue ?
On Tue, Aug 28, 2018 at 7:36 AM, Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote: > Afterwards I vacuumed all the databases but nothing helped. I tried to > reindex the databases but I got the next error : > > 2018-08-28 21:50:03 +08 db2 24360 ERROR: could not access status of > transaction 32212695 > 2018-08-28 21:50:03 +08 db2 24360 DETAIL: Could not open file > "pg_subtrans/01EB": No such file or directory. > Are you sure you created a checkpoint before copying the data directory over to your PC?
Yes indeed.
I took a cold backup - shutdown the database and copy all the data dir.
בתאריך יום ג׳, 28 באוג׳ 2018 ב-22:34 מאת Asim R P <apraveen@pivotal.io>:
On Tue, Aug 28, 2018 at 7:36 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> wrote:
> Afterwards I vacuumed all the databases but nothing helped. I tried to
> reindex the databases but I got the next error :
>
> 2018-08-28 21:50:03 +08 db2 24360 ERROR: could not access status of
> transaction 32212695
> 2018-08-28 21:50:03 +08 db2 24360 DETAIL: Could not open file
> "pg_subtrans/01EB": No such file or directory.
>
Are you sure you created a checkpoint before copying the data
directory over to your PC?
>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes: Mariel> Hi, Mariel> I sent already an email about this topic to pgsql-admins but I Mariel> think that it might be more relevant to this mailing list. The -hackers mailing list is about the development of postgresql. Mariel> I'm trying to investigate a corruption that happened on a Mariel> machine of one of our clients. A good place to get help with that is actually the IRC channel (#postgresql on chat.freenode.net, or http://webchat.freenode.net for the web interface - note that currently you need to register a nickname, see http://freenode.net/kb/answer/registration because of some annoying spammers, but we can help you with that if you just go ahead and try and join the channel anyway). -- Andrew (irc:RhodiumToad)
Hi Andrew,
what is the name of the channel ?
Thanks , Mariel.
בתאריך יום ד׳, 29 באוג׳ 2018 ב-14:31 מאת Andrew Gierth <andrew@tao11.riddles.org.uk>:
>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
Mariel> Hi,
Mariel> I sent already an email about this topic to pgsql-admins but I
Mariel> think that it might be more relevant to this mailing list.
The -hackers mailing list is about the development of postgresql.
Mariel> I'm trying to investigate a corruption that happened on a
Mariel> machine of one of our clients.
A good place to get help with that is actually the IRC channel
(#postgresql on chat.freenode.net, or http://webchat.freenode.net for
the web interface - note that currently you need to register a nickname,
see http://freenode.net/kb/answer/registration because of some annoying
spammers, but we can help you with that if you just go ahead and try and
join the channel anyway).
--
Andrew (irc:RhodiumToad)
>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes: Mariel> Hi Andrew, Mariel> what is the name of the channel ? The name of the channel is #postgresql (including the # character) -- Andrew (irc:RhodiumToad)