search

hapostgres / pg_auto_failover

Postgres extension and service for automated failover and high-availability
Other
1.09k stars 114 forks source link

Specify PGDATA and PGCONF_DIR #854

Closed wenq1 closed 2 years ago

wenq1 commented 2 years ago

When using an existing cluster primary by specifying --pgdata on Ubuntu 18.04, the following error log is produced:

19:43:41 2520 WARN  Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_ident.conf" does not exist
19:43:41 2520 WARN  Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_hba.conf" does not exist
19:43:41 2520 FATAL Failed to setup your Postgres instance the PostgreSQL way, see above for details
19:43:41 2511 ERROR pg_autoctl service node-init exited with exit status 12
sudo -Hiu postgres pg_autoctl create postgres \
    --skip-pg-hba \
    --no-ssl \
    --username ha-admin \
    --dbname testdb \
    --hostname testhost \
    --pgdata /var/lib/postgresql/13/main \
    --pgctl /usr/lib/postgresql/13/bin/pg_ctl \
    --monitor 'postgres://autoctl_node@testhost/pg_auto_failover'

Note that /var/lib/postgresql/13/main exists, but the config file is located at /etc/postgresql/13/main/postgresql.conf as per the default installation. Is there anyway to specify the config file location along with --pgdata?

DimCitus commented 2 years ago

What version of pg_auto_failover are you using? Any modern/recent enough version of pg_autoctl should be able to figure out where the configuration files are... can you also paste the logs when using the -vv verbosity level?

wenq1 commented 2 years ago

latest version (v1.6) for postgresql-13 is used.

$ apt list --installed | grep auto-failover
pg-auto-failover-cli-1.6/bionic,now 1.6.3-1 amd64 [installed,automatic]
postgresql-13-auto-failover-1.6/bionic,now 1.6.3-1 amd64 [installed]
wenq1 commented 2 years ago 
00:17:52 8920 WARN  pgsetup.c:1747: No encryption is used for network traffic! This allows an attacker on the network to read all replication data.
00:17:52 8920 WARN  pgsetup.c:1749: Using --ssl-self-signed instead of --no-ssl is recommend to achieve more security with the same ease of deployment.
00:17:52 8920 WARN  pgsetup.c:1751: See https://www.postgresql.org/docs/current/libpq-ssl.html for details on how to improve
00:17:52 8920 INFO  pgsetup.c:1759: Using default --ssl-mode "prefer"
00:17:52 8920 DEBUG keeper_config.c:395: Reading configuration from /var/lib/postgresql/.config/pg_autoctl/var/lib/postgresql/13/main/pg_autoctl.cfg
00:17:52 8920 DEBUG pgsetup.c:122: pg_setup_init: /usr/lib/postgresql/13/bin/pg_ctl version 13.5
00:17:52 8920 DEBUG supervisor.c:100: Starting pg_autoctl postgres service
00:17:52 8920 DEBUG service_postgres_ctl.c:83: pg_autoctl started postgres controller in subprocess 8924
00:17:52 8920 INFO  supervisor.c:114: Started pg_autoctl postgres service with pid 8924
00:17:52 8920 DEBUG supervisor.c:100: Starting pg_autoctl node-init service
00:17:52 8924 INFO  service_postgres_ctl.c:151:  /usr/bin/pg_autoctl do service postgres --pgdata /var/lib/postgresql/13/main -vv
00:17:52 8920 DEBUG service_keeper_init.c:155: pg_autoctl node installer process started in subprocess 8925
00:17:52 8920 INFO  supervisor.c:114: Started pg_autoctl node-init service with pid 8925
00:17:52 8925 DEBUG pgctl.c:212: /usr/lib/postgresql/13/bin/pg_controldata /var/lib/postgresql/13/main
00:17:52 8925 WARN  debian.c:500: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_ident.conf" does not exist
00:17:52 8925 WARN  debian.c:507: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_hba.conf" does not exist
00:17:52 8925 FATAL keeper_pg_init.c:117: Failed to setup your Postgres instance the PostgreSQL way, see above for details
00:17:53 8924 DEBUG cli_common.c:1021: Managing PostgreSQL installation at "/var/lib/postgresql/13/main"
00:17:53 8924 DEBUG config.c:287: Probing configuration file "/var/lib/postgresql/.config/pg_autoctl/var/lib/postgresql/13/main/pg_autoctl.cfg"
00:17:53 8924 DEBUG config.c:320: ProbeConfigurationFileRole: keeper
00:17:53 8924 DEBUG keeper_config.c:395: Reading configuration from /var/lib/postgresql/.config/pg_autoctl/var/lib/postgresql/13/main/pg_autoctl.cfg
00:17:53 8924 DEBUG pgsetup.c:122: pg_setup_init: /usr/lib/postgresql/13/bin/pg_ctl version 13.5
00:17:53 8924 DEBUG pgctl.c:212: /usr/lib/postgresql/13/bin/pg_controldata /var/lib/postgresql/13/main
00:17:53 8920 ERROR supervisor.c:693: pg_autoctl service node-init exited with exit status 12
00:17:53 8920 DEBUG supervisor.c:834: supervisor_may_restart: service "node-init" restarted 1 times, most recently at Thu Dec 16 00:17:52 2021, 1 seconds ago
00:17:53 8920 INFO  supervisor.c:777: Restarting service node-init
00:17:53 8920 DEBUG service_keeper_init.c:155: pg_autoctl node installer process started in subprocess 8929
00:17:53 8929 DEBUG pgctl.c:212: /usr/lib/postgresql/13/bin/pg_controldata /var/lib/postgresql/13/main
00:17:53 8929 WARN  debian.c:500: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_ident.conf" does not exist
00:17:53 8929 WARN  debian.c:507: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_hba.conf" does not exist
00:17:53 8929 FATAL keeper_pg_init.c:117: Failed to setup your Postgres instance the PostgreSQL way, see above for details
00:17:53 8920 ERROR supervisor.c:693: pg_autoctl service node-init exited with exit status 12
00:17:53 8920 DEBUG supervisor.c:834: supervisor_may_restart: service "node-init" restarted 2 times, most recently at Thu Dec 16 00:17:53 2021, 0 seconds ago
00:17:53 8920 INFO  supervisor.c:777: Restarting service node-init
00:17:53 8920 DEBUG service_keeper_init.c:155: pg_autoctl node installer process started in subprocess 8931
00:17:53 8931 DEBUG pgctl.c:212: /usr/lib/postgresql/13/bin/pg_controldata /var/lib/postgresql/13/main
00:17:53 8931 WARN  debian.c:500: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_ident.conf" does not exist
00:17:53 8931 WARN  debian.c:507: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_hba.conf" does not exist
00:17:53 8931 FATAL keeper_pg_init.c:117: Failed to setup your Postgres instance the PostgreSQL way, see above for details
00:17:53 8920 ERROR supervisor.c:693: pg_autoctl service node-init exited with exit status 12
00:17:53 8920 DEBUG supervisor.c:834: supervisor_may_restart: service "node-init" restarted 3 times, most recently at Thu Dec 16 00:17:53 2021, 0 seconds ago
00:17:53 8920 INFO  supervisor.c:777: Restarting service node-init
00:17:53 8920 DEBUG service_keeper_init.c:155: pg_autoctl node installer process started in subprocess 8933
00:17:53 8933 DEBUG pgctl.c:212: /usr/lib/postgresql/13/bin/pg_controldata /var/lib/postgresql/13/main
00:17:53 8933 WARN  debian.c:500: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_ident.conf" does not exist
00:17:53 8933 WARN  debian.c:507: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_hba.conf" does not exist
00:17:53 8933 FATAL keeper_pg_init.c:117: Failed to setup your Postgres instance the PostgreSQL way, see above for details
00:17:53 8920 ERROR supervisor.c:693: pg_autoctl service node-init exited with exit status 12
00:17:53 8920 DEBUG supervisor.c:834: supervisor_may_restart: service "node-init" restarted 4 times, most recently at Thu Dec 16 00:17:53 2021, 0 seconds ago
00:17:53 8920 INFO  supervisor.c:777: Restarting service node-init
00:17:53 8920 DEBUG service_keeper_init.c:155: pg_autoctl node installer process started in subprocess 8935
00:17:53 8935 DEBUG pgctl.c:212: /usr/lib/postgresql/13/bin/pg_controldata /var/lib/postgresql/13/main
00:17:53 8935 WARN  debian.c:500: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_ident.conf" does not exist
00:17:53 8935 WARN  debian.c:507: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_hba.conf" does not exist
00:17:53 8935 FATAL keeper_pg_init.c:117: Failed to setup your Postgres instance the PostgreSQL way, see above for details
00:17:53 8920 ERROR supervisor.c:693: pg_autoctl service node-init exited with exit status 12
00:17:53 8920 DEBUG supervisor.c:834: supervisor_may_restart: service "node-init" restarted 5 times, most recently at Thu Dec 16 00:17:53 2021, 0 seconds ago
00:17:53 8920 INFO  supervisor.c:777: Restarting service node-init
00:17:53 8920 DEBUG service_keeper_init.c:155: pg_autoctl node installer process started in subprocess 8937
00:17:53 8937 DEBUG pgctl.c:212: /usr/lib/postgresql/13/bin/pg_controldata /var/lib/postgresql/13/main
00:17:53 8937 WARN  debian.c:500: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_ident.conf" does not exist
00:17:53 8937 WARN  debian.c:507: Failed to find Postgres configuration files in PGDATA, as expected: "/var/lib/postgresql/13/main/pg_hba.conf" does not exist
00:17:53 8937 FATAL keeper_pg_init.c:117: Failed to setup your Postgres instance the PostgreSQL way, see above for details
00:17:53 8920 ERROR supervisor.c:693: pg_autoctl service node-init exited with exit status 12
00:17:53 8920 DEBUG supervisor.c:834: supervisor_may_restart: service "node-init" restarted 6 times, most recently at Thu Dec 16 00:17:53 2021, 0 seconds ago
00:17:53 8920 FATAL supervisor.c:860: pg_autoctl service node-init has already been restarted 5 times in the last 0 seconds, stopping now
00:17:53 8924 INFO  service_postgres_ctl.c:209: Postgres controller service received signal SIGTERM, terminating
00:17:53 8924 DEBUG service_postgres_ctl.c:432: pg_autoctl: stop postgres (pid -1)
00:17:53 8924 INFO  service_postgres.c:126: Stopping pg_autoctl postgres service
00:17:53 8924 INFO  pgctl.c:1801: /usr/lib/postgresql/13/bin/pg_ctl --pgdata /var/lib/postgresql/13/main --wait stop --mode fast
00:17:53 8920 INFO  supervisor.c:589: Waiting for subprocesses to terminate.
00:17:54 8920 FATAL supervisor.c:170: Something went wrong in sub-process supervision, stopping now. See above for details.
00:17:54 8920 INFO  supervisor.c:486: Stop pg_autoctl
wenq1 commented 2 years ago 
root@def:~# ll /var/lib/postgresql/13/main/
total 388
drwx------ 20 postgres postgres   4096 Dec 29 19:50 ./
drwxr-xr-x  5 postgres postgres   4096 Dec 15 19:03 ../
-rw-------  1 postgres postgres    219 Jun  1  2021 backup_label.old
-rw-------  1 postgres postgres 283845 Jun  1  2021 backup_manifest
drwx------  9 postgres postgres   4096 Dec 29 19:50 base/
drwx------  2 postgres postgres   4096 Dec 30 16:46 global/
drwx------  2 postgres postgres   4096 Jun  1  2021 pg_commit_ts/
drwx------  2 postgres postgres   4096 Jun  1  2021 pg_dynshmem/
drwx------  3 postgres postgres   4096 Jul 27 20:17 pg_foreign_file/
drwx------  4 postgres postgres   4096 Jan  2 12:41 pg_logical/
drwx------  4 postgres postgres   4096 Jun  1  2021 pg_multixact/
drwx------  2 postgres postgres   4096 Jun  1  2021 pg_notify/
drwx------  2 postgres postgres   4096 Dec 15 23:50 pg_replslot/
drwx------  2 postgres postgres   4096 Jun  1  2021 pg_serial/
drwx------  2 postgres postgres   4096 Jun  1  2021 pg_snapshots/
drwx------  2 postgres postgres   4096 Dec 27 10:35 pg_stat/
drwx------  2 postgres postgres   4096 Dec 29 19:50 pg_stat_tmp/
drwx------  2 postgres postgres   4096 Dec 30 22:40 pg_subtrans/
drwx------  2 postgres postgres   4096 Jun  1  2021 pg_tblspc/
drwx------  2 postgres postgres   4096 Jun  1  2021 pg_twophase/
-rw-------  1 postgres postgres      3 Jun  1  2021 PG_VERSION
drwx------  3 postgres postgres  12288 Dec 31 16:48 pg_wal/
drwx------  2 postgres postgres   4096 Nov 10 18:30 pg_xact/
-rw-------  1 postgres postgres    174 Jun  1  2021 postgresql.auto.conf
-rw-r--r--  1 postgres postgres      0 Jun  1  2021 postgresql.conf
-rw-------  1 postgres postgres    130 Dec 29 19:50 postmaster.opts
-rw-------  1 postgres postgres    108 Dec 29 19:50 postmaster.pid

