Обсуждение: [ADMIN] recreating point-in-time recovery when tables are in non-default tablespace

Mark Steben wrote:
> I have in the past been successful in creating a hot standby environment as all my tables were in one cluster on one
disk.
> The base backup (I used tar) easily copied all tables and then the tar extract easily restored them on our standby
server.
> Since the last time this process was invoked I moved about 14 of these tables to a non-default tablespace on another
disk
> (to alleviate space issues)  Now the base backup does NOT pick up these tables and I am getting errors on the restore
side.
>
>  Here is a copy the error:
>    
> : WARNING:  page 36107 of relation pg_tblspc/1057768125/PG_9.4_201409291/16469/2231796047 is uninitialized
> Nov 14 13:46:20 ardbc01dr postgres[58399]: [572-2] @: CONTEXT:  xlog redo delete: index 1057768125/16469/2231796047;
iblk36107, heap 1663/16469/2202113342;
 
> Nov 14 13:46:20 ardbc01dr postgres[58399]: [573-1] @: PANIC:  WAL contains references to invalid pages
> Nov 14 13:46:20 ardbc01dr postgres[58399]: [573-2] @: CONTEXT:  xlog redo delete: index 1057768125/16469/2231796047;
iblk36107, heap 1663/16469/2202113342;
 
> Nov 14 13:46:21 ardbc01dr postgres[58398]: [3-1] @: LOG:  startup process (PID 58399) was terminated by signal 6:
Aborted
> 
> Am I out of luck here?  Do I need to somehow move these tables back to the original cluster?  Or is there another
way?

I'd guess that you forgot to run
  SELECT pg_start_backup('your_label');

before you copied the file and
  SELECT pg_start_backup();

afterwards.

That will cause the backup to be corrupt.

Yours,
Laurenz Albe


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Tue, Nov 14, 2017 at 02:54:06PM -0500, Mark Steben wrote:
> Good afternoon,
>
> I have in the past been successful in creating a hot standby environment as
> all my tables were in one cluster on one disk. The base backup (I used tar)
> easily copied all tables and then the tar extract easily restored them on
> our standby server. [...]

Why don't you use pg_basebackup instead? It will take care of the gory details
and also handles tablespaces and tablespace mapping for you.

--
with best regards:
- Johannes Truschnigg ( johannes@truschnigg.info )

www:   https://johannes.truschnigg.info/
phone: +43 650 2 133337
xmpp:  johannes@truschnigg.info

Please do not bother me with HTML-email or attachments. Thank you.