how to reload pg_hba.conf or restart postgresql ?

[changchichung]

I already create a 3 nodes cluster and get pg_auto_failover.
but when I try to connect via URI , there's messages complain about pg_hba.conf

postgres@pg-slave:~$ psql -d postgres://pg-slave:5432,pg-primary:5432/postgres?target_session_attrs=read-write
psql: FATAL:  no pg_hba.conf entry for host "192.168.11.153", user "postgres", database "postgres", SSL off
postgres@pg-slave:~$

so I modify /var/lib/postgresql/11/main/pg_hba.conf (my pgdata path) using the command below

echo "host all postgres 192.168.11.0/24 trust" >> /var/lib/postgresql/11/main/pg_hba.conf
tail -5 /var/lib/postgresql/11/main/pg_hba.conf

and the pg_hba.conf looks like

host "postgres" "postgres" pg-primary trust # Auto-generated by pg_auto_failover
host all "pgautofailover_monitor" monitor trust # Auto-generated by pg_auto_failover
host replication "pgautofailover_replicator" pg-slave trust # Auto-generated by pg_auto_failover
host "postgres" "pgautofailover_replicator" pg-slave trust # Auto-generated by pg_auto_failover
host	all	postgres	192.168.11.0/24	trust

ok , now , in normal situation , I have to restart postgresql service to make new pg_hba.conf works
but I have no idea how to do so with pg_auto_failover .
there is a systemd config with pg_auto_failover , but looks like not iomplement into OS ? (or it did , but I find nothing related in /etc/systemd/system/multi-user.target.wants)

so , here is my question , how do I reload/restart postgresql to make new pg_hba.conf works ?
I already try pg_autoctl reload

2019-09-19 07:51:58 [administrator@pg-slave ~]$ sudo runuser -l postgres -c "pg_autoctl reload --pgdata /var/lib/postgresql/11/main"
07:52:15 INFO  Managing PostgreSQL installation at "/var/lib/postgresql/11/main"
2019-09-19 07:52:15 [administrator@pg-slave ~]$ sudo runuser -l postgres -c "psql -d postgres://pg-slave:5432,pg-primary:5432/postgres?target_session_attrs=read-write"
psql: FATAL:  no pg_hba.conf entry for host "192.168.11.153", user "postgres", database "postgres", SSL off
2019-09-19 07:52:24 [administrator@pg-slave ~]$

stop the keeper and run again

2019-09-19 07:53:22 [administrator@pg-slave ~]$ sudo runuser -l postgres -c "pg_autoctl run --pgdata /var/lib/postgresql/11/main &"
2019-09-19 07:53:32 [administrator@pg-slave ~]$ 07:53:32 INFO  Managing PostgreSQL installation at "/var/lib/postgresql/11/main"
07:53:32 INFO  Found a stale pidfile at "/tmp/pg_autoctl/var/lib/postgresql/11/main/pg_autoctl.pid"
07:53:32 WARN  Removing the stale pid file "/tmp/pg_autoctl/var/lib/postgresql/11/main/pg_autoctl.pid"
07:53:32 INFO  The version of extenstion "pgautofailover" is "1.0" on the monitor
07:53:32 INFO  pg_autoctl service is starting
07:53:32 INFO  Calling node_active for node default/2/0 with current state: secondary, PostgreSQL is running, sync_state is "", WAL delta is 0.

2019-09-19 07:53:34 [administrator@pg-slave ~]$ sudo runuser -l postgres -c "psql -d postgres://pg-slave:5432,pg-primary:5432/postgres?target_session_attrs=read-write"07:53:37 INFO  Calling node_active for node default/2/0 with current state: secondary, PostgreSQL is running, sync_state is "", WAL delta is 0.

psql: FATAL:  no pg_hba.conf entry for host "192.168.11.153", user "postgres", database "postgres", SSL off
.

please , any suggestions ?

[DimCitus]

Hi @changchichung,

Your change in HBA requires a reload of PostgreSQL, you don't have to implement a restart at this point. This can be done in SQL with the following query, when connected to your PostgreSQL node:

SELECT pg_reload_conf();

You can do it from the command line too, using pg_ctl reload and other OS provided variants.

Now, the pg_autoctl commands have impact on the pg_autoctl service itself. The pg_autoctl reload command makes pg_autoctl reloads its own configuration, and has no impact on the underlying PostgreSQL service.

About systemd integration, please have a look at the documentation at https://pg-auto-failover.readthedocs.io/en/latest/install.html#installing-a-pgautofailover-systemd-unit. The command provides the content of the configuration file you can use with systemd. You still need to log-in as root and make the systemd changes yourself.