4.2 註冊主節點

root@ubuntu12:~# su - postgres
postgres@ubuntu12:~$
postgres@ubuntu12:~$
postgres@ubuntu12:~$
postgres@ubuntu12:~$
postgres@ubuntu12:~$ pg_autoctl create postgres --hostname ubuntu12 --name ubuntu12 --auth trust --ssl-self-signed --pgdata /usr/local/pgsql16/pg9300/data/ --pgport 9300 --monitor 'postgres://autoctl_node@Ubuntu11:9300/pg_auto_failover?sslmode=require'
05:17:27 2094 INFO  Using default --ssl-mode "require"
05:17:27 2094 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic
05:17:27 2094 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.
05:17:27 2094 WARN  See https://www.postgresql.org/docs/current/libpq-ssl.html for details
05:17:27 2094 INFO  Started pg_autoctl postgres service with pid 2096
05:17:27 2094 INFO  Started pg_autoctl node-init service with pid 2097
05:17:27 2096 INFO   /usr/local/pgsql16/server/bin/pg_autoctl do service postgres --pgdata /usr/local/pgsql16/pg9300/data/ -v
05:17:27 2097 INFO  Registered node 52 "ubuntu12" (ubuntu12:9300) in formation "default", group 0, state "single"
05:17:27 2097 INFO  Writing keeper state file at "/home/postgres/.local/share/pg_autoctl/usr/local/pgsql16/pg9300/data/pg_autoctl.state"
05:17:27 2097 INFO  Writing keeper init state file at "/home/postgres/.local/share/pg_autoctl/usr/local/pgsql16/pg9300/data/pg_autoctl.init"
05:17:27 2097 INFO  Successfully registered as "single" to the monitor.
05:17:27 2097 INFO  FSM transition from "init" to "single": Start as a single node
05:17:27 2097 INFO  Initialising postgres as a primary
05:17:27 2097 INFO  Initialising a PostgreSQL cluster at "/usr/local/pgsql16/pg9300/data"
05:17:27 2097 INFO  /usr/local/pgsql16/server/bin/pg_ctl initdb -s -D /usr/local/pgsql16/pg9300/data --option '--auth=trust'
05:17:27 2097 INFO   /usr/bin/openssl req -new -x509 -days 365 -nodes -text -out /usr/local/pgsql16/pg9300/data/server.crt -keyout /usr/local/pgsql16/pg9300/data/server.key -subj "/CN=ubuntu12"
05:17:28 2122 INFO   /usr/local/pgsql16/server/bin/postgres -D /usr/local/pgsql16/pg9300/data -p 9300 -h *
05:17:28 2096 INFO  Postgres is now serving PGDATA "/usr/local/pgsql16/pg9300/data" on port 9300 with pid 2122
05:17:28 2097 INFO  The user "postgres" already exists, skipping.
05:17:28 2097 INFO  CREATE USER postgres
05:17:28 2097 INFO  CREATE DATABASE postgres;
05:17:28 2097 INFO  The database "postgres" already exists, skipping.
05:17:28 2097 INFO  CREATE EXTENSION pg_stat_statements;
05:17:28 2097 INFO  Disabling synchronous replication
05:17:28 2097 INFO  Reloading Postgres configuration and HBA rules
05:17:28 2097 WARN  Failed to resolve hostname "Ubuntu11" to an IP address that resolves back to the hostname on a reverse DNS lookup.
05:17:28 2097 WARN  Postgres might deny connection attempts from "Ubuntu11", even with the new HBA rules.
05:17:28 2097 WARN  Hint: correct setup of HBA with host names requires proper reverse DNS setup. You might want to use IP addresses.
05:17:28 2097 WARN  Using IP address "192.168.152.121" in HBA file instead of hostname "Ubuntu11"
05:17:28 2097 INFO  Reloading Postgres configuration and HBA rules
05:17:28 2097 INFO  Transition complete: current state is now "single"
05:17:28 2097 INFO  keeper has been successfully initialized.
05:17:28 2094 WARN  pg_autoctl service node-init exited with exit status 0
05:17:28 2096 INFO  Postgres controller service received signal SIGTERM, terminating
05:17:28 2096 INFO  Stopping pg_autoctl postgres service
05:17:28 2096 INFO  /usr/local/pgsql16/server/bin/pg_ctl --pgdata /usr/local/pgsql16/pg9300/data --wait stop --mode fast
05:17:28 2094 INFO  Stop pg_autoctl
postgres@ubuntu12:~$

root@ubuntu11:/usr/local/pg_install_packgae#
root@ubuntu11:/usr/local/pg_install_packgae# pg_autoctl show uri
        Type |    Name | Connection String
-------------+---------+-------------------------------
     monitor | monitor | postgres://autoctl_node@127.0.0.1:9300/pg_auto_failover?sslmode=require
   formation | default | postgres://ubuntu12:9300/postgres?target_session_attrs=read-write&sslmode=require

root@ubuntu11:/usr/local/pg_install_packgae#
root@ubuntu11:/usr/local/pg_install_packgae#
root@ubuntu11:/usr/local/pg_install_packgae#
root@ubuntu11:/usr/local/pg_install_packgae# pg_autoctl show state
    Name |  Node |     Host:Port |       TLI: LSN |   Connection |      Reported State |      Assigned State
---------+-------+---------------+----------------+--------------+---------------------+--------------------
ubuntu12 |     8 | ubuntu12:9300 |   1: 0/15596F8 |   read-write |              single |              single

root@ubuntu11:/usr/local/pg_install_packgae#

root@ubuntu13:/usr/local/pg_install_package# systemctl stop postgresql9300
root@ubuntu13:/usr/local/pg_install_package# systemctl disable postgresql9300
root@ubuntu13:/usr/local/pg_install_package# rm -rf /usr/local/pgsql16/pg9300

