Date: Mon, 8 Jan 2018 14:53:20 +0100 (CET)
From: flumbador@virgilio.it
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-admin@lists.postgresql.org
Message-ID: <1623608978.635173.1515419600795@mail.virgilio.it>
In-Reply-To: <20180102153854.nldw2ouxnehtp63k@alvherre.pgsql>
References: <524817289.230206.1514560886805@mail.virgilio.it>
 <20180102153854.nldw2ouxnehtp63k@alvherre.pgsql>
Subject: Re: Issue on restore / recover
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_635172_1058878442.1515419600791"
Importance: Medium
Precedence: bulk

------=_Part_635172_1058878442.1515419600791
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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 wh=
en I started the recovery. The table is Persistent (Relpersistence =3D 'p')=
.
The postgresql was succesfully restored from another backup and now is up a=
nd 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 issu=
e.
Thanks to all
Best Regards
Gabriele

>=20
>     Il 2 gennaio 2018 alle 16.38 Alvaro Herrera <alvherre@alvh.no-ip.org>=
 ha scritto:
>=20
>     flumbador@virgilio.it wrote:
>=20
>         > >=20
> >         During the restore 3 files were missing; these three files belo=
ng to a table with high transaction workload, and for sure during the backu=
p many transactions had modified this table and those missing files. What i=
s 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 thos=
e files. After the recover I find that these three file has been created du=
ring recover but when I try to query the table I get the error:
> >=20
> >         db4=3D# select count(*) from pgbench_accounts ;
> >=20
> >         ERROR: could not read block 1999996 in file "pg_tblspc/16471/PG=
_9.4_201409291/16474/16593.15": read only 0 of 8192 bytes
> >=20
> >     >=20
>     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 p=
art
>     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 trunca=
ted
>     improperly (because no index pointer to the truncated part should
>     remain).
>=20
>     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 siz=
e
>     it is)
>=20
>     --
>     =C3=81lvaro Herrera https://www.2ndQuadrant.com/
>     PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>=20

------=_Part_635172_1058878442.1515419600791
MIME-Version: 1.0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html>
<html><head>
    <meta charset=3D"UTF-8">
</head><body><p>Hi postgresql is 9.4.9 on SUSE 11sp4 ... The backup is a fi=
lesystem backup with Networker.<br>I confirm that I handle WAL archiving an=
d backup_label file was in place when I started the recovery. The table is =
Persistent (Relpersistence =3D &#39;p&#39;).<br>The postgresql was succesfu=
lly restored from another backup and now is up and running; i&#39;m not abl=
e to provide further datails.<br>I try to reproduce the issue on a test env=
ironment.<br>I&#39;ll update the thread with further details as soon as I r=
eproduce the issue.<br>Thanks to all<br>Best Regards<br>Gabriele</p><blockq=
uote><p>Il 2 gennaio 2018 alle 16.38 Alvaro Herrera &#60;alvherre@alvh.no-i=
p.org&#62; ha scritto:</p><p>flumbador@virgilio.it wrote:</p><blockquote><p=
>During the restore 3 files were missing; these three files belong to a tab=
le with high transaction workload, and for sure during the backup many tran=
sactions had modified this table and those missing files. What is surprisin=
g to me is that even if the files were missing the recover phase ended succ=
essfully. I expect an error (for example file not found) raised when postgr=
esql try to apply the wal entries related to this table and those files. Af=
ter the recover I find that these three file has been created during recove=
r but when I try to query the table I get the error:</p><p>db4=3D# select c=
ount(*) from pgbench_accounts ;</p><p>ERROR: could not read block 1999996 i=
n file &#34;pg_tblspc/16471/PG_9.4_201409291/16474/16593.15&#34;: read only=
 0 of 8192 bytes</p></blockquote><p>Can you please get a backtrace from thi=
s error? I suspect that the<br>problem here is that the planner is trying t=
o estimate cardinality by<br>descending a btree index, and failing because =
the index points to a part<br>of the table that has been truncated. But if =
the table has been<br>truncated because it&#39;s unlogged, then the index s=
hould have been<br>truncated too; and if it&#39;s a permanent table, then i=
t has been truncated<br>improperly (because no index pointer to the truncat=
ed part should<br>remain).</p><p>What files do exist for pg_tblspc/16471/PG=
_9.4_201409291/16474/16593* ?<br>(In particular I&#39;m curious about wheth=
er 16593.14 exists and what size<br>it is)</p><p>--<br>&#193;lvaro Herrera =
<a href=3D"https://www.2ndQuadrant.com/">https://www.2ndQuadrant.com/</a><b=
r>PostgreSQL Development, 24x7 Support, Remote DBA, Training &#38; Services=
</p></blockquote></body></html>
=20
------=_Part_635172_1058878442.1515419600791--