root@def:~# ll /etc/postgresql/13/main/
total 72
drwxr-xr-x 3 postgres postgres  4096 May 31  2021 ./
drwxr-xr-x 4 postgres postgres  4096 Aug 20 21:33 ../
drwxr-xr-x 2 postgres postgres  4096 Oct 22  2020 conf.d/
-rw-r--r-- 1 postgres postgres   315 Oct 22  2020 environment
-rw-r--r-- 1 postgres postgres   143 Oct 22  2020 pg_ctl.conf
-rw-r----- 1 postgres postgres  5805 Apr  9  2021 pg_hba.conf
-rw-r----- 1 postgres postgres  1636 Oct 22  2020 pg_ident.conf
-rw-r--r-- 1 postgres postgres 29112 Dec 15 17:34 postgresql.conf
-rw-r--r-- 1 root     root       864 Jun  1  2021 repmgr.conf
-rw-r--r-- 1 postgres postgres   317 Oct 22  2020 start.conf
DimCitus commented 2 years ago

So you have both /var/lib/postgresql/13/main/postgresql.conf and also /etc/postgresql/13/main/postgresql.conf and then pg_hba.conf and pg_ident.conf are only to be found in /etc/postgresql/13/main/.

I also note that /var/lib/postgresql/13/main/postgresql.conf is empty. Do you happen to know how that file was created? I hope pg_auto_failover didn't do that, that would be a bug then.

Otherwise, please clean-up your setup in a way that makes sense to pg_auto_failover: I would suggest removing the file /var/lib/postgresql/13/main/postgresql.conf that's empty anyway.

wenq1 commented 2 years ago

Thanks. the leftover postgresql.conf and postgresql.auto.conf in /var/lib/postgresql/13/main was actually for repmgr, which I'm trying to migrate away from. Will try again after the cleanup. Thanks!