How to deploy, install and manage a high-availability PostgreSQL cluster on Ubuntu Server

doc tutorials
1
Key Value
Summary Deploy a PostgreSQL database with hot standby replica.
Categories deploy-applications
Difficulty 2
Author Tim McNamara tsm@canonical.com

Introduction

Duration: 1:00

Running your own database might sound like a silly thing to do these days. There are many hosted databases in the cloud. But, self-hosting is significantly cheaper. And it’s also probably easier than you think.

Install requirements

Duration: 1:00

This tutorial assumes that you have installed Juju and registered your security credentials.

If you are new to Juju, consider our Get started Tutorials.

Deploy PostgreSQL

Duration: 3:00

Deploying a high-availability PostgreSQL cluster to Ubuntu Server with Juju

$ juju deploy postgresql -n 2 --storage="pgdata=500G" --constraints="cores=4 mem=8G"
Located charm "postgresql" in charm-hub, revision 246
Deploying "postgresql" from charm-hub charm "postgresql", revision 246 in channel stable on ubuntu@20.04/stable

The storage options and constraints provided to the juju deploy command are all that Juju needs to identify the correct instance.

  • -n 2 asks for 2 “units” of the postgresql charm to be deployed. When multiple units are deployed, PostgreSQL will be deployed with a failover secondary node.
  • --storage requests that the cloud provider allocate storage that is not destroyed when instances are torn down. The pgdata label is defined by the charm.
  • --constraints="cores=4 mem=8G" tells Juju to request instances from the cloud provider with at least 4 CPU cores and 8GB RAM each.
About --constraint:

A constraint is a minimum requirement placed on Juju when it selects which instances to provision from the cloud provider.

Several application constraints are available. The most commonly used are cores, mem, and root-disk.

Feel free to customise the application’s constraints. If you have low demands, then specify less memory than 8GB in the mem constraint.

Several cloud providers make persistent storage volumes available to you. Check the Juju Storage documentation for details.

Check configuration

Duration: 2:00

The postgresql charm has several configuration options. View them all via the juju config command:

$ juju config postgresql

Useful options to consider are

  • admin_addresses to whitelist IP addresses that administrative tools such as pgAdmin can connect from
  • backup_schedule to ensure that pg_dump backups are performed with sufficient frequency, and
  • extra_pg_conf to tune the database server.

(Optional) Expose PostgreSQL to the Internet

Duration: 00:10

asciicast

$ juju expose postgresql
Note:

Applications deployed with Juju within this model do not actually require PostgreSQL to be exposed to the Internet.

2

Hey @timClicks

Do you know if it’s possible to use the switchover juju action with postgres 12.4?
I’m getting a validation failure when trying a failover.

root@maas-01:~# juju run-action -m postgresql-home postgresql/0 switchover
{}
ERROR validation failed: (root) : "master" property is missing and required, given {}


Model            Controller                Cloud/Region              Version  SLA          Timestamp
postgresql-home  lab.devzero.home-default  lab.devzero.home/default  2.8.1    unsupported  14:18:08Z

App         Version  Status  Scale  Charm       Store       Rev  OS      Notes
postgresql  12.4     active      2  postgresql  jujucharms  208  ubuntu

Unit           Workload  Agent  Machine  Public address  Ports     Message
postgresql/0   active    idle   0        192.168.51.30   5432/tcp  Live master (12.4)
postgresql/1*  active    idle   1        192.168.51.3    5432/tcp  Live secondary (12.4)

Machine  State    DNS            Inst id       Series  AZ    Message
0        started  192.168.51.30  postgresql-0  focal   AZ-1  Deployed
1        started  192.168.51.3   postgresql-1  focal   AZ-2  Deployed