Someone runs DELETE FROM orders without the WHERE at 14:05, and the last backup is from 03:00. With nightly backups only, eleven hours of orders are gone along with the bad ones. Yesterday I shipped point-in-time recovery for Railyard’s managed Postgres, so a database can be rebuilt as it was at 14:04:59 instead.

What the managed Postgres add-on is

Railyard deploys apps onto servers the customer owns. When an app asks for Postgres, Railyard does not create a separate machine for it. Each server runs one Postgres in a Docker container, and every app on that server gets its own database and its own login inside it. The app receives a DATABASE_URL and never sees the others.

That shared instance matters for everything below. Postgres calls one running server with its data directory a cluster, and a cluster holds many databases. Some backup tools work per database. Others only work on the whole cluster.

Two kinds of backup

Railyard already had scheduled backups. On an interval the owner picks per database, the control plane runs pg_dump -Fc against the app’s database and uploads the file to S3-compatible object storage. Optionally, a second job downloads that file, restores it into a scratch database and counts the tables, so a backup is only marked good once something has read it back.

pg_dump makes a logical backup. It connects like any client, opens a single transaction, and writes out the schema and rows as they look inside that transaction. The file is portable: you can restore it into a newer Postgres, onto another machine, or into a database with a different name. Its weakness is time. A dump describes one moment, and everything that happened after it is not in the file.

A physical backup copies the files Postgres keeps on disk instead. On its own, that is no more useful than a dump. It becomes useful when you pair it with the write-ahead log.

The write-ahead log

Postgres never changes a table file without first writing a record of the change to the write-ahead log (WAL). The WAL is a sequence of 16 MB segment files. If the server crashes, Postgres starts from the last checkpoint and replays the WAL to bring the table files back to a consistent state. That is crash recovery, and it happens on every unclean restart.

Point-in-time recovery uses the same machinery on purpose. Keep a physical copy of the data directory from some moment (a base backup), and keep every WAL segment written since. To get the database as it was at 14:04:59, start Postgres on the base backup and tell it to replay WAL until it reaches that time, then stop.

Two settings make Postgres keep its WAL somewhere safe. archive_mode turns archiving on, and archive_command is a shell command Postgres runs each time a segment is full, with %p as the segment’s path and %f as its file name. Postgres only recycles a segment after the command exits 0. Here are the settings Railyard adds when you turn the feature on:

# enough detail in the WAL to rebuild the data files
wal_level = replica
# hand every finished segment to archive_command
archive_mode = on
archive_command = 'test ! -f /archive/wal/%f && cp %p /archive/wal/%f'
archive_timeout = 60

The test ! -f part refuses to overwrite a segment that is already archived. The Postgres docs recommend it: if two clusters were ever pointed at the same archive by mistake, the command fails loudly instead of quietly replacing good history with another server’s.

archive_timeout deals with quiet databases. A segment is archived when it fills, and a small app can take hours to write 16 MB. With a 60-second timeout, Postgres switches to a new segment at least once a minute when there has been activity, so the archive is never more than about a minute behind.

Turning it on

The feature lives on the server, not the database, because WAL belongs to the cluster. On the server’s Maintenance tab there is one button. The agent (the Go program Railyard runs on each server) reads the current container’s configuration, removes the container, and starts it again with a dedicated Docker volume mounted at the archive path and the settings above added to its command line. Postgres reads archive_mode only at startup, so that restart is unavoidable, and apps on the server pause for a few seconds. If the new container fails to start, the agent starts the old configuration again and reports the error.

Then it takes the first base backup with pg_basebackup -Ft -z -X none, run inside the container. -Ft -z writes a compressed tar. -X none leaves WAL out of the base backup, which is safe here because the archive already has every segment written while the backup ran. Each base backup goes in a folder named after its Unix timestamp, which makes “the newest base backup before 14:04:59” a plain number comparison.

A recurring job on the control plane asks every server with the feature on for a fresh base backup at 3 a.m. The same command deletes base backups older than seven days and WAL segments older than eight. The extra day of WAL is there because the oldest base backup Railyard keeps still needs every segment from the moment it started.

The window a user can restore to is the later of “when it was turned on” and “seven days ago”, up to now. The date picker on the database’s Restore tab uses those bounds, and the model checks them again before anything reaches the agent.

Restoring without touching the live database

The restore had one rule from the start: the live Postgres keeps serving traffic the whole time, and nothing is written to it until the recovered data is ready.

Recovery replays the whole cluster. There is no way to replay WAL for one database; the log records changes to files, and all databases share it. So the agent builds a second, temporary Postgres for the restore. It picks the newest base backup at or before the target, extracts it into a fresh Docker volume, and adds the recovery settings:

