Re: Corrupted data due to system power failure

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 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