Corrupted data due to system power failure - Mailing list pgsql-hackers
| From | Enzo Diletti |
|---|---|
| Subject | Corrupted data due to system power failure |
| Date | |
| Msg-id | 90a69462d5894ea09d83e7ba746c4e41@SELEXCH1.selettra.local Whole thread Raw |
| Responses |
Re: Corrupted data due to system power failure
|
| List | 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@%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 IgnoreSystemIndexesRIGA 1: ....tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog...^pg_dump: ATTENZIONE: using index "pg_toast_2619_index" despite IgnoreSystemIndexespg_dump: ATTENZIONE: using index "pg_toast_2618_index" despite IgnoreSystemIndexesRIGA 1: ....tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog...^pg_dump: ATTENZIONE: using index "pg_toast_2619_index" despite IgnoreSystemIndexespg_dump: ATTENZIONE: using index "pg_toast_2619_index" despite IgnoreSystemIndexespg_dump: ATTENZIONE: using index "pg_toast_2618_index" despite IgnoreSystemIndexesRIGA 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 trovatapg_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
pgsql-hackers by date: