What is point-in-time recovery in database management?

Point-in-time recovery (PITR) allows you to restore a database to a specific moment in

time, which can be crucial in scenarios where:

• Data corruption or accidental deletion occurs.
• You want to rollback to a specific moment before a harmful event.

How it works:

• First, a full backup is restored.
• Then, transaction log backups are applied, allowing you to replay changes made

after the full backup until the specified point in time.

SQL Server Example:

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak';

RESTORE LOG MyDatabase FROM DISK = 'C:\Backups\MyDatabase_log.trn'

WITH STOPAT = '2023-09-14T15:30:00'; -- Restore up to a specific

time

PostgreSQL Example:

To enable point-in-time recovery, you need to restore a base backup, then use WAL

(Write-Ahead Logging) archives to apply changes until the desired point.

• You would also set the restore_command in recovery.conf and specify the

recovery_target_time.