Note:

• This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
• It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Backup and Restore an OCI Database with PostgreSQL

Introduction

This tutorial discusses how to backup and restore an Oracle Cloud Infrastructure (OCI) Database with PostgreSQL. Create, schedule, and restore from an OCI Database with PostgreSQL backups.

Backups can be created manually or automated through a management policy. Backups are a copy of the database data that is taken and stored remotely so that it can be used to restore the original if needed, such as after a data loss event. Backups can be created manually or automated through a management policy. Automatic backups can be scheduled to be created daily, weekly, and monthly. Automatic backups have a retention period of up to 35 days before they are deleted by the system. When you create a backup manually, you can choose whether the backup is retained indefinitely or deleted on a specific date, or after a specified number of days.

PostgreSQL is a fully managed service that saves your time on routing tasks such as create, schedule, and restore from OCI Database with PostgreSQL backups and our special feature is database optimized storage architecture which decouples a SQL transaction processing engine from storage layer, and it boosts the system resilience and performance, and it is designed to be 60 percent and less expensive than Amazon Aurora and backups can be created manually or automated through a management policy. Restoration is quite simple from anOCI Database with PostgreSQL backups.

Objectives

• Backup and restore an OCI Database with PostgreSQL

Prerequisites

• Ensure that the OCI tenancy administrator has performed the following steps.

  1. Create Virtual Cloud Network (VCN) and Subnets, go to Virtual Cloud Networks, Start VCN Wizard and Create a VCN with Internet Connectivity.

  2. Create a group for user and add the user to the group.

  3. The following Oracle Cloud Infrastructure Identity and Access Management (OCI IAM) policy statements allow a group of administrators to manage OCI Database with PostgreSQL resources. Without these policies, database system management will not function correctly.

     Allow group <group_name> to read compartments in tenancy
     
     Allow group <group_name> to manage postgres-db-systems in [ tenancy | compartment <compartment_name> | compartment id <compartment_ocid> ]
     
     Allow group <group_name> to manage postgres-backups in [ tenancy | compartment <compartment_name> | compartment id <compartment_ocid> ]
     
     Allow group <group_name> to read postgres-work-requests in [ tenancy | compartment <compartment_name> | compartment id <compartment_ocid> ]
     
     Allow group <group_name> to manage postgres-configuration in [ tenancy | compartment <compartment_name> | compartment id <compartment_ocid> ]
     
     Allow group <group_name> to manage virtual-network-family in [ tenancy | compartment <compartment_name> | compartment id <compartment_ocid> ]
     
     Allow group <group_name> to read secret-family in [ tenancy | compartment <compartment_name> | compartment id <compartment_ocid> ]
     
     Allow group <group_name> to read vaults in [ tenancy | compartment <compartment_name> | compartment id <compartment_ocid> ]
     
     Allow group <group_name> to read metrics in [ tenancy | compartment <compartment_name> | compartment id <compartment_ocid> ]
     
     Allow group <group_name> to use tag-namespaces in tenancy
     

Considerations

• It only supports the private endpoint, you cannot have a public IP address created for OCI PostgreSQL.

Environment: Oracle Cloud Infrastructure

Database Name: postgres

Version: PostgreSQL 14.9

Task 1: Get the Details of an OCI Database with PostgreSQL Backups

1. Open the navigation menu and click Databases. Under PostgreSQL, click Backups.

2. (Optional) In the List scope section, under Compartments, select a compartment.

3. Click the name of the backup for which you want to see details.

Task 2: Schedule OCI Database with PostgreSQL Backups

1. Open the navigation menu and click Databases. Under PostgreSQL, click Databases.

2. (Optional) In the List scope section, under Compartments, select a compartment.

3. Click the name of the database that you want to enable automatic backups for.

4. On the Database system details page, click More actions and select Edit management policy.

   

5. In the Edit management policy window, select Enable automatic backups and enter the following scheduling options, then click Save changes.

   • Backup frequency: Select how often a backup is created.
   • Day of the week: Select the day of the week that the backup is created.
   • Backup start time: Select the time that the backup is created.
   • Backup retention period (days): Enter a value, that the backup will be retained before it is deleted by the system.

   

Task 3: Move an OCI Database with PostgreSQL Backup to another Compartment

1. Open the navigation menu and click Databases. Under PostgreSQL, click Backups.

2. (Optional) In the List scope section, under Compartment, select a compartment.

3. Click the name of the backup that you want to move.

4. On the Backup details page, click View details and select Move resource.

   

5. In the Move resource window, select the destination compartment that you want to move the backup to and click Move resource.

   

Task 4: Backup a Database System Manually

1. Open the navigation menu and click Databases. Under PostgreSQL, click Databases.

2. (Optional) In the List scope section, under Compartments, select a compartment.

3. Click the name of the database system that you want to back up.

4. On the Database system details page, click Create backup.

   

