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.
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.
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?
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.
Please come into this loop..can you suggest this solution.please
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.
can you explain step by step..please..i have all time-line histrory..
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
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
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.