Upgrade Postgres Under a Running Go Service Safely

A Postgres major version upgrade is two problems wearing one name. The first is moving the data to a server running the new version, which the PostgreSQL project documents well. The second is moving your application's writes from the old server to the new one without losing any, without writing to both, and without an hour of errors. The second is the one that causes incidents, and most of it happens in the Go service's connection handling. This article works through the application side on Go 1.25.6 with pgx v5.11.0, using a primary and a streaming standby of PostgreSQL 18.6, including the failure that makes cutover dangerous.

It belongs to the database cornerstone. The standby used here is the same kind of server as in read replicas. Backups, which you take before any of this, are in backing up and restoring Postgres, and the statements that are safe on a live table are in zero downtime migrations.

The ways to upgrade

There are three routes, and they differ in downtime and in risk.

In place with pg_upgrade. Stop the server, run pg_upgrade against the old and new binaries, start the new one. With the --link option it is quick, minutes for a large cluster, because it reuses the data files. It means downtime for the whole upgrade, and rolling back means restoring from a backup, since the old data directory is modified once the new server starts. Right for a service that can take a maintenance window.

Logical replication to a new server. Create the new server at the new version, copy the schema, publish the tables on the old one and subscribe from the new one. The data streams across while the old server keeps serving. When the new server has caught up, you stop writes, wait for the last changes, switch the application and drop the subscription. Downtime is the seconds between stopping writes and starting them on the new server. The costs: it copies tables, not sequences, so you must set sequence values on the new server before cutover or the first insert reuses an id; it needs a primary key or a replica identity on every table; and it does not carry schema changes, large objects or some other objects. This was not run in this lab; the steps are in the manual's chapter on logical replication.

Managed blue and green. Cloud providers offer a feature that builds the new server, replicates, and switches a name. It automates the logical replication route. Use it if you have it, and still read this article for what your application must do.

Whichever route, the cutover has the same shape: the application must stop writing to the old server and start writing to the new one, with no moment where both accept writes.

Making the service find the primary

A Go service should not need a redeploy to follow the primary. pgx, like libpq, accepts several hosts in one connection string and picks one by what the server says about itself:

host=127.0.0.1,127.0.0.1 port=55432,55434 target_session_attrs=read-write

The client tries the hosts in order and keeps the first that satisfies target_session_attrs: read-write for a server that accepts writes, read-only for one that does not, and prefer-standby for a standby if one is available.

package main

import (
	"context"
	"fmt"
	"log"
	"os/exec"
	"strings"
	"time"

	"github.com/jackc/pgx/v5"
)

// Two servers, listed in order. target_session_attrs picks the first one that accepts writes.
const base = "host=127.0.0.1,127.0.0.1 port=55432,55434 user=postgres password=secret dbname=lab sslmode=disable connect_timeout=2 "

func docker(args ...string) string {
	out, _ := exec.Command("docker", args...).CombinedOutput()
	return strings.TrimSpace(string(out))
}

// connect dials with the given session attribute and reports which server it reached and what it is.
func connect(attrs string) (string, error) {
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()
	start := time.Now()
	conn, err := pgx.Connect(ctx, base+"target_session_attrs="+attrs)
	if err != nil {
		return "", fmt.Errorf("after %v: %w", time.Since(start).Round(100*time.Millisecond), err)
	}
	defer conn.Close(ctx)
	var recovery bool
	if err := conn.QueryRow(ctx, "select pg_is_in_recovery()").Scan(&recovery); err != nil {
		return "", err
	}
	role := "primary"
	if recovery {
		role = "standby"
	}
	addr := conn.PgConn().Conn().RemoteAddr().String()
	return fmt.Sprintf("port %s is a %s (connected in %v)", addr[strings.LastIndex(addr, ":")+1:], role, time.Since(start).Round(100*time.Millisecond)), nil
}

func show(label, attrs string) {
	res, err := connect(attrs)
	if err != nil {
		res = "failed " + err.Error()
	}
	fmt.Printf("%-58s %s\n", label, res)
}