postgres@ubuntu13:~$ pg_autoctl create postgres --hostname ubuntu13 --name ubuntu13 --auth trust --ssl-self-signed --pgdata /usr/local/pgsql16/pg9300/data/ --pgport 9300 --monitor 'postgres://autoctl_node@Ubuntu11:9300/pg_auto_failover?sslmode=require'
05:46:07 11100 INFO  Using default --ssl-mode "require"
05:46:07 11100 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic
05:46:07 11100 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.
05:46:07 11100 WARN  See https://www.postgresql.org/docs/current/libpq-ssl.html for details
05:46:07 11100 INFO  Started pg_autoctl postgres service with pid 11102
05:46:07 11100 INFO  Started pg_autoctl node-init service with pid 11103
05:46:07 11102 INFO   /usr/local/pgsql16/server/bin/pg_autoctl do service postgres --pgdata /usr/local/pgsql16/pg9300/data/ -v
05:46:07 11103 INFO  Registered node 60 "ubuntu13" (ubuntu13:9300) in formation "default", group 0, state "wait_standby"
05:46:07 11103 INFO  Writing keeper state file at "/home/postgres/.local/share/pg_autoctl/usr/local/pgsql16/pg9300/data/pg_autoctl.state"
05:46:07 11103 INFO  Writing keeper init state file at "/home/postgres/.local/share/pg_autoctl/usr/local/pgsql16/pg9300/data/pg_autoctl.init"
05:46:07 11103 INFO  Successfully registered as "wait_standby" to the monitor.
05:46:07 11103 INFO  FSM transition from "init" to "wait_standby": Start following a primary
05:46:07 11103 INFO  Transition complete: current state is now "wait_standby"
05:46:07 11103 INFO  New state for node 52 "ubuntu12" (ubuntu12:9300): single ➜ wait_primary
05:46:07 11103 INFO  New state for node 52 "ubuntu12" (ubuntu12:9300): wait_primary ➜ wait_primary
05:46:07 11103 INFO  FSM transition from "wait_standby" to "catchingup": The primary is now ready to accept a standby
05:46:07 11103 INFO  Initialising PostgreSQL as a hot standby
05:46:07 11103 INFO   /usr/local/pgsql16/server/bin/pg_basebackup -w -d 'application_name=pgautofailover_standby_60 host=ubuntu12 port=9300 user=pgautofailover_replicator sslmode=require' --pgdata /usr/local/pgsql16/pg9300/backup/node_60 -U pgautofailover_replicator --verbose --progress --max-rate 100M --wal-method=stream --slot pgautofailover_standby_60
05:46:07 11103 INFO  pg_basebackup:
05:46:07 11103 INFO
05:46:07 11103 INFO  initiating base backup, waiting for checkpoint to complete
05:46:07 11103 INFO  pg_basebackup:
05:46:07 11103 INFO
05:46:07 11103 INFO  checkpoint completed
05:46:07 11103 INFO  pg_basebackup:
05:46:07 11103 INFO
05:46:07 11103 INFO  write-ahead log start point: 0/2000028 on timeline 1
05:46:07 11103 INFO  pg_basebackup:
05:46:07 11103 INFO
05:46:07 11103 INFO  starting background WAL receiver
05:46:07 11103 INFO  22591/22591 kB (100%), 0/1 tablespace (...backup/node_60/global/pg_control)
05:46:08 11103 INFO  22591/22591 kB (100%), 1/1 tablespace
05:46:08 11103 INFO  pg_basebackup: write-ahead log end point: 0/2000138
05:46:08 11103 INFO  pg_basebackup: waiting for background process to finish streaming ...
05:46:08 11103 INFO  pg_basebackup: syncing data to disk ...
05:46:08 11103 INFO  pg_basebackup: renaming backup_manifest.tmp to backup_manifest
05:46:08 11103 INFO  pg_basebackup: base backup completed
05:46:08 11103 INFO  Creating the standby signal file at "/usr/local/pgsql16/pg9300/data/standby.signal", and replication setup at "/usr/local/pgsql16/pg9300/data/postgresql-auto-failover-standby.conf"
05:46:08 11103 INFO   /usr/bin/openssl req -new -x509 -days 365 -nodes -text -out /usr/local/pgsql16/pg9300/data/server.crt -keyout /usr/local/pgsql16/pg9300/data/server.key -subj "/CN=ubuntu13"
05:46:08 11116 INFO   /usr/local/pgsql16/server/bin/postgres -D /usr/local/pgsql16/pg9300/data -p 9300 -h *
05:46:08 11102 INFO  Postgres is now serving PGDATA "/usr/local/pgsql16/pg9300/data" on port 9300 with pid 11116
05:46:08 11103 INFO  PostgreSQL started on port 9300
05:46:08 11103 INFO  Fetched current list of 1 other nodes from the monitor to update HBA rules, including 1 changes.
05:46:08 11103 INFO  Ensuring HBA rules for node 52 "ubuntu12" (ubuntu12:9300)
05:46:08 11103 INFO  Adding HBA rule: hostssl replication "pgautofailover_replicator" ubuntu12 trust
05:46:08 11103 INFO  Adding HBA rule: hostssl "postgres" "pgautofailover_replicator" ubuntu12 trust
05:46:08 11103 INFO  Writing new HBA rules in "/usr/local/pgsql16/pg9300/data/pg_hba.conf"
05:46:08 11103 INFO  Reloading Postgres configuration and HBA rules
05:46:08 11103 INFO  Transition complete: current state is now "catchingup"
05:46:08 11103 INFO  keeper has been successfully initialized.
05:46:08 11100 WARN  pg_autoctl service node-init exited with exit status 0
05:46:08 11102 INFO  Postgres controller service received signal SIGTERM, terminating
05:46:08 11102 INFO  Stopping pg_autoctl postgres service
05:46:08 11102 INFO  /usr/local/pgsql16/server/bin/pg_ctl --pgdata /usr/local/pgsql16/pg9300/data --wait stop --mode fast
05:46:08 11100 INFO  Stop pg_autoctl
postgres@ubuntu13:~$

root@ubuntu13:/usr/local/pg_install_package#  pg_autoctl  show systemd
05:48:11 12172 INFO  HINT: to complete a systemd integration, run the following commands (as root):
05:48:11 12172 INFO  pg_autoctl -q show systemd --pgdata "/usr/local/pgsql16/pg9300/data" | tee /etc/systemd/system/pgautofailover.service
05:48:11 12172 INFO  systemctl daemon-reload
05:48:11 12172 INFO  systemctl enable pgautofailover
05:48:11 12172 INFO  systemctl start pgautofailover
[Unit]
Description = pg_auto_failover

[Service]
WorkingDirectory = /home/postgres
Environment = 'PGDATA=/usr/local/pgsql16/pg9300/data'
User = postgres
ExecStart = /usr/local/pgsql16/server/bin/pg_autoctl run
Restart = always
StartLimitBurst = 0
ExecReload = /usr/local/pgsql16/server/bin/pg_autoctl reload

[Install]
WantedBy = multi-user.target
root@ubuntu13:/usr/local/pg_install_package# pg_autoctl -q show systemd --pgdata "/usr/local/pgsql16/pg9300/data" | tee /etc/systemd/system/pgautofailover.service
[Unit]
Description = pg_auto_failover

