# Configure Replication and Enable Point-in-Time-Recovery for Cloud SQL for PostgreSQL  - GSP922

## **eOverview**

Point-in-time recovery helps you recover an instance to a specific point in time. For example, if an error causes a loss of data, you can recover a database to its state before the error occurred. A point-in-time recovery always creates a new instance; you cannot perform a point-in-time recovery to an existing instance. The new instance inherits the settings of the source instance.

In this lab, you configure and test point-in-time recovery for a Cloud SQL for PostgreSQL instance.

### What you'll do

* Enable point-in-time recovery on a Cloud SQL for PostgreSQL instance.
    
* Perform a point-in-time recovery.
    
* Confirm that the recovered database reflects an earlier point in time.
    

## **Setup and requirements**

### Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click **Start Lab**, shows how long Google Cloud resources are made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

* Access to a standard internet browser (Chrome browser recommended).
    

**Note:** Use an Incognito (recommended) or private browser window to run this lab. This prevents conflicts between your personal account and the student account, which may cause extra charges incurred to your personal account.

* Time to complete the lab—remember, once you start, you cannot pause a lab.
    

**Note:** Use only the student account for this lab. If you use a different Google Cloud account, you may incur charges to that account.

### How to start your lab and sign in to the Google Cloud console

1. Click the **Start Lab** button. If you need to pay for the lab, a dialog opens for you to select your payment method. On the left is the Lab Details pane with the following:
    
    * The Open Google Cloud console button
        
    * Time remaining
        
    * The temporary credentials that you must use for this lab
        
    * Other information, if needed, to step through this lab
        
2. Click **Open Google Cloud console** (or right-click and select **Open Link in Incognito Window** if you are running the Chrome browser).
    
    The lab spins up resources, and then opens another tab that shows the Sign in page.
    
    ***Tip:*** Arrange the tabs in separate windows, side-by-side.
    
    **Note:** If you see the **Choose an account** dialog, click **Use Another Account**.
    
3. If necessary, copy the **Username** below and paste it into the **Sign in** dialog.
    
    ```apache
    student-00-e703381c9600@qwiklabs.net
    ```
    
    You can also find the Username in the Lab Details pane.
    
4. Click **Next**.
    
5. Copy the **Password** below and paste it into the **Welcome** dialog.
    
    ```apache
    PbSSwajYesBR
    ```
    
    You can also find the Password in the Lab Details pane.
    
6. Click **Next**.
    
    **Important:** You must use the credentials the lab provides you. Do not use your Google Cloud account credentials.
    
    **Note:** Using your own Google Cloud account for this lab may incur extra charges.
    
7. Click through the subsequent pages:
    
    * Accept the terms and conditions.
        
    * Do not add recovery options or two-factor authentication (because this is a temporary account).
        
    * Do not sign up for free trials.
        

After a few moments, the Google Cloud console opens in this tab.

**Note:** To access Google Cloud products and services, click the **Navigation menu** or type the service or product name in the **Search** field.

