PITR

Am 22.11.23 um 09:11 schrieb Rajesh Kumar:

Hi

A person dropped the table and don't know time of drop.

How do I do PITR. Backup strategy is weekly full backup and daily
differential backup. Using pgbackrest.

Also. In future how do i monitor time of drop commands.

Monitoring DDL commands works fine using event triggers

https://www.postgresql.org/docs/current/event-triggers.html

Code of event triggers may even fail a command by raising an exception
preventing the command becoming effective.

Regards,

Holger

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

On Wed, 2023-11-22 at 13:41 +0530, Rajesh Kumar wrote:

A person dropped the table and don't know time of drop.

How do I do PITR. Backup strategy is weekly full backup and daily differential backup. Using pgbackrest.

Also. In future how do i monitor time of drop commands. 

You can monitor that by setting "log_statement = 'ddl'".

Yours,
Laurenz Albe

On Wed, Nov 22, 2023 at 3:12 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:

Hi

A person dropped the table and don't know time of drop.

Revoke his permission to drop tables?

How do I do PITR. Backup strategy is weekly full backup and daily
differential backup. Using pgbackrest.

Also. In future how do i monitor time of drop commands.

Set log_statement to 'all' or 'ddl', and then grep the log files for "DROP
TABLE ".

Am 22.11.23 um 11:50 schrieb Ron Johnson:

On Wed, Nov 22, 2023 at 3:12 AM Rajesh Kumar
<rajeshkumar.dba09@gmail.com> wrote:

How do I do PITR. Backup strategy is weekly full backup and daily
differential backup. Using pgbackrest.

Also. In future how do i monitor time of drop commands.

https://blog.hagander.net/locating-the-recovery-point-just-before-a-dropped-table-230/

Andreas

--
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com