Point-in-time Recovery for OCI Database with PostgreSQL

Point-in-time recovery causes PostgreSQL to retain write-ahead logs (WAL) that capture database changes. The feature takes periodic backups of the data volume and the WAL volume.

Point-in-time backups appear in the Backups page in the same manner as regular backups. For more information, see Listing Backups.

You enable point-in-time recovery by applying a point-in-time recovery management policy to your database system. An example would be a standard policy with a retention window of 10 days. After point-in-time-recovery is enabled and the database system returns to an Active state, point-in-time recovery backups begin automatically. Your database system shows an active recovery window, indicating the time range available for point-in-time restores.

A point-in-time restore creates a new DB system restored to your chosen timestamp. You provide the following:

• The source database system identifier.
• The target recovery timestamp ("time to restore").

After the new database system is created and becomes active, you can connect to it and validate the database state.

Note the following regarding point-in-time recovery

• You can only restore within the configured "restore days" window.
• The restoration process creates a new database system. You must plan the networking, access, and naming for this database system.
• For best results, select a time just before the unwanted change occurred.
• Ensure you have appropriate permissions and that your environment (VCN, security rules, certificates, and so forth) supports connecting to the restored system.

To enable and use point-in-time recovery, the following prerequisites apply:

• You have access to the compartment containing the database system.
• You have permissions to perform the following tasks:
  • Update an existing PostgreSQL database system to enable point-in-time recovery.
  • Create a new PostgreSQL database system to perform a restoration.
  • View backups and recovery information.
• You know the required retention window. For example, "restore days = 10" means you can restore to any point within the last 10 days).
• If connecting using Transport Layer Security (TLS), ensure you have the required CA certificate and network access to the database system endpoint (VCN/security rules).

Point-in-time recovery is an option you can select when you create a database system. For more information, see Creating a Database System.

You can also enable point-in-time recovery for an existing database system. For more information, see Editing the Management Policy of a Database System.

In both cases, when you enable point-in-time recovery, you must specify the number of days the database system's data is available for recovery. The maximum number of days is 35.

You can create a new database system using a point-in-time source as the base. For more information, see Creating a Database System from Point-in-Time Source.

You can restore an existing database system using a point-in-time source as the base. For more information, see Restoring from a Point-in-Time Source.

If you don't see a recovery window, confirm the following:

• The point-in-time recovery feature is enabled and in an active state.
• A point-in-time recovery policy is set (not "None").
• Backups have started and enough time has elapsed to establish a window.

If restore fails or takes longer than expected:

• Verify the timestamp is within the recovery window.
• Ensure quotas and limits allow creating a new database system.