cancel
Showing results for 
Search instead for 
Did you mean: 

Is it possible to create replica DB with a base backup of old timeline?

Level 2 Adventurer

Is it possible to create replica DB with a base backup of old timeline?

Hi,

 

Currently I am wondering about the possibility of quickly create a new standby database after database failover happens in a high availability setup (for example, 1 master, 1 standby, 1 witness with EFM HA agents).

 

As far as I know, one can use pg_rewind command to make old master DB become a standby of the promoted database, or just use pg_basebackup to pull a clone from the promoted database.

 

However, if the database size is not small and old master is corrupted, users will prefer to reconstruct a standby DB from old backup file, plus all archived WAL segments.

 

SInce the timeline is different between the new standby and the backup file, is it possible to use old backup to create a standby DB in this scenario?

 

The intended environment is PGSQL/EPAS 11.

 

Thanks.

8 REPLIES 8
EDB Team Member

Re: Is it possible to create replica DB with a base backup of old timeline?

Hi Alexc168,

 

Yes it is possible you to create a replica database with a base backup of old timeline for that you need to have .history files and WAL(archive) files.

Level 2 Adventurer

Re: Is it possible to create replica DB with a base backup of old timeline?

Hi, Kapil,

 

Thanks for confirmation, I'll check out this scenario later.

 

One more further scenation is that, one might compress their archived WAL files to save backup storage space. For example, one can put compression command directly in archive_command, or use WAL compression feature of BART.

 

However, it seems that it is not easy to make pg_rewind to recognize compressed WALs; previously I quickly went through this scenario but failed with history file not found.

 

I have not thoroughly test it yet, but I think there might be some gochas in this situation.

 

Is there any further suggestion on rewinding in combination with compressed WALs?

 

Thanks.

EDB Team Member

Re: Is it possible to create replica DB with a base backup of old timeline?

Hi @alexc168,

 

Regarding the replaying of compressed WAL files, you need to uncompress them first and then start the WAL replay.

 

Another way, is that you can add the uncompress command is restore_command of recovery.conf file as well, which will uncompress the archive first and then apply it.

 

However, either we need to uncompress the archives, before replaying or while replaying using the restore_command.

 

Regards,

Sudhir

 

Silver Adventurer

Re: Is it possible to create replica DB with a base backup of old timeline?

Hi Slonkar,

 

                i have also same query..winthout rebuild how to up a standby database from backup..

Can u send me the steps hwo to do that activities...

 

FYI

   @slonkar  @alexc168  @kapil  

  we have same 1 master,1slave,1 witness so please send me asap,Thanks

 

Rgards

Diny

Silver Adventurer

Re: Is it possible to create replica DB with a base backup of old timeline?

Hi @sivamekala 

 

 Please come into this loop..can you suggest this solution.please

 

thanks

 

 

Regards

Diny

Highlighted
Level 3 Adventurer

Re: Is it possible to create replica DB with a base backup of old timeline?

Hi Din2pg,

 

You can make the standby database from the backup file, but need to have a timeline history file along with all wal archive file to get it restored to the current timeline as of master. 

 

It is better to use a recent backup for getting standby up so it needs less archive backup files. If any of the wal file/timeline file not found, the database engine tries to restore to the latest consistent point it can. If the standby's restore point is not consistent with the current master, it won't follow the master. In that case, rebuild is a must.

 

Silver Adventurer

Re: Is it possible to create replica DB with a base backup of old timeline?

Hi

 can you explain step by step..please..i have all time-line histrory..

 

FYI

       This is timeline information ,Right!!!!

[postgres@**********wal_logs]$ cat 00000001000000D1000000EA.00000028.backup
START WAL LOCATION: D1/EA000028 (file 00000001000000D1000000EA)
STOP WAL LOCATION: D1/EA000168 (file 00000001000000D1000000EA)
CHECKPOINT LOCATION: D1/EA000098
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2019-02-11 00:01:15 SGT
LABEL: pg_basebackup base backup
STOP TIME: 2019-02-11 00:01:23 SGT
[postgres@********* wal_logs]$

 

Level 3 Adventurer

Re: Is it possible to create replica DB with a base backup of old timeline?

Hi  Din2pg,

 

Here is the summarize answer to your queries.

1. Regarding recovering to old timeline

- As said earlier you need to have required wal files present with you for recovering to old timeline. recovery.conf file is the place where you can specify timeline up to which want to recover. Please find the below useful links 

https://www.postgresql.org/docs/9.1/recovery-target-settings.html

https://www.enterprisedb.com/docs/en/9.5/pg/continuous-archiving.html

 

2. If postgres unable to find require wal/timeline history file, the only option left is to rebuild standby to get it in sync with the master. Please go through below link for more information.

http://info.enterprisedb.com/rs/069-ALB-339/images/Tutorial_Configuring_EDB_Postgres_Advanced_Server...