Skip to main content

Your submission was sent successfully! Close

Thank you for signing up for our newsletter!
In these regular emails you will find the latest updates from Canonical and upcoming events where you can meet our team.Close

Thank you for contacting us. A member of our team will be in touch shortly. Close

CLI helpers

This page describes some useful low-level tools shipped withing Charmed PostgreSQL for advanced troubleshooting.

These tools can be dangerous in a production environment if they are not used correctly.

When in doubt, contact us.

Patroni

Troubleshooting tools include:

  • The command-line tool patronictl
  • The Patroni REST API
  • The Raft library

Learn more about Patroni in the Architecture page.

patronictl

The main Patroni tool is patronictl.

It should only be used under the snap context, via the user snap_daemon.

Cluster status

patronictl checks the low-level Patroni status of the cluster.

Example: cluster status 
> juju deploy postgresql -n 3 # and wait for deployment
> juju ssh postgresql/2
...

ubuntu@juju-b87344-2:~$ sudo -u snap_daemon patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml topology
+ Cluster: postgresql (7496847632512033809) ------+-----------+----+-----------+
| Member          | Host           | Role         | State     | TL | Lag in MB |
+-----------------+----------------+--------------+-----------+----+-----------+
| postgresql-1    | 10.189.210.201 | Leader       | running   |  1 |           |
| + postgresql-2  | 10.189.210.55  | Sync Standby | streaming |  1 |         0 |
| + postgresql-3  | 10.189.210.26  | Sync Standby | streaming |  1 |         0 |
+-----------------+----------------+--------------+-----------+----+-----------+

Useful Patroni actions

Use --help to find all the available Patroni actions.

Example: Patroni actions 
>  sudo -u snap_daemon patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml --help
...
  failover     Failover to a replica
  history      Show the history of failovers/switchovers
  list         List the Patroni members for a given Patroni
  pause        Disable auto failover
  query        Query a Patroni PostgreSQL member
  reinit       Reinitialize cluster member
  reload       Reload cluster member configuration
  remove       Remove cluster from DCS
  restart      Restart cluster member
  resume       Resume auto failover
  switchover   Switchover to a replica
  topology     Prints ASCII topology for given cluster

Switchover/failover

Patroni can perform a low-level switchover/failover inside one cluster.

Example: switchover (healthy cluster only) 
ubuntu@juju-b87344-2:~$ sudo -u snap_daemon patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml switchover postgresql --candidate postgresql-2 --force
Current cluster topology
+ Cluster: postgresql (7496847632512033809) ----+-----------+----+-----------+
| Member        | Host           | Role         | State     | TL | Lag in MB |
+---------------+----------------+--------------+-----------+----+-----------+
| postgresql-1  | 10.189.210.201 | Sync Standby | streaming |  2 |         0 |
| postgresql-2  | 10.189.210.55  | Sync Standby | streaming |  2 |         0 |
| postgresql-3  | 10.189.210.26  | Leader       | running   |  2 |           |
+---------------+----------------+--------------+-----------+----+-----------+
2025-04-25 04:59:10.87214 Successfully switched over to "postgresql-2"
+ Cluster: postgresql (7496847632512033809) -----------+----+-----------+
| Member        | Host           | Role    | State     | TL | Lag in MB |
+---------------+----------------+---------+-----------+----+-----------+
| postgresql-1  | 10.189.210.201 | Replica | running   |  2 |         0 |
| postgresql-2  | 10.189.210.55  | Leader  | running   |  2 |           |
| postgresql-3  | 10.189.210.26  | Replica | stopped   |    |   unknown |
+---------------+----------------+---------+-----------+----+-----------+

ubuntu@juju-b87344-2:~$ sudo -u snap_daemon patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml list
+ Cluster: postgresql (7496847632512033809) ----+-----------+----+-----------+
| Member        | Host           | Role         | State     | TL | Lag in MB |
+---------------+----------------+--------------+-----------+----+-----------+
| postgresql-1  | 10.189.210.201 | Sync Standby | streaming |  3 |         0 |
| postgresql-2  | 10.189.210.55  | Leader       | running   |  3 |           |
| postgresql-3  | 10.189.210.26  | Sync Standby | streaming |  3 |         0 |
+---------------+----------------+--------------+-----------+----+-----------+
Example: failover 
ubuntu@juju-b87344-2:~$ sudo -u snap_daemon patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml failover postgresql --candidate postgresql-3              
Current cluster list
+ Cluster: postgresql (7496847632512033809) ----+-----------+----+-----------+
| Member        | Host           | Role         | State     | TL | Lag in MB |
+---------------+----------------+--------------+-----------+----+-----------+
| postgresql-1  | 10.189.210.201 | Leader       | running   |  1 |           |
| postgresql-2  | 10.189.210.55  | Sync Standby | streaming |  1 |         0 |
| postgresql-3  | 10.189.210.26  | Sync Standby | streaming |  1 |         0 |
+---------------+----------------+--------------+-----------+----+-----------+
Are you sure you want to failover cluster postgresql, demoting current leader postgresql-1? [y/N]: y
2025-04-25 04:44:53.69748 Successfully failed over to "postgresql-3"
+ Cluster: postgresql (7496847632512033809) ---------+----+-----------+
| Member        | Host           | Role    | State   | TL | Lag in MB |
+---------------+----------------+---------+---------+----+-----------+
| postgresql-1  | 10.189.210.201 | Replica | stopped |    |   unknown |
| postgresql-2  | 10.189.210.55  | Replica | running |  1 |         0 |
| postgresql-3  | 10.189.210.26  | Leader  | running |  1 |           |
+---------------+----------------+---------+---------+----+-----------+

