Check for PostgreSQL Major Version

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter tree box, select Type and PostgreSQL then press Enter, to list only the PostgreSQL database instances provisioned for the selected GCP project.

05 Click on the identifier (ID) of the database instance that you want to examine.

06 In the navigation panel, select Overview to access the configuration details available for the selected instance.

07 In the Configuration section, check the PostgreSQL database major version available for the Database version is <postgresql-version> attribute, where <postgresql-version> is the PostgreSQL engine version installed. Compare the installed version with the latest major version of the PostgreSQL database engine supported by Google Cloud SQL service. If the PostgreSQL version installed is lower than the latest major version supported by Google Cloud PostgreSQL instances, the selected instance is not using the newest version of the PostgreSQL database.

08 Repeat step no. 5 – 7 to check the database engine version for other PostgreSQL instances available within the selected project.

09 Repeat steps no. 2 – 8 for each project deployed in your Google Cloud account.

01 Run projects list command (Windows/macOS/Linux) using custom query filters to list the IDs of all the Google Cloud Platform (GCP) projects available in your Google Cloud account:

gcloud projects list
	--format="table(projectId)"

02 The command output should return the requested GCP project identifiers:

PROJECT_ID
cc-mobile-project-123123
cc-bigdata-project-112233

03 Run sql instances list command (Windows/macOS/Linux) using custom filtering to describe the name of each PostgreSQL database instance provisioned for the selected Google Cloud project:

gcloud sql instances list
	--project cc-mobile-project-123123
	--filter='DATABASE_VERSION:POSTGRES*'
	--format="(NAME)"

04 The command output should return the requested database instance name(s):

NAME
cc-web-sql-server-instance
cc-app-sql-server-instance

05 Run sql instances describe command (Windows/macOS/Linux) using the name of the PostgreSQL database instance that you want to examine as identifier parameter and custom query filters to describe the PostgreSQL major version installed on the selected database instance:

gcloud sql instances describe cc-app-postgres-instance
	--format=json | jq '.databaseVersion'

06 The command output should return the requested flag configuration value:

"POSTGRES_10"

07 Repeat step no. 5 and 6 to check the database engine version for other PostgreSQL database instances created for the selected project.

08 Repeat steps no. 3 – 7 for each project available within your Google Cloud account.

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter tree box, select Type and PostgreSQL then press Enter, to display only the PostgreSQL database instances available for the selected project.

05 Click on the ID of the database instance that you want to re-create and collect all the configuration information available for the selected resource.

06 Navigate back to the Cloud SQL Instances at https://console.cloud.google.com/sql/instances and click on the CREATE INSTANCE button from the dashboard top menu to initiate the PostgreSQL instance setup process.

07 From Choose your database engine panel, select Choose PostgreSQL.

08 On the Instance info panel, perform the following operations:

1. Provide a unique identifier for the new database instance within the Instance ID box.
2. Type your password for the "postgres" user in the Default user password text box.
3. Use the Region and Zone settings to deploy the new database instance in the same region and zone as the source instance.
4. Select the latest major version of the PostgreSQL database engine from the Database version dropdown list.
5. Click on Show configuration options to open the Configuration options panel, and configure the rest of the instance settings based on the configuration information taken from the source instance at step no. 5. Ensure that the new instance has sufficient storage to hold the source instance data, the same authorized networks as the source instance and the same user accounts, with the same PostgreSQL privileges and passwords.
6. Click Create to launch the new PostgreSQL database instance.

09 Once the new Google Cloud PostgreSQL database instance is provisioned, export the source instance data to a PostgreSQL dump file and import that data to the newly created (target) instance.

10 Update your application(s) to connect to the new database instance.

11 Now that your new instance is operating successfully, you can remove the source instance in order to stop adding charges to your Google Cloud monthly bill. Click on the identifier (ID) of the resource that you want to delete (see Audit section part I to identify the source instance).

12 Click on the DELETE button from the dashboard top menu to initiate the removal process.

