Google Cloud PostgreSQL: Setting up Cloud SQL for PostgreSQL Made Easy 101

PostgreSQL is a powerful, open-source object-relational database system. It has more than 15 years of active development phase and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. Cloud SQL is a Google Cloud Platform service. It is a database-as-a-service (DBaaS) or a cloud database service. The data in the database is stored in the cloud, using the cloud service provider’s infrastructure, and access is provided by the Google Cloud Platform Console or via the command line. 

In this article, you will gain information about Google Cloud PostgreSQL. You will also gain a holistic understanding of the Cloud SQL, PostgreSQL, its key features, Cloud SQL Instances, its key features, and steps for setting up Google Cloud SQL for PostgreSQL. Read along to find out in-depth information about Google Cloud PostgreSQL.

Table of Contents

• What is Cloud SQL?
• What is PostgreSQL?
  • Key Features of PostgreSQL
• What is a Cloud SQL instance?
• Key Features of Google Cloud PostgreSQL
• Setting up Google Cloud PostgreSQL
  • Google Cloud PostgreSQL: Set up your Project
  • Google Cloud PostgreSQL: Create a Cloud SQL Instance
  • Google Cloud PostgreSQL: Connect to your Instance using the psql client in Cloud Shell
  • Google Cloud PostgreSQL: Create a Database & Upload Data
  • Google Cloud PostgreSQL: Cleanup
• Conclusion

What is Cloud SQL?

Cloud SQL is a Fully-managed Database Service that assists you in creating, maintaining, managing and administering relational databases on the Google Cloud Platform.

Cloud SQL can be used with MySQL, PostgreSQL, or SQL Server. It is a cloud-based alternative to local databases such as MySQL, PostgreSQL, and SQL Server. If you want to spend less time managing your database and more time using it, Cloud SQL is the way to go.

Cloud SQL is used for database storage by many applications running on Compute Engine, App Engine, and other Google Cloud services.

What is PostgreSQL?

PostgreSQL, also known as Postgres is a free, open-source Relational Database Management System (RDBMS) that emphasizes extensibility and SQL Compliance. It was developed at the University of California, Berkeley, and was first released on 8th July 1996. It was the successor to the Ignes database. Instead of storing data like documents, PostgreSQL stores it as Structured objects. It follows the traditional SQL format and syntax.

It is programmed in C and follows a monolithic architecture, which means that the components are completely united and work systematically. It offers community support along with additional support to some of its paid customers. It is widely used in the healthcare, banking, and manufacturing industries due to its innovative backup mechanisms.

Key Features of PostgreSQL

PostgreSQL houses some unique features that make it a good alternative compared to other traditional RDBMSs. Some of these features are:

• PostgreSQL supports a wide variety of data types, document types, and customizations.
• It has a monolithic architecture where all the components work together in an automated manner.
• It is ideal for transactional workflows such as in bank systems, for performing risk assessments, BI (Business Intelligence), and powering various business applications. 
• It has multiple fail-safe’s and redundancies that make storage reliable.
• It is open-source and so any user can use all of its features, free of cost.
• It has limited scalability as its processing power depends on the machine it runs on.
• It has a robust access control system that has additional features like row and column level security and multi-factor authentication with certificates.
• It runs successfully on major operating systems and is ACID (Atomicity, Consistency, Isolation, and Durability) compliant.

To learn more about PostgreSQL, you can click the following link.

What is a Cloud SQL instance?

A Cloud SQL instance is a Cloud-based MySQL database. Cloud SQL instances can be used to store, replicate, and protect your MySQL databases. You can configure the instance’s behavior, such as when and where data will be replicated, or when database maintenance is acceptable.

Each Cloud SQL instance is powered by a virtual machine (VM) that runs on a Google Cloud host server. Each VM runs the database program, such as MySQL Server, PostgreSQL Server, or SQL Server, as well as service agents that provide auxiliary services like logging and monitoring. The high availability option also includes a standby VM in another zone with the same configuration as the primary VM.

The database is kept on a scalable, long-lasting network storage device known as a persistent disk, which is connected to the VM. A static IP address is assigned to each VM to ensure that the IP address to which an application connects remains constant throughout the life of the Cloud SQL instance.

Cloud SQL instance overview:

Key Features of Google Cloud PostgreSQL

The key features are as follows:

• It supports PostgreSQL databases that are fully managed in the cloud.
• Machines with up to 624 GB of RAM and 96 CPUs can be customized.
• Storage capacity of up to 64 TB is available, with the ability to automatically increase storage size as needed.
• In the Google Cloud Console, you can create and manage instances.
• Instances are available in the Americas, the European Union, Asia, and Australia.
• Customer information is encrypted on Google’s internal networks, as well as in database tables, temporary files, and backups.
• Secure external connections are supported via the Cloud SQL Auth proxy or the SSL/TLS protocol.
• Data replication is supported across multiple zones with failover.
• SQL dump files are used to import and export databases.
• PostgreSQL client-server protocol and standard PostgreSQL connectors are supported.
• Backups that are automated and on-demand, as well as point-in-time recovery, are possible.
• Cloning of instances is also supported.
• Logging and monitoring integration with Google Cloud’s operations suite.
• Multiple PostgreSQL versions are supported.
• It also supports logical replication.

