How to Backup Google Cloud SQL for PostgreSQL and Restore on Your Own Server

Google Cloud SQL for PostgreSQL is a fully managed database service to create and manage PostgreSQL relational databases using a Google Cloud Console. Google Cloud for PostgreSQL gives you more time to spend on developing and enhancing your application instead of maintaining the infrastructure running your database. Google Cloud SQL services take off the need to invest your resources time to manage hardware, operating systems, database patches and installation, backup services, high availability, and scalability.

In this blog, you will get to know the highlights of Google Cloud SQL for PostgreSQL, a tour of its backup methods, and how to backup your Cloud SQL for PostgreSQL using Backup Ninja.

Google Cloud SQL for PostgreSQL Key Highlights and Updates

Following are the key highlights of Google Cloud SQL for PostgreSQL extracted from the extensive Google Cloud SQL for PostgreSQL feature list:

• Create and manage PostgreSQL instances using Google Cloud Console.
• Instances are available in the US, EU, Asia, and Australia.
• Supports PostgreSQL client-server protocol and standard PostgreSQL connectors.
• Support for PostgreSQL 13, 12 (default), 11, 10, and 9.6 versions.
• Supports many PostgreSQL extensions.

Also, following are the latest disaster recovery features for your Google Cloud SQL for PostgreSQL to ensure your PostgreSQL data is available and protected always:

• Cross-region replication: With cross-region replication, you will get to have a near real-time copy of your PostgreSQL data in any region around the world other than your primary region with full security. Cross-region replication can be set up easily without any extra network configuration.
• Point-in-Time Recovery for PostgreSQL: Google Cloud stores transaction logs for the past seven days and you can select the recovery point at millisecond within the seven days.
• Export your data in a portable format: You can export your portable PostgreSQL data to Google Cloud Storage (GCS), or any other object storage; on-premises or on any other cloud provider.
• High availability: When high availability is enabled, google cloud will do a health check on your database instances and setup synchronous replication between zones so Google can do automatic failover on your behalf.

Google Cloud SQL for PostgreSQL Backups

Google Cloud SQL allows you to take automated and on-demand backups for your PostgreSQL instances. Cloud backups are incremental and only change after your last full backup. Once the oldest backup is deleted, the next oldest backup size will increase, to always retain a copy of the full backup. On-demand backup is for you to take full PostgreSQL backup at any time you want instead of waiting for the backup window. On-demand backups will not be deleted automatically, so you should appropriately delete these backups to avoid storage consumption. Automated backups start the instance backup in a 4-hour backup window period and runs daily if your instance is running in the past 36 hours. The seven most recent backups are retained.

Google Cloud SQL for PostgreSQL Backups Using Backup Ninja

Google Cloud SQL for PostgreSQL allows you to have automated or on-demand database instance backup with preset configurations in the Google Cloud Console. If you would like to have preset configurations for backups according to your company backup and recovery policy, then you can use backup services like Backup Ninja, Solarwind, or Veeam.

Backup Ninja allows you to take PostgreSQL backups without any scripts using a simple web interface. Install the Backup Ninja agent on your PostgreSQL database server, and the agent will manage your PostgreSQL backups in an automated way. You can set the backup frequency, enable compression and encryption when you schedule PostgreSQL backups. Also, you can easily monitor the status of your backups and other details like storage via the Backup Ninja monitoring dashboard.

Installing the Backup Ninja Agent on Your Server

To schedule backups on Backup Ninja, you will need to install the agent on your backup server. This agent will then communicate with your database and take backups according to the backup schedule you have created. For security purposes, Backup Ninja does not store any security keys and uses advanced encryption algorithms to protect your backup. Following are the simple steps to install the Backup Ninja agent on your server.

Step 1: Set your backup target

In the first step, you need to decide the type of backup you would like to have. You can either choose to backup and restore or only the backup option where you can always edit and add in the restore option. Next, select the database technology and version. The version selection is enabled when you choose the database server type here.

Step 2: Creating a Database User

Backup Ninja requires a database user with the privilege to backup and restore the database. For a managed database, click on the checkbox below to use the existing user to manage the database backups. Fill in the hostname, which could be the endpoint or an IP address, and the password, and proceed to the next step.

Step 3: Install the Backup Ninja Agent

To install the agent, you will need to copy and execute the installation script on the backup server.

Step 4: Server Discovery

Once the installation is complete, the server will be discovered automatically and displayed on the screen.

You will see the discovered server added to the list of servers on the Servers listing page.

The next sections show you how to schedule backups for a Google Cloud SQL database instance.

Scheduling Google SQL Database Backup Using Backup Ninja

Step 1: Complete the schedule details

Give a name for the backup schedule and select the server you want to backup. The backup method will list the backup tools installed in the server automatically. Backup type allows you to choose either a full or partial backup type. Select the mysqldump and partial backup type to backup the selected Google SQL database from the list.

Step 2: Configure the backup storage

Next select where you would like to store the backup. You can have a combination of local and cloud storage configured on the same screen. If you would like to add more storage locations, simply click on the Add New Storage button and fill in the next storage location. Retention here is the duration of how long you would want to keep the backup.

Step 3: Backup name, compression, and encryption setting

Give a name to your backup, or you can choose to leave it at the default setting. If you are concerned about the backup size, enable the compression toggle and the backup will be compressed using the gzip compression. You can also enable the encryption to have encrypted backups.

Step 4: Set the backup frequency.

The last step is to set the frequency of the backup task, and the backups will be scheduled to run according to the database server settings.

Check the created schedule on the scheduled listing. Click on the play button to start or pause the schedule.

You can view the details of the schedule, including the backup information when you click on the View link.

You can also view the status of the backups completed for this schedule at the bottom of the page. Here, the backup listing will show you the status of the backup, the storage location path, and you can restore the selected backup at any point in time.

Conclusion

Google Cloud SQL for PostgreSQL allows you to spin up the PostgreSQL database instance either for development or testing using the Google Cloud Console. It also offers two backup methods to backup PostgreSQL database instances. However, the backup management configuration is pre-set in the Google platform. If you would like to have backup management for your PostgreSQL databases according to your backup and disaster recovery strategy, Backup Ninja is a good tool for you to consider. The tool allows you to create and restore PostgreSQL backups to your preferred location. Backup Ninja allows you to store your PostgreSQL backups on-premises or any S3 compliant object storage of your choice.