Search code examples
sqlpostgresqlpg-dumppostgresql-11

Postgresql pg_dump for "invalid page in block" database does not work properly

We have setup database postgresql 11 on a system. There are about 8 tables. From few days, we are facing this problem

ERROR: invalid page in block 9698 of relation base/16385/16560 SQL state: XX001

According to my research, we should set set zero_damaged_pages=on; to fix this issue. We have set and executed few select queries that works fine. Then we decided to take backup of this database via pg_dump. This job was not completed successfully but copied all records.

Now, on new system, we imported backup, we have come to know that database schema is not properly copied here due to which data duplication occured. We repeated this with another database that was fine. All things works fine including restore.

Finally, I conclude that due to error in a block of page, database was not properly backed up. Is there any such option like set zero_damaged_pages=on; in pg_dump to ignore error page but complete the back or any other solution.

Solution

  • This is not a solution to "fix" broken data, just a way to be able to dump what's left.

    And I am in no way postgres expert, but I think the question is valid and deserves at least some form of answer. And since I just used it myself to dump data from a database which run for several hours on machine with full disks and with no space available, I know it enables you to make a dump, provided you understand that some data is lost:

    1. find table name from error description - for example in pg_dump: The command was: COPY foo.some_table (col1, col2, col3) TO stdout; it's foo.some_table

    2. run psql as pgadmin on database which you were trying to dump (let's say it's my_cool_db):

      $ sudo su - postgres
psql my_cool_db

    3. "fix" broken pages:

      my_cool_db=# SET zero_damaged_pages = on;
SET
my_cool_db=# VACUUM FULL foo.some_table;
WARNING:  invalid page in block 54809 of relation base/48461770/48462009; zeroing out page
WARNING:  invalid page in block 54810 of relation base/48461770/48462009; zeroing out page
...
VACUUM
my_cool_db=# REINDEX TABLE foo.some_table;

    4. log out of psql and log out of pgadmin

    5. dump your db (or repeat for other broken tables)

    Source: https://lxadm.com/PostgreSQL:_ERROR:_invalid_page_header_in_block_13760_of_relation_base/16995/67484