Back Up and Restore a Go Postgres Service for Real

Every team has a backup. Fewer have ever restored one. The difference decides whether the first real incident is a bad afternoon or the end of the product, and it is why this article spends most of its words on the restore. It shows the logical backup tools of PostgreSQL 18.6 driven from Go 1.25.6, times the three dump formats on a million rows, restores each into a scratch database and proves the copy with a checksum, then explains what logical backups cannot do and what to add for a recovery point measured in seconds.

It belongs to the database cornerstone. The same idea for SQLite is in Litestream and SQLite backups, and safe schema changes, the other half of operating a database, are in zero downtime migrations.

Two kinds of backup

A logical backup is a description of the data as SQL or an archive of rows: pg_dump writes the schema and the contents, and pg_restore or psql replays them. It is portable across machines, operating systems and (forward) major versions, it can take one table, and it is easy to reason about. Restoring replays every insert and rebuilds every index, so it is slow for big databases, and its recovery point is the moment the dump started.

A physical backup is a copy of the data files: pg_basebackup or a file system or volume snapshot. It restores as fast as you can copy the files, it includes everything, and with archived write ahead log (WAL) it supports point in time recovery: restore the base backup, replay the log up to a chosen moment, and stop just before the mistake. It is tied to the same major version and architecture.

Most services need both: logical dumps for portability, small restores and a safety net, and physical backups with WAL archiving for fast, fine grained recovery of a large database. Managed PostgreSQL services provide the physical kind as a feature; the logical kind is still yours to schedule.

Driving the tools from Go

The lab makes a table of one million rows (95 MB with its primary key index), records a checksum of its contents, then times each dump and each restore.

package main

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

// pg runs a command inside the lab database container. A production job would run the same binaries on a
// backup host, over the network, with the connection settings in the environment.
func pg(ctx context.Context, args ...string) (string, time.Duration, error) {
	cmd := exec.CommandContext(ctx, "docker", append([]string{"exec", "-e", "PGPASSWORD=secret", "gh-lab-pg"}, args...)...)
	start := time.Now()
	out, err := cmd.CombinedOutput()
	return strings.TrimSpace(string(out)), time.Since(start), err
}

func must(out string, d time.Duration, err error) (string, time.Duration) {
	if err != nil {
		log.Fatalf("%v: %s", err, out)
	}
	return out, d
}

func main() {
	ctx, cancel := context.WithTimeout(context.Background(), 4*time.Minute)
	defer cancel()

	// A table of 1,000,000 rows to back up.
	must(pg(ctx, "psql", "-U", "postgres", "-d", "lab", "-qc", "drop table if exists bk653; create table bk653 as select i as id, md5(i::text) as v, now() as made from generate_series(1, 1000000) i; alter table bk653 add primary key (id)"))
	sum, _ := must(pg(ctx, "psql", "-U", "postgres", "-d", "lab", "-Atc", "select count(*) || ' rows, checksum ' || left(md5(string_agg(v, '' order by id)), 8) from bk653"))
	fmt.Println("source:", sum)
	size, _ := must(pg(ctx, "psql", "-U", "postgres", "-d", "lab", "-Atc", "select pg_size_pretty(pg_total_relation_size('bk653'))"))
	fmt.Println("table and index size on disk:", size)

	must(pg(ctx, "sh", "-c", "rm -rf /tmp/bk653*"))
	fileSize := func(path string) string {
		out, _ := must(pg(ctx, "sh", "-c", "du -sk "+path+" | cut -f1"))
		return out + " KB"
	}
	type step struct {
		label string
		args  []string
		size  string
	}
	fmt.Printf("%-44s %10s %12s\n", "step", "time", "output size")
	run := func(label string, size string, args ...string) {
		_, d := must(pg(ctx, args...))
		s := ""
		if size != "" {
			s = fileSize(size)
		}
		fmt.Printf("%-44s %10v %12s\n", label, d.Round(10*time.Millisecond), s)
	}
	run("pg_dump plain SQL", "/tmp/bk653.sql", "pg_dump", "-U", "postgres", "-d", "lab", "-t", "bk653", "-Fp", "-f", "/tmp/bk653.sql")
	run("pg_dump custom format (-Fc)", "/tmp/bk653.dump", "pg_dump", "-U", "postgres", "-d", "lab", "-t", "bk653", "-Fc", "-f", "/tmp/bk653.dump")
	run("pg_dump directory format, 4 jobs (-Fd -j4)", "/tmp/bk653.dir", "pg_dump", "-U", "postgres", "-d", "lab", "-t", "bk653", "-Fd", "-j", "4", "-f", "/tmp/bk653.dir")

	fresh := func(name string) {
		must(pg(ctx, "psql", "-U", "postgres", "-qc", "drop database if exists "+name))
		must(pg(ctx, "psql", "-U", "postgres", "-qc", "create database "+name))
	}
	verify := func(name string) string {
		out, _ := must(pg(ctx, "psql", "-U", "postgres", "-d", name, "-Atc", "select count(*) || ' rows, checksum ' || left(md5(string_agg(v, '' order by id)), 8) from bk653"))
		return out
	}
	fresh("restore653a")
	run("restore plain SQL with psql", "", "psql", "-U", "postgres", "-d", "restore653a", "-q", "-f", "/tmp/bk653.sql")
	fmt.Println("   restored:", verify("restore653a"))
	fresh("restore653b")
	run("restore custom format, one job", "", "pg_restore", "-U", "postgres", "-d", "restore653b", "--no-owner", "/tmp/bk653.dump")
	fmt.Println("   restored:", verify("restore653b"))
	fresh("restore653c")
	run("restore directory format, 4 jobs", "", "pg_restore", "-U", "postgres", "-d", "restore653c", "--no-owner", "-j", "4", "/tmp/bk653.dir")
	fmt.Println("   restored:", verify("restore653c"))

	// A backup is only worth what its restore proves: the checksum of the copy has to equal the source's.
	fmt.Println("source again:", sum)
	for _, d := range []string{"restore653a", "restore653b", "restore653c"} {
		must(pg(ctx, "psql", "-U", "postgres", "-qc", "drop database "+d))
	}
	must(pg(ctx, "sh", "-c", "rm -rf /tmp/bk653*"))
}

