Getting Started With Cloud SQL For PostgreSQL

In the field of cloud computing, Google Cloud Platform is one of the leading players. Cloud SQL is one of the very famous services in GCP. Google Cloud SQL is a completely managed relational database service. Among the various database engines supported by Google Cloud SQL, PostgreSQL is famous for it’s reliability and wide user base. Cloud SQL for PostgreSQL provides a reliable and high-performance environment to build, manage, and scale relational databases. In this article, we will learn how we can create and connect to a PostgreSQL instance and perform operations using a cloud console and client.

What Is Cloud SQL?

Cloud SQL is a fully managed relational database management service provided by Google Cloud Platform. It offers a convenient and scalable solution for deploying and managing relational databases in the cloud. Cloud SQL supports several popular database engines like MySQL, PostgreSQL, and SQL server. User can choose their preferred database engine and use the services in the Cloud. Cloud SQL simplifies the process of deploying and managing relational databases which allows users to focus on developing their applications rather than investing time in managing database systems.

Set Up Cloud SQL For PostgreSQL Instance: A Step-By-Step Guide

Step 1: Open GCP Console

• To get start with creating cloud SQL, we first need to log into Google Cloud Platform. In your web browser go to console.cloud.google.com and log in with your valid credentials.
• Please note that the services which we are going to use are not free, so make sure that you have a valid subscription or any trial benefits.

Step 2: Enable API

• To move forward, we need to enable the Cloud SQL Admin API. In the cloud console, go to the APIs page.

• Click on it, and in the next page select Enable APIs and services, which will open a search bar. Type Cloud SQL Admin API and select the API from search result.

• This will open up the page showing details of this API. click on enable to use this API.

Step 3: Choose Database Instance

• On your cloud console’s sidebar, select SQL, and you a new screen will open.
• You will get database engine options to choose for your SQL database.

• Here, we need to choose PostgreSQL, click on the choose PostgreSQL button.

Step 4: Create PostgreSQL Instance

• Once we choose the database engine, now it’s time to set up the configurations to create a new database instance.

A) Instance Info: In place of Instance ID, give a name of your choice using the defined combinations. This is the ID given to the instance to identify it.

• Set up the password for the instance and save it somewhere as we will be using it later. This is the root password for your database instance.
• You can also choose to click on GENERATE to get a root password suggested for your instance.

B) Database Version: Choose the newest version available for the selected database. Currently the latest version available for PostgreSQL is 15, which is pre-selected.

C) Choose Cloud SQL Edition: In this section, we have two edition available for cloud sql which are Enterprise Plus and Enterprise.

• The Plus edition provides high performance and reliability than the normal one.
• You can choose any of these as per your requirements and budget. For this time, I am going with the Enterprise edition.
• Now select the preset for the selected edition. Here we will be using “Development” which has 4 vCPU with 16GB RAM and 100GB of storage.

D) Region and Availability zone: Choose the region which is near to you or suitable for you work. You can also choose the pre-selected one if you want.

• For availability Zone, choose Multiple Zone for high availability, otherwise choose Single Zone.

• Leave all other settings as default and review all the configurations you have made so far. Then click on Create Instance.

• Wait for few seconds and you will be able to see the created instance.

• We have successfully created a Cloud SQL for PostgreSQL database.

Connecting To Cloud SQL For PostgreSQL

1. Using Cloud Shell

• Click on the Cloud shell button available on the top right side in your GCP cloud console which will open a terminal to execute commands.

• Here we will be connecting to our PostgreSQL instance and perform some database operations.

• Use the following command to connect to the database instance.

gcloud sql connect [instance ID] --user=postgres

• Replace the instance ID which the instance ID given to your PostgreSQL instance. In any prompt appears click on authorize. Use the password given to the instance to connect.

• Now we are connected to the PostgreSQL instance and can perform database operations.
• Let’s create a database schema named guestbook in the database instance.

CREATE DATABASE guestbook;

• Let’s now use this schema using the following command. Use the root password if asked for password.

\connect guestbook;

• create a Table named entries and insert few sample data/rows into the database table.

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!');

Output

• Let’s check if the table contains the data as we have inserted or not. For this we will be selecting all the data form the table.

SELECT * FROM entries;

Output

2. Using PgAdmin

• PgAdmin is an open-source administration and development platform for PostgreSQL. PgAdmin provides a graphical user interface (GUI) for users to interact with PostgreSQL RDBMS.
• It offers features to use and manage PostgreSQL databases efficiently. Here we are going to connect the Cloud SQL PostgreSQL with the PgAdmin application available in our local system and interact with the database instance.
• Open the PgAdmin application in your computer and then click on servers and in register click on Server.

• This will open a new window in the application. In the General section, give a name to the server.

• Now to connect to the Cloud SQL, go to the database instance in GCP and copy the public IP address.

• Paste this in the Host Name box under Connection section. Enter the password used for creating the database instance.

• Now click on save and for few seconds for it to be connected with the Cloud SQL.

• As you can see, it’s now connected with the Cloud SQL and we are able to see the guestbook schema we have created using cloud shell. Let’s run the same query we have used to check contents of the entries table in guestbook.

select * from entries;

• And you can see the same rows as we have inserted in the table. Thus we have successfully connected with the PostgreSQL running on Cloud SQL and able to interact with it using external application.

Conclusion

Using PostgreSQL with Cloud SQL provides developers or the users a fully managed database management system for deploying, managing, and scaling PostgreSQL databases in cloud. Using Cloud SQL also helps in better integration with other Google cloud services, high availability, scalability, and better security. Overall, Google Cloud SQL for PostgreSQL provides a reliable and efficient solution for relational database management.

Cloud SQL For PostgreSQL – FAQ’s

Is Cloud Sql Is Same As Postgresql ?

Cloud SQL is a database service provided by GCP, and it supports PostgreSQL. The PostgreSQL database instance created in Cloud SQL is same as the locally available

Which Database Engines Are Supported By Cloud Sql ?

There are three database engines which are supported by Google Cloud SQL, which are,

1. MySQL
2. PostgreSQL
3. SQL Server

Can I Migrate My Existing Postgresql Database To Cloud Sql?

Yes, Cloud SQL provides tools and documentations to help users migrating their existing database to Cloud SQL. There are also third-party tools available to help cloud migration.

What To Do If Pgadmin Show Connection Failed ?

This can happen if there are issues while connecting with Cloud SQL.

1. Try changing the port number used.
2. Check ip address and password entered is correct or not.
3. In your cloud database add your local network’s IP.