Attempt to create a secondary node instead renames the first node (follow-up to #647)

[sacrophyte]

(following the same environment from #647 )

I am attempting to add a secondary node on a different host - however, when I do, the first node is renamed. So I have two questions:

1. Why was the first node renamed (when that is clearly what I did not want)?
2. How do I add a secondary?

[postgres@urblimsdbdev2a ~]$ cat ./autoctl_create_postgres_dev2b
pg_autoctl create postgres \
 --pgdata $DB_HOME/data \
 --pgport 5432 \
 --pghost urblimsdbdev2b.admin.uillinois.edu \
 --hostname urblimsdbdev2b.admin.uillinois.edu \
 --dbname postgres \
 --name dev2b \
 --pg-hba-lan \
 --auth trust \
 --ssl-self-signed \
 --monitor 'postgres://autoctl_node@urblimsdbdev2a.admin.uillinois.edu:5433/pg_auto_failover?sslmode=require'
[postgres@urblimsdbdev2a ~]$ ./autoctl_create_postgres_dev2b
09:25:06 1497021 INFO  Using default --ssl-mode "require"
09:25:06 1497021 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic
09:25:06 1497021 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.
09:25:06 1497021 WARN  See https://www.postgresql.org/docs/current/libpq-ssl.html for details
09:25:07 1497021 INFO  Node name is now "dev2b", used to be "dev2a"
09:25:07 1497021 INFO  Node hostname is now "urblimsdbdev2b.admin.uillinois.edu", used to be "urblimsdbdev2a.admin.uillinois.edu"
09:25:07 1497021 INFO  Started pg_autoctl postgres service with pid 1497030
09:25:07 1497030 INFO   /usr/pgsql-12/bin/pg_autoctl do service postgres --pgdata /var/lib/pgsql/12/data -v
09:25:07 1497021 INFO  Started pg_autoctl node-init service with pid 1497031
09:25:07 1497031 FATAL The state file "/var/lib/pgsql/.local/share/pg_autoctl/var/lib/pgsql/12/data/pg_autoctl.state" exists and there's no init in progress
09:25:07 1497031 INFO  HINT: use `pg_autoctl run` to start the service.
09:25:07 1497034 INFO   /usr/pgsql-12/bin/postgres -D /var/lib/pgsql/12/data -p 5432 -h *
09:25:07 1497021 WARN  pg_autoctl service node-init exited with exit status 0
09:25:07 1497030 INFO  Postgres is now serving PGDATA "/var/lib/pgsql/12/data" on port 5432 with pid 1497034
09:25:07 1497030 INFO  Postgres controller service received signal SIGTERM, terminating
09:25:07 1497030 INFO  Stopping pg_autoctl postgres service
09:25:07 1497030 INFO  /usr/pgsql-12/bin/pg_ctl --pgdata /var/lib/pgsql/12/data --wait stop --mode fast
09:25:07 1497021 INFO  Waiting for subprocesses to terminate.
09:25:07 1497021 INFO  Stop pg_autoctl
[postgres@urblimsdbdev2a ~]$  pg_autoctl show state --pgdata $DB_HOME/monitor
 Name |  Node |                               Host:Port |       LSN | Reachable |       Current State |      Assigned State
------+-------+-----------------------------------------+-----------+-----------+---------------------+--------------------
dev2b |     4 | urblimsdbdev2b.admin.uillinois.edu:5432 | 0/17EEFB0 |       yes |              single |              single

[DimCitus]

I see the following:

09:25:07 1497021 INFO  Node name is now "dev2b", used to be "dev2a"
09:25:07 1497021 INFO  Node hostname is now "urblimsdbdev2b.admin.uillinois.edu", used to be "urblimsdbdev2a.admin.uillinois.edu"

And then

09:25:07 1497031 FATAL The state file "/var/lib/pgsql/.local/share/pg_autoctl/var/lib/pgsql/12/data/pg_autoctl.state" exists and there's no init in progress

And my first diagnostic is that you somehow did copy all your configuration and state files from the first node in the second node. You're not creating a new node from scratch, you're reusing the first node as the second node on a different host, but will all the same pg_autoctl internal files again. Confusing.

[sacrophyte]

Your response actually gives me a clue. If you look closely, I am running this second command on the same host as the first. I think what you are saying is that I should run this second command from the second host. Not the first host.

[sacrophyte]

I am running on the second node now but still get strange errors. Would you please help me understand what is going on and how to fix the problem?

[postgres@urblimsdbdev2b ~]$ cat  ./autoctl_create_postgres_dev2b
pg_autoctl create postgres \
 --pgdata $DB_HOME/data \
 --pgport 5432 \
 --dbname postgres \
 --name dev2b \
 --pg-hba-lan \
 --auth trust \
 --ssl-self-signed \
 --monitor 'postgres://autoctl_node@urblimsdbdev2a.admin.uillinois.edu:5433/pg_auto_failover?sslmode=require'
[postgres@urblimsdbdev2b ~]$ ./autoctl_create_postgres_dev2b
11:38:23 3584645 INFO  Using default --ssl-mode "require"
11:38:23 3584645 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic
11:38:23 3584645 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.
11:38:23 3584645 WARN  See https://www.postgresql.org/docs/current/libpq-ssl.html for details
11:38:23 3584645 INFO  Started pg_autoctl postgres service with pid 3584648
11:38:23 3584648 INFO   /usr/pgsql-12/bin/pg_autoctl do service postgres --pgdata /var/lib/pgsql/12/data -v
11:38:23 3584645 INFO  Started pg_autoctl node-init service with pid 3584649
11:38:23 3584649 INFO  Continuing from a previous `pg_autoctl create` failed attempt
11:38:23 3584649 INFO  PostgreSQL state at registration time was: PGDATA does not exists
11:38:23 3584649 INFO  FSM transition from "wait_standby" to "catchingup": The primary is now ready to accept a standby
11:38:23 3584649 INFO  Initialising PostgreSQL as a hot standby
11:38:23 3584649 WARN  Failed to connect to "port=5432 dbname=postgres host=urblimsdbdev2a.admin.uillinois.edu user=pgautofailover_replicator", retrying until the server is ready
11:38:23 3584649 WARN  Connection to database failed: fe_sendauth: no password supplied
11:38:23 3584649 WARN  Failed to connect after successful ping, please verify authentication and logs on the server at "port=5432 dbname=postgres host=urblimsdbdev2a.admin.uillinois.edu user=pgautofailover_replicator"
11:38:23 3584649 WARN  Authentication might have failed on the Postgres server due to missing HBA rules.

Note that pg_autoctl did add entries into the pg_hba.conf. It is hard for me to know if they are the "correct" entries, but they look good to me:

hostssl replication "pgautofailover_replicator" urblimsdbdev2b.admin.uillinois.edu trust # Auto-generated by pg_auto_failover
hostssl "postgres" "pgautofailover_replicator" urblimsdbdev2b.admin.uillinois.edu trust # Auto-generated by pg_auto_failover

[sacrophyte]

First, thanks to @DimCitus pointing out that the configuration seemed the same, I was actually running the command on the first (primary) node. So I switched to the secondary node.

On the secondary node, I encountered another problem where pg_autoctl could not connect to the primary. I solved this second problem by changing a default rule in pg_hba.conf that was requiring all incoming connections to use a password, despite various other rules that set the replicator to trust mode.