I run Coolify on a Contabo box, and Postgres is a container on that same box. That sentence alone tends to get a reaction from people who've been burned by self-hosting a database before, and the reaction is fair — most of the horror stories about self-hosted Postgres aren't about Postgres being fragile, they're about someone skipping one of four specific things this post covers, and finding out the hard way which one they skipped.
Why did my data disappear after I redeployed the container?
Because the volume holding it was never actually named. This is the single most common way people lose a self-hosted Postgres database, and it's entirely avoidable. Reference a path like /var/lib/postgresql/data in a service's volumes: list without declaring that volume under the top-level volumes: key first, and Compose quietly creates an anonymous volume — one tied to an internal ID, not a name you'd recognize. Recreate the container, and depending on exactly how the recreation happens, you can end up pointed at a fresh, empty volume while your actual data sits orphaned on disk under a name nothing references anymore. If self hosting needs to survive contact with a marketing team, XenGrowth's revenue operations work has the operational side.
Declare the volume under the top-level volumes: key with a real name — pgdata:, not an implicit path
Reference that exact name in the service definition: volumes: ["pgdata:/var/lib/postgresql/data"]
Run docker volume ls before and after any container recreation and confirm the named volume is the one actually mounted
Treat an anonymous volume showing up in that list as a warning sign worth investigating immediately, not later
What should shared_buffers and effective_cache_size actually be set to?
Postgres ships conservative on both, on purpose — the defaults have to work on hardware with a fraction of what a modern VPS carries. shared_buffers defaults to 128 MB, which is Postgres's own memory area for caching data pages, and the documentation's own guidance for a dedicated database server is to start at 25% of system RAM, going as high as roughly 40% before you stop seeing benefit. effective_cache_size is a different kind of setting entirely — it doesn't allocate memory itself, it tells the query planner how much memory is realistically available for caching across shared_buffers and the OS page cache combined, which shapes whether it picks an index scan or a sequential scan. It defaults to 4 GB in current Postgres versions, and the commonly cited, documentation-consistent starting point is 50-75% of total RAM on a box where Postgres is the main occupant.
Setting | Documented default | Starting point for a dedicated 8 GB box | What it actually controls |
|---|---|---|---|
shared_buffers | 128 MB | ~2 GB (25% of RAM) | Postgres's own cache of data pages in memory |
effective_cache_size | 4 GB | 4-6 GB (50-75% of RAM) | Tells the planner how much cacheable memory exists; doesn't allocate anything itself |
max_connections | 100 | Left at default, fronted by a pooler | Ceiling on concurrent backend connections; each one costs real shared memory |
None of these are magic numbers to copy blindly — a box also running the app container and a reverse proxy doesn't get to hand Postgres the full 75%, since the OS and everything else on the box needs headroom too. But starting from Postgres's own documented ranges instead of leaving the out-of-the-box conservative defaults in place is the single highest-leverage tuning change most self-hosted setups skip entirely. It costs nothing but a config edit and a restart, and it's the difference between a database that's using a meaningful fraction of the RAM you're already paying for and one that's still behaving as though it might be running on a machine from a decade ago. The XenGrowth resource library works through the operations side of this in more operational detail.
Do I actually need a connection pooler?
Probably, and earlier than it feels like you do. max_connections defaults to 100, and Postgres's documentation is explicit that raising it isn't free — it increases shared memory allocation directly, which means "just set it to 1000" isn't a solution, it's a different problem. The actual issue a pooler solves is that each application instance, each serverless function invocation, each background worker wants its own connection, and that count climbs fast in a way that has nothing to do with how many queries you're actually running concurrently. A pooler like PgBouncer sits between your app and Postgres, multiplexing many client connections onto a much smaller number of real backend connections, so the app can open connections liberally without Postgres ever seeing anywhere near that number. For a single-box SaaS this is worth wiring in well before you're anywhere near hitting 100 — the failure mode when you do hit it isn't graceful, it's new connections being refused outright.
Does it matter that Postgres is sharing the box with the app itself?
It does, and it's worth being deliberate about it rather than letting both containers fight over the same RAM by default. shared_buffers and effective_cache_size are only sensible settings if Postgres can actually rely on the memory they assume is available — tune them as though Postgres has the whole box to itself while the app container is also running, and the OS ends up arbitrating a resource conflict neither container was configured to expect. Capping the app container's memory explicitly, using the deploy.resources.limits key that Compose enforces natively without Swarm, is what keeps a memory-hungry request in the app from eating into the headroom Postgres's tuning assumed it had. It's a small piece of config, and it's the difference between the tuning numbers above being real guarantees or optimistic guesses.
Is copying the data directory a valid backup?
No, and Postgres's own documentation is unambiguous about this in a way that's worth quoting rather than paraphrasing: the database server must be shut down to get a usable backup from a plain filesystem copy, because tools like tar don't take an atomic snapshot of the filesystem and the server buffers data internally in ways that would leave a live copy inconsistent. A cron job that tars up the data directory while Postgres is running isn't a backup with a small risk attached — it's very likely to be a file you cannot restore from at all, and you won't find out until the day you need it. XenGrowth on governed AI marketing workflows works through AI agents and marketing automation in more operational detail.
There are exactly three approaches that actually work, and the difference matters: pg_basebackup, which takes a consistent base backup while the server keeps running; continuous WAL archiving, which layers point-in-time recovery on top of a base backup by continuously shipping write-ahead log segments somewhere durable; and a true filesystem-level atomic snapshot (the kind some cloud block storage or filesystems support), which works live specifically because it captures the WAL alongside the data directory in one instant rather than copying files one at a time.
Use pg_basebackup or continuous WAL archiving, not a manual file copy of a running instance
Ship the backup off the same box entirely — object storage in a separate account, not a second directory on the same disk
Test the restore path periodically, not just the backup job's exit code — a backup nobody has ever restored from is a hypothesis, not a plan
If you're relying on filesystem snapshots instead, confirm the WAL is included in the snapshot boundary, not just the data directory
What is WAL actually doing, and why does it matter for backups specifically?
The write-ahead log is Postgres recording every change before applying it to the actual data files, which is what makes crash recovery, replication, and point-in-time recovery all possible from the same underlying mechanism. max_wal_size defaults to 1 GB — the threshold that triggers an automatic checkpoint, the point where in-memory changes get flushed to the actual data files — and checkpoint_timeout defaults to 5 minutes, capping how long Postgres will go between checkpoints even if that size threshold isn't hit. Continuous WAL archiving works by shipping every completed WAL segment somewhere durable as it's generated, so a base backup plus the full sequence of archived segments since it was taken can replay the database's state up to almost any specific moment — which is exactly what point-in-time recovery is: not just "restore to last night's backup" but "restore to 2:14pm before the bad migration ran." On AI search, GEO and discovery specifically, XenGrowth on building one SEO and GEO content system is worth reading.
Approach | Safe on a live server? | What it actually gives you |
|---|---|---|
Plain filesystem copy (tar/cp) of a running data directory | No — explicitly documented as unsafe | A file that looks like a backup and very likely isn't restorable |
pg_basebackup | Yes — designed for this | A consistent full copy of the cluster at one point in time |
Continuous WAL archiving + a base backup | Yes | Point-in-time recovery to almost any moment, not just the last base backup |
Filesystem-level atomic snapshot including WAL | Yes, if the snapshot is truly atomic and includes WAL | A fast, storage-layer alternative to pg_basebackup, when the underlying disk supports it |
A backup you haven't restored from is a file that exists. Whether it's a backup is a separate, unverified claim.
What's the actual painful part of running this yourself?
Not the day-to-day. Once shared_buffers and effective_cache_size are set sensibly, a pooler is in front, and backups are running against object storage, a self-hosted Postgres instance mostly just runs — it's boring in the way you want infrastructure to be boring. The genuinely hard part is a major version upgrade, and it's hard because it isn't in-place: you can't just swap the container image from postgres:17 to postgres:18 and restart. A major version bump requires either pg_upgrade, which works but has real prerequisites and a real risk of extended downtime on a large database, or a full dump and restore, which is simpler to reason about but means your database is unavailable for however long the dump and restore actually take. There's no version of this that's a one-line config change, and it's the one recurring task on this list I'd genuinely rather not own if the database were significantly larger than what a bootstrapped SaaS is running.
So when does managed Postgres actually win?
Situation | Self-hosted (this setup) | Managed Postgres |
|---|---|---|
Day-to-day operation once tuned | Fine — boring in a good way | Fine, with less setup work up front |
Point-in-time recovery | You build and test it yourself against WAL archiving | Usually a built-in feature, a few clicks to restore to a timestamp |
Failover on hardware failure | Manual, unless you've built and tested standby promotion yourself | Typically automated, often within seconds |
Major version upgrades | pg_upgrade or dump/restore, downtime you own and schedule | Frequently a guided, lower-downtime path the provider has run thousands of times |
Cost at small scale | A few dollars of the VPS's own cost, effectively free beyond that | A real line item, often tens of dollars a month minimum even for a small instance |
Right for | A small team willing to own the operational list above in exchange for near-zero marginal cost | A team that would rather pay to not think about any of the rows above |
The honest trigger for switching isn't a specific database size or traffic number — it's whichever row in that table you'd rather stop owning, weighed against what managed Postgres actually costs on top of the box you're already running. For a bootstrapped SaaS on a Contabo Cloud VPS 4 at €5.50 a month (promotional, first 24 months, per Contabo's own pricing page), self-hosting the database is close to free beyond the box itself, provided the four things above are actually done rather than assumed. That's the argument this whole architecture rests on — covered in full in the simplest production architecture for a bootstrapped SaaS — and it's a genuinely different tradeoff than the one a larger, funded team should be making.
Further reading from XenGrowth
The XenGrowth resource library — what you'll learn: how the commercial side of this work is run, across search, automation and revenue operations.
XenGrowth on governed AI marketing workflows — what you'll learn: how the teams running AI marketing agents keep them governed and measurable.
XenGrowth on building one SEO and GEO content system — what you'll learn: how search and AI-answer visibility get run as a single content system.
Where this work meets go-to-market
The operational playbooks that sit alongside self hosting live with XenGrowth's growth engineering practice.
Four questions about what you'd be taking on. The post runs its own Postgres and still thinks plenty of people shouldn't — the deciding factor is rarely the database itself.
