pgAdmin 4 has added functionality to deploy cloud instances for PostgreSQL on AWS, Azure and BigAnimal. pgAdmin version 7.0 has introduced the new functionality for deploying a Google Cloud SQL PostgreSQL instance from pgAdmin. To launch a Google Cloud SQL PostgreSQL instance, the prerequisite is to create and download a client secret file containing the client ID, client secret, and other OAuth 2.0 parameters for google authentication. This feature enables you to quickly deploy the PostgreSQL instance to the desired region on Google Cloud, with custom configuration, and connect to it with pgAdmin.
Creating client secrets
- Go to the Google Cloud Platform Console.
- From the projects list, select a project or create a new one.
- If the APIs & services page isn't already open, open the console left side menu and select APIs & services.
- On the left, click Credentials.
- Click Create Credentials, then select OAuth client ID.
- If this is your first time creating a client ID, you can also configure your consent screen by clicking Consent Screen.
- Click Create client ID.
- Download client secret by clicking the download button.
Deploying Google Cloud SQL PostgreSQL Instance
To deploy the PostgreSQL server on the Google Cloud SQL, open ‘Deploy Cloud Instance..’ from the server group's Register contextual menu or select a server group from the object menu and navigate to Register > Deploy Cloud Instance… option.
On the Deploy Cloud Instance wizard, select the Google Cloud SQL cloud provider and move to the next step.
The Credentials step requires a client secret file to initiate google authentication. Select client secret json file downloaded previously & click on button to authenticate yourself to Google. Clicking the button will open a popup to enter Google credentials.
Enter the Google credentials and complete the authentication. On s uccessful authentication, the popup will be closed automatically. Move to the next step.
You can specify the configuration details about the instance in the Instance Specification step.
The Google Cloud provides high availability in the region by spreading infrastructure across the different availability zones. Choose the desired location & availability zone generally called as primary availability zone to create the instance.You can only set the region during instance creation.
The Google Cloud SQL supports database instances running several editions of PostgreSQL. Currently PostgreSQL version 10.0 onwards. Use the Database version to specify the PostgreSQL version of the cloud PostgreSQL instance.
The Instance class determines the CPU and memory capacity of the host computer used for your instance. Standard class instances are most commonly used for general purpose work load.Shared core ones are low cost instances while High Memory provides instances for heavy workloads.
The Instance Type provides more information on hardware specifications such as CPU, memory etc. Your machine type affects the cost of your instance. For more information, see Instance Pricing. You can also see pricing for various scenarios with the Pricing Calculator.
SSD and HDD are the storage types available for our database instance.SSDs provide lower latency and higher data throughput. If you do not need high-performance access to your data, for example for long-term storage or rarely accessed data, you can reduce your costs by choosing HDD.
Choose a storage capacity to fit your database size. The amount of storage capacity allocated for your instance affects the cost of your instance. For more information, see Storage and Networking Pricing.
You can add specific IP addresses or ranges of addresses to open your instance to those addresses through the Public IP range field.
Turning on the High availability option will create a replica in another availability zone.This enables automatic failover to another zone within your selected region
The Database Details page gathers information about database specifications such as database password. Database username is the default : postgres’ and the port: 5432.
The review section gives summary information about the cloud database instance specification. Clicking the Finish button will send a request to the Google Cloud SQL to create the PostgreSQL instance.
A background process will start which will deploy the instance in the cloud and monitor the progress of the deployment. The Server will be added to the tree with the cloud deployment icon. Once the deployment is done, the server details will be updated.
Using Google Cloud SQL - PostgreSQL Instance
When a database instance is successfully created, you connect to the cloud database using the password specified in the creation.
Conclusion
One can conveniently launch a PostgreSQL instance in the Google Cloud SQL environment and connect to it with a few clicks using pgAdmin. This allows PostgreSQL databases to be quickly and easily deployed for development, test, and production purposes across different cloud providers.