[Service]
WorkingDirectory = /home/postgres
Environment = 'PGDATA=/usr/local/pgsql16/pg9300/data'
User = postgres
ExecStart = /usr/local/pgsql16/server/bin/pg_autoctl run
Restart = always
StartLimitBurst = 0
ExecReload = /usr/local/pgsql16/server/bin/pg_autoctl reload

[Install]
WantedBy = multi-user.target
root@ubuntu13:/usr/local/pg_install_package# systemctl daemon-reload
root@ubuntu13:/usr/local/pg_install_package# systemctl enable pgautofailover
root@ubuntu13:/usr/local/pg_install_package# systemctl start pgautofailover
root@ubuntu13:/usr/local/pg_install_package#
root@ubuntu13:/usr/local/pg_install_package# systemctl status pgautofailover
● pgautofailover.service - pg_auto_failover
     Loaded: loaded (/etc/systemd/system/pgautofailover.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2025-10-09 05:48:26 UTC; 5s ago
   Main PID: 12381 (pg_autoctl)
      Tasks: 9 (limit: 4550)
     Memory: 42.0M
     CGroup: /system.slice/pgautofailover.service
             ├─12381 /usr/local/pgsql16/server/bin/pg_autoctl run
             ├─12391 pg_autoctl: start/stop postgres
             ├─12392 pg_autoctl: node active
             ├─12402 /usr/local/pgsql16/server/bin/postgres -D /usr/local/pgsql16/pg9300/data -p 9300 -h *
             ├─12403 postgres: logger
             ├─12404 postgres: checkpointer
             ├─12405 postgres: background writer
             ├─12406 postgres: startup recovering 000000010000000000000003
             └─12407 postgres: walreceiver streaming 0/3000110

Oct 09 05:48:27 ubuntu13 pg_autoctl[12391]: 05:48:27 12391 INFO  Postgres is now serving PGDATA "/usr/local/pgsql16/pg9300/data" on port 9300 with pid 12402
Oct 09 05:48:27 ubuntu13 pg_autoctl[12392]: 05:48:27 12392 WARN  PostgreSQL was not running, restarted with pid 12402
Oct 09 05:48:28 ubuntu13 pg_autoctl[12392]: 05:48:28 12392 INFO  Updated the keeper's state from the local PostgreSQL instance, which is running
Oct 09 05:48:28 ubuntu13 pg_autoctl[12392]: 05:48:28 12392 INFO  pg_autoctl managed to ensure current state "catchingup": PostgreSQL is running
Oct 09 05:48:29 ubuntu13 pg_autoctl[12392]: 05:48:29 12392 INFO  Monitor assigned new state "secondary"
Oct 09 05:48:29 ubuntu13 pg_autoctl[12392]: 05:48:29 12392 INFO  FSM transition from "catchingup" to "secondary": Convinced the monitor that I'm up and running, and eligible for promotion again
Oct 09 05:48:29 ubuntu13 pg_autoctl[12392]: 05:48:29 12392 INFO  Reached timeline 1, same as upstream node 52 "ubuntu12" (ubuntu12:9300)
Oct 09 05:48:29 ubuntu13 pg_autoctl[12392]: 05:48:29 12392 INFO  Creating replication slot "pgautofailover_standby_52"
Oct 09 05:48:29 ubuntu13 pg_autoctl[12392]: 05:48:29 12392 INFO  Transition complete: current state is now "secondary"
Oct 09 05:48:29 ubuntu13 pg_autoctl[12392]: 05:48:29 12392 INFO  New state for node 52 "ubuntu12" (ubuntu12:9300): primary ➜ primary
root@ubuntu13:/usr/local/pg_install_package#

5.2 monitor 節點查看已註冊的從節點

再次回到monitor節點查看，可看到ubuntu11作為monitor節點，ubuntu12作為主節點，ubuntu13作為從節點

root@ubuntu11:~#
root@ubuntu11:~# pg_autoctl show uri;
        Type |    Name | Connection String
-------------+---------+-------------------------------
     monitor | monitor | postgres://autoctl_node@ubuntu11:9300/pg_auto_failover?sslmode=require
   formation | default | postgres://ubuntu12:9300,ubuntu13:9300/postgres?target_session_attrs=read-write&sslmode=require

root@ubuntu11:~#
root@ubuntu11:~#
root@ubuntu11:~# pg_autoctl show state
    Name |  Node |     Host:Port |       TLI: LSN |   Connection |      Reported State |      Assigned State
---------+-------+---------------+----------------+--------------+---------------------+--------------------
ubuntu12 |    52 | ubuntu12:9300 |   1: 0/3000148 |   read-write |             primary |             primary
ubuntu13 |    60 | ubuntu13:9300 |   1: 0/3000148 |    read-only |           secondary |           secondary

root@ubuntu11:~#
root@ubuntu11:~#

6，pg_auto_failover外部訪問配置

ALTER USER postgres WITH PASSWORD 'a-strong-password';

1，修改從節點ubuntu13的hba.conf，添加一下訪問規則
hostssl postgres all 192.168.0.0/16 md5

2，重啓主節點ubuntu12，此時故障轉移，ubuntu13從節點提升為主節點，同時ubuntu13作為新的主節點，配置規則會覆蓋從節點ubuntu12
pg_auto_failover的hba.conf覆蓋規則是“新的主節點覆蓋舊的主節點”，這一點有點繞，有興趣的自己測試驗證

然後從客户端連接至pg_auto_failover集羣的主節點，查看複製狀態，其實跟手動搭建的流複製就一樣了，只不過是pg_auto_failover把整個postgresql集羣的搭建過程給屏蔽掉了

SELECT * FROM pg_replication_slots;

slot_name                |plugin|slot_type|datoid|database|temporary|active|active_pid|xmin|catalog_xmin|restart_lsn|confirmed_flush_lsn|wal_status|safe_wal_size|two_phase|conflicting|
-------------------------+------+---------+------+--------+---------+------+----------+----+------------+-----------+-------------------+----------+-------------+---------+-----------+
pgautofailover_standby_60|      |physical |      |        |false    |true  |     11783|747 |            |0/5020868  |                   |reserved  |             |false    |           |



select * from pg_stat_replication;

pid  |usesysid|usename                  |application_name         |client_addr    |client_hostname|client_port|backend_start                |backend_xmin|state    |sent_lsn |write_lsn|flush_lsn|replay_lsn|write_lag      |flush_lag      |replay_lag     |sync_priority|sync_state|reply_time                   |
-----+--------+-------------------------+-------------------------+---------------+---------------+-----------+-----------------------------+------------+---------+---------+---------+---------+----------+---------------+---------------+---------------+-------------+----------+-----------------------------+
11783|   16416|pgautofailover_replicator|pgautofailover_standby_60|192.168.152.123|ubuntu13       |      60426|2025-10-09 13:23:00.095 +0800|            |streaming|0/5020868|0/5020868|0/5020868|0/5020868 |00:00:00.001064|00:00:00.001766|00:00:00.001773|            1|quorum    |2025-10-09 13:51:42.416 +0800|

可以看到，pg_auto_failover在一主一從的模式下，是同步複製

select * from pg_settings where name like '%synchronous_commit%';

name              |setting|unit|category                  |short_desc                                           |extra_desc|context|vartype|source            |min_val|max_val|enumvals                                |boot_val|reset_val|sourcefile                                                  |sourceline|pending_restart|
------------------+-------+----+--------------------------+-----------------------------------------------------+----------+-------+-------+------------------+-------+-------+----------------------------------------+--------+---------+------------------------------------------------------------+----------+---------------+
synchronous_commit|on     |    |Write-Ahead Log / Settings|Sets the current transaction''s synchronization level.|          |user   |enum   |configuration file|       |       |{local,remote_write,remote_apply,on,off}|on      |on       |/usr/local/pgsql16/pg9300/data/postgresql-auto-failover.conf|        12|false          |


select * from pg_settings where name like '%synchronous_standby_names%' ;

name                     |setting                          |unit|category                    |short_desc                                                                     |extra_desc|context|vartype|source            |min_val|max_val|enumvals|boot_val|reset_val                        |sourcefile                                         |sourceline|pending_restart|
-------------------------+---------------------------------+----+----------------------------+-------------------------------------------------------------------------------+----------+-------+-------+------------------+-------+-------+--------+--------+---------------------------------+---------------------------------------------------+----------+---------------+
synchronous_standby_names|ANY 1 (pgautofailover_standby_60)|    |Replication / Primary Server|Number of synchronous standbys and list of names of potential synchronous ones.|          |sighup |string |configuration file|       |       |NULL    |        |ANY 1 (pgautofailover_standby_60)|/usr/local/pgsql16/pg9300/data/postgresql.auto.conf|         4|false          |