Self-Hosting a Database: Personal vs Production

Running PostgreSQL on your own server is a reasonable choice for a personal project. For a business it is a serious operational commitment. The software is identical in both cases. What changes is the list of duties that come with it, and whether anyone is on the other end when one of those duties fails.

We are not neutral on this. Self-hosting gives you control over where the data sits, what the machine costs and how the server is configured. We also run managed databases for client work, because the duties below are real work.

What does self-hosting give you?

• Data location. The rows sit on a machine you chose, in a jurisdiction you chose, on a disk you can physically locate.
• Cost shape. A small VPS has a predictable monthly price that does not grow with read volume the way a managed plan can. Whether it is cheaper overall depends on how you price your own hours.
• Configuration freedom. Extensions, postgresql.conf settings, filesystem layout, connection limits and upgrade timing are yours.

Portability belongs on the list: a PostgreSQL dump restores into any other PostgreSQL, and the format is documented and stable. The trade is this: a hosted service sells you the duties, not the software.

What does a minimal safe personal setup look like?

For a side project the goal is not high availability. The goal is that a stolen password, a lost machine or a wrong DELETE does not end the project. Four things do most of that work.

Keep the server off the public internet

PostgreSQL listens on localhost by default, as the connection settings documentation states. To reach it from your laptop, put an SSH tunnel or a private network in front of it instead of opening 5432 to the world. If it shares a network with the application, restrict the firewall to that subnet.

Use real credentials, with the smallest role that works

PostgreSQL stores passwords with SCRAM-SHA-256 by default, and the MD5 option is deprecated and marked for removal. Create a separate login role for the application and grant it only the tables and operations it uses. The application does not need to be a superuser, and a leaked connection string should not be able to drop the schema.

Connection rules live in pg_hba.conf, and the documentation is blunt about how they are read: the first matching record is used, with no fall-through. A trust line near the top lets anyone who can reach the port log in as any user. After editing, reload with pg_ctl reload or SELECT pg_reload_conf(), then check SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL.

Encrypt the connection, and verify the certificate

SSL is off by default. Turning it on takes a certificate, a key and ssl = on. On the client side the default sslmode is prefer, which attempts encryption and falls back to plaintext. require encrypts the traffic but, as the libpq documentation puts it, leaves you trusting the network to connect you to the right server, so it stops eavesdropping but not a man in the middle. Use verify-full with a root certificate for anything crossing a network you do not control.

Take backups, then restore one

On a single machine a nightly pg_dump is enough. Two details are easy to miss. pg_dump dumps one database and does not include roles or tablespaces, so you also need pg_dumpall --globals-only. The dump is internally consistent, a snapshot from the moment it started, but different databases inside a pg_dumpall are not synchronised with each other.

Write the file somewhere other users cannot read it, copy it to another machine, and keep more than one generation. Then do the part most people skip: restore it into a scratch database with pg_restore and look at the rows. A backup nobody has restored is a hope, not a backup.

What changes when the database is production?

Production means other people's data, and usually a promise that the service is available. The same items remain, but each gains a second layer, and the second layer is where the time goes.

A replica is not a backup

Streaming replication keeps a standby server applying the primary's write-ahead log. A hot standby can also answer read queries, but the point is what replication does not protect you from. It replicates the DELETE. A mistaken migration, a bad script or a corrupted table reaches the standby within seconds.

Two further facts matter in operation. Streaming replication is asynchronous by default, and the documentation warns that some committed transactions may not have reached the standby when the primary crashes. And promotion is an explicit act, pg_ctl promote or pg_promote(), not something the standby decides on its own. If nobody is awake to run it, the standby is spare capacity.

Point-in-time recovery

The mechanism that answers "undo the last twenty minutes" is continuous archiving. You keep a base backup taken with pg_basebackup, ship every WAL segment into an archive with archive_command, and on restore replay the log up to a chosen timestamp. The documentation is precise about the limits: you can recover to any time since your base backup was taken, but not to a point during the backup itself, and you need a continuous WAL sequence reaching back that far.

This is also the part that fails quietly. If archive_command stops succeeding, PostgreSQL keeps retrying and pg_wal keeps growing; if that filesystem fills, the server shuts down with a PANIC and stays offline until space is freed. Some archiver failures are not reported in pg_stat_archiver at all, which is why the documentation says to monitor archiving directly. The recovery window you believe you have is only as long as the WAL you actually kept.