5. In the Create PostgreSQL database backup window, enter the following information and click Create backup.

   • Backup name: Enter a name for the backup.
   • Description: Enter a description for the backup.
   • Compartment: Select a compartment to contain the backup.
   • Backup retention period (days):
     • Select number of days: Enter the number of days that the backup is retained until it is deleted.(Retention days must be an integer between 1 and 365).
     • Select end date: Select the date that the backup will be deleted.
   • Show advanced options for the backup: (Optional) If you have permissions to create a resource, then you also have permissions to apply free-form tags to that resource. To apply a defined tag, you must have permissions to use the tag namespace. For more information about tags, see Resource Tags. If you are not sure whether to apply tags, skip this option or ask an administrator. You can apply tags later.

   

Task 5: Restore from an OCI Database with PostgreSQL Backup

1. Open the navigation menu and click Databases. Under PostgreSQL, click Databases.

2. (Optional) In the List scope section, under Compartment, select a compartment.

3. Click the name of the database system that you want to edit.

4. On the Database system details page, click Restore.

   

5. In the Restore window, select the backup that you want to restore from and click Restore.

   

Task 6: Create a Database System from Backup

After the necessary OCI prerequisites and network are in place, create the database system.

1. Create or select a vault for the database administrator password and create a key and secret for the database password. For more information, see Managing Vaults, Managing Keys and Managing Vault Secrets.

2. Open the navigation menu and click Databases. Under PostgreSQL, click Databases.

3. (Optional) In the List scope section, under Compartments, select a compartment.

4. Click Create PostgreSQL Database system, Create new system from backup and Next.

   

   

5. Configure the database system.

   1. In the Database system configuration section, enter the following information.

      • Database system name: Enter a name for the database system.
      • Description: (Optional), enter a description for the database system.
      • PostgreSQL major version: Select a major version of the PostgreSQL database. The only allowed value is 14.

   2. In the Database system section, enter the following information.

      • Node count: One primary node is required and extra nodes are reserved as read replica nodes.
      • Performance tier: Select the performance tier for the database. The default value is 300,000 IOPS. For more information, see Performance Tiers.
      • Data placement:
        • Regional: Data is durably stored in multiple availability domains in the region.
        • Availability Domain-specific: Data is durably stored in the selected availability domain.

   3. Set the Hardware configuration for the database system using the following information.

      • OCPU count: Select the number of OCPUs per node.
      • Available Shapes: Select the shape of the database nodes.

   4. Set the Network configuration of the database system using the following information.

      • Virtual Cloud Network: Select the VCN in which you want to create the database. If you need to create a VCN, click create a VCN. For more inforamtion VCN and Subnet Management.
      • Subnet: Choose the private subnet of the selected VCN.
      • Private IP address: A private IP address in the subnet for the database instance. The value is optional. If an IP address is not provided, one is chosen from among the available IP addresses in the specified subnet.

   5. Enter the database system administrator credentials to the database. Administrators do not get superuser access. An OCI Database with PostgreSQL admin user can create users and roles.

      • Username: Specify the username of the administrator. This value cannot be changed after the database system is created.
      • Choose one of the following Password options:
        • Input password:
          • Password: Specify the administrator password.
          • Confirm password: Confirm the administrator password.
        • Use OCI Vault:
          • Vault: Select the vault that contains the administrator password.
          • Secret: Select the secret containing the administrator password.
          • Secret version: Select a secret version.

   6. The Management policy section gives you the opportunity to specify backup and maintenance policies for the database system.

      • Automatic backups: Select Enable automatic backups to enable automatic backups. If you do not select automatic backups, you must manage backups manually. We recommend that you enable automatic backups. After enabling automatic backups, you can set the following options:
        • Backup frequency: Select how often a backup is created.
        • Day of the week: Select the day of the week that the backup is created.
        • Backup start time: Select the time that the backup is created.
        • Backup retention period (days): Enter a value, in days, that the backup will be retained before it is deleted by the system.
      • Maintenance: Select a Maintenance type option. For more information, see Maintenance.
      • Set by Oracle: Oracle chooses the best day and time to start any essential maintenance.
      • Schedule your own maintenance: Specify the day of the week and the maintenance start time (in UTC timezone) that you want to schedule any essential maintenance activity, such as OS or instance upgrades. If you do not specify a day and time, Oracle chooses it for you.

   7. (Optional) Click Show advanced options to set configuration variables or add tags for the database system.

      • Configurations: Select an existing database configuration. For more information, see Managing OCI Database with PostgreSQL Configurations
      • Tags: If you have permissions to create a resource, then you also have permissions to apply free-form tags to that resource. To apply a defined tag, you must have permissions to use the tag namespace. For more information about tagging, see Resource Tags. If you are not sure whether to apply tags, skip this option or ask an administrator. You can apply tags later.

   

6. In Review and create section, review the database system configuration and click Create.

Related Links

• OCI Database with PostgreSQL

• Creating a Database System

Acknowledgments

• Author - Aditya Kumar Srivastawa (Principal Cloud Architect)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

---

Title and Copyright Information

Backup and Restore an OCI Database with PostgreSQL

F92248-01

January 2024

Copyright © 2024, Oracle and/or its affiliates.