# appended to postgresql.auto.conf in the restored data directory
# fetch each segment Postgres asks for from the archive
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2026-09-26 14:04:59+00'
recovery_target_action = 'promote'

It also creates an empty file named recovery.signal in the data directory, which is how Postgres 12 and later know to start in targeted recovery mode. restore_command is the mirror of archive_command: when Postgres needs the next segment, it runs this command to fetch it. recovery_target_time tells it where to stop, and promote tells it to open for writes once it gets there instead of pausing.

The archive volume is mounted into the temporary container read-only. A restore can read history but never change it.

The agent starts the container from the same image as the live Postgres, since physical files only work with the same major version, and polls SELECT pg_is_in_recovery() once a second. When it returns false, replay has finished and the temporary server holds the whole cluster as it was at the target second. Then the agent copies out only the one database the user asked for:

func copyOut(ctx context.Context, db, into, role string, replace bool) error {
	flags := "--no-owner --role=" + role
	if replace {
		flags += " --clean --if-exists"
	} else {
		if err := createDatabase(ctx, into, role); err != nil {
			return err
		}
	}
	pipe := fmt.Sprintf(
		"docker exec %s pg_dump -U postgres -Fc %s | docker exec -i %s pg_restore -U postgres -d %s %s",
		scratchContainer, db, liveContainer, into, flags)
	out, err := exec.CommandContext(ctx, "sh", "-c", pipe).CombinedOutput()
	if err != nil && !strings.Contains(string(out), "errors ignored on restore") {
		return fmt.Errorf("copying the recovered data failed: %w", err)
	}
	return nil
}

That is a logical copy taken from a physical restore, and it is the reason the feature is safe on a shared instance. Restoring the files over the live cluster would roll back every other app’s database on the server to 14:04:59 as well. Copying one database through pg_dump touches only that one.

By default the copy goes into a new database named after the original plus the target minute, owned by the app’s own login. The live database is untouched, so the user can open both, compare, and copy back the rows they lost. Heroku’s rollback works the same way: it gives you a new database rather than overwriting the old one. There is a second option, “Replace the live database”, which runs the same pipe with --clean --if-exists straight into the original. The form says in red that everything after the chosen time is lost, and the confirm dialog says it again.

When the copy finishes, or fails at any step, a deferred cleanup removes the temporary container and its volume.

The Rails side

In the control plane, the request is a REST controller that enqueues a job, and the job calls one model method:

def restore_to!(time, into: nil)
  server = application.server
  raise Error, "Point-in-time recovery is only for Postgres" unless engine == "postgres"
  raise Error, "Turn on point-in-time recovery for #{server.display_name} first" unless server.recovery_enabled?
  raise Error, "Pick a time within the recovery window" unless server.recovery_window.cover?(time)

  target = into.presence || "#{db_name}_at_#{time.utc.strftime('%Y%m%d%H%M')}"
  server.recovery!("restore", database: db_name, target_time: time.utc.iso8601,
    into_database: target, role: username)
  target
end

recovery! opens a streaming RPC to the agent and collects its progress lines until a completed event with an exit code. A non-zero code becomes an exception carrying the agent’s last line, and the job writes either “restored” or “couldn’t restore” with that reason to the app’s activity feed. On the agent, the database, target and role names must match a strict lowercase pattern before they reach any shell command, even though the control plane chose them.

Proving it

I tested it on a production server the way a user would hit it. I wrote a row, noted the second, deleted the row and confirmed the live table was empty. Then I restored to that second in copy mode. The new database had the row, and the live database still had none. Right after the test, the archive for that server took about 70 MB.

Limits

The archive lives on a Docker volume on the same server. That covers the common disaster, where a person or a bad migration deletes the wrong data. It does not cover losing the disk. If the server dies, the base backups and the WAL die with it, and the scheduled pg_dump files in object storage are what is left. Shipping the archive off the server to object storage is the next step.

Recovery reaches back only to when it was turned on. There is no history from before that button was pressed.

A restore replays the whole cluster, so its cost scales with every database on the server, not just the one you want back. The agent gives replay five minutes before it gives up. A server with a lot of daily writes would need a longer limit or more frequent base backups.

The newest minute or so may not be restorable. Changes still in the current WAL segment have not been archived yet, and if the target is later than the last archived change, Postgres stops recovery with an error instead of promoting. The agent catches that, shows the last lines of the recovery log, and says the target may be past the newest archived change.

Turning the feature on costs one Postgres restart and disk for a week of changes. Restoring costs a temporary second Postgres for as long as the replay and copy take. With the live database left alone throughout, the 14:05 DELETE becomes a second database ending in _at_202609261404, next to the live one, with every order still in it.