Operator Guide for gcp-cloud-sql-postgres

Google Cloud SQL for PostgreSQL enables you to run a relational database with the resiliency, security, scalability, and ease of use of a fully managed cloud service. You gain most of the functionality of a conventional PostgreSQL database management system, while Google Cloud takes care of daily maintenance and regular backups, allowing you to focus on your core competencies.

Use Cases

Highly Scalable Data Store

PostgreSQL is an open-source object-relational database-management system. With decades of development behind it and as one of the most popular solutions in its class, PostgreSQL excels as a backend database. It is versatile and adaptable.

ACID Compliant

PostgreSQL supports the rigorous requirements of financial institutions and others needing exceptional reliability. It supports atomicity, consistency, isolation, and durability (ACID) and online transaction processing (OLTP).

Spatial Data

PostgreSQL is highly extensible. PostGIS, an extension for a geographical information system (GIS), provides hundreds of functions to process geometric data. In the decades since its initial release, PostGIS has become one of the de facto standards in the open-source GIS world.

JSON Support

PostgreSQL also supports JSON data in either json or jsonb format. For querying JSON data, PostgreSQL supports the jsonpath data type.

Configuration Presets

Development

The development preset does not have deletion protection, allowing you to deprovision the database as needed. Since this preset is not intended for production, it will keep only one backup, and it comes with only a 20 GB disk and a shared-core CPU. This preset is suitable as a low-cost test and development instance only.

Staging

The staging preset provides high availability and the same backup and deletion protection as the production preset, with seven backups kept at all times. However, staging has only 1 core and a smaller 200 GB disk.

Production

The production preset comes with ten cores and a 1 TB disk. High availability, backups, and deletion protection (with seven backups) are enabled by default. For Postgres, we use the latest managed version of the 14.x series. This preset has sufficient resources to support a full production environment.

Design

A typical relational database has a primary instance for writes and a read replica for offline workloads, which protects the performance of the primary database. The read replica typically acts as a failover in case the primary goes offline. In Google Cloud SQL for Postgres, we offer the same functionality through a slightly different design.

High Availability

Google supports high availability by provisioning a standby instance in a different zone than your primary instance. While you cannot access the standby under normal conditions, Google will automatically direct traffic to it if the primary instance fails. This approach doubles the service cost in exchange for full service-level agreements (SLAs).

Read Replicas

We will support read replicas in a future release of this bundle.

Best Practices

The bundle includes a number of best practices without needing any additional work on your part:

Security

The database is not provisioned with a public IP.

High availability

This bundle supports regional high availability, which is enabled by default. In the case of a zonal outage, failover is performed automatically for you. We do not currently support failbacks (that is, moving the primary instance back to the original zone after recovery).

Backup and recovery

Backups are created to recover the database in the event of disaster recovery, and cannot be disabled.

Replication

Replica configuration will be supported in a future release. Google recommends that you keep fewer than ten replicas for your primary instance.

Security

Auto-generated password

Upon database creation, we generate a random sixteen-character password.

Private deployment

This database will be available only in the gcp-global-network to which it is connected.

Data encrypted in transit

By default, all data in transit will be encrypted with Secure Sockets Layer and Transport Layer Security (SSL/TLS).

Data encrypted at rest

By default, all customer content in Google Cloud is encrypted at rest using AES256.

Auditing

This bundle allows you to configure the transaction log policy. The minimum allowed days are 1, and the maximum is 7. Transaction logs allow you to perform the following:

• Individual transaction recovery
• Recovery of all incomplete transactions when SQL Server is started
• Rolling a restored database, file, filegroup, or page forward to the point of failure
• Supporting transactional replication
• Supporting high availability and disaster-recovery solutions (for example, database mirroring, log shipping, etc.)

Observability

This bundle comes with three preconfigured alarms:

• CPU: when the CPU usage exceeds 60%
• Disk: when the database storage-disk usage exceeds 60%
• Memory: when the database is using more than 60% of its allocated memory

Trade-Offs

• This bundle does not support a public IP.
• The bundle supports only SSL/TLS. It does not support unencrypted traffic.
• As noted above, this bundle supports regional high availability but not failbacks. In case of a failover, the primary instance will not move back to the original zone after the original primary recovers.