Making a pg_auto_failover formation from an existing (not a new) Citus cluster

[mattklein]

I'm very psyched to see the Citus support recently released. I'm trying to follow the instructions in the Citus tutorial, but this seems to assume we're working with a from-scratch environment and that we want pg_auto_failover to create the Citus DB when we create the pg_auto_failover formation.

That's not what I want to do. Instead, what I'd like to do is take an existing Citus cluster (with a coordinator citus-coord and two workers citus-01 and citus-02) and set these nodes up as a pg_auto_failover formation. Eventually I plan to have a replica for each: e.g., citus-coord-a, citus-coord-b, citus-01-a, citus-01-b, citus-02-a, citus-02-b. But for now I'm trying to keep things as simple as possible and not set up the replicas yet. (I realize this wouldn't actually give me HA, but I just want to see if I can get the basic formation up and running first.)

I've created a new monitor node (pgaf-monitor), installed pg_auto_failover on it, and run create monitor like this:

pg_autoctl create monitor --pgdata /var/lib/pgsql/data --hostname localhost --auth trust --ssl-self-signed

The monitor comes up successfully and seems healthy.

Then I create a new formation on the monitor:

pg_autoctl create formation --kind citus --formation citus --dbname jellydb --disable-secondary \
--monitor postgresql://[email protected]/pg_auto_failover

Then I run this on the coordinator (citus-coord):

pg_autoctl create coordinator --pgdata /var/lib/pgsql/data --username jellyfish --dbname jellydb \
--formation citus --name citus-coord-a --hostname citus-coord.stg-citus-full-power \
--monitor postgresql://[email protected]/pg_auto_failover \
--ssl-self-signed --auth trust

And I run these on the workers (citus-01 and citus-02):

pg_autoctl create worker --pgdata /var/lib/pgsql/data --username jellyfish --dbname jellydb \
--formation citus --name citus-01-a --hostname citus-01.stg-citus-full-power \
--monitor postgresql://[email protected]/pg_auto_failover \
--ssl-self-signed --auth trust

pg_autoctl create worker --pgdata /var/lib/pgsql/data --username jellyfish --dbname jellydb \
--formation citus --name citus-02-a --hostname citus-02.stg-citus-full-power \
--monitor postgresql://[email protected]/pg_auto_failover \
--ssl-self-signed --auth trust

But what happens on the coordinator is that I see errors like this (full log file):

23:46:01 39759 ERROR PostgreSQL is already running at "/citus-data/postgresql/data", refusing to initialize a new cluster on-top of the current one.
23:46:01 39759 ERROR Failed to transition from state "init" to state "single", see above.
23:46:01 39756 ERROR pg_autoctl service node-init exited with exit status 12

And on each of the workers I see similar errors like this (full log file):

23:55:07 45697 ERROR PostgreSQL is already running at "/citus-data/postgresql/data", refusing to initialize a new cluster on-top of the current one.
23:55:07 45697 ERROR Failed to transition from state "init" to state "single", see above.
23:55:08 45694 ERROR pg_autoctl service node-init exited with exit status 12

And this (full log file):

23:55:24 45743 ERROR PostgreSQL is already running at "/citus-data/postgresql/data", refusing to initialize a new cluster on-top of the current one.
23:55:24 45743 ERROR Failed to transition from state "init" to state "single", see above.
23:55:24 45740 ERROR pg_autoctl service node-init exited with exit status 12

If I re-run the same create worker commands, I actually get a different first error:

23:55:30 45955 ERROR Failed to create extension "pg_stat_statements"[XX000]: ERROR:  operation is not allowed on this node
HINT:  Connect to the coordinator and run it again.

23:55:30 45955 ERROR Failed to create extension pg_stat_statements
23:55:30 45955 ERROR Failed to transition from state "init" to state "single", see above.
23:55:30 45951 ERROR pg_autoctl service node-init exited with exit status 12

And then I get more of the same errors:

23:55:31 45980 ERROR PostgreSQL is already running at "/citus-data/postgresql/data", refusing to initialize a new cluster on-top of the current one.
23:55:31 45980 ERROR Failed to transition from state "init" to state "single", see above.
23:55:31 45951 ERROR pg_autoctl service node-init exited with exit status 12

If I then try to start the pgautofailover service on the coordinator and worker nodes, PostgreSQL on each node keeps bouncing up and down, as apparently the monitor keeps marking it unhealthy, then healthy, then unhealthy again.

These are some events I see via pg_autoctl watch:

