Thread: [ADMIN] recreating point-in-time recovery when tables are in non-default tablespace
[ADMIN] recreating point-in-time recovery when tables are in non-default tablespace
From
Mark Steben
Date:
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. 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; iblk 36107, 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; iblk 36107, 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?
Appreciate your time looking at this.
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com
Re: [ADMIN] recreating point-in-time recovery when tables are innon-default tablespace
From
Laurenz Albe
Date:
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
Re: [ADMIN] recreating point-in-time recovery when tables are innon-default tablespace
From
Johannes Truschnigg
Date:
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.
Re: [ADMIN] recreating point-in-time recovery when tables are innon-default tablespace
From
Mark Steben
Date:
Thank you gentlemen, for your prompt response.
Laurenz, I have pg_start_backup and pg_stop_backup included in my base backup shell script. They ran.
Johannes, I will attempt using pg_basebackup. It looks like the tablespace-mapping option would help here.
Thanks again
On Wed, Nov 15, 2017 at 4:32 AM, Johannes Truschnigg <johannes@truschnigg.info> wrote:
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.
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com