Geek asks and answers

How to repair a damaged system table in PostgreSQL

I just tried pg_upgrade on a fairly large PostgreSQL database cluster from version 8.3.0 to version 9.0.4. Everything looked as if everything would work well until a new scheme was created in the target cluster. He died trying to create a group role twice for some reason.

Having looked at all the scenarios, it was obvious that he duplicated the group role 4 times. I returned the database 8.3.0, and it was very obvious that the pg_authidrow was repeated in the table .

I tried to bring the database into single user mode to try REINDEX TABLE pg_authid. This failed when trying to create a new index with duplicate values.

I tried to remove the role of attackers. This removed one of the four lines in pg_authid, but it just seemed like it was even more confusing.

I saw a mention that a complete vacuum on the table can restore such corruption, but I have little hope that it works. So, while the data is being restored, I will fish for ideas.

+3
source share
2 answers

Did you study the pg_upgrade modification to correct incorrect statements, as Sean suggested, before continuing with the upgrade? I believe that dump / restore occurs in pg_upgrade for system tables.

+1
source

pg_upgrade steps? , datadir , ... ?: (

A REINDEX TABLE ,

# From old database
pg_dump -t my_problem_table(s) ... > my_screwed_up_data.sql
pg_dump -T my_problem_table(s) ... > my_not_screwed_up_data.sql


# Fix whatever isn't right
${EDITOR} my_screwed_up_data.sql

# In to a fresh database instance
cat my_screwed_up_data.sql | psql
cat my_not_screwed_up_data.sql | psql

. , , , . , , .

+2

More articles:


All Articles