Обсуждение: Postgresql 9.3 - problem after crash server
Hi, I have problem with postgresql-9.3 in our clients. They suddenly lost power connection and all servers halt.. I repiar xfs filesystem on CentOS7 postgresql partition. Now when I want run reindex database I receive error: reindexdb -d ex200 NOTICE: table "pg_catalog.pg_class" was reindexed reindexdb: reindexing of database "ex200" failed: ERROR: invalid page in block 101 of relation base/157934/12685 I want to try run dump and save database on plaintext but when I start pg_dump it's hang... pg_dump -a -v -d ex200 > ex200.sql pg_dump: last built-in OID is 16383 pg_dump: reading extensions pg_dump: identifying extension members pg_dump: reading schemas pg_dump: reading user-defined tables pg_dump: reading user-defined functions Any ideas what can I do.. Client told me they have a backup from 2 montgs ago....
Hi,
I have problem with postgresql-9.3 in our clients. They suddenly lost
power connection and all servers halt.. I repiar xfs filesystem on
CentOS7 postgresql partition. Now when I want run reindex database I
receive error:
reindexdb -d ex200
NOTICE: table "pg_catalog.pg_class" was reindexed
reindexdb: reindexing of database "ex200" failed: ERROR: invalid page
in block 101 of relation base/157934/12685
I want to try run dump and save database on plaintext but when I start
pg_dump it's hang...
pg_dump -a -v -d ex200 > ex200.sql
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
Any ideas what can I do.. Client told me they have a backup from 2
montgs ago....
W dniu 02.08.2018 o 15:18, Thomas Poty pisze:Hi,I don't know if it will help you but you could try this:Identify " base/157934/12685"If It is an index than drop this index and rebuild itif It is a table than drop all indexes and rebuild them allHi,
How I can identify base/157934/12685 what is exactly is?
W dniu 02.08.2018 o 15:52, Thomas Poty pisze:select oid, datname from pg_database where oid =157934;157934 is the oid of your databaseif you run => select oid, datname from pg_database where oid =157934;The result should be oid = 157934 and datname = e200
oid | datname
--------+---------
157934 | e200
(1 row)12685 is the oid of your relationif you run => select * from pg_class where oid=12685;it will give you information about the relationselect * from pg_class where oid=12685;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey |
relhasrules | relhastriggers | relhassubclass | relispopulated | relfrozenxid | relminmxid | relacl | reloptions
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-
------------+----------------+----------------+----------------+--------------+------------+--------+------------
(0 rows)reindexdb -d e200
NOTICE: table "pg_catalog.pg_class" was reindexed
reindexdb: reindexing of database "e200" failed: ERROR: invalid page in block 101 of relation base/157934/12685I still have that error when I run reindexdb -d e200...
Yes, I connected to the database e200 but still select results nothing select * from pg_class where oid=12685;
I think database is corrupted...
W dniu 2018-08-02 o 16:33, Thomas Poty pisze:Are you connected to the database "e200"?Le jeu. 2 août 2018 à 16:05, Albert <forall@stalowka.info> a écrit :W dniu 02.08.2018 o 15:52, Thomas Poty pisze:select oid, datname from pg_database where oid =157934;157934 is the oid of your databaseif you run => select oid, datname from pg_database where oid =157934;The result should be oid = 157934 and datname = e200
oid | datname
--------+---------
157934 | e200
(1 row)12685 is the oid of your relationif you run => select * from pg_class where oid=12685;it will give you information about the relationselect * from pg_class where oid=12685;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey |
relhasrules | relhastriggers | relhassubclass | relispopulated | relfrozenxid | relminmxid | relacl | reloptions
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-
------------+----------------+----------------+----------------+--------------+------------+--------+------------
(0 rows)reindexdb -d e200
NOTICE: table "pg_catalog.pg_class" was reindexed
reindexdb: reindexing of database "e200" failed: ERROR: invalid page in block 101 of relation base/157934/12685I still have that error when I run reindexdb -d e200...
I am pretty sure the database is corrupted.Unfortunately, i am not able to help you more. SorryThe only advices I have are :Invest in a battery between power and server (UPS)Have backups (logical and physical) more recentMaybe community will able to help you...Good luckLe jeu. 2 août 2018 à 18:12, Forall <forall@stalowka.info> a écrit :Yes, I connected to the database e200 but still select results nothing select * from pg_class where oid=12685;
I think database is corrupted...
W dniu 2018-08-02 o 16:33, Thomas Poty pisze:Are you connected to the database "e200"?Le jeu. 2 août 2018 à 16:05, Albert <forall@stalowka.info> a écrit :W dniu 02.08.2018 o 15:52, Thomas Poty pisze:select oid, datname from pg_database where oid =157934;157934 is the oid of your databaseif you run => select oid, datname from pg_database where oid =157934;The result should be oid = 157934 and datname = e200
oid | datname
--------+---------
157934 | e200
(1 row)12685 is the oid of your relationif you run => select * from pg_class where oid=12685;it will give you information about the relationselect * from pg_class where oid=12685;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey |
relhasrules | relhastriggers | relhassubclass | relispopulated | relfrozenxid | relminmxid | relacl | reloptions
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-
------------+----------------+----------------+----------------+--------------+------------+--------+------------
(0 rows)reindexdb -d e200
NOTICE: table "pg_catalog.pg_class" was reindexed
reindexdb: reindexing of database "e200" failed: ERROR: invalid page in block 101 of relation base/157934/12685I still have that error when I run reindexdb -d e200...