Setting up Google Cloud PostgreSQL

This section demonstrates how to use the Google Cloud Console and a client to create and connect to a PostgreSQL instance, as well as perform basic SQL operations.

The different steps are as follows:

• Google Cloud PostgreSQL: Set up your Project
• Google Cloud PostgreSQL: Create a Cloud SQL Instance
• Google Cloud PostgreSQL: Connect to your Instance using the psql client in Cloud Shell
• Google Cloud PostgreSQL: Create a Database & Upload Data
• Google Cloud PostgreSQL: Cleanup

Step 1: Set up your Project

Before performing the steps, you need to complete the following tasks:

• Step 1: Create an account in Google Cloud to evaluate how our products perform in real-world scenarios.
• Step 2: On the project selector page of the Google Cloud Console, select or create a Google Cloud project.
• Step 3: Ensure that billing for your Cloud project is enabled.
• Step 4: Confirm that your user account has the Cloud SQL Admin role.
  • Navigate to the IAM page.
  • Now, click the “Add” button.
  • In the New Principals section, add your user account name.
  • In the Select a role section, filter for Cloud SQL Admin and select it from the list.
  • Click the “Save” button.
• Step 5: Enable the Cloud SQL Admin API.

Note: If you are running a local PostgreSQL instance, you must stop it before working with a Cloud SQL instance. Otherwise, you may encounter issues such as an address that is already in use.

Step 2: Create a Cloud SQL instance

In this part of the process for implementing Google Cloud PostgreSQL, you use the Google Cloud Console.

The steps followed for creating a Cloud SQL instance are as follows:

• Step 1: Navigate to the Cloud SQL Instances page in the Google Cloud Console.
• Step 2: Click the “Create Instance” option.
• Step 3: Now, click on the “PostgreSQL” option.
• Step 4: If you’re prompted to enable the Compute API, click on the “Enable API” button.
• Step 5: When prompted to enable the Compute API, click the “Enable API” button.
• Step 6: In the Instance ID field, type myinstance.
• Step 7: Enter a password for the PostgreSQL user.
• Step 8: For the remaining fields, use the default values.
• Step 9: Click the “Create” button.

You are returned to the list of instances; you can view the details of the new instance right away, but it will not be available until it initializes and starts.

Step 3: Connect to your Instance using the psql client in Cloud Shell

Note: Cloud Shell does not support private IP addresses. These instructions only apply to instances with public IP addresses.

The steps followed for connecting your instance using the psql client in Cloud Shell are as follows:

• Step 1: Navigate to Google Cloud Console.
• Step 2: In the upper right corner, click the Cloud Shell icon ().
• Step 3: When Cloud Shell completes initializing, the following appears:

Welcome to Cloud Shell! Type "help" to get started.
username@example-id:~$

• Step 4: Connect to your Cloud SQL instance at the Cloud Shell prompt using the following command:

gcloud sql connect myinstance --user=postgres

• Step 5: Enter your PostgreSQL password. The psql prompt appears.

Step 4: Create a Database & Upload Data

The steps followed for creating a database and uploading data in the process of setting Cloud SQL for Google Cloud PostgreSQL are as follows:

• Step 1: On your Cloud SQL instance, create a SQL database.

CREATE DATABASE guestbook;

• Step 2: Now, you can connect to the database.

connect guestbook;

• Step 3: You can insert any sample data into the database:

CREATE TABLE entries (guestName VARCHAR(255), content VARCHAR(255),
                        entryID SERIAL PRIMARY KEY);
INSERT INTO entries (guestName, content) values ('first guest', 'I got here!');
INSERT INTO entries (guestName, content) values ('second guest', 'Me too!');

• Now, you can retrieve the data entered in the database using the following statement.

SELECT * FROM entries;

Output:

 guestname   |   content   | entryid
--------------+-------------+---------
 first guest  | I got here! |       1
 second guest | Me too!     |       2
(2 rows)
postgres=>

Step 5: Cleanup

The steps followed to perform Cleanup in the process of setting Cloud SQL for Google Cloud PostgreSQL are as follows:

• Step 1: Navigate to the Cloud SQL Instances page in the Google Cloud Console.
• Step 2: Select the name of your instance to open the Overview page.
• Step 3: In the icon bar at the top of the page, click the “Delete” option.
• Step 4: In the Delete instance window, enter the name of your instance.
• Step 5: Now, click the “Delete” button to delete the instance. (After an instance is deleted, you cannot reuse its name for about 7 days.)

For further information on Cloud SQL on Google Cloud PostgreSQL, you can visit here.

Conclusion

In this article, you have learned about Cloud SQL on Google Cloud PostgreSQL. This article also provided information on Cloud SQL, PostgreSQL, its key features, Cloud SQL instances, and the steps for setting up Google Cloud PostgreSQL in detail. For further information on Google Cloud SQL vs BigQuery, Google Cloud MySQL Integration, PostgreSQL vs MySQL, you can visit the following links.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Visit our Website to Explore Hevo

Hevo Data with its strong integration with 100+ data sources (including 40+ Free Sources) allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows integrating data from non-native sources using Hevo’s in-built Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools. 

Want to give Hevo a try?

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding Cloud SQL using Google Cloud PostgreSQL in the comment section below! We would love to hear your thoughts.