Date: Tue, 2 Jan 2018 12:38:54 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: flumbador@virgilio.it
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Issue on  restore / recover
Message-ID: <20180102153854.nldw2ouxnehtp63k@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <524817289.230206.1514560886805@mail.virgilio.it>
User-Agent: NeoMutt/20170306-137-4415bd-dirty (1.8.0)
Precedence: bulk

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