![Navigation menu icon and Search field](https://cdn.qwiklabs.com/9Fk8NYFp3quE9mF%2FilWF6%2FlXY9OUBi3UWtb2Ne4uXNU%3D align="left")

### Target audience

The content of this hands-on lab will be most applicable to PostgreSQL DBAs. This lab is designed to give professionals hands-on experience setting up and configuring Google Cloud resources to support PostgreSQL.

## **Task 1. Enable backups on the Cloud SQL for PostgreSQL instance**

In this task you will enable scheduled backups on a Cloud SQL for PostgreSQL instance.

1. In Cloud Shell, display the instance details:
    

```apache
export CLOUD_SQL_INSTANCE=postgres-orders
gcloud sql instances describe $CLOUD_SQL_INSTANCE
```

Click the **Authorize** button if prompted.

2. In Cloud Shell, get the current UTC time in 24 hour format:
    
    ```apache
    date +"%R"
    ```
    
3. In Cloud Shell, enter the following command to enable scheduled back-ups, replacing `HH:MM` with a time that is earlier than the time that was displayed in the previous step.
    

```apache
gcloud sql instances patch $CLOUD_SQL_INSTANCE \
    --backup-start-time=HH:MM
```

**Note:** For the purposes of this lab, it is imperative that you specify a backup start time earlier than the time displayed in the previous step. This is because you do not want a back-up to start while you are running the lab.

For example if the date command shows that the current time is `14:25` you could replace `HH:MM` with `13:25`, or even `12:00`. You must make sure it is a valid time in 24 hour format or you will receive an error saying the request was invalid.

4. Confirm your changes. Note the **format** parameter, which extracts only the desired fields.
    

```apache
gcloud sql instances describe $CLOUD_SQL_INSTANCE --format 'value(settings.backupConfiguration)'
```

You will see a response similar to the following showing that backups are set for 7 days, and run at 14:00 daily in this example:

```apache
backupRetentionSettings={'retainedBackups': 7, 'retentionUnit': 'COUNT'}; enabled=True;kind=sql#backupConfiguration; startTime=14:00; transactionLogRetentionDays=7
```

Enable Backups on the Cloud SQL for PostgreSQL instance

## **Task 2. Enable and run point-in-time recovery**

In this task you will enable and configure point-in-time recovery on a Cloud SQL for PostgreSQL instance. A point-in-time recovery always creates a new instance; you cannot perform a point-in-time recovery to an existing instance. The new instance inherits the settings of the source instance.

### Enable point-in-time recovery

In this step you will enable point-in-time recovery.

* In Cloud Shell, enable point-in-time recovery:
    

```apache
  gcloud sql instances patch $CLOUD_SQL_INSTANCE \
     --enable-point-in-time-recovery \
     --retained-transaction-log-days=1
```

It will take a minute or two for this command to complete.

### Make a change to the Cloud SQL for PostgreSQL database

In this step you will add a row to the `orders.distribution_centers` table in the database. After point-in-time recovery we will expect this row to be absent from the database.

1. In Cloud Console, on the **Navigation menu** (), click **Databases** > **SQL** and click on the Cloud SQL instance named `postgres-orders`.
    
2. In Cloud Console, in the `Connect to this instance` section, click **Open Cloud Shell**. A command will be auto-populated to the Cloud Shell.
    
3. Run that command and enter the password `supersecret!` when prompted. A **psql** session will start in Cloud Shell.
    
4. In **psql**, change to the `orders` database:
    

```apache
\c orders
```

5. When prompted, enter the password `supersecret!` again.
    
6. In **psql**, get the row count of the `distribution_centers` table:
    

```apache
SELECT COUNT(*) FROM distribution_centers;
```

**Output:**

```apache
orders=> SELECT COUNT(*) FROM distribution_centers;
 count
-------
    10
(1 row)
```

7. In Cloud Shell, open a new tab (**+**), get the current UTC time in RFC 3339 format. This is the timestamp you will use for the point-in-time replica that you will create in the next task.
    

```apache
date --rfc-3339=seconds
```

You should wait for a few moments at this point to make sure that the changes you make in the next step occur after this timestamp.

**Note:** For the purposes of this lab, it is imperative that you specify a timestamp after point-in-time recovery was enabled (if not a successful back-up will be required as a starting point), but before the source instance was modified. If not your changes at the source will be replicated to the clone and the roll back won't be evident.

8. In **psql**, to add a row to the `orders.distribution_centers` table and get the new COUNT, run:
    

```apache
INSERT INTO distribution_centers VALUES(-80.1918,25.7617,'Miami FL',11);
SELECT COUNT(*) FROM distribution_centers;
```

**Output:**

```apache
orders=> SELECT COUNT(*) FROM distribution_centers;
 count
-------
    11
(1 row)
```

9. Exit **psql**:
    

```apache
\q
```

### Perform a point-in-time recovery

In this step you will make a clone of the `postgres-orders` Cloud SQL instance at a specific point in time.

* In Cloud Shell, to create a point-in-time clone, run:
    

```apache
export NEW_INSTANCE_NAME=postgres-orders-pitr
gcloud sql instances clone $CLOUD_SQL_INSTANCE $NEW_INSTANCE_NAME \
    --point-in-time 'TIMESTAMP'
```

You must replace the TIMESTAMP placeholder with the exact timestamp displayed by the `date` command you used earlier in the second Cloud Shell tab.

This TIMESTAMP must be UTC timezone, RFC 3339 format, for example, '2021-11-01 15:00:00'. The TIMESTAMP indicates the time to which you want to recover the state of the database. It should be enclosed in single quotes. The alternate RFC3339 variant is also supported: '2021-11-01T15:00:00.000Z'.

It could take 10 minutes or more for the replica to be created and ready for use. In the mean time, continue with the next task.

Enable and run point-in-time recovery

Check my progress

## **Task 3. Confirm database has been restored to the correct point-in-time**

In this task you will confirm that a row of data that was added to the original database after the point-in-time recovery timestamp is not in the cloned database.

1. In Cloud Console, on the **Overview** page, click the **All Instances** breadcrumb and then click on the Cloud SQL instance named `postgres-orders-pitr`.
    

Now you will have to wait for the replica to come online.

2. In Cloud Console, in the `Connect to this instance` section, click **Open Cloud Shell**. A command will be auto-populated to the Cloud Shell.
    
3. Run that command and enter the password `supersecret!` when prompted. A **psql** session will start in Cloud Shell.
    
4. In **psql**, change to the `orders` database:
    

```apache
\c orders
```

5. When prompted, enter the password `supersecret!` again.
    
6. In **psql**, get the row count of the `distribution_centers` table:
    

```apache
SELECT COUNT(*) FROM distribution_centers;
```

**Output:**

```apache
orders=> SELECT COUNT(*) FROM distribution_centers;
 count
-------
    10
(1 row)
```

You will see that the `distribution_centers` table in the new Cloud SQL for PostgreSQL instance has the 10 rows that it had on the source instance at the point-in-time of cloning. If your query returns 11 rows check that you have connected to the replica instance and not the original.

Cloned database does not include changes made after the recovery timestamp

---

## Solution of Lab

%[https://www.youtube.com/watch?v=_bXASA8-AG0]