GSP922

Overview

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).

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

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.

   {{{user_0.username | "Username"}}}

   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.

   {{{user_0.password | "Password"}}}

   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.

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:

Click the Authorize button if prompted.

2. In Cloud Shell, get the current UTC time in 24 hour format:

   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.

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

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:

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:

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:

5. When prompted, enter the password supersecret! again.

6. In psql, get the row count of the distribution_centers table:

Output:

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.

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.

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

Output:

9. Exit psql:

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:

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.

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:

5. When prompted, enter the password supersecret! again.

6. In psql, get the row count of the distribution_centers table:

Output:

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.

Congratulations!

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

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated April 23, 2024

Lab Last Tested Dececember 06, 2023

Copyright 2025 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.