Thread: Postgresql Database cant start after restore using pgBackRest

 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

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

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.

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

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

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.

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