Re: Issue on restore / recover

How was the backup taken, and what platform are you on?

Are these tables in unlogged mode? Check pg_class.relpersistence to find
out.

On Fri, Dec 29, 2017 at 10:21 AM, <flumbador(at)virgilio(dot)it> wrote:

> Hello
>
> I've a question about postgresql restore / recover.
>
> I have restored and recovered a Postgresql 9.4.9 from an hot backup. The
> backup is a filesystem copy taken while the Postgrsql is in backup mode (I
> mean between start and stop backup).
>
> 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
>
> This error confirm that my database is corrupted! The question is: why
> during the recover phase Postgresql doesn't throw any errors? I think that
> is better to know immediately that we have restored our database from a
> corrupted backup rather then discover the issue after maybe a long
> time when a query is executed on the corrupted table.
>
> Thanks in advance
>
> Regards
>
> Gabriele
>

--

*Ben Primrose | Postgres DBA | TraceLink Inc.*400 Riverpark Dr.
Floor 2, Suite 200
North Reading, MA 01864
o: +1.978.396.6507
e: bprimrose(at)tracelink(dot)com