Corrupted data due to system power failure

From: Enzo Diletti <E(dot)Diletti(at)selettra(dot)com>
To: "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Michele Santucci <m(dot)santucci(at)selettra(dot)com>
Subject: Corrupted data due to system power failure
Date: 2018-03-12 13:27:57
Message-ID: 90a69462d5894ea09d83e7ba746c4e41@SELEXCH1.selettra.local
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

A description of what you are trying to achieve and what results you expect: we'd like to recover the more data possible from a damaged psql database

PostgreSQL version number you are running: PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit

How you installed PostgreSQL: Installed via APT, version 9.6+181+deb9u1 (/var/lib/apt/lists/ftp.it.debian.org_debian_dists_stretch_main_binary-amd64_Packages) (/var/lib/apt/lists/security.debian.org_debian-security_dists_stretch_updates_main_binary-amd64_Packages)

Changes made to the settings in the postgresql.conf file:
name | current_setting | source
----------------------------+------------------------------------------+----------------------
application_name | psql | client
client_encoding | UTF8 | client
cluster_name | 9.6/main | configuration file
DateStyle | ISO, DMY | configuration file
default_text_search_config | pg_catalog.italian | configuration file
dynamic_shared_memory_type | posix | configuration file
external_pid_file | /var/run/postgresql/9.6-main.pid | configuration file
ignore_system_indexes | on | configuration file
lc_messages | it_IT.UTF-8 | configuration file
lc_monetary | it_IT.UTF-8 | configuration file
lc_numeric | it_IT.UTF-8 | configuration file
lc_time | it_IT.UTF-8 | configuration file
listen_addresses | * | configuration file
log_line_prefix | %m [%p] %q%u(at)%d | configuration file
log_timezone | localtime | configuration file
max_connections | 100 | configuration file
max_stack_depth | 2MB | environment variable
port | 5432 | configuration file
shared_buffers | 128MB | configuration file
ssl | on | configuration file
ssl_cert_file | /etc/ssl/certs/ssl-cert-snakeoil.pem | configuration file
ssl_key_file | /etc/ssl/private/ssl-cert-snakeoil.key | configuration file
stats_temp_directory | /var/run/postgresql/9.6-main.pg_stat_tmp | configuration file
TimeZone | localtime | configuration file
unix_socket_directories | /var/run/postgresql | configuration file
zero_damaged_pages | on | configuration file

Operating system and version: Linux sbiron 4.9.0-4-amd64 #1 SMP Debian 4.9.65-3 (2017-12-03) x86_64 GNU/Linux

What program you're using to connect to PostgreSQL: command line tool and pgAdmin4

Is there anything relevant or unusual in the PostgreSQL server logs?: there are many errors also due to a read-only mount of the filesystem after the server rebooted; we can still read "incomplete boot packet" (I don't the exact text because we have italian language text, that says "pacchetto di avvio incompleto").

For questions about any kind of error:

What you were doing when the error happened / how to cause the error: a system power failure happened. When it happened, none was working on the database because the working day was already finished from some hour. No scheduled job was running.

The EXACT TEXT of the error message you're getting, if there is one: when we had the info early this morning, postgres failed to start. We tried to run pg_resetxlog, after that psql was able to start but we cannot access the data. Tried to reindex, vacuum analyze: finally we can access to data, but a very few part of them. Then, we added ignore_system_index=on and zero_damaged_pages=on and we tried to reindex but it fails. When we try to rum pg_dumpall we have:
pg_dump: ATTENZIONE: using index "pg_toast_2618_index" despite IgnoreSystemIndexes
RIGA 1: ....tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog...
^
pg_dump: ATTENZIONE: using index "pg_toast_2619_index" despite IgnoreSystemIndexes
pg_dump: ATTENZIONE: using index "pg_toast_2618_index" despite IgnoreSystemIndexes
RIGA 1: ....tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog...
^
pg_dump: ATTENZIONE: using index "pg_toast_2619_index" despite IgnoreSystemIndexes
pg_dump: ATTENZIONE: using index "pg_toast_2619_index" despite IgnoreSystemIndexes
pg_dump: ATTENZIONE: using index "pg_toast_2618_index" despite IgnoreSystemIndexes
RIGA 1: ....tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog...
^
pg_dump: controllo di integrit? fallito, tabella con OID 17909 proprietaria della sequenza con OID 17907 non trovata
pg_dumpall: pg_dump fallito per il database "smartboard_users_op", in uscita

Some other details about the server: it is runnig on Hyper-V (6.3.9600.16384 on Win Server 2012 R2 Standard) as the only virtual machine on the host with 80GB vhd dinamic disk, 2 vCPU and 2GB RAM.

Thanks in advance

Enzo Diletti

Attachment Content-Type Size
postgresql-9.6-main.log text/x-log 955.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2018-03-12 13:39:40 Re: FOR EACH ROW triggers on partitioned tables
Previous Message David Steele 2018-03-12 13:24:21 Re: [HACKERS] Commitfest 2018-9 duplicate patch deletion request.