From: flumbador@virgilio.it
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Issue on restore / recover
Date: Mon, 8 Jan 2018 14:53:20 +0100 (CET)
Message-ID: <1623608978.635173.1515419600795@mail.virgilio.it> (raw)
In-Reply-To: <20180102153854.nldw2ouxnehtp63k@alvherre.pgsql>
References: <524817289.230206.1514560886805@mail.virgilio.it>
<20180102153854.nldw2ouxnehtp63k@alvherre.pgsql>
Hi postgresql is 9.4.9 on SUSE 11sp4 ... The backup is a filesystem backup with Networker.
I confirm that I handle WAL archiving and backup_label file was in place when I started the recovery. The table is Persistent (Relpersistence = 'p').
The postgresql was succesfully restored from another backup and now is up and running; i'm not able to provide further datails.
I try to reproduce the issue on a test environment.
I'll update the thread with further details as soon as I reproduce the issue.
Thanks to all
Best Regards
Gabriele
>
> Il 2 gennaio 2018 alle 16.38 Alvaro Herrera <alvherre@alvh.no-ip.org> ha scritto:
>
> flumbador@virgilio.it wrote:
>
> > >
> > During the restore 3 files were missing; these three files belong to a table with high transaction workload, and for sure during the backup many transactions had modified this table and those missing files. What is surprising to me is that even if the files were missing the recover phase ended successfully. I expect an error (for example file not found) raised when postgresql try to apply the wal entries related to this table and those files. After the recover I find that these three file has been created during recover but when I try to query the table I get the error:
> >
> > db4=# select count(*) from pgbench_accounts ;
> >
> > ERROR: could not read block 1999996 in file "pg_tblspc/16471/PG_9.4_201409291/16474/16593.15": read only 0 of 8192 bytes
> >
> > >
> Can you please get a backtrace from this error? I suspect that the
> problem here is that the planner is trying to estimate cardinality by
> descending a btree index, and failing because the index points to a part
> of the table that has been truncated. But if the table has been
> truncated because it's unlogged, then the index should have been
> truncated too; and if it's a permanent table, then it has been truncated
> improperly (because no index pointer to the truncated part should
> remain).
>
> What files do exist for pg_tblspc/16471/PG_9.4_201409291/16474/16593* ?
> (In particular I'm curious about whether 16593.14 exists and what size
> it is)
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: flumbador@virgilio.it, alvherre@alvh.no-ip.org, pgsql-admin@lists.postgresql.org
Subject: Re: Issue on restore / recover
In-Reply-To: <1623608978.635173.1515419600795@mail.virgilio.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox