pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedrecreating point-in-time recovery when tables are in non-default tablespace
4+ messages / 3 participants
[nested] [flat]
* recreating point-in-time recovery when tables are in non-default tablespace
@ 2017-11-14 19:54 Mark Steben <mark.steben@drivedominion.com>
0 siblings, 2 replies; 4+ messages in thread
From: Mark Steben @ 2017-11-14 19:54 UTC (permalink / raw)
To: pgsql-admin
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 <http://www.autorevenue.com/; | Autobase
<http://www.autobase.net/;
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 <http://www.autorevenue.com/;
<http://autobasedigital.net/marketing/DD12_sig.jpg;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: recreating point-in-time recovery when tables are in non-default tablespace
@ 2017-11-15 09:27 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Mark Steben <mark.steben@drivedominion.com>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2017-11-15 09:27 UTC (permalink / raw)
To: Mark Steben <mark.steben@drivedominion.com>; pgsql-admin
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; 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?
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: recreating point-in-time recovery when tables are in non-default tablespace
@ 2017-11-15 09:32 Johannes Truschnigg <johannes@truschnigg.info>
parent: Mark Steben <mark.steben@drivedominion.com>
1 sibling, 1 reply; 4+ messages in thread
From: Johannes Truschnigg @ 2017-11-15 09:32 UTC (permalink / raw)
To: Mark Steben <mark.steben@drivedominion.com>; +Cc: 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.
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: recreating point-in-time recovery when tables are in non-default tablespace
@ 2017-11-15 12:30 Mark Steben <mark.steben@drivedominion.com>
parent: Johannes Truschnigg <johannes@truschnigg.info>
0 siblings, 0 replies; 4+ messages in thread
From: Mark Steben @ 2017-11-15 12:30 UTC (permalink / raw)
To: Johannes Truschnigg <johannes@truschnigg.info>; +Cc: pgsql-admin
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 <http://www.autorevenue.com/; | Autobase
<http://www.autobase.net/;
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 <http://www.autorevenue.com/;
<http://autobasedigital.net/marketing/DD12_sig.jpg;
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-11-15 12:30 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-14 19:54 recreating point-in-time recovery when tables are in non-default tablespace Mark Steben <mark.steben@drivedominion.com>
2017-11-15 09:27 ` Laurenz Albe <laurenz.albe@cybertec.at>
2017-11-15 09:32 ` Johannes Truschnigg <johannes@truschnigg.info>
2017-11-15 12:30 ` Mark Steben <mark.steben@drivedominion.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox