PostgreSQL is an open source object-relational database known for reliability and data integrity. It is ACID -compliant, and supports foreign keys, joins, views, triggers, and stored procedures.
This document is intended for database administrators, cloud architects, and operations professionals interested in deploying a highly-available PostgreSQL topology on Google Kubernetes Engine (GKE).
Objectives
In this tutorial, you will learn how to:- Use Terraform to create a regional GKE cluster.
- Deploy a highly-available PostgreSQL database.
- Set up monitoring for the PostgreSQL application.
- Perform PostgreSQL database and GKE cluster upgrades.
- Simulate cluster disruption and PostgreSQL replica failover.
- Perform backup and restore of the PostgreSQL database.
Architecture
This section describes the architecture of the solution you'll build in this tutorial.
You'll provision two GKE clusters in different regions: a primary
cluster
and a backup cluster
. For this tutorial, the primary cluster is in
the us-central1
region and the backup cluster is in the us-west1
region. This
architecture lets you provision a highly-available PostgreSQL database and
test for disaster recovery, as described later in this tutorial.
For the source cluster, you'll use a Helm
chart
( bitnami/postgresql-ha
) to
set up a high-availability PostgreSQL cluster.
Costs
In this document, you use the following billable components of Google Cloud:
To generate a cost estimate based on your projected usage, use the pricing calculator .
When you finish the tasks that are described in this document, you can avoid continued billing by deleting the resources that you created. For more information, see Clean up .
Before you begin
Set up your project
- Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
-
In the Google Cloud console, on the project selector page, click Create project to begin creating a new Google Cloud project.
-
Verify that billing is enabled for your Google Cloud project .
-
Enable the Google Kubernetes Engine, Backup for GKE, Artifact Registry, Compute Engine, and IAM APIs.
-
In the Google Cloud console, on the project selector page, click Create project to begin creating a new Google Cloud project.
-
Verify that billing is enabled for your Google Cloud project .
-
Enable the Google Kubernetes Engine, Backup for GKE, Artifact Registry, Compute Engine, and IAM APIs.
Set up roles
-
Make sure that you have the following role or roles on the project: roles/storage.objectViewer, roles/logging.logWriter, roles/artifactregistry.Admin, roles/container.clusterAdmin, roles/container.serviceAgent, roles/serviceusage.serviceUsageAdmin, roles/iam.serviceAccountAdmin
Check for the roles
-
In the Google Cloud console, go to the IAM page.
Go to IAM - Select the project.
-
In the Principal column, find all rows that identify you or a group that you're included in. To learn which groups you're included in, contact your administrator.
- For all rows that specify or include you, check the Role column to see whether the list of roles includes the required roles.
Grant the roles
-
In the Google Cloud console, go to the IAM page.
Go to IAM - Select the project.
- Click Grant access .
-
In the New principals field, enter your user identifier. This is typically the email address for a Google Account.
- In the Select a role list, select a role.
- To grant additional roles, click Add another role and add each additional role.
- Click Save .
-
Set up your environment
In this tutorial, you use Cloud Shell
to manage resources hosted on
Google Cloud. Cloud Shell comes preinstalled with the software you'll need
for this tutorial, including Docker
, kubectl
, the gcloud CLI
, Helm
,
and Terraform
.
To use Cloud Shell to set up your environment:
-
Launch a Cloud Shell session from the Google Cloud console, by clicking
Activate Cloud Shellin the Google Cloud console
.
This launches a session in the bottom pane of Google Cloud console. -
Set environment variables.
export PROJECT_ID = PROJECT_ID export SOURCE_CLUSTER = cluster-db1 export REGION = us-central1Replace the following values:
- PROJECT_ID : your Google Cloud project ID .
-
Set the default environment variables.
gcloud config set project PROJECT_ID -
Clone the code repository.
git clone https://github.com/GoogleCloudPlatform/kubernetes-engine-samples -
Change to the working directory.
cd kubernetes-engine-samples/databases/gke-stateful-postgres
Create your cluster infrastructure
In this section, you'll run a Terraform script to create a custom
Virtual Private Cloud (VPC), a
Artifact Registry repository to store PostgreSQL images, and two regional GKE clusters
.
One cluster will be deployed in us-central1
and the second cluster for backup
will be deployed in us-west1
.
To create the cluster, follow these steps:
Autopilot
In Cloud Shell, run the following commands:
terraform
-chdir =
terraform/gke-autopilot
init
terraform
-chdir =
terraform/gke-autopilot
apply
-var
project_id
=
$PROJECT_ID
When prompted, type yes
.
Understand the Terraform configuration
The Terraform configuration files create the following resources to deploy your infrastructure:
- Create a Artifact Registry repository to store the Docker images.
- Create the VPC network and subnet for the VM's network interface.
-
Create a primary GKE cluster.
Terraform creates a private cluster in the
us-central1region, and enables Backup for GKE for disaster recovery and Managed Service for Prometheus for cluster monitoring.Managed Service for Prometheus is only supported on Autopilot clusters running GKE version 1.25 or later.
-
Create a backup cluster in the
us-west1region for disaster recovery.
Standard
In Cloud Shell, run the following commands:
terraform
-chdir =
terraform/gke-standard
init
terraform
-chdir =
terraform/gke-standard
apply
-var
project_id
=
$PROJECT_ID
When prompted, type yes
.
Understand the Terraform configuration
The Terraform configuration files create the following resources to deploy your infrastructure:
- Create a Artifact Registry repository to store the Docker images.
- Create the VPC network and subnet for the VM's network interface.
-
Create a primary GKE cluster.
Terraform creates a private cluster in the
us-central1region, and enables Backup for GKE for disaster recovery and Managed Service for Prometheus for cluster monitoring. -
Create a backup cluster in the
us-west1region for disaster recovery.
Deploy PostgreSQL on your cluster
In this section, you'll deploy a PostgreSQL database instance to run on GKE by using a Helm chart.
Install PostgreSQL
To install PostgreSQL on your cluster, follow these steps.
-
Configure Docker access.
gcloud auth configure-docker us-docker.pkg.dev -
Populate Artifact Registry with the required PostgreSQL Docker images.
./scripts/gcr.sh bitnami/postgresql-repmgr 15 .1.0-debian-11-r0 ./scripts/gcr.sh bitnami/postgres-exporter 0 .11.1-debian-11-r27 ./scripts/gcr.sh bitnami/pgpool 4 .3.3-debian-11-r28The script pushes the following Bitnami images to the Artifact Registry for Helm to install:
-
postgresql-repmgr: This PostgreSQL cluster solution includes the PostgreSQL replication manager (repmgr) , an open-source tool for managing replication and failover on PostgreSQL clusters. -
postgres-exporter: PostgreSQL Exporter gathers PostgreSQL metrics for Prometheus consumption. -
pgpool: Pgpool-II is the PostgreSQL proxy. It provides connection pooling and load balancing.
-
-
Verify that the correct images are stored in the repo.
gcloud artifacts docker images list us-docker.pkg.dev/ $PROJECT_ID /main \ --format = "flattened(package)"The output is similar to the following:
--- image: us-docker.pkg.dev/[PROJECT_ID]/main/bitnami/pgpool --- image: us-docker.pkg.dev/[PROJECT_ID]/main/bitnami/postgres-exporter --- image: us-docker.pkg.dev/h[PROJECT_ID]/main/bitnami/postgresql-repmgr -
Configure
kubectlcommand line access to the primary cluster.gcloud container clusters get-credentials $SOURCE_CLUSTER \ --location = $REGION --project = $PROJECT_ID -
Create a namespace.
export NAMESPACE = postgresql kubectl create namespace $NAMESPACE -
If you are deploying to an Autopilot cluster, configure node provisioning across three zones. You can skip this step if you are deploying to a Standard cluster.
By default, Autopilot provisions resources in only two zones. The deployment defined in
prepareforha.yamlensures that Autopilot provisions nodes across three zones in your cluster, by setting these values:-
replicas:3 -
podAntiAffinitywithrequiredDuringSchedulingIgnoredDuringExecutionandtopologyKey: "topology.kubernetes.io/zone"
kubectl -n $NAMESPACE apply -f scripts/prepareforha.yaml -
-
Update the Helm dependency.
cd helm/postgresql-bootstrap helm dependency update -
Inspect and verify the charts that Helm will install.
helm -n postgresql template postgresql . \ --set global.imageRegistry = "us-docker.pkg.dev/ $PROJECT_ID /main" -
Install the Helm chart.
helm -n postgresql upgrade --install postgresql . \ --set global.imageRegistry = "us-docker.pkg.dev/ $PROJECT_ID /main"The output is similar to the following:
NAMESPACE: postgresql STATUS: deployed REVISION: 1 TEST SUITE: None -
Verify that the PostgreSQL replicas are running.
kubectl get all -n $NAMESPACEThe output is similar to the following:
NAME READY STATUS RESTARTS AGE pod/postgresql-postgresql-bootstrap-pgpool-75664444cb-dkl24 1/1 Running 0 8m39s pod/postgresql-postgresql-ha-pgpool-6d86bf9b58-ff2bg 1/1 Running 0 8m39s pod/postgresql-postgresql-ha-postgresql-0 2/2 Running 0 8m39s pod/postgresql-postgresql-ha-postgresql-1 2/2 Running 0 8m39s pod/postgresql-postgresql-ha-postgresql-2 2/2 Running 0 8m38s NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE service/postgresql-postgresql-ha-pgpool ClusterIP 192.168.99.236 <none> 5432/TCP 8m39s service/postgresql-postgresql-ha-postgresql ClusterIP 192.168.90.20 <none> 5432/TCP 8m39s service/postgresql-postgresql-ha-postgresql-headless ClusterIP None <none> 5432/TCP 8m39s service/postgresql-postgresql-ha-postgresql-metrics ClusterIP 192.168.127.198 <none> 9187/TCP 8m39s NAME READY UP-TO-DATE AVAILABLE AGE deployment.apps/postgresql-postgresql-bootstrap-pgpool 1/1 1 1 8m39s deployment.apps/postgresql-postgresql-ha-pgpool 1/1 1 1 8m39s NAME DESIRED CURRENT READY AGE replicaset.apps/postgresql-postgresql-bootstrap-pgpool-75664444cb 1 1 1 8m39s replicaset.apps/postgresql-postgresql-ha-pgpool-6d86bf9b58 1 1 1 8m39s NAME READY AGE statefulset.apps/postgresql-postgresql-ha-postgresql 3/3 8m39s
Create a test dataset
In this section, you'll create a database and a table with sample values. The database serves as a test dataset for the failover process you'll test later in this tutorial.
-
Connect to your PostgreSQL instance.
cd ../../ ./scripts/launch-client.shThe output is similar to the following:
Launching Pod pg-client in the namespace postgresql ... pod/pg-client created waiting for the Pod to be ready Copying script files to the target Pod pg-client ... Pod: pg-client is healthy -
Start a shell session.
kubectl exec -it pg-client -n postgresql -- /bin/bash -
Create a database and a table, and then insert some test rows.
psql -h $HOST_PGPOOL -U postgres -a -q -f /tmp/scripts/generate-db.sql -
Verify the number of rows for each table.
psql -h $HOST_PGPOOL -U postgres -a -q -f /tmp/scripts/count-rows.sqlThe output is similar to the following:
select COUNT(*) from tb01; count -------- 300000 (1 row) select COUNT(*) from tb02; count -------- 300000 (1 row) -
Generate test data.
export DB = postgres pgbench -i -h $HOST_PGPOOL -U postgres $DB -s 50The output is similar to the following:
dropping old tables... creating tables... generating data (client-side)... 5000000 of 5000000 tuples (100%) done (elapsed 29.85 s, remaining 0.00 s) vacuuming... creating primary keys... done in 36.86 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 31.10 s, vacuum 1.88 s, primary keys 3.86 s). -
Exit the postgres client Pod.
exit
Monitor PostgreSQL
In this section, you'll view metrics and set up alerts for your PostgreSQL instance. You'll use Google Cloud Managed Service for Prometheus to perform monitoring and alerting.
View metrics
Your PostgreSQL deployment includes a postgresql-exporter
sidecar
container. This container exposes a /metrics
endpoint.
Google Cloud Managed Service for Prometheus is configured to monitor the PostgreSQL Pods on this
endpoint. You can view these metrics through Google Cloud console dashboards
.
The Google Cloud console provides a few ways to create and save dashboard configuration:
- Creation and Export: You can create dashboards directly in Google Cloud console, then export and store them in a code repository. To do this, in the dashboard toolbar, open the JSON editor and download the dashboard JSON file.
- Storage and Import: You can import a dashboard from a JSON file by clicking +Create Dashboard and uploading the dashboard's JSON content using the JSON editor menu).
To visualize data from your PostgreSQL application and GKE cluster, follow these steps:
-
Create the following dashboards.
cd monitoring gcloud monitoring dashboards create \ --config-from-file = dashboard/postgresql-overview.json \ --project = $PROJECT_ID gcloud monitoring dashboards create \ --config-from-file dashboard/gke-postgresql.json \ --project $PROJECT_ID -
In the Google Cloud console, navigate to the Cloud Monitoring Dashboard. Go to the Cloud Monitoring Dashboard
-
Select Customfrom the dashboard list. The following dashboards appear:
- PostgreSQL Overview: Displays metrics from the PostgreSQL application, including database uptime, database size, and transaction latency.
- GKE PostgreSQL Cluster: Displays metrics from the GKE cluster that PostgreSQL is running on, including CPU usage, memory usage, and volume utilization.
-
Click on each link to examine the dashboards generated.
Set up alerts
Alerting gives you timely awareness of problems in your applications so you can resolve the problems quickly. You can create an alerting policy to specify the circumstances under which you want to be alerted and how you want to be notified. You can also create notification channels that let you select where alerts are sent.
In this section, you'll use Terraform to configure the following example alerts:
-
db_max_transaction: Monitors the max lag of transactions in seconds; an alert will be triggered if the value is greater than 10. -
db_node_up: Monitors the status of database Pods; 0 means a Pod is down and triggers an alert.
To set up alerts, follow these steps:
-
Configure alerts with Terraform.
EMAIL = YOUR_EMAIL cd alerting/terraform terraform init terraform plan -var project_id = $PROJECT_ID -var email_address = $EMAIL terraform apply -var project_id = $PROJECT_ID -var email_address = $EMAILReplace the following values:
- YOUR_EMAIL : your email address.
The output is similar to the following :
Apply complete! Resources: 3 added, 0 changed, 0 destroyed. -
Connect to the client Pod.
cd ../../../ kubectl exec -it --namespace postgresql pg-client -- /bin/bash -
Generate a load test to test the
db_max_transactionalert.pgbench -i -h $HOST_PGPOOL -U postgres -s 200 postgresThe output is similar to the following:
dropping old tables... creating tables... generating data (client-side)... 20000000 of 20000000 tuples (100%) done (elapsed 163.22 s, remaining 0.00 s) vacuuming... creating primary keys... done in 191.30 s (drop tables 0.14 s, create tables 0.01 s, client-side generate 165.62 s, vacuum 4.52 s, primary keys 21.00 s).The alert triggers and sends an email to YOUR_EMAIL with a subject line that starts with "[ALERT] Max Lag of transaction".
-
In the Google Cloud console, navigate to the Alert Policy page.
-
Select
db_max_transactionfrom the listed policies. From the chart, you should see a spike from the load test which exceeds the threshold hold of 10 for the Prometheus metricpg_stat_activity_max_tx_duration/gauge. -
Exit the postgres client Pod.
exit
Manage PostgreSQL and GKE upgrades
Version updates for both PostgreSQL and Kubernetes are released on a regular schedule. Follow operational best practices to update your software environment regularly. By default, GKE manages cluster and node pool upgrades for you.
Upgrade PostgreSQL
This section shows how you can perform a version upgrade for PostgreSQL. For this tutorial, you'll use a rolling update strategy for upgrading your Pods, so that at no point all of the Pods are down.
To perform a version upgrade, follow these steps:
-
Push an updated version of the
postgresql-repmgrimage to Artifact Registry. Define the new version (for example,postgresql-repmgr 15.1.0-debian-11-r1).NEW_IMAGE = us-docker.pkg.dev/ $PROJECT_ID /main/bitnami/postgresql-repmgr:15.1.0-debian-11-r1 ./scripts/gcr.sh bitnami/postgresql-repmgr 15 .1.0-debian-11-r1 -
Trigger a rolling update using
kubectl.kubectl set image statefulset -n postgresql postgresql-postgresql-ha-postgresql postgresql = $NEW_IMAGE kubectl rollout restart statefulsets -n postgresql postgresql-postgresql-ha-postgresql kubectl rollout status statefulset -n postgresql postgresql-postgresql-ha-postgresqlYou will see the StatefulSet complete a rolling update , starting with the highest ordinal replica to the lowest.
The output is similar to the following:
Waiting for 1 pods to be ready... waiting for statefulset rolling update to complete 1 pods at revision postgresql-postgresql-ha-postgresql-5c566ccf49... Waiting for 1 pods to be ready... Waiting for 1 pods to be ready... waiting for statefulset rolling update to complete 2 pods at revision postgresql-postgresql-ha-postgresql-5c566ccf49... Waiting for 1 pods to be ready... Waiting for 1 pods to be ready... statefulset rolling update complete 3 pods at revision postgresql-postgresql-ha-postgresql-5c566ccf49...
Plan for GKE upgrades on Standard clusters
This section is applicable if you are running Standard clusters. You can take proactive steps and set configurations to mitigate risk and facilitate a smoother cluster upgrade when you are running stateful services, including:
-
Follow GKE best practices for upgrading clusters . Choose an appropriate upgrade strategy to ensure the upgrades happen during the period of the maintenance window:
- Choose surge upgrades if cost optimization is important and if your workloads can tolerate a graceful shutdown in less than 60 minutes.
- Choose blue-green upgrades if your workloads are less tolerant of disruptions, and a temporary cost increase due to higher resource usage is acceptable.
To learn more, see Upgrade a cluster running a stateful workload .
-
Use the Recommender service to check for deprecation insights and recommendations to avoid service interruptions.
-
Use maintenance windows to ensure upgrades happen when you intend them. Before the maintenance window, ensure your database backups are successful.
-
Before allowing traffic to the upgraded nodes, use readiness and liveness probes to ensure they are ready for traffic.
-
Create Probes that assess whether replication is in sync before accepting traffic. This can be done through custom scripts, depending on the complexity and scale of your database.
Verify database availability during Standard cluster upgrades
This section is applicable if you are running Standard clusters. To
verify PostgreSQL availability during upgrades, the general process is to generate
traffic against the PostgreSQL database during the
upgrade process. Then, use pgbench
to check that the database can handle a baseline level of traffic during an
upgrade, compared to when the database is fully available.
-
Connect to your PostgreSQL instance.
./scripts/launch-client.shThe output is similar to the following:
Launching Pod pg-client in the namespace postgresql ... pod/pg-client created waiting for the Pod to be ready Copying script files to the target Pod pg-client ... Pod: pg-client is healthy -
In Cloud Shell, shell into the client Pod.
kubectl exec -it -n postgresql pg-client -- /bin/bash -
Initialize pgbench .
pgbench -i -h $HOST_PGPOOL -U postgres postgres -
Use the following command to get baseline results for confirming that your PostgreSQL application stays highly-available during the time window for an upgrade. To get a baseline result, test with multi-connections via multi jobs (threads) for 30 seconds.
pgbench -h $HOST_PGPOOL -U postgres postgres -c10 -j4 -T 30 -R 200The output looks similar to the following:
pgbench (14.5) starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: simple number of clients: 10 number of threads: 4 duration: 30 s number of transactions actually processed: 5980 latency average = 7.613 ms latency stddev = 2.898 ms rate limit schedule lag: avg 0.256 (max 36.613) ms initial connection time = 397.804 ms tps = 201.955497 (without initial connection time) -
To ensure availability during upgrades, you can generate some load against your database, and ensure that the PostgreSQL application provides a consistent response rate during the upgrade. To perform this test, generate some traffic against the database, using the
pgbenchcommand. The following command will runpgbenchfor one hour, targeting 200 TPS (transactions per second), and listing the request rate every 2 seconds.pgbench -h $HOST_PGPOOL -U postgres postgres --client = 10 --jobs = 4 --rate = 200 --time = 3600 --progress = 2 --select-onlyWhere:
-
--client: Number of clients simulated, that is, number of concurrent database sessions. -
--jobs: Number of worker threads within pgbench. Using more than one thread can be helpful on multi-CPU machines. Clients are distributed as evenly as possible among available threads. The default is 1. -
--rate: The rate is given in transactions per second -
--progress: Show progress report every sec seconds.
The output is similar to the following:
pgbench ( 14 .5 ) starting vacuum...end. progress: 5 .0 s, 354 .8 tps, lat 25 .222 ms stddev 15 .038 progress: 10 .0 s, 393 .8 tps, lat 25 .396 ms stddev 16 .459 progress: 15 .0 s, 412 .8 tps, lat 24 .216 ms stddev 14 .548 progress: 20 .0 s, 405 .0 tps, lat 24 .656 ms stddev 14 .066 -
-
In the Google Cloud console, navigate back to the PostgreSQL Overviewdashboard in Cloud Monitoring. Notice the spike on the Connection per DBand Connection per Podgraphs.
-
Exit the client Pod.
exit -
Delete the client Pod.
kubectl delete pod -n postgresql pg-client
Simulate a PostgreSQL service disruption
In this section, you'll simulate a service disruption in one of the PostgreSQL replicas by stopping the replication manager service. This will prevent the Pod from serving traffic to its peer replicas and its liveness probes to fail.
-
Open a new Cloud Shell session and configure
kubectlcommand line access to the primary cluster.gcloud container clusters get-credentials $SOURCE_CLUSTER \ --location = $REGION --project = $PROJECT_ID -
View the PostgreSQL events emitted in Kubernetes.
kubectl get events -n postgresql --field-selector = involvedObject.name = postgresql-postgresql-ha-postgresql-0 --watch -
In the earlier Cloud Shell session, simulate a service failure by stopping PostgreSQL
repmgr.-
Attach your session to the database container.
kubectl exec -it -n $NAMESPACE postgresql-postgresql-ha-postgresql-0 -c postgresql -- /bin/bash -
Stop the service using
repmgr, and remove the checkpoint and thedry-runargument.export ENTRY = '/opt/bitnami/scripts/postgresql-repmgr/entrypoint.sh' export RCONF = '/opt/bitnami/repmgr/conf/repmgr.conf' $ENTRY repmgr -f $RCONF node service --action = stop --checkpoint
-
The liveness probe configured for the PostgreSQL container will start to fail
within five seconds. This repeats every ten seconds, until the failure threshold
of six failures is reached. Once the failureThreshold
value is reached, the
container is restarted. You can configure these parameters to decrease the
liveness probe tolerance to tune the SLO requirements of your deployment.
From the event stream, you will see the Pod's liveness and readiness probes fail, and a message that the container needs to be restarted. The output is similar to the following:
0s Normal Killing pod/postgresql-postgresql-ha-postgresql-0 Container postgresql failed liveness probe, will be restarted
0s Warning Unhealthy pod/postgresql-postgresql-ha-postgresql-0 Readiness probe failed: psql: error: connection to server at "127.0.0.1", port 5432 failed: Connection refused...
0s Normal Pulled pod/postgresql-postgresql-ha-postgresql-0 Container image "us-docker.pkg.dev/psch-gke-dev/main/bitnami/postgresql-repmgr:14.5.0-debian-11-r10" already present on machine
0s Normal Created pod/postgresql-postgresql-ha-postgresql-0 Created container postgresql
0s Normal Started pod/postgresql-postgresql-ha-postgresql-0 Started container postgresql
Prepare for disaster recovery
To ensure that your production workloads remain available in the event of a service-interrupting event, you should prepare a disaster recovery (DR) plan. To learn more about DR planning, see the Disaster recovery planning guide .
Disaster recovery for Kubernetes can be implemented in two phases:
- Backup involves creating a point-in-time snapshot of your state or data before a service-interrupting event occurs.
- Recovery involves restoring your state or data from a backup copy after the occurrence of a disaster.
To backup and restore your workloads on GKE clusters, you can use Backup for GKE . You can enable this service on new and existing clusters . This deploys a Backup for GKE agent that runs in your clusters; the agent is responsible for capturing configuration and volume backup data and orchestrating recovery.
Backups and restores can be scoped to an entire cluster, a namespace, or an
application (defined by selectors such as matchLabels
).
Example PostgreSQL backup and restore scenario
The example in this section shows how you can perform a backup and restore operation
at the application scope, using the ProtectedApplication
Custom Resource.
The following diagram shows the component resources in the ProtectedApplication,
namely a StatefulSet representing the postgresql-ha
application and a deployment
of pgpool
, which use the same label ( app.kubernetes.io/name: postgresql-ha
).
To prepare to backup and restore your PostgreSQL workload, follow these steps:
-
Set up the environment variables. In this example you'll use a ProtectedApplication to restore the PostgreSQL workload and its volumes from the source GKE cluster (
us-central1), then restore to another GKE cluster in a different region (us-west1).export SOURCE_CLUSTER = cluster-db1 export TARGET_CLUSTER = cluster-db2 export REGION = us-central1 export DR_REGION = us-west1 export NAME_PREFIX = g-db-protected-app export BACKUP_PLAN_NAME = $NAME_PREFIX -bkp-plan-01 export BACKUP_NAME = bkp- $BACKUP_PLAN_NAME export RESTORE_PLAN_NAME = $NAME_PREFIX -rest-plan-01 export RESTORE_NAME = rest- $RESTORE_PLAN_NAME -
Verify that Backup for GKE is enabled on your clusters. It should already be enabled as part of the Terraform setup you performed earlier.
gcloud container clusters describe $SOURCE_CLUSTER \ --project = $PROJECT_ID \ --location = $REGION \ --format = 'value(addonsConfig.gkeBackupAgentConfig)'If Backup for GKE is enabled, the output of the command shows
enabled=True.
Set up a backup plan and perform a restore
Backup for GKE allows you to create a backup plan as a cron job. A backup plan contains a backup configuration including the source cluster, the selection of which workloads to back up, and the region in which backup artifacts produced under this plan are stored.
To perform a backup and restore, follow these steps:
-
Verify the status of ProtectedApplication on
cluster-db1.kubectl get ProtectedApplication -AThe output looks similar to the following:
NAMESPACE NAME READY TO BACKUP postgresql postgresql-ha true -
Create a backup plan for the ProtectedApplication.
export NAMESPACE = postgresql export PROTECTED_APP = $( kubectl get ProtectedApplication -n $NAMESPACE | grep -v 'NAME' | awk '{ print $1 }' )gcloud beta container backup-restore backup-plans create $BACKUP_PLAN_NAME \ --project = $PROJECT_ID \ --location = $DR_REGION \ --cluster = projects/ $PROJECT_ID /locations/ $REGION /clusters/ $SOURCE_CLUSTER \ --selected-applications = $NAMESPACE / $PROTECTED_APP \ --include-secrets \ --include-volume-data \ --cron-schedule = "0 3 * * *" \ --backup-retain-days = 7 \ --backup-delete-lock-days = 0 -
Manually create a backup.
gcloud beta container backup-restore backups create $BACKUP_NAME \ --project = $PROJECT_ID \ --location = $DR_REGION \ --backup-plan = $BACKUP_PLAN_NAME \ --wait-for-completion -
Set up a restore plan.
gcloud beta container backup-restore restore-plans create $RESTORE_PLAN_NAME \ --project = $PROJECT_ID \ --location = $DR_REGION \ --backup-plan = projects/ $PROJECT_ID /locations/ $DR_REGION /backupPlans/ $BACKUP_PLAN_NAME \ --cluster = projects/ $PROJECT_ID /locations/ $DR_REGION /clusters/ $TARGET_CLUSTER \ --cluster-resource-conflict-policy = use-existing-version \ --namespaced-resource-restore-mode = delete-and-restore \ --volume-data-restore-policy = restore-volume-data-from-backup \ --selected-applications = $NAMESPACE / $PROTECTED_APP \ --cluster-resource-scope-selected-group-kinds = "storage.k8s.io/StorageClass" , "scheduling.k8s.io/PriorityClass" -
Restore from the backup.
gcloud beta container backup-restore restores create $RESTORE_NAME \ --project = $PROJECT_ID \ --location = $DR_REGION \ --restore-plan = $RESTORE_PLAN_NAME \ --backup = projects/ $PROJECT_ID /locations/ $DR_REGION /backupPlans/ $BACKUP_PLAN_NAME /backups/ $BACKUP_NAME \ --wait-for-completion
Verify that your cluster is restored
To verify that the restored cluster has all the expected Pods, PersistentVolume, and StorageClass resources, follow these steps:
-
Configure
kubectlcommand line access to the backup clustercluster-db2.gcloud container clusters get-credentials $TARGET_CLUSTER --location $DR_REGION --project $PROJECT_ID -
Verify that the StatefulSet is ready with 3/3 Pods.
kubectl get all -n $NAMESPACEThe output is similar to the following:
NAME READY STATUS RESTARTS AGE pod/postgresql-postgresql-ha-pgpool-778798b5bd-k2q4b 1/1 Running 0 4m49s pod/postgresql-postgresql-ha-postgresql-0 2/2 Running 2 (4m13s ago) 4m49s pod/postgresql-postgresql-ha-postgresql-1 2/2 Running 0 4m49s pod/postgresql-postgresql-ha-postgresql-2 2/2 Running 0 4m49s NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE service/postgresql-postgresql-ha-pgpool ClusterIP 192.168.241.46 <none> 5432/TCP 4m49s service/postgresql-postgresql-ha-postgresql ClusterIP 192.168.220.20 <none> 5432/TCP 4m49s service/postgresql-postgresql-ha-postgresql-headless ClusterIP None <none> 5432/TCP 4m49s service/postgresql-postgresql-ha-postgresql-metrics ClusterIP 192.168.226.235 <none> 9187/TCP 4m49s NAME READY UP-TO-DATE AVAILABLE AGE deployment.apps/postgresql-postgresql-ha-pgpool 1/1 1 1 4m49s NAME DESIRED CURRENT READY AGE replicaset.apps/postgresql-postgresql-ha-pgpool-778798b5bd 1 1 1 4m49s NAME READY AGE statefulset.apps/postgresql-postgresql-ha-postgresql 3/3 4m49s -
Verify all Pods in the
postgresnamespace are running.kubectl get pods -n $NAMESPACEThe output is similar to the following:
postgresql-postgresql-ha-pgpool-569d7b8dfc-2f9zx 1/1 Running 0 7m56s postgresql-postgresql-ha-postgresql-0 2/2 Running 0 7m56s postgresql-postgresql-ha-postgresql-1 2/2 Running 0 7m56s postgresql-postgresql-ha-postgresql-2 2/2 Running 0 7m56s -
Verify the PersistentVolumes and StorageClass. During the restore process, Backup for GKE creates a Proxy Class in the target workload to replace the StorageClass provisioned in the source workload (
gce-pd-gkebackup-dnin the example output).kubectl get pvc -n $NAMESPACEThe output is similar to the following:
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE data-postgresql-postgresql-ha-postgresql-0 Bound pvc-be91c361e9303f96 8Gi RWO gce-pd-gkebackup-dn 10m data-postgresql-postgresql-ha-postgresql-1 Bound pvc-6523044f8ce927d3 8Gi RWO gce-pd-gkebackup-dn 10m data-postgresql-postgresql-ha-postgresql-2 Bound pvc-c9e71a99ccb99a4c 8Gi RWO gce-pd-gkebackup-dn 10m
Validate that the expected data is restored
To validate that the expected data is restored, follow these steps:
-
Connect to your PostgreSQL instance.
./scripts/launch-client.sh kubectl exec -it pg-client -n postgresql -- /bin/bash -
Verify the number of rows for each table.
psql -h $HOST_PGPOOL -U postgres -a -q -f /tmp/scripts/count-rows.sql select COUNT ( * ) from tb01 ;You should see a similar result to the data you wrote earlier in the Create a test dataset . The output is similar to the following:
300000 ( 1 row ) -
Exit the client Pod.
exit
Clean up
To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.
Delete the project
The easiest way to avoid billing is to delete the project you created for the tutorial.
What's next
- Learn the best practices for deploying databases on GKE.
- Explore Persistent Volumes in more detail.
- See an example of how to use Pgpool-II for streaming replication with high-availability PostgreSQL clusters.
