Re: Corrupted data due to system power failure

This kind of reports is the exact reason you should never install the OS in
a different language than english. you could have at least googled for the
exact phrase "controllo di integrita fallito...." to see how other people
have solved it.

On Mon, 12 Mar 2018 at 14:50 Enzo Diletti <E(dot)Diletti(at)selettra(dot)com> wrote:

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