changing PRIMARY_REGION for postgres cluster leader

Questions / Help
1

Hello!

I recently tried to change my postgres cluster leader ( via PRIMARY_REGION ) from sea to sjc but ran into some errors when following all the steps outlined here: how to change the postgres leader region? - #2 by kurt

After saving and editing the generated fly.toml with the desired primary region I ran fly deploy -i flyio/postgres:14.4 which unfortunately failed with the following:

2022-10-05T00:21:15Z   [info]proxy    | [WARNING] 277/002115 (545) : parsing [/fly/haproxy.cfg:38]: Missing LF on last line, file might have been truncated at position 96. This will become a hard error in HAProxy 2.3.
2022-10-05T00:21:15Z   [info]proxy    | [NOTICE] 277/002115 (545) : New worker #1 (570) forked
2022-10-05T00:21:16Z   [info]checking stolon status
2022-10-05T00:21:16Z   [info]exporter | ERRO[0001] Error opening connection to database (postgresql://flypgadmin:PASSWORD_REMOVED@[fdaa:0:7a94:a7b:23c5:2:3283:2]:5433/postgres?sslmode=disable): dial tcp [fdaa:0:7a94:a7b:23c5:2:3283:2]:5433: connect: connection refused  source="postgres_exporter.go:1658"
2022-10-05T00:21:16Z   [info]proxy    | [WARNING] 277/002116 (570) : bk_db/pg1 changed its IP from (none) to fdaa:0:7a94:a7b:ad1:2:32c0:2 by flydns/dns1.
2022-10-05T00:21:16Z   [info]proxy    | [WARNING] 277/002116 (570) : Server bk_db/pg1 ('sjc.hnhired-db.internal') is UP/READY (resolves again).
2022-10-05T00:21:16Z   [info]proxy    | [WARNING] 277/002116 (570) : Server bk_db/pg1 administratively READY thanks to valid DNS answer.
2022-10-05T00:21:17Z   [info]keeper is healthy, db is healthy, role: standby
2022-10-05T00:21:17Z   [info]keeper   | 2022-10-05T00:21:17.824Z	ERROR	cmd/keeper.go:719	cannot get configured pg parameters	{"error": "dial unix /tmp/.s.PGSQL.5433: connect: no such file or directory"}
2022-10-05T00:21:18Z   [info]proxy    | [WARNING] 277/002118 (570) : Server bk_db/pg1 is DOWN, reason: Layer7 invalid response, info: "HTTP content check did not match", check duration: 286ms. 0 active and 1 backup servers left. Running on backup. 0 sessions active, 0 requeued, 0 remaining in queue.
2022-10-05T00:21:20Z   [info]keeper   | 2022-10-05T00:21:20.325Z	ERROR	cmd/keeper.go:719	cannot get configured pg parameters	{"error": "dial unix /tmp/.s.PGSQL.5433: connect: no such file or directory"}
2022-10-05T00:21:22Z   [info]proxy    | [WARNING] 277/002122 (570) : Backup Server bk_db/pg is DOWN, reason: Layer7 timeout, check duration: 5001ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
2022-10-05T00:21:22Z   [info]proxy    | [NOTICE] 277/002122 (570) : haproxy version is 2.2.9-2+deb11u3
2022-10-05T00:21:22Z   [info]proxy    | [NOTICE] 277/002122 (570) : path to executable is /usr/sbin/haproxy
2022-10-05T00:21:22Z   [info]proxy    | [ALERT] 277/002122 (570) : backend 'bk_db' has no server available!
2022-10-05T00:21:22Z   [info]keeper   | 2022-10-05T00:21:22.827Z	ERROR	cmd/keeper.go:719	cannot get configured pg parameters	{"error": "dial unix /tmp/.s.PGSQL.5433: connect: no such file or directory"}
2022-10-05T00:21:23Z   [info]keeper   | 2022-10-05 00:21:23.091 UTC [592] LOG:  starting PostgreSQL 14.4 (Debian 14.4-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-10-05T00:21:23Z   [info]keeper   | 2022-10-05 00:21:23.091 UTC [592] LOG:  listening on IPv6 address "fdaa:0:7a94:a7b:23c5:2:3283:2", port 5433
2022-10-05T00:21:23Z   [info]keeper   | 2022-10-05 00:21:23.092 UTC [592] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2022-10-05T00:21:23Z   [info]keeper   | 2022-10-05 00:21:23.095 UTC [593] LOG:  database system was shut down in recovery at 2022-10-05 00:21:07 UTC
2022-10-05T00:21:23Z   [info]keeper   | 2022-10-05 00:21:23.095 UTC [593] LOG:  entering standby mode
2022-10-05T00:21:23Z   [info]keeper   | 2022-10-05 00:21:23.098 UTC [593] LOG:  redo starts at 0/C060188
2022-10-05T00:21:23Z   [info]keeper   | 2022-10-05 00:21:23.099 UTC [593] LOG:  consistent recovery state reached at 0/C060A98
2022-10-05T00:21:23Z   [info]keeper   | 2022-10-05 00:21:23.099 UTC [593] LOG:  invalid record length at 0/C060A98: wanted 24, got 0
2022-10-05T00:21:23Z   [info]keeper   | 2022-10-05 00:21:23.100 UTC [592] LOG:  database system is ready to accept read-only connections
2022-10-05T00:21:24Z   [info]keeper   | 2022-10-05 00:21:24.094 UTC [597] LOG:  started streaming WAL from primary at 0/C000000 on timeline 1
2022-10-05T00:21:46Z   [info]exporter | INFO[0030] Established new database connection to "fdaa:0:7a94:a7b:23c5:2:3283:2:5433".  source="postgres_exporter.go:970"
2022-10-05T00:21:46Z   [info]exporter | INFO[0030] Semantic Version Changed on "fdaa:0:7a94:a7b:23c5:2:3283:2:5433": 0.0.0 -> 14.4.0  source="postgres_exporter.go:1539"
2022-10-05T00:21:46Z   [info]exporter | INFO[0030] Established new database connection to "fdaa:0:7a94:a7b:23c5:2:3283:2:5433".  source="postgres_exporter.go:970"
2022-10-05T00:21:46Z   [info]exporter | INFO[0030] Semantic Version Changed on "fdaa:0:7a94:a7b:23c5:2:3283:2:5433": 0.0.0 -> 14.4.0  source="postgres_exporter.go:1539"
--> v6 failed - Failed due to unhealthy allocations and deploying as v7

--> Troubleshooting guide at https://fly.io/docs/getting-started/troubleshooting/
Error abort

The cluster scale count and configured regions were not changed prior to attempting the leader change in case that helps. ( scale count of four with one leader in sea and three replicas in other regions )

[Update]. I was able to partially revert this by just changing the newly edited fly.toml primary region from ‘sjc’ (new) to its original value, ‘sea’; however there now doesn’t appear to be either a leader or replica designation in the status output.

after the first attempt to change region:

ID      	PROCESS	VERSION	REGION	DESIRED	STATUS                	HEALTH CHECKS                 	RESTARTS	CREATED
7ada4e5d	app    	6 ⇡    	ams   	run    	running (HTTP GET htt)	3 total, 2 passing, 1 critical	0       	7m57s ago
f0397d51	app    	6 ⇡    	ewr   	run    	running (HTTP GET htt)	3 total, 2 passing, 1 critical	0       	9m12s ago
ed8a3f18	app    	6 ⇡    	sea   	run    	running (HTTP GET htt)	3 total, 3 passing            	0       	9m12s ago
a958be3a	app    	5      	sjc   	run    	running (replica)     	3 total, 3 passing            	0       	6h14m ago

after reverting the change:

Deployment Status
  ID          = 2edd9f9f-42bc-9a10-499e-b3fdd3fd0c81
  Version     = v10
  Status      = successful
  Description = Deployment completed successfully
  Instances   = 4 desired, 4 placed, 4 healthy, 0 unhealthy

Instances
ID      	PROCESS	VERSION	REGION	DESIRED	STATUS                	HEALTH CHECKS     	RESTARTS	CREATED
8d7ebfa5	app    	10     	sjc   	run    	running (HTTP GET htt)	3 total, 3 passing	0       	3m12s ago
83289372	app    	10     	ams   	run    	running (HTTP GET htt)	3 total, 3 passing	0       	3m16s ago
58d835e8	app    	10     	ewr   	run    	running (HTTP GET htt)	3 total, 3 passing	0       	4m24s ago
85d26132	app    	10     	sea   	run    	running (HTTP GET htt)	3 total, 3 passing	0       	4m24s ago

Appreciate any help or insights! thanks :slight_smile:

2

It looks like sea is currently the leader. The check output there is stale, I don’t know why it’s not showing the role properly.

I think this failed because it stopped the primary first. sjc didn’t get updated, so it couldn’t take over as primary. This is probably because it’s running multiple regions (which we love).

You probably need to do fly deploy --strategy immediate to change the PRIMARY_REGION. This will replace all the VMs without waiting for health checks to pass. It will make the cluster inaccessible for a short time, though.

2 Likes
3

Thanks for the help and really quick response! I’ll try out the deploy w/ the immediate strategy later tonight :+1:

4

quick update on this!

I tried an immediate deploy, fly deploy -i flyio/postgres:14.4 --strategy immediate, w/ the same desired changed region in the fly.toml and for some reason the status output might still be off:

App
  Name     = hnhired-db
  Owner    = personal
  Version  = 14
  Status   = running
  Hostname = hnhired-db.fly.dev
  Platform = nomad

Instances
ID      	PROCESS	VERSION	REGION	DESIRED	STATUS                	HEALTH CHECKS                 	RESTARTS	CREATED
04ee4f99	app    	14     	ewr   	run    	running (HTTP GET htt)	3 total, 2 passing, 1 critical	0       	23m58s ago
ab1e88c0	app    	14     	ams   	run    	running (HTTP GET htt)	3 total, 2 passing, 1 critical	0       	23m58s ago
372d4351	app    	14     	lax   	run    	running (HTTP GET htt)	3 total, 2 passing, 1 critical	0       	23m58s ago
1d8c4b44	app    	14     	sjc   	run    	running (HTTP GET htt)	3 total, 2 passing, 1 critical	0       	23m58s ago
2d84f26f	app    	14     	sea   	run    	running (HTTP GET htt)	3 total, 3 passing            	0       	23m58s ago

Another odd effect is that the app this cluster is attached to doesn’t seem to be able to access what would normally be the leader on top2.nearest.of.hnhired-db.internal:5432 … but can connect to 5433.

from an app instance in sjc:

root@aff0f04d:/myapp# psql postgres://<USER>:<PASS>@top2.nearest.of.hnhired-db.internal:5432/hnhired
psql: error: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
  
root@aff0f04d:/myapp# psql postgres://<USER>:<PASS>@top2.nearest.of.hnhired-db.internal:5433/hnhired
psql (13.8 (Debian 13.8-0+deb11u1), server 14.4 (Debian 14.4-1.pgdg110+1))
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
Type "help" for help.

hnhired=#

So effectively the instances can read but can’t write. I am however able to connect to the leader ( 5432 ) via wireguard on my local

5

Hey there,

It looks like the failover was never performed. The PRIMARY_REGION was configured to sjc but leadership was still held by the PG member within sea. I went ahead and issued the failover for you and things are back to normal.

For future reference, you can get a better picture of things by running the following:

fly ssh console --app <app-name>

stolonctl status

I wrote up details on this here: What is the correct process to change the postgres leader region? - #2 by shaun

If you have any additional questions on this, just let us know!

1 Like
6

Thanks for the detailed reply and help with the failover!

This is pretty minor but the output of fly status -a <db app> doesn’t list the roles anymore; just wondering if there’s something I need to do in order to clear the presumably stale info(?).

Instances
ID      	PROCESS	VERSION	REGION	DESIRED	STATUS                	HEALTH CHECKS     	RESTARTS	CREATED
87a7239d	app    	14     	lax   	run    	running (HTTP GET htt)	3 total, 3 passing	0       	56m58s ago
04ee4f99	app    	14     	ewr   	run    	running (HTTP GET htt)	3 total, 3 passing	0       	13h51m ago
ab1e88c0	app    	14     	ams   	run    	running (HTTP GET htt)	3 total, 3 passing	0       	13h51m ago
1d8c4b44	app    	14     	sjc   	run    	running (HTTP GET htt)	3 total, 3 passing	0       	13h51m ago
2d84f26f	app    	14     	sea   	run    	running (HTTP GET htt)	3 total, 3 passing	0       	13h51m ago
7

No problem!

With regards to the output, that looks like a bug on our end. Will see about getting that fixed.

1 Like
8

@geoffrey The status output bug has been fixed. Can you confirm that things are looking better on your end?

9

Thanks! Ya everything works as expected now :grinning:

1 Like