source: 1000000 rows, checksum a007be95
table and index size on disk: 95 MB
step                                               time  output size
pg_dump plain SQL                                 440ms     67276 KB
pg_dump custom format (-Fc)                       1.35s     22596 KB
pg_dump directory format, 4 jobs (-Fd -j4)        2.01s     22576 KB
restore plain SQL with psql                        1.9s             
   restored: 1000000 rows, checksum a007be95
restore custom format, one job                    1.78s             
   restored: 1000000 rows, checksum a007be95
restore directory format, 4 jobs                  1.76s             
   restored: 1000000 rows, checksum a007be95
source again: 1000000 rows, checksum a007be95

The program calls the PostgreSQL command line tools with exec.CommandContext, so a deadline kills a stuck dump. Here they run inside the database container through docker exec, which stands in for a backup host; in production the same binaries run on a job host, over the network, with PGHOST, PGUSER and PGPASSWORD (or a password file) from the environment, never on the command line, where ps would show them. Run these as a scheduled job, not from the web service itself, and give the job a database role with read access only.

The three formats

Plain SQL is a text file of COPY data and DDL. It was the fastest to write, since it does no compression, and three times the size. You can read it, edit it, and restore it with psql. It is the right format for small databases and for committing a schema.

Custom format is a compressed archive with a table of contents. It took longer because it compresses, and it is a third of the size. It supports pg_restore features: restoring selected tables, reordering, and listing contents with pg_restore -l. It is the usual default for a single database dump.

Directory format writes one compressed file per table and can dump tables in parallel with -j. For this one table, -j4 gained nothing, and cost a little (2.01s against 1.35s), because parallelism is across tables and there was only one. On a database with a hundred tables it is the fastest way to dump and to restore. Do not assume -j helps without checking the shape of your data.

The restore is the test

Each format was restored into a new database, and the program compared the row count and a checksum of the contents with the source.

All three printed 1000000 rows, checksum a007be95, the same as the source, so the copies are identical in content. That check is the whole point. A dump file that exists, and has a plausible size, proves nothing: it may be truncated, it may lack a table because of a permissions error that scrolled by, or it may fail halfway through the restore. Only a restore, followed by a comparison, proves it.

Restoring took longer than dumping, as expected: it inserts rows and builds the index, and that is the cost that grows with size. A 500 GB database can take many hours to restore logically, which is the argument for physical backups when the time to recover matters. Measure your own restore time at your own size, and write it in your runbook next to the target you promised.

Making it routine

A backup job that you can trust does six things.

1. Dumps from a consistent snapshot. pg_dump takes a snapshot at the start, so the dump is consistent even while the service writes. It holds a long transaction while it runs, which delays vacuum, so run it off peak, or dump from a replica; see read replicas.
2. Checks the exit code and the output, and fails loudly. A non zero exit with a partial file is the common failure.
3. Compresses and encrypts before storage, and uploads to a different place from the database: another account or region, object storage with versioning and a retention rule, so one mistake cannot delete both.
4. Keeps a schedule of copies: daily for a week, weekly for a month, monthly for a year, or whatever the business needs.
5. Restores automatically. A second job takes the latest dump, restores it into a scratch database, runs the checksum or a set of sanity queries, and alerts if it fails or if the data is older than expected. This is the lab's verify step run on a timer.
6. Records the numbers: dump size, dump time, restore time and age of the newest verified backup, as metrics with alerts.

Also dump the globals: roles and tablespaces live outside any one database, so pg_dumpall --globals-only belongs in the job, or a restore on a new server will fail on missing roles.

Point in time recovery

For a recovery point of seconds, add WAL archiving. PostgreSQL writes every change to its write ahead log; with archive_mode = on and an archive_command it ships each finished segment to storage. A base backup taken with pg_basebackup plus the archived segments lets you restore to any moment: create the recovery configuration with a recovery_target_time, start the server, and it replays the log to that time. That is how you recover from "someone ran a delete without a where clause at 14:02".

This lab did not run it, since it needs a server configured for archiving and a storage target, and the details are in the manual's chapter on continuous archiving. In practice most teams use a tool built on it, such as pgBackRest or WAL-G, or the feature of their managed service, instead of writing the archive command themselves. Whichever you choose, rehearse a point in time restore, because it has more moving parts than a dump.

What to do before an incident

Write down the recovery point you can accept and the recovery time you can accept, and check that your backups meet both. Test the restore on a schedule, and test the unpleasant case once: lose the database host entirely, restore onto a new one, point a staging copy of the Go service at it and run the smoke tests. Keep the credentials for the backup storage somewhere that does not depend on the system you are restoring. And do not count replicas as backups: a replica faithfully copies a mistaken delete within seconds. The tools are documented in the PostgreSQL manual under pg_dump, pg_restore and continuous archiving and point in time recovery.