36  2022-11-04 00:53:01     citus-01-a   Node node 2 "citus-01-a" (citus-01.stg-citus-full-power:5432) is marked as unhealthy by the monitor
35  2022-11-04 00:52:56     citus-01-a   Node node 2 "citus-01-a" (citus-01.stg-citus-full-power:5432) is marked as healthy by the monitor
34  2022-11-04 00:52:54  citus-coord-a   Node node 1 "citus-coord-a" (citus-coord.stg-citus-full-power:5432) is marked as healthy by the monitor
33  2022-11-04 00:52:52     citus-01-a   Node node 2 "citus-01-a" (citus-01.stg-citus-full-power:5432) is marked as unhealthy by the monitor
32  2022-11-04 00:52:50  citus-coord-a   Node node 1 "citus-coord-a" (citus-coord.stg-citus-full-power:5432) is marked as unhealthy by the monitor
31  2022-11-04 00:52:46     citus-02-a   Node node 3 "citus-02-a" (citus-02.stg-citus-full-power:5432) is marked as healthy by the monitor
30  2022-11-04 00:52:46     citus-02-a   Node node 3 "citus-02-a" (citus-02.stg-citus-full-power:5432) is marked as unhealthy by the monitor
29  2022-11-04 00:52:40     citus-01-a   Node node 2 "citus-01-a" (citus-01.stg-citus-full-power:5432) is marked as healthy by the monitor
28  2022-11-04 00:52:40     citus-01-a   Node node 2 "citus-01-a" (citus-01.stg-citus-full-power:5432) is marked as unhealthy by the monitor
27  2022-11-04 00:52:34  citus-coord-a   Node node 1 "citus-coord-a" (citus-coord.stg-citus-full-power:5432) is marked as healthy by the monitor
26  2022-11-04 00:52:33  citus-coord-a   Node node 1 "citus-coord-a" (citus-coord.stg-citus-full-power:5432) is marked as unhealthy by the monitor
25  2022-11-04 00:52:31     citus-02-a   Node node 3 "citus-02-a" (citus-02.stg-citus-full-power:5432) is marked as healthy by the monitor
24  2022-11-04 00:52:29     citus-02-a   Node node 3 "citus-02-a" (citus-02.stg-citus-full-power:5432) is marked as unhealthy by the monitor
23  2022-11-04 00:52:22     citus-02-a   Node node 3 "citus-02-a" (citus-02.stg-citus-full-power:5432) is marked as healthy by the monitor
22  2022-11-04 00:52:22  citus-coord-a   Node node 1 "citus-coord-a" (citus-coord.stg-citus-full-power:5432) is marked as healthy by the monitor
21  2022-11-04 00:52:18  citus-coord-a   Node node 1 "citus-coord-a" (citus-coord.stg-citus-full-power:5432) is marked as unhealthy by the monitor
20  2022-11-04 00:52:11     citus-02-a   Node node 3 "citus-02-a" (citus-02.stg-citus-full-power:5432) is marked as unhealthy by the monitor
19  2022-11-04 00:52:07     citus-01-a   Node node 2 "citus-01-a" (citus-01.stg-citus-full-power:5432) is marked as healthy by the monitor
18  2022-11-04 00:52:06     citus-01-a   Node node 2 "citus-01-a" (citus-01.stg-citus-full-power:5432) is marked as unhealthy by the monitor
17  2022-11-04 00:52:04  citus-coord-a   Node node 1 "citus-coord-a" (citus-coord.stg-citus-full-power:5432) is marked as healthy by the monitor
16  2022-11-04 00:52:02  citus-coord-a   Node node 1 "citus-coord-a" (citus-coord.stg-citus-full-power:5432) is marked as unhealthy by the monitor
15  2022-11-04 00:51:51     citus-02-a   Node node 3 "citus-02-a" (citus-02.stg-citus-full-power:5432) is marked as healthy by the monitor
14  2022-11-04 00:51:46     citus-01-a   Node node 2 "citus-01-a" (citus-01.stg-citus-full-power:5432) is marked as healthy by the monitor
13  2022-11-04 00:51:45  citus-coord-a   Node node 1 "citus-coord-a" (citus-coord.stg-citus-full-power:5432) is marked as healthy by the monitor
12  2022-11-04 00:51:45     citus-01-a   Node node 2 "citus-01-a" (citus-01.stg-citus-full-power:5432) is marked as unhealthy by the monitor
11  2022-11-04 00:51:45     citus-02-a   Node node 3 "citus-02-a" (citus-02.stg-citus-full-power:5432) is marked as unhealthy by the monitor
10  2022-11-04 00:51:41  citus-coord-a   Node node 1 "citus-coord-a" (citus-coord.stg-citus-full-power:5432) is marked as unhealthy by the monitor
 9  2022-11-04 00:51:37     citus-01-a   Node node 2 "citus-01-a" (citus-01.stg-citus-full-power:5432) is marked as healthy by the monitor
 8  2022-11-04 00:51:35     citus-02-a   Node node 3 "citus-02-a" (citus-02.stg-citus-full-power:5432) is marked as healthy by the monitor
 7  2022-11-04 00:51:29  citus-coord-a   Node node 1 "citus-coord-a" (citus-coord.stg-citus-full-power:5432) is marked as healthy by the monitor

Can anybody provide some advice on what I'm trying to do?

Specifically, how to take an existing Citus cluster and convert it into a pg_auto_failover formation?

[mattklein]

Eh, I should have just done it the way the tutorial suggests. Have successfully gone that route. Closing this discussion.

[machinify-hankhuang]

Hi @mattklein , can you link me the tutorial you are referring to? I am experiencing the same thing. Thanks.

[mattklein]

Hi @machinify-hankhuang.

It was a while ago now but I believe the tutorial I was referring to was this one. If I recall correctly, I believe what I was getting wrong that it wasn't valid to turn an existing Citus cluster into a pg_auto_failover formation; I instead needed to use pg_auto_failover to create a new Citus cluster from scratch (which is what the tutorial does).

[machinify-hankhuang]

Ah, ok, @mattklein , thank you for sharing. Last question, were you able to migrate the data from existing Citus to the new one you build from scratch using pg_auto_failover? If so, which method did you use.

[mattklein]

Sorry, I wasn't trying to migrate an existing Citus cluster to the new one I built with pg_auto_failover, so I can't speak to that. But I believe one method to do that would be with plain old pg_dump and pg_restore. I'm curious to hear how things go for you so please send an update if you can!