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: cluster status
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"
}
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)