pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql Database cant start after restore using pgBackRest
14+ messages / 4 participants
[nested] [flat]
* Postgresql Database cant start after restore using pgBackRest
@ 2023-06-25 16:53 Deo Felix <deofelix9@gmail.com>
0 siblings, 3 replies; 14+ messages in thread
From: Deo Felix @ 2023-06-25 16:53 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
I am doing backup with pgbackrest in the database server every day at
night, I was testing to restore the backup in another server, the restore
was successfully however the database cant start
*pgbackrest --stanza=data --delta --log-level-console=detail restore*
[image: image.png]
[image: image.png]
Attachments:
[image/png] image.png (19.4K, ../../CALW=F4MZsscvQ-x+fGmxSY2q4xaGk1stKPsKuts-om-ewdeR5A@mail.gmail.com/3-image.png)
download | view image
[image/png] image.png (55.7K, ../../CALW=F4MZsscvQ-x+fGmxSY2q4xaGk1stKPsKuts-om-ewdeR5A@mail.gmail.com/4-image.png)
download | view image
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-25 17:08 Doug Reynolds <mav@wastegate.net>
parent: Deo Felix <deofelix9@gmail.com>
2 siblings, 1 reply; 14+ messages in thread
From: Doug Reynolds @ 2023-06-25 17:08 UTC (permalink / raw)
To: Deo Felix <deofelix9@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Is this a case of not removing the archive command from the postgresql.conf? Seems that the initial start of the server before restore created some empty Wal logs.
Sent from my iPhone
> On Jun 25, 2023, at 12:55 PM, Deo Felix <deofelix9@gmail.com> wrote:
>
>
> I am doing backup with pgbackrest in the database server every day at night, I was testing to restore the backup in another server, the restore was successfully however the database cant start
>
> pgbackrest --stanza=data --delta --log-level-console=detail restore
>
> <image.png>
>
>
> <image.png>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-25 17:30 Ron <ronljohnsonjr@gmail.com>
parent: Deo Felix <deofelix9@gmail.com>
2 siblings, 2 replies; 14+ messages in thread
From: Ron @ 2023-06-25 17:30 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 6/25/23 11:53, Deo Felix wrote:
>
> I am doing backup with pgbackrest in the database server every day at
> night, I was testing to restore the backup in another server, the restore
> was successfully however the database cant start
>
> *pgbackrest --stanza=data --delta --log-level-console=detail restore*
>
Here's what worked for me:
SetName=20221106-000003F
pgbackrest restore \
--stanza=$SomeStanza \
--pg1-path=$NewPGData \
--log-level-file=detail \
--log-level-console=detail \
--process-max=$ThreadCnt \
--type=immediate --set=$SetName
An alternative which should also work:
RestoreTime="2022-11-06 02:00:00.0000"
pgbackrest restore \
--stanza=$SomeStanza \
--pg1-path=$NewPGData \
--log-level-file=detail \
--log-level-console=detail \
--process-max=$ThreadCnt \
--type=time --target="$RestoreTime"
> image.png
>
> *
> *
>
> image.png
In Thunderbird, at least, it's impossible to see those tiny images.
--
Born in Arizona, moved to Babylonia.
Attachments:
[image/png] image.png (19.4K, ../../08b27fdc-8caa-4498-039d-d07565b3aed9@gmail.com/3-image.png)
download | view image
[image/png] image.png (55.7K, ../../08b27fdc-8caa-4498-039d-d07565b3aed9@gmail.com/4-image.png)
download | view image
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-25 18:07 Deo Felix <deofelix9@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Deo Felix @ 2023-06-25 18:07 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
I have tried removing the archive command but still does not work
On Sun, 25 Jun 2023 at 20:30, Ron <ronljohnsonjr@gmail.com> wrote:
> On 6/25/23 11:53, Deo Felix wrote:
>
> I am doing backup with pgbackrest in the database server every day at
> night, I was testing to restore the backup in another server, the restore
> was successfully however the database cant start
>
> *pgbackrest --stanza=data --delta --log-level-console=detail restore*
>
>
> Here's what worked for me:
> SetName=20221106-000003F
> pgbackrest restore \
> --stanza=$SomeStanza \
> --pg1-path=$NewPGData \
> --log-level-file=detail \
> --log-level-console=detail \
> --process-max=$ThreadCnt \
> --type=immediate --set=$SetName
>
> An alternative which should also work:
> RestoreTime="2022-11-06 02:00:00.0000"
> pgbackrest restore \
> --stanza=$SomeStanza \
> --pg1-path=$NewPGData \
> --log-level-file=detail \
> --log-level-console=detail \
> --process-max=$ThreadCnt \
> --type=time --target="$RestoreTime"
>
> [image: image.png]
>
>
> [image: image.png]
>
>
> In Thunderbird, at least, it's impossible to see those tiny images.
>
>
> --
> Born in Arizona, moved to Babylonia.
>
Attachments:
[image/png] image.png (19.4K, ../../CALW=F4O7ZmTy+MqZX4Mu6=SA_yYcwOf1pc+cXPQkc=Zm3RfUaA@mail.gmail.com/3-image.png)
download | view image
[image/png] image.png (55.7K, ../../CALW=F4O7ZmTy+MqZX4Mu6=SA_yYcwOf1pc+cXPQkc=Zm3RfUaA@mail.gmail.com/4-image.png)
download | view image
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-25 18:07 Deo Felix <deofelix9@gmail.com>
parent: Doug Reynolds <mav@wastegate.net>
0 siblings, 0 replies; 14+ messages in thread
From: Deo Felix @ 2023-06-25 18:07 UTC (permalink / raw)
To: Doug Reynolds <mav@wastegate.net>; +Cc: pgsql-admin@lists.postgresql.org
I have tried removing the archive command but still does not work
On Sun, 25 Jun 2023 at 20:08, Doug Reynolds <mav@wastegate.net> wrote:
> Is this a case of not removing the archive command from the
> postgresql.conf? Seems that the initial start of the server before restore
> created some empty Wal logs.
>
> Sent from my iPhone
>
> On Jun 25, 2023, at 12:55 PM, Deo Felix <deofelix9@gmail.com> wrote:
>
>
>
> I am doing backup with pgbackrest in the database server every day at
> night, I was testing to restore the backup in another server, the restore
> was successfully however the database cant start
>
> *pgbackrest --stanza=data --delta --log-level-console=detail restore*
> <image.png>
>
>
> <image.png>
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-25 18:15 Deo Felix <deofelix9@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Deo Felix @ 2023-06-25 18:15 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
[image: image.png]
<2023-06-25 21:14:17.647 EAT::@:[1038849]:>LOG: restored log file
"0000000100001834000000F4" from archive
<2023-06-25 21:14:18.311 EAT::@:[1038841]:>LOG: received smart shutdown
request
<2023-06-25 21:14:18.386 EAT::@:[1038841]:>LOG: received SIGHUP, reloading
configuration files
<2023-06-25 21:14:18.507 EAT::@:[1038841]:>LOG: startup process (PID
1038849) was terminated by signal 9: Killed
<2023-06-25 21:14:18.507 EAT::@:[1038841]:>LOG: terminating any other
active server processes
<2023-06-25 21:14:18.507 EAT::@:[1038841]:>LOG: abnormal database system
shutdown
<2023-06-25 21:14:19.438 EAT::@:[1038841]:>LOG: database system is shut
down
On Sun, 25 Jun 2023 at 20:30, Ron <ronljohnsonjr@gmail.com> wrote:
> On 6/25/23 11:53, Deo Felix wrote:
>
> I am doing backup with pgbackrest in the database server every day at
> night, I was testing to restore the backup in another server, the restore
> was successfully however the database cant start
>
> *pgbackrest --stanza=data --delta --log-level-console=detail restore*
>
>
> Here's what worked for me:
> SetName=20221106-000003F
> pgbackrest restore \
> --stanza=$SomeStanza \
> --pg1-path=$NewPGData \
> --log-level-file=detail \
> --log-level-console=detail \
> --process-max=$ThreadCnt \
> --type=immediate --set=$SetName
>
> An alternative which should also work:
> RestoreTime="2022-11-06 02:00:00.0000"
> pgbackrest restore \
> --stanza=$SomeStanza \
> --pg1-path=$NewPGData \
> --log-level-file=detail \
> --log-level-console=detail \
> --process-max=$ThreadCnt \
> --type=time --target="$RestoreTime"
>
> [image: image.png]
>
>
> [image: image.png]
>
>
> In Thunderbird, at least, it's impossible to see those tiny images.
>
>
> --
> Born in Arizona, moved to Babylonia.
>
Attachments:
[image/png] image.png (19.4K, ../../CALW=F4Myz1VJw+=bC-+hTQO-4vEAb6Crt=AHL2tvo3aqLB6Lww@mail.gmail.com/3-image.png)
download | view image
[image/png] image.png (55.7K, ../../CALW=F4Myz1VJw+=bC-+hTQO-4vEAb6Crt=AHL2tvo3aqLB6Lww@mail.gmail.com/4-image.png)
download | view image
[image/png] image.png (42.5K, ../../CALW=F4Myz1VJw+=bC-+hTQO-4vEAb6Crt=AHL2tvo3aqLB6Lww@mail.gmail.com/5-image.png)
download | view image
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-26 13:08 David Steele <david@pgmasters.net>
parent: Deo Felix <deofelix9@gmail.com>
2 siblings, 1 reply; 14+ messages in thread
From: David Steele @ 2023-06-26 13:08 UTC (permalink / raw)
To: Deo Felix <deofelix9@gmail.com>; pgsql-admin@lists.postgresql.org
On 6/25/23 18:53, Deo Felix wrote:
> I am doing backup with pgbackrest in the database server every day at
> night, I was testing to restore the backup in another server, the
> restore was successfully however the database cant start
>
> *pgbackrest --stanza=data --delta --log-level-console=detail restore*
Usually this happens when you promote a standby while the primary is
still running. The promotion of the standby will create a new timeline
(in this case 2) and then push WAL to the archive on this new timeline.
When you restore, PostgreSQL will try to recover to timeline 2 (by
default) even though timeline 2 can't be reached from timeline 1 backups
that were made after the promotion.
So, you need to instruct PostgreSQL to stay on timeline 1 rather than
try to switch to timeline 2. You can do that by setting
--target-timeline=current, i.e. stay on the timeline that was current
when the backup was made.
Regards,
-David
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-26 13:55 Ron <ronljohnsonjr@gmail.com>
parent: David Steele <david@pgmasters.net>
0 siblings, 2 replies; 14+ messages in thread
From: Ron @ 2023-06-26 13:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 6/26/23 08:08, David Steele wrote:
> On 6/25/23 18:53, Deo Felix wrote:
>> I am doing backup with pgbackrest in the database server every day at
>> night, I was testing to restore the backup in another server, the restore
>> was successfully however the database cant start
>>
>> *pgbackrest --stanza=data --delta --log-level-console=detail restore*
>
> Usually this happens when you promote a standby while the primary is still
> running. The promotion of the standby will create a new timeline (in this
> case 2) and then push WAL to the archive on this new timeline.
>
> When you restore, PostgreSQL will try to recover to timeline 2 (by
> default) even though timeline 2 can't be reached from timeline 1 backups
> that were made after the promotion.
>
> So, you need to instruct PostgreSQL to stay on timeline 1 rather than try
> to switch to timeline 2. You can do that by setting
> --target-timeline=current, i.e. stay on the timeline that was current when
> the backup was made.
Did I not have this problem because I restored to a different pg-path?
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-27 05:41 Deo Felix <deofelix9@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Deo Felix @ 2023-06-27 05:41 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
In the postgresql.auto.conf i have this configs
restore_command = 'pgbackrest --pg1-path=/var/lib/postgres/main
--stanza=data archive-get %f "%p"'
recovery_target = 'immediate'
recovery_target_timeline = 'current'
But still I the database cant start
[image: image.png]
On Mon, 26 Jun 2023 at 16:55, Ron <ronljohnsonjr@gmail.com> wrote:
> On 6/26/23 08:08, David Steele wrote:
> > On 6/25/23 18:53, Deo Felix wrote:
> >> I am doing backup with pgbackrest in the database server every day at
> >> night, I was testing to restore the backup in another server, the
> restore
> >> was successfully however the database cant start
> >>
> >> *pgbackrest --stanza=data --delta --log-level-console=detail restore*
> >
> > Usually this happens when you promote a standby while the primary is
> still
> > running. The promotion of the standby will create a new timeline (in
> this
> > case 2) and then push WAL to the archive on this new timeline.
> >
> > When you restore, PostgreSQL will try to recover to timeline 2 (by
> > default) even though timeline 2 can't be reached from timeline 1 backups
> > that were made after the promotion.
> >
> > So, you need to instruct PostgreSQL to stay on timeline 1 rather than
> try
> > to switch to timeline 2. You can do that by setting
> > --target-timeline=current, i.e. stay on the timeline that was current
> when
> > the backup was made.
>
> Did I not have this problem because I restored to a different pg-path?
>
> --
> Born in Arizona, moved to Babylonia.
>
>
>
Attachments:
[image/png] image.png (45.5K, ../../CALW=F4PgGSJX-ix0NgbD4XFCezt5ZNTDvTLvJUG0yR89z08tog@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-27 08:24 David Steele <david@pgmasters.net>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: David Steele @ 2023-06-27 08:24 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
On 6/26/23 15:55, Ron wrote:
> On 6/26/23 08:08, David Steele wrote:
>> On 6/25/23 18:53, Deo Felix wrote:
>>> I am doing backup with pgbackrest in the database server every day
>>> at night, I was testing to restore the backup in another server, the
>>> restore was successfully however the database cant start
>>>
>>> *pgbackrest --stanza=data --delta --log-level-console=detail restore*
>>
>> Usually this happens when you promote a standby while the primary is
>> still running. The promotion of the standby will create a new timeline
>> (in this case 2) and then push WAL to the archive on this new timeline.
>>
>> When you restore, PostgreSQL will try to recover to timeline 2 (by
>> default) even though timeline 2 can't be reached from timeline 1
>> backups that were made after the promotion.
>>
>> So, you need to instruct PostgreSQL to stay on timeline 1 rather than
>> try to switch to timeline 2. You can do that by setting
>> --target-timeline=current, i.e. stay on the timeline that was current
>> when the backup was made.
>
> Did I not have this problem because I restored to a different pg-path?
I believe you did not have this problem because you do not have an
00000002.history file in your archive to cause a problem for your recovery.
Regards,
-David
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-27 08:26 David Steele <david@pgmasters.net>
parent: Deo Felix <deofelix9@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: David Steele @ 2023-06-27 08:26 UTC (permalink / raw)
To: Deo Felix <deofelix9@gmail.com>; Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On 6/27/23 07:41, Deo Felix wrote:
> In the postgresql.auto.conf i have this configs
> restore_command = 'pgbackrest --pg1-path=/var/lib/postgres/main
> --stanza=data archive-get %f "%p"'
> recovery_target = 'immediate'
> recovery_target_timeline = 'current'
>
> But still I the database cant start
You will need to redo your pgbackrest restore with
--target-timeline=current and then restart postgres to start recovery
with the correct settings.
Regards,
-David
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-27 10:12 Ron <ronljohnsonjr@gmail.com>
parent: David Steele <david@pgmasters.net>
1 sibling, 1 reply; 14+ messages in thread
From: Ron @ 2023-06-27 10:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 6/27/23 03:26, David Steele wrote:
> On 6/27/23 07:41, Deo Felix wrote:
>> In the postgresql.auto.conf i have this configs
>> restore_command = 'pgbackrest --pg1-path=/var/lib/postgres/main
>> --stanza=data archive-get %f "%p"'
>> recovery_target = 'immediate'
>> recovery_target_timeline = 'current'
>>
>> But still I the database cant start
>
> You will need to redo your pgbackrest restore with
> --target-timeline=current and then restart postgres to start recovery with
> the correct settings.
Is this standard procedure for an "in place" --delta restore?
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-27 14:46 David Steele <david@pgmasters.net>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: David Steele @ 2023-06-27 14:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 6/27/23 12:12, Ron wrote:
> On 6/27/23 03:26, David Steele wrote:
>> On 6/27/23 07:41, Deo Felix wrote:
>>> In the postgresql.auto.conf i have this configs
>>> restore_command = 'pgbackrest --pg1-path=/var/lib/postgres/main
>>> --stanza=data archive-get %f "%p"'
>>> recovery_target = 'immediate'
>>> recovery_target_timeline = 'current'
>>>
>>> But still I the database cant start
>>
>> You will need to redo your pgbackrest restore with
>> --target-timeline=current and then restart postgres to start recovery
>> with the correct settings.
>
> Is this standard procedure for an "in place" --delta restore?
No -- this is mitigation for the split-brain situation which was causing
the OP's recovery error.
Regards,
-David
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgresql Database cant start after restore using pgBackRest
@ 2023-06-28 12:27 Deo Felix <deofelix9@gmail.com>
parent: David Steele <david@pgmasters.net>
1 sibling, 0 replies; 14+ messages in thread
From: Deo Felix @ 2023-06-28 12:27 UTC (permalink / raw)
To: David Steele <david@pgmasters.net>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Thank you, this worked
On Tue, 27 Jun 2023 at 11:26, David Steele <david@pgmasters.net> wrote:
> On 6/27/23 07:41, Deo Felix wrote:
> > In the postgresql.auto.conf i have this configs
> > restore_command = 'pgbackrest --pg1-path=/var/lib/postgres/main
> > --stanza=data archive-get %f "%p"'
> > recovery_target = 'immediate'
> > recovery_target_timeline = 'current'
> >
> > But still I the database cant start
>
> You will need to redo your pgbackrest restore with
> --target-timeline=current and then restart postgres to start recovery
> with the correct settings.
>
> Regards,
> -David
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2023-06-28 12:27 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-25 16:53 Postgresql Database cant start after restore using pgBackRest Deo Felix <deofelix9@gmail.com>
2023-06-25 17:08 ` Doug Reynolds <mav@wastegate.net>
2023-06-25 18:07 ` Deo Felix <deofelix9@gmail.com>
2023-06-25 17:30 ` Ron <ronljohnsonjr@gmail.com>
2023-06-25 18:07 ` Deo Felix <deofelix9@gmail.com>
2023-06-25 18:15 ` Deo Felix <deofelix9@gmail.com>
2023-06-26 13:08 ` David Steele <david@pgmasters.net>
2023-06-26 13:55 ` Ron <ronljohnsonjr@gmail.com>
2023-06-27 05:41 ` Deo Felix <deofelix9@gmail.com>
2023-06-27 08:26 ` David Steele <david@pgmasters.net>
2023-06-27 10:12 ` Ron <ronljohnsonjr@gmail.com>
2023-06-27 14:46 ` David Steele <david@pgmasters.net>
2023-06-28 12:27 ` Deo Felix <deofelix9@gmail.com>
2023-06-27 08:24 ` David Steele <david@pgmasters.net>
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