13 Within Delete instance? confirmation box, confirm resource deletion by typing the instance identifier, then click DELETE.

14 Repeat step no. 5 – 13 to manually upgrade other PostgreSQL instances available in the selected project to the latest major version of PostgreSQL database.

15 Repeat steps no. 2 – 14 for each project deployed in your Google Cloud account.

01 Run sql instances describe command (Windows/macOS/Linux) using the name of the PostgreSQL database instance that you want to re-create as identifier parameter and custom query filters to describe the configuration metadata available for the selected database server:

gcloud sql instances describe cc-app-postgres-instance
	--format=json

02 The command output should return the requested configuration information (metadata):

{
  "backendType": "SECOND_GEN",
  "databaseVersion": "POSTGRES_10",
  "gceZone": "us-central1-a",
  "instanceType": "CLOUD_SQL_INSTANCE",
  "kind": "sql#instance",
  "name": "cc-app-postgres-instance",
  "project": "cc-mobile-project-123123",
  "region": "us-central1",
  "serviceAccountEmailAddress": "pabcd1234abcd-abcabc@gcp-sa-cloud-sql.iam.gserviceaccount.com",
  "settings": {
    "activationPolicy": "ALWAYS",
    "availabilityType": "ZONAL",

    ...

    "dataDiskSizeGb": "150",
    "dataDiskType": "PD_SSD",
    "ipConfiguration": {
      "ipv4Enabled": true
    },
    "locationPreference": {
      "kind": "sql#locationPreference",
      "zone": "us-central1-a"
    },
    "pricingPlan": "PER_USE",
    "replicationType": "SYNCHRONOUS",
    "tier": "db-n1-standard-2"
  },
  "state": "RUNNABLE"
}

03 Run sql instances create command (Windows/macOS/Linux) using the information returned at the previous step as configuration data for the command parameters, to create a new Google Cloud SQL database instance with the PostgreSQL database major version set to PostgreSQL 12:

gcloud sql instances create cc-new-postgres-instance
	--database-version=POSTGRES_12
	--tier=db-n1-standard-2
	--storage-size=150
	--storage-type=SSD
	--zone=us-central1-a
	--availability-type=ZONAL
	--activation-policy=always
	--assign-ip
	--require-ssl
	--root-password=xxxxxxxxxx
	--project=cc-mobile-project-123123

04 The command output should return the metadata for the newly created PostgreSQL 12 database instance:

Creating Cloud SQL instance...done.
Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-mobile-project-123123/instances/cc-new-postgres-instance].
NAME                      DATABASE_VERSION  LOCATION       TIER              PRIMARY_ADDRESS  STATUS
cc-new-postgres-instance  POSTGRES_12       us-central1-a  db-n1-standard-2  xxx.xxx.xxx.xxx  RUNNABLE

05 Once the new Google Cloud PostgreSQL database instance is provisioned, export the source instance data to a PostgreSQL dump file and import that data to the newly created (target) instance.

06 Update your application(s) to connect to the new database instance.

07 Now that your new instance is operating successfully, you can remove the source instance in order to stop adding charges to your Google Cloud bill. Run sql instances delete command (Windows/macOS/Linux) using the ID of the PostgreSQL database instance that you want to remove as identifier parameters (see Audit section part II to identify the right resource), to delete the specified database instance:

gcloud sql instances delete cc-app-postgres-instance

08 Type Y to confirm the resource removal. All data, including backups, is permanently lost when that instance is deleted, therefore make sure that your data has been successfully exported to the new PostgreSQL instance before removal:

All of the instance data will be lost when the instance is deleted.
Do you want to continue (Y/n)? Y

09 The output should return the sql instances delete command request status:

Deleting Cloud SQL instance...done.
Deleted [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-mobile-project-123123/instances/cc-app-postgres-instance].

10 Repeat step no. 1 – 9 to upgrade other PostgreSQL instances available in the selected project to the latest major version of PostgreSQL database.

11 Repeat steps no. 1 – 10 for each project created within your Google Cloud account.