ubuntu@juju-b87344-2:~$ sudo -u snap_daemon patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml history
+----+-----------+------------------------------+----------------------------------+--------------+
| TL |       LSN | Reason                       | Timestamp                        | New Leader   |
+----+-----------+------------------------------+----------------------------------+--------------+
|  1 | 335544480 | no recovery target specified | 2025-04-25T04:44:53.137152+00:00 | postgresql-3 |
+----+-----------+------------------------------+----------------------------------+--------------+

ubuntu@juju-b87344-2:~$ sudo -u snap_daemon patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml list
+ Cluster: postgresql (7496847632512033809) ----+-----------+----+-----------+
| Member        | Host           | Role         | State     | TL | Lag in MB |
+---------------+----------------+--------------+-----------+----+-----------+
| postgresql-1  | 10.189.210.201 | Sync Standby | streaming |  2 |         0 |
| postgresql-2  | 10.189.210.55  | Sync Standby | streaming |  2 |         0 |
| postgresql-3  | 10.189.210.26  | Leader       | running   |  2 |           |
+---------------+----------------+--------------+-----------+----+-----------+

Re-initialisation

Sometimes the cluster member might stuck in the middle of nowhere, the easiest way to try is reinit the Patroni cluster member.

Example: cluster member re-initialization 
ubuntu@juju-b87344-2:~$ sudo -u snap_daemon patronictl -c /var/snap/charmed-postgresql/current/etc/patroni/patroni.yaml reinit postgresql postgresql-1
+ Cluster: postgresql (7496847632512033809) ----+-----------+----+-----------+
| Member        | Host           | Role         | State     | TL | Lag in MB |
+---------------+----------------+--------------+-----------+----+-----------+
| postgresql-1  | 10.189.210.201 | Sync Standby | streaming |  3 |         0 |
| postgresql-2  | 10.189.210.55  | Leader       | running   |  3 |           |
| postgresql-3  | 10.189.210.26  | Sync Standby | streaming |  3 |         0 |
+---------------+----------------+--------------+-----------+----+-----------+
Are you sure you want to reinitialize members postgresql-1? [y/N]: y
Success: reinitialize for member postgresql-1

Patroni REST API

Patroni provides most patronictl actions as a REST API. Use port 8008 to access Patroni REST API on any member/unit of Charmed PostgreSQL.

Example: read-only access via Patroni REST API 
ubuntu@juju360:~$ curl 10.189.210.55:8008/cluster | jq # where 10.189.210.55 is IP of Charmed PostgreSQL Juju unit
...
{
  "members": [
    {
      "name": "postgresql-1",
      "role": "sync_standby",
      "state": "streaming",
      "api_url": "http://10.189.210.201:8008/patroni",
      "host": "10.189.210.201",
      "port": 5432,
      "timeline": 3,
      "lag": 0
    },
    {
      "name": "postgresql-2",
      "role": "leader",
      "state": "running",
      "api_url": "http://10.189.210.55:8008/patroni",
      "host": "10.189.210.55",
      "port": 5432,
      "timeline": 3
    },
    {
      "name": "postgresql-3",
      "role": "sync_standby",
      "state": "streaming",
      "api_url": "http://10.189.210.26:8008/patroni",
      "host": "10.189.210.26",
      "port": 5432,
      "timeline": 3,
      "lag": 0
    }
  ],
  "scope": "postgresql"
}

Note: Patroni REST API can be accessed anonymously in read-only mode only. The Juju secret patroni-password is mandatory to apply any chances via Patroni REST API. Example of authenticated changes via Patroni REST API:

Example: write access via Patroni REST API 
> juju deploy postgresql -n 3 # and wait for deployment
> juju secrets | grep postgresql # find ID with 'patroni-password'
> juju show-secret --reveal ccccaaabbbbbbcgoi12345 | grep patroni-password # reveal password to access Patroni REST API
    patroni-password: patr0n1sup3rs3cretpassw0rd
...
> curl -k -u patroni:patr0n1sup3rs3cretpassw0rd -X POST https://10.151.27.242:8008/switchover -d '{"leader": "postgresql-0", "candidate": "postgresql-1"}'
Successfully switched over to "postgresql-1"

Hint: use dedicated promote-to-primary action to switchover Primary.

Pay attention to TLS relation with PostgreSQL and access Patroni REST API accordingly:

Example: access Patroni REST API with(out) TLS 
> curl    http://x.x.x.x:8008/cluster  # to access without TLS
> curl    https://x.x.x.x:8008/cluster # to access with trusted certificate
> curl -k https://x.x.x.x:8008/cluster # to access with self-signed certificate

