Running databases on your own servers

A logical backup exports the data as SQL statements or an archive, using tools like pg_dump or mysqldump. It is portable between versions and lets you restore a single table, but it is slow to create and restore for large databases. A physical backup copies the database files, for example with pg_basebackup, pgBackRest or Percona XtraBackup. It is much faster for large data sets and, combined with continuous archiving of the WAL or binary log, enables point-in-time recovery. Most serious setups use physical backups with log archiving for recovery and occasional logical dumps for portability and for restoring individual tables.

Several layers. First, the service binds to 127.0.0.1 or a private network interface, which I verify with ss -tlnp. Second, the host firewall denies incoming traffic by default and only allows 22, 80 and 443; if app servers need access, I allow the private subnet only. Third, the cloud provider firewall mirrors those rules. Fourth, authentication is still strict: no remote root login, scram-sha-256 in PostgreSQL, strong passwords per app. For admin access from a laptop I use an SSH tunnel instead of opening a port. With Docker, I bind published ports to 127.0.0.1, because Docker can bypass ufw rules.

I start with memory. For MySQL, innodb_buffer_pool_size takes most of the RAM on a dedicated server, around 50 to 70 percent, and less if the app shares the machine. For PostgreSQL, shared_buffers around 25 percent of RAM, effective_cache_size around 50 to 75 percent, and a modest work_mem because it multiplies per operation and connection. I keep max_connections reasonable and add PgBouncer for many clients. Then I turn on slow query logging, look at the worst queries with EXPLAIN and add indexes. Most real performance wins come from queries and indexes, not from exotic configuration flags.

RPO, recovery point objective, is how much data you can afford to lose, measured in time. RTO, recovery time objective, is how long you can be down while restoring. A nightly dump gives an RPO of up to 24 hours; if the business can only lose minutes, you need continuous WAL or binlog archiving, or a managed database with point-in-time recovery. RTO depends on data size and practice: a 200 GB logical restore can take hours, while promoting a replica takes minutes. I agree on these numbers with the business first, then choose the tools, and verify both with timed restore drills.

Managed is the safer default when downtime or data loss is expensive and the team has no database specialist: you get automated backups, point-in-time recovery, patching, replicas and failover. The trade-offs are cost, less control over versions and extensions, and some vendor lock-in. Self-hosting is cheaper and fully flexible, and works well for small or medium projects when backups, monitoring and updates are automated and restores are tested. I often recommend starting self-hosted on a well-configured server for an MVP, and moving to managed once revenue or availability requirements justify it, since the migration is a planned dump and restore.

I create a dedicated read-only role with SELECT on the needed schema or tables only, plus default privileges so new tables are covered. Connections go through an SSH tunnel or a bastion, never a public port. Ideally the role points at a read replica or a recent restored copy, so heavy reports cannot slow production. If the data includes personal information, I limit columns through views or provide an anonymised copy. Access is personal, not shared, so it can be revoked individually, and statement logging or audit extensions record what was run. The account is removed when the person no longer needs it.

Failover means promoting a standby when the primary dies, then pointing the application at it. The dangerous case is split-brain: the old primary is only unreachable, not dead, and both accept writes, creating divergent data that is painful to merge. Prevention needs a consensus or fencing mechanism. Patroni with etcd for PostgreSQL, or a managed service, holds a leader lock and demotes or fences the old primary before promoting. Applications connect through a stable endpoint, such as a virtual IP, a proxy or DNS managed by the cluster. Synchronous replication can guarantee zero data loss at the cost of write latency and availability if the standby fails. For small teams, a managed database usually beats building this correctly.

Redis offers RDB snapshots, periodic point-in-time dumps that are compact and fast to load but lose writes since the last snapshot, and AOF, an append-only log of every write. With appendfsync everysec, the usual setting, a crash loses at most about a second. For queues and sessions I enable AOF plus RDB, set maxmemory-policy noeviction so Redis returns errors rather than silently dropping jobs, and alert on memory well before the limit. I also separate concerns: a cache instance with allkeys-lru and no persistence, and a queue instance with durability. If losing even a second of jobs is unacceptable, the job's source of truth should be the SQL database, with Redis only as transport.

Assume an attacker with root on the database server will try to delete backups too. So the server should be able to write backups but not delete or overwrite them: use a bucket in a separate account with object lock or versioning plus a retention policy, and credentials that only allow upload. Encrypt backups client-side, with keys stored outside the server, so a leaked bucket reveals nothing. Keep at least one copy with a different provider. Monitor for missing or unusually small backups, because a quiet failure looks the same as an attack. Finally, run restore drills from the off-site copy, using no access to the original server, which is exactly the situation after an incident.

Both are excellent, and the team's experience often matters more than features. PostgreSQL offers richer SQL, strong JSONB with indexes, partial and expression indexes, extensions like PostGIS and pgvector, transactional DDL and strict correctness, which makes it my default for complex domains and analytics-heavy apps. MySQL with InnoDB is simple to operate, has very mature replication, a huge hosting ecosystem and instant column additions for many schema changes, and frameworks like Laravel work equally well on it. Operational differences matter too: PostgreSQL needs connection pooling and vacuum awareness earlier, MySQL needs care with online DDL on big tables. I pick PostgreSQL unless existing systems, hosting or team skills favour MySQL.