func main() {
	ctx := context.Background()
	first, err := pgx.Connect(ctx, "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer first.Close(ctx)
	_, err = first.Exec(ctx, "drop table if exists orders655; create table orders655 (id serial primary key, note text)")
	if err != nil {
		log.Fatal(err)
	}
	for i := 0; i < 3; i++ {
		_, _ = first.Exec(ctx, "insert into orders655 (note) values ('before the upgrade')")
	}
	time.Sleep(time.Second)

	fmt.Println("1. both servers up: the standby is the second host in the list")
	show("target_session_attrs=read-write", "read-write")
	show("target_session_attrs=read-only", "read-only")
	show("target_session_attrs=prefer-standby", "prefer-standby")

	fmt.Println("2. the primary stops answering (frozen), and the standby is promoted")
	docker("pause", "gh-lab-pg")
	promoted := docker("exec", "-e", "PGPASSWORD=secret", "gh-lab-pg-standby", "psql", "-U", "postgres", "-d", "lab", "-Atc", "select pg_promote(true, 30)")
	fmt.Println("   pg_promote returned:", promoted)
	show("target_session_attrs=read-write, first host frozen", "read-write")
	show("target_session_attrs=read-only, no standby is left", "read-only")

	fmt.Println("3. the old primary comes back")
	docker("unpause", "gh-lab-pg")
	time.Sleep(500 * time.Millisecond)
	show("target_session_attrs=read-write, two primaries now", "read-write")
	ctx2, cancel := context.WithTimeout(ctx, 10*time.Second)
	defer cancel()
	conn, err := pgx.Connect(ctx2, base+"target_session_attrs=read-write")
	if err == nil {
		_, _ = conn.Exec(ctx2, "insert into orders655 (note) values ('written after the cutover through the DSN')")
		conn.Close(ctx2)
	}
	for _, p := range []string{"55432", "55434"} {
		c, err := pgx.Connect(ctx, "postgres://postgres:[email protected]:"+p+"/lab?sslmode=disable")
		if err != nil {
			continue
		}
		var n int
		_ = c.QueryRow(ctx, "select count(*) from orders655").Scan(&n)
		c.Close(ctx)
		fmt.Printf("   rows in orders655 on the server at port %s: %d\n", p, n)
	}
	fmt.Println("   the write went to the first host that accepts writes, which is the old primary: the two servers now disagree")
}

set -e
cleanup() { docker unpause gh-lab-pg >/dev/null 2>&1 || true; docker rm -f gh-lab-pg-standby >/dev/null 2>&1 || true; }
trap cleanup EXIT
docker rm -f gh-lab-pg-standby >/dev/null 2>&1 || true
# A fresh standby of the lab primary, on port 55434.
docker run -d --name gh-lab-pg-standby --user postgres -p 127.0.0.1:55434:5432 -e PGPASSWORD=secret -e PGDATA=/var/lib/postgresql/standby postgres:18-alpine \
  bash -c 'pg_basebackup -h 172.17.0.2 -U postgres -D $PGDATA -R -X stream -c fast && exec postgres -D $PGDATA' >/dev/null
for i in $(seq 1 60); do
  docker exec gh-lab-pg-standby pg_isready -U postgres >/dev/null 2>&1 && break
  sleep 1
done
sleep 2
go run .

1. both servers up: the standby is the second host in the list
target_session_attrs=read-write                            port 55432 is a primary (connected in 100ms)
target_session_attrs=read-only                             port 55434 is a standby (connected in 100ms)
target_session_attrs=prefer-standby                        port 55434 is a standby (connected in 0s)
2. the primary stops answering (frozen), and the standby is promoted
   pg_promote returned: t
target_session_attrs=read-write, first host frozen         port 55434 is a primary (connected in 2s)
target_session_attrs=read-only, no standby is left         failed after 2s: failed to connect to `user=postgres database=lab`:
	127.0.0.1:55432 (127.0.0.1): failed to receive message: timeout: context deadline exceeded
	127.0.0.1:55434 (127.0.0.1): ValidateConnect failed: connection is not read only
3. the old primary comes back
target_session_attrs=read-write, two primaries now         port 55432 is a primary (connected in 0s)
   rows in orders655 on the server at port 55432: 4
   rows in orders655 on the server at port 55434: 3
   the write went to the first host that accepts writes, which is the old primary: the two servers now disagree

The script starts a fresh streaming standby of the lab primary on port 55434, runs the program, and always unfreezes the primary and removes the standby afterwards. The program prints what each connection reached.

What the run shows

While both servers are up the attributes behave as designed. read-write connected to port 55432, a primary, in 100 milliseconds. read-only skipped the primary and connected to the standby on 55434. prefer-standby also chose the standby. A service can use two pools built from two strings: writes through read-write, reads through prefer-standby, the same split as in the replica article.

Then the primary stops answering. The program froze it with docker pause, which keeps the port open and stops the process, the worst kind of failure because connections hang instead of failing. It then promoted the standby with pg_promote(), which returned t. The same read-write string now connected to port 55434, which had become a primary, but it took 2 seconds: the client waited out the connect_timeout=2 on the frozen first host before moving on. That delay is the price of a hung server, paid by every new connection until the old one is removed from the string or from service. Set a short connect_timeout, and expect it to show as latency during a failover. A read-only request failed, as it should: with the standby promoted there was no read only server left, and the error names both hosts.

Existing connections in the pool do not move on their own. A connection to the old primary stays open until it errors; database/sql discards it and opens a new one, which goes through the string again. ConnMaxLifetime bounds how long stale connections can live, and the ErrBadConn behaviour in bad connection errors is what carries the pool over.

The split brain

The last step is the one to remember. The old primary was unfrozen. It had not been told it was no longer the primary, and nothing stopped it from accepting writes. The same connection string now found two writable servers, and connected to the first in the list, the old primary, on port 55432. The program inserted a row through the string. Counting afterwards, the old primary held 4 rows and the promoted server 3. The data had forked: one write exists on one server only, and every write made since the promotion to the new server is missing from the old.

This is split brain, and it is how failovers lose data. The connection string did what it was told, and ordering hosts is not a safety mechanism. Preventing it is your job, and it has a fixed order:

1. Fence the old primary before promoting the new one. Stop it, cut its network, or revoke its power. Do not rely on it being down by itself. Managed services do this for you; hand rolled setups need a tool (Patroni, repmgr with its fencing, or a documented manual step).
2. Promote the standby.
3. Switch the application to the new primary by name or by string.
4. Never bring the old primary back as a primary. Rebuild it from the new one as a standby, with pg_rewind or a fresh base backup.

For a planned upgrade the order is gentler: stop writes in the application (a maintenance flag or a connection drain), wait for the standby to catch up, promote it, and point the service at it. There is no window with two writers, because writes were stopped first.

The application's part of the cutover

• Connect by a name you control, a DNS record, a load balancer or a proxy such as PgBouncer, or by a multi-host string, so a switch does not need a deploy. DNS needs a short TTL and a pool that reconnects; remember that Go caches nothing by itself, and your pool holds connections that ignore a DNS change until they break.
• Retry what is safe to retry and nothing else. During a switch, statements fail with connection errors. A select can be retried. An insert whose reply was lost may have been applied; make writes idempotent with a unique key, as in upserts, before relying on retries.
• Drain deliberately. For a planned cutover, make the service return 503 on write endpoints for the few seconds of the switch, and keep reads on the standby. Short, clear errors are better than a minute of hung requests.
• Check the sequences. After a logical replication cutover, every sequence on the new server must be at or above the old one's value. Set them with setval from the old values plus a margin, or the first inserts collide on primary keys.
• Verify before and after. Compare row counts and checksums of important tables, as in the backup article, and run the application's smoke tests against the new server before it takes traffic.
• Keep the way back. Until you are sure, keep the old server intact and a path to return, and write down the point after which going back means losing writes.

Rehearse it

Do the whole upgrade on a copy first: restore the latest backup, build the new version server, run the application's tests against it, and time each step. Many upgrade problems are found there: an extension with no build for the new version, a deprecated setting, a query whose plan changed. After a major upgrade, run analyze on every table, since statistics are not carried over by pg_upgrade, and watch query times for the first day. The procedures are documented in the PostgreSQL manual's pages on upgrading a cluster, logical replication and pg_upgrade, and the connection parameters in the libpq connection string reference.