Raft library

Patroni relies on the Raft library for the consensus handling and Primary election. It is implemented using pySyncObj and available as a CLI tool.

While you should not interact with Raft library manually, you can check its internal status:

Example: check Raft status 
> juju deploy postgresql -n 3 # and wait for deployment
> juju secrets | grep postgresql # find ID with 'raft-password'
> juju show-secret --reveal cvia1ibjihbbbcgoi12300 | grep raft-password # reveal password to access Raft
    raft-password: r@ftsup3rs3cretp@ssw0rd
> juju ssh postgresql/2
...
ubuntu@juju-b87344-2:~$ charmed-postgresql.syncobj-admin -status -conn 10.151.27.242:2222 -pass r@ftsup3rs3cretp@ssw0rd # where IP is a PostgreSQL Juju unit IP
commit_idx: 1396297
enabled_code_version: 0
has_quorum: True    <<<<<<<<<<<< Important Raft cluster health!
last_applied: 1396297
leader: 10.151.27.205:2222    <<<<<<<<<<<< Shows you the Raft leader
leader_commit_idx: 1396297
log_len: 69
match_idx_count: 2
match_idx_server_10.151.27.103:2222: 1310986
match_idx_server_10.151.27.205:2222: 1310986
next_node_idx_count: 2
next_node_idx_server_10.151.27.103:2222: 1310987
next_node_idx_server_10.151.27.205:2222: 1310987
partner_node_status_server_10.151.27.103:2222: 2
partner_node_status_server_10.151.27.205:2222: 2
partner_nodes_count: 2
raft_term: 92
readonly_nodes_count: 0    <<<<<<<<<<<< Raft members health
revision: deprecated
self: 10.151.27.242:2222
self_code_version: 0
state: 0    <<<<<<<<<<<< Self health
uptime: 2482881
version: 0.3.12

Note: password is mandatory to access Raft.

Note: pay attention to the CLI syntax (simple - used, avoid misstype with common -- prefix for parametes)

PostgreSQL tools

The Charmed PostgreSQL ships long list of standard pg_* tools, including pg_dump, pg_dumpall (and more). Due to the SNAP naming requirements they are available as pg-dump and pg-dumpall accordingly.

The entire list can bu found on systems with snap charmed-postgresql installed, e.g.:

Example: list of pg_* tools shipped by snap 
> snap list charmed-postgresql
Name                Version  Rev  Tracking       Publisher   Notes
charmed-postgresql  14.15    143  latest/stable  canonical✓  held

> charmed-postgresql.pg<TAB><TAB>
charmed-postgresql.pg-archivecleanup  charmed-postgresql.pg-conftool        charmed-postgresql.pg-dump            charmed-postgresql.pg-recvlogical     charmed-postgresql.pg-upgradecluster  charmed-postgresql.pgbouncer-server
charmed-postgresql.pg-backupcluster   charmed-postgresql.pg-createcluster   charmed-postgresql.pg-dumpall         charmed-postgresql.pg-renamecluster   charmed-postgresql.pg-virtualenv      
charmed-postgresql.pg-basebackup      charmed-postgresql.pg-ctl             charmed-postgresql.pg-isready         charmed-postgresql.pg-restore         charmed-postgresql.pgbackrest         
charmed-postgresql.pg-buildext        charmed-postgresql.pg-ctlcluster      charmed-postgresql.pg-lsclusters      charmed-postgresql.pg-restorecluster  charmed-postgresql.pgbench            
charmed-postgresql.pg-config          charmed-postgresql.pg-dropcluster     charmed-postgresql.pg-receivewal      charmed-postgresql.pg-updatedicts     charmed-postgresql.pgbouncer

pg_dump / pg_dumpall

Those tools are designed to dump the PostgreSQL content. Due to the strictly confined SNAP, the context is limited to SNAP itself (and not the POSIX user triggered the tool). Also, due to security reasons the default postgres user cannot access DB, use user operator (you can find it’s credential in Juju Secrets).

Example: pg-dump/pg-dumpall usage 
charmed-postgresql.pg-dump -c -C -U operator -W -h /tmp -d mydb | tee mydb.sql >/dev/null

Also you can pass password using env variable PGPASSWORD, however it is NOT recommended for productions as DB password will be stored in shell history:

PGPASSWORD=my$ecretp@ss charmed-postgresql.pg-dumpall -c -U operator -h /tmp  | tee all_DBs.txt >/dev/null

Note, the local SNAP /tmp could be used as well:

PGPASSWORD=my$ecretp@ss charmed-postgresql.pg-dumpall -c -U operator -h /tmp  -f /tmp/test.sql

> sudo ls -la /tmp/snap-private-tmp/snap.charmed-postgresql/tmp/test.sql 
-rw-rw-r-- 1 ubuntu ubuntu 19416 Jun  4 08:24 /tmp/snap-private-tmp/snap.charmed-postgresql/tmp/test.sql
Previous Overview

Last updated 2 days ago. Help improve this document in the forum.