pg_autoctl : Get list of all formations

[PatrickBzh]

Hello,
I'm playing with a single monitor, with several saved formations.

Each formation is composed of two PostgreSQL nodes.

I haven't found a way to list all the formations of a monitor using pg_autoctl.

The only workaround I've found is to query the monitor's pg_auto_failover database directly, but I don't really like this idea

[postgres@vpostgres-test3-ope:~ 02:51 PM]$ psql -p 3000 pg_auto_failover -c "select formationid, nodename, nodeport, goalstate, reportedstate, reportedpgisrunning, health  from pgautofailover.node;"
 formationid |          nodename           | nodeport | goalstate | reportedstate | reportedpgisrunning | health 
-------------+-----------------------------+----------+-----------+---------------+---------------------+--------
 14-isi      | 14-isi-vpostgres-test1-ope  |     8433 | primary   | primary       | t                   |      1
 14-isi      | 14-isi-vpostgres-test2-ope  |     8433 | secondary | secondary     | t                   |      1
 17-test     | 17-test-vpostgres-test1-ope |     8432 | primary   | primary       | t                   |      1
 17-test     | 17-test-vpostgres-test2-ope |     8432 | secondary | secondary     | t                   |      1
(4 rows)

Is there a pg_autoctl way to get a list of all formations for a specified monitor instance ?

Many thanks !!

Patrick.

[PatrickBzh]

I've found the pg_autoctl show uri command.. Filtering out default & monitor rows is quite a fine/fast solution

pg_autoctl show uri --pgdata "/pg_data/17-monitor/instance" |grep -Ev ' (monitor|default) '
        Type |    Name | Connection String
-------------+---------+-------------------------------
   formation | 17-test | postgres://vpostgres-test2-ope.ifremer.fr:8432,vpostgres-test1-ope.ifremer.fr:8432/postgres?target_session_attrs=read-write&sslmode=require
   formation |  14-isi | postgres://vpostgres-test2-ope.ifremer.fr:8433,vpostgres-test1-ope.ifremer.fr:8433/postgres?target_session_attrs=read-write&sslmode=require

it should be a good starting point for shell script usages.

[dimitri]

For scripting, have a look at the --json output format support. Then you can get advanced processing with the jq tool for example.

[PatrickBzh]

yep, I use it too ^^
the example was just a basic one without any additionnal package.