A restore procedure someone else can follow

Write the steps down, with the commands, the file locations, where the credentials live and the order of operations. Give the procedure an owner. Then run it on a schedule against a copy, on a machine that is not the production one, and time it. That measured number is your real recovery time; anything else is a guess. The documentation recommends testing archiving before the first base backup, keeping several backup sets and inspecting the recovered database.

Patch windows

PostgreSQL supports each major version for five years, and minor releases arrive at least once every three months, according to the project's versioning policy. Minor releases carry fixes for security issues, data corruption and frequently hit bugs, and the project recommends running the current minor release for your major version. A self-hosted server that nobody upgrades drifts out of that window without announcing it.

A patch window is the operational answer: a recurring slot, a check on staging and a rollback plan. It is also the duty a managed service performs on its own schedule. Amazon RDS, for example, runs maintenance in a weekly 30-minute window and schedules required security and reliability patches automatically. The window sets when maintenance starts, not when it ends, and the documentation warns that it does not prevent mandatory upgrades if you keep deferring them.

Access logs and separated environments

Turn on log_connections, which is empty by default, and log_disconnections. Failed authentication is logged regardless, which is the signal you want when someone is trying passwords. A slow query threshold such as log_min_duration_statement gives you a record of what the application actually does at three in the morning.

Keep production, staging and development on separate databases, with separate roles and credentials. Staging that shares the production connection string is a production incident with extra steps, and a restored copy of production data in a development environment is a data protection problem before it is a technical one.

What does a hosted service actually take over?

On Amazon RDS, automated backups run daily in a backup window, the retention period can be set between 0 and 35 days, and 0 disables them. Within the retention period you can restore to a point in time. Deleting the instance deletes its automated backups unless you choose to retain them, while manual snapshots are independent and survive.

Google Cloud SQL takes automated backups on a cadence you configure and stores them incrementally; its documentation notes that the retention range depends on the backup option you use, so read the setting rather than assuming a default. Its point-in-time recovery restores into a new instance rather than the existing one, which is good hygiene and a difference worth knowing before an incident.

What neither takes over is the part that decides whether the restore is useful: the schema, the indexes, the query plans, the credentials, the knowledge of what correct data looks like, and the decision to drill. A managed database removes the maintenance work, not the responsibility.

What does being wrong cost?

We will not put figures on this. The mechanisms are the same everywhere.

• The recovery point. If your last restorable backup is from last night, a failure at 17:00 costs a working day of records. The gap is set by your backup cadence.
• The recovery time. A restore is download, unpack, replay the log, verify. It grows with database size and with how rusty the person doing it is. Until you have timed it, you have no estimate.
• The silent failure. Backup jobs fail for weeks without anyone noticing, and the problem surfaces during the incident.
• The logical mistake. A bad migration or a mistaken delete is copied to every replica instantly. Only a point-in-time archive or an older dump gives it back.
• The patch gap. Months without an update are months of known vulnerabilities left open, on a port somebody can reach.
• The blast radius. A superuser credential in an application config, or a database port exposed to the internet, turns one small bug into a full compromise.

None of these requires malice. They are ordinary mistakes, and the operational setup exists to make them survivable.

A short self-assessment

Answer these honestly. They are ordered roughly by how often the answer is no.

• Can you restore your database to a specific hour, not just to last night?
• Have you restored a backup in the last three months, on a machine other than the one that took it?
• Is the application's database role unable to drop tables or read other schemas?
• Is the database reachable only from the private network, or from an address you control?
• Do you know which PostgreSQL minor version you run, and when it was last updated?
• Can someone other than you perform the restore, using only the written procedure?

Three or more "no" answers on a database that holds a business, a customer list or personal data means the setup is personal-grade. That is a fact about the current state, fixable in an afternoon or by contract.

Where we land

Self-host the personal project. The control is real, the cost is legible and the minimal setup above is a few hours of work. For a business, choose deliberately: a managed database if nobody wants to own the duties, or a self-hosted one with a named owner, a written restore procedure, a patch calendar and a monitoring check that reaches a human.

The combination we would avoid is a business database that is self-hosted in name and unmanaged in practice. It carries the cost of both models and the safety of neither.