Charmed PostgreSQL VM Operator

Description

This repository contains a Juju Charm for deploying PostgreSQL on virtual machines (LXD). To deploy on Kubernetes, please use Charmed PostgreSQL K8s Operator.

This operator provides a PostgreSQL database with replication enabled: one primary instance and one (or more) hot standby replicas. The Operator in this repository is a Python script which wraps PostgreSQL versions distributed by Ubuntu Jammy series and adding Patroni on top of it, providing lifecycle management and handling events (install, configure, integrate, remove, etc).

Usage

Bootstrap a lxd controller and create a new Juju model:

juju add-model postgresql

Basic Usage

To deploy a single unit of PostgreSQL using its default configuration.

juju deploy postgresql --channel edge

It is customary to use PostgreSQL with replication. Hence usually more than one unit (preferably an odd number to prohibit a "split-brain" scenario) is deployed. To deploy PostgreSQL with multiple replicas, specify the number of desired units with the -n option.

juju deploy postgresql --channel edge -n <number_of_units>

To retrieve primary replica one can use the action get-primary on any of the units running PostgreSQL.

juju run-action postgresql/leader get-primary --wait

Similarly, the primary replica is displayed as a status message in juju status, however one should note that this hook gets called on regular time intervals and the primary may be outdated if the status hook has not been called recently.

Replication

Adding Replicas

To add more replicas one can use the juju add-unit functionality i.e.

juju add-unit postgresql -n <number_of_units_to_add>

The implementation of add-unit allows the operator to add more than one unit, but functions internally by adding one replica at a time, avoiding multiple replicas syncing from the primary at the same time.

Removing Replicas

Similarly to scale down the number of replicas the juju remove-unit functionality may be used i.e.

juju remove-unit postgresql <name_of_unit1> <name_of_unit2>

The implementation of remove-unit allows the operator to remove more than one unit. The functionality of remove-unit functions by removing one replica at a time to avoid downtime.

Password rotation

Charm users

For users used internally by the Charmed PostgreSQL Operator an action can be used to rotate their passwords.

juju run-action postgresql/leader set-password username=<username> password=<password> --wait

Note: currently, the users used by the operator are operator, replication, backup and rewind. Those users should not be used outside the operator.

Related applications users

To rotate the passwords of users created for related applications the relation should be removed and the application should be related again to the Charmed PostgreSQL Operator. That process will generate a new user and password for the application (removing the old user).

Integrations (Relations)

Supported relations:

New postgresql_client interface:

Current charm relies on Data Platform libraries. Your application should define an interface in metadata.yaml:

requires:
  database:
    interface: postgresql_client

Please read usage documentation about data_interfaces library for more information about how to enable PostgreSQL interface in your application.

Relations to new applications are supported via the postgresql_client interface. To create a relation:

juju v2.x:

juju relate postgresql application

juju v3.x

juju integrate postgresql application

To remove a relation:

juju remove-relation postgresql application

Legacy pgsql interface:

We have also added support for the two database legacy relations from the original version of the charm via the pgsql interface. Please note that these relations will be deprecated.

juju relate postgresql:db mailman3-core
juju relate postgresql:db-admin landscape-server

tls-certificates interface:

The Charmed PostgreSQL Operator also supports TLS encryption on internal and external connections. To enable TLS:

# Deploy the TLS Certificates Operator. 
juju deploy tls-certificates-operator --channel=edge
# Add the necessary configurations for TLS.
juju config tls-certificates-operator generate-self-signed-certificates="true" ca-common-name="Test CA" 
# Enable TLS via relation.
juju relate postgresql tls-certificates-operator
# Disable TLS by removing relation.
juju remove-relation postgresql tls-certificates-operator

Note: The TLS settings shown here are for self-signed-certificates, which are not recommended for production clusters. The TLS Certificates Operator offers a variety of configurations. Read more on the TLS Certificates Operator here.

Security

Security issues in the Charmed PostgreSQL Operator can be reported through LaunchPad. Please do not file GitHub issues about security issues.

Contributing

Please see the Juju SDK docs for guidelines on enhancements to this charm following best practice guidelines, and CONTRIBUTING.md for developer guidance.

License

The Charmed PostgreSQL Operator is distributed under the Apache Software License, version 2.0. It installs/operates/depends on PostgreSQL, which is licensed under PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses.

Trademark Notice

PostgreSQL is a trademark or registered trademark of PostgreSQL Global Development Group. Other trademarks are property of their respective owners.