Thread: How to restore database to previous state

Hi!

Postgres 12 database is runing on Debian

max_wal_size = 5GB

is specified in postgres.conf  file

How to restore database to some previous state, e.q. before important data was accidently deleted.

Binary asynchrunous replication with slot is also used.

I looks like it is possible to create standby server which replicates changes using 24 hour delay and data directory
backupsin 
 
every night.
However this requires separate delayed standby server .
Is there simpler solution, e.q reverse playback of wal segments starting from current data directory contents.

Andrus. 

Hi,

Have you looked at the recovery parameter as below ?

recovery_target_time

Using this, you could achieve a PITR of the current database. If the recovery requires WAL that does not exist on disk
thenyou may have to restore it from previous night's backup. 

Please have a look at the docs for other such recovery parameters:
https://www.postgresql.org/docs/12/runtime-config-wal.html


Regards,
Nawaz

-----Original Message-----
From: Andrus <kobruleht2@hot.ee>
Sent: Monday, 11 May 2020 7:43 AM
To: pgsql-general <pgsql-general@postgresql.org>
Subject: How to restore database to previous state

Hi!

Postgres 12 database is runing on Debian

max_wal_size = 5GB

is specified in postgres.conf  file

How to restore database to some previous state, e.q. before important data was accidently deleted.

Binary asynchrunous replication with slot is also used.

I looks like it is possible to create standby server which replicates changes using 24 hour delay and data directory
backupsin every night. 
However this requires separate delayed standby server .
Is there simpler solution, e.q reverse playback of wal segments starting from current data directory contents.

Andrus.




IMPORTANT NOTE: Fuji Xerox email transmission, including any attachments, is private and confidential and may contain
legallyprivileged information. It is for the addressee's attention only. If you are not the intended recipient and have
receivedthis transmission, you must not use, edit, print, copy or disclose its contents to any person or disseminate
theinformation contained herein or hereto attached, and you must notify sender immediately by return email and delete
thistransmission from your system. Any confidentiality, privilege or copyright is not waived or lost because this
e-mailhas been sent to you in error. We have used reasonable efforts to protect this transmission from computer viruses
andother malicious software, but no warranty is made and the sender takes no responsibility for any loss or damage
incurredfrom using this email or the information contained in this email.