Google Cloud Postgres SQL: Set up, Connection Guide and Pricing

Discover the step-by-step process with hands on tutorial to set up Google Cloud Postgres SQL.
Learn the advantages, High Availability options, pricing, and get answers to frequently asked questions.

Advantages of PostgreSQL on Google Cloud SQL

• Scalability : One of the standout advantages of using PostgreSQL on Google Cloud SQL is its scalability. As your data grows, you can easily scale your PostgreSQL instance vertically or horizontally to meet your evolving needs. This ensures that your database can handle increased workloads and maintain optimal performance.
• High Availability : Google Cloud SQL offers built-in high availability with automatic failover. In the event of a hardware failure or other issues, your PostgreSQL instance will seamlessly switch to a standby instance, minimizing downtime and ensuring data integrity.
• Managed Service : Google Cloud SQL is a fully managed database service. This means that Google handles routine database tasks such as backups, patch management, and updates, allowing you to focus on your application development rather than database administration.
• Security : Google Cloud SQL provides robust security features, including encryption at rest and in transit, identity and access management (IAM) integration, and automated security patching. You can rest assured that your data is protected.
• Global Reach : With Google Cloud’s global network infrastructure, you can deploy PostgreSQL instances in data centers around the world, ensuring low-latency access for your users, no matter where they are located.
• Compatibility : PostgreSQL is known for its adherence to SQL standards. This ensures that your applications built on PostgreSQL can easily transition to Google Cloud SQL without major code changes.

Google Cloud Postgres SQL Set Up

To create a new Cloud SQL instance, you must have a valid google cloud account and billing must be enabled for your project.

If you are new to Google cloud platform and interested in exploring GCP learn set up you free trial account with step-by-step instructions mentioned here Google Cloud Free Credits – Google Cloud Tutorials .

GCP offers free credits up to 400 $ to learn and experience cloud services.

In your project settings, enable the Google Cloud SQL API .

Search “Cloud SQL ” in console search box and navigate to the Cloud SQL section to create a new PostgreSQL instance.

Choose Database engine as PostgreSQL.

Set instance ID and password.

This Instance ID and password would be required to connect to this instance.

Google cloud also allows you to configure password policies for instance configuration.

Password policies allow you to impose requirements on passwords used to authenticate all local users. Once you’ve created this instance, manage additional user account policies and passwords on the Users page.

It offers following list of passwords policies.

• Set minimum length – Allows you to specify minimum length requirements for password.
• Require complexity – Passwords must contain an uppercase letter, lowercase letter, number and symbol.
• Restrict password reuse – Passwords must be changed a minimum number of times before they can be reused again.
• Disallow username in password – Root or admin passwords can’t contain the username associated with them.
• Set password change interval – Passwords must be in use for a minimum duration before they can be changed

Select Database version.

Google Cloud SQL offers 2 editions to support various business and application needs.

Cloud SQL Enterprise edition : It provides all core capabilities and features of cloud SQL suitable for small to medium scale applications that requires balance of performance, availability, and cost.

Cloud SQL Enterprise Plus edition: It provides core capabilities of Cloud SQL Enterprise edition and best performance with highest availability.

For this demo we will go ahead with Cloud SQL Enterprise edition.

Choose correct preset for your selected editions.

Presents allows you to set default configuration for your instance.

Presets can be customized later as needed.

Learn more about Cloud SQL Enterprise edition Cloud SQL Enterprise Plus edition comparison here Introduction to Cloud SQL editions  |  Cloud SQL Documentation  |  Google Cloud

gcp allows you to deploy cloud database in single region or multiple zones depending on your desired availability options.

In case of multi zone option select primary region and secondary region for new database

Choose from the most commonly used shapes or choose a custom shape. Make sure your selection has enough memory to hold your largest table.

Machine configuration, storage type ,Storage capacity, Database backup and maintenance configuration is prepopulated based on selected Database edition and preset envrioment.

Review configuration as per your business needs, data criticality and cost.

SSD storage provides high performance.

Enable automatic storage to increase for highly available database servers.

Database advance configuration allows you to configure data encryptions options.

By default, it uses google managed encryptions to encrypt your data.

To learn more about data encryptions refer Google Cloud Storage Encryption – Cloud KMS – Google Cloud Tutorials .

Automated backups options help to protect data loss in case of failures.

Configure automated daily backups and saved it in single region or multiple regions.

Review configuration and click on create instance.

It will take 2-3 for instance to set up.

Once green tick is enabled DB instance is ready to connect .

Google Cloud Postgres SQL Connection

Create a new Database in our cloud SQL instance.

Navigate to cloud SQL instance and under Database tab click on create new Database.

In this tutorial we will connect to cloud SQL database using Google cloud shell.

Cloud Shell doesn’t work with a private IP address. These instructions are only for an instance with a public IP address.

Enable Cloud SQL Admin API

In Database overview under connection instance tab click on connect using cloud shell.

gcloud sql connect will be auto populated and it will use root user to connect this instance.

Press enter and Authorize

Once authorization is complete it will whitelist cloud shell IP address and ask for password.

Enter the valid password created at the time of instance set up and you are ready to go.

Create a new Employee table using below SQL query

CREATE TABLE employeedetails (EmployeeId int, Name VARCHAR(255));
INSERT INTO employeedetails (EmployeeId, Name) values (1, 'Rakesh');
INSERT INTO employeedetails (EmployeeId, Name) values (2, 'Rajesh');


SELECT * FROM employeedetails;

To avoid incurring charges to your Google Cloud account for the resources used on this page, follow these steps.

In Google cloud console search Cloud SQL

Select instance name and right click at the end and click delete.

In the Delete instance window, type your instance’s name and then click Delete.

f you’re not using the APIs that were enabled as part of this quickstart, you can disable them.

Disable Cloud SQL Admin API

Disable Cloud SQL API

Google Cloud SQL pricing

Cloud SQL Estimates

Google cloud allows to plan budget for Cloud SQL using Google Cloud Pricing Calculator

Cloud SQL offers two editions, Enterprise and Enterprise Plus.

These editions provide different levels of availability, performance and data protection. Pricing for instances in each edition will vary. Cloud SQL Enterprise edition supports MySQL, PostgreSQL, and SQL Server. Cloud SQL Enterprise Plus edition supports MySQL and PostgreSQL.

Cloud SQL pricing is dependent on

• CPU and memory configuration – The cost depends on the virtual CPU (vCPU) and memory configuration you choose for your instance. Higher performance configurations will incur higher costs.
• Storage – The amount of storage you allocate for your PostgreSQL database affects pricing. Google Cloud SQL offers both standard and SSD storage options.
• Network Egress – Outbound data transfer from your PostgreSQL instance to the internet or other Google Cloud services may incur additional charges
• High Availability – If you opt for high availability by configuring a failover replica, you will have additional costs associated with the replica instance.
• Backups – Automated backups are included in the pricing. However, you may incur costs if you enable binary log backups for point-in-time recovery.

It’s essential to use the Google Cloud Pricing Calculator to estimate your PostgreSQL on Google Cloud SQL costs accurately. Additionally, take advantage of Google Cloud’s cost management tools to monitor and optimize your expenses.

Detailed pricing based on configuration can be checked here Pricing  |  Cloud SQL for PostgreSQL  |  Google Cloud