Warm standby database on another host: minimal replication for MySQL and PostgreSQL

For a small website or online store, a full high-availability database cluster is obvious overkill: it costs more than the business it is supposed to protect. A more reasonable goal is humbler — to have a current copy of the data on another server that can be quickly brought up manually in case the primary hosting fails. This is called a warm standby: it is updated in real time but does not accept traffic itself and does not fail over automatically — a human decides whether to switch.

Below is the minimal setup for such a copy for MySQL/MariaDB and for PostgreSQL, without additional orchestration tools. Everything was tested on two real machines (Debian 12 and Debian 13 — the database versions in the examples are deliberately different, that was part of the test).

MySQL and MariaDB: built-in replication

MariaDB (and compatible MySQL) has replication built into the server — no separate package is needed. The primary writes all changes to the binary log (binlog), and the replica connects to the primary as a client and replays those changes locally.

On the primary you need to enable the log and set a unique server identifier:

# /etc/mysql/mariadb.conf.d/60-repl.cnf — on the primary
[mysqld]
server_id = 1
log_bin = /var/lib/mysql/mariadb-bin
binlog_format = ROW
bind-address = 0.0.0.0

binlog_format = ROW means the log records the actual changed rows, not the SQL statements that changed them — this is more reliable because it avoids divergences that can occur when queries use non-deterministic functions (for example, NOW() or RAND(), which give different results on the primary and on the replica). Next — a user for replication, accessible only from the second hosting address:

CREATE USER 'repl'@'<replica_address>' IDENTIFIED BY '<password>';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'<replica_address>';

On the replica side — its own server identifier and connection using GTID (Global Transaction ID — a global transaction number that is the same on primary and replica; with it you don’t need to manually find the position in the binlog as the older setup method required):

CHANGE MASTER TO MASTER_HOST='<primary_address>',
                  MASTER_USER='repl', MASTER_PASSWORD='<password>',
                  MASTER_USE_GTID=slave_pos,
                  MASTER_SSL=0;
START SLAVE;

The line MASTER_SSL=0 was not decorative: when testing on MariaDB 11.8 the replica without this parameter immediately returned the error SSL is required, but the server does not support it, even though no encryption was explicitly requested — in that version the replication client tries to use TLS by default. Without a configured certificate on the primary it’s simpler to explicitly disable the attempt than to deal with certificates for a test task; for real hosting, if replication traffic goes over the public internet (and not a provider private network), you should still set up TLS — otherwise the connection between primary and replica is sent in cleartext, including the password used for authentication and all database contents.

The replica status is checked with one command:

SHOW SLAVE STATUS\G

Two fields decide everything: Slave_IO_Running: Yes means the replica is receiving the binlog from the primary, Slave_SQL_Running: Yes means it is successfully applying the received changes. Seconds_Behind_Master shows lag in seconds — in the test it was zero: an inserted row on the primary appeared on the replica almost instantly.

PostgreSQL: streaming replication

PostgreSQL works on the same principle — the transaction log (WAL, Write-Ahead Log) is sent from the primary to the standby — but the setup is arranged differently: the replica is not created from scratch, it is made as an exact binary copy of the current database state, to which the stream of changes is then connected.

An important requirement that is easy to miss: the PostgreSQL version on both servers must match — streaming replication works at the level of the internal page format, and that can differ between major versions. If two hostings provide different OS versions with different PostgreSQL versions “out of the box” (that was the case in the test — Debian 12 and Debian 13 default to PostgreSQL 15 and 17 respectively), you need to install the same version explicitly on both servers from the project’s official repository (apt.postgresql.org), not via the system package.

On the primary server:

ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 5;
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '<password>';
SELECT pg_create_physical_replication_slot('standby_shop');

A replication slot is a named reservation of WAL on the primary: while the replica is connected to this slot, the primary will not remove WAL files, even if the replica temporarily lags or disconnects. Without a slot the primary can remove WAL needed by the replica before it has a chance to fetch it, and then the replica would have to be recreated from scratch.

In pg_hba.conf you need a line allowing replication only from the second hosting address:

host replication replicator <replica_address>/32 scram-sha-256

The replica itself is created with a single command run on the future standby server — it downloads a full copy of the database from the primary and immediately configures the connection to continue the stream:

pg_basebackup -h <primary_address> -U replicator -D /var/lib/postgresql/17/main -P -R -X stream -S standby_shop

The -R flag creates a standby.signal file and writes the primary connection parameters — you don’t need to write them manually, the command does it for you. After starting the server on the replica, SELECT pg_is_in_recovery(); returns t (true) — the server is in recovery from the stream, i.e. it is the standby, not the primary. On the primary, replication status is visible in pg_stat_replication: the replay_lag field shows apply delay — in the test it was about one millisecond.

What this setup does not protect against

It’s important to understand the boundaries of the minimal solution, not only what it can do.

Replication is not a backup. It primarily repeats mistakes, not protects against them: an accidental DELETE without a WHERE clause, corrupted data, or ransomware — all of these will be replicated to the standby within seconds. Backups (regular dumps stored separately and versioned over time) are required in any case, regardless of whether replication is configured.

Failover is manual. This is a warm standby, not a hot one: when the primary fails the application will not switch to the standby by itself — you must manually stop recovery on PostgreSQL (pg_promote()) or reconfigure MariaDB on the replica to act as an independent server, and manually switch the address the application points to. Automatic failover is a different, clustering-level task, and for a small shop its cost is usually unjustified.

The database port is exposed. In the examples above the firewall restricts access to ports 3306 and 5432 only to the address of the second hosting, but the port still listens on the external interface. For a slightly more secure setup, instead of direct IP-based allow rules it is more appropriate to use a tunnel (for example, WireGuard) between the two servers — then the database port is not visible on the internet at all, and traffic goes through an encrypted channel. That’s the next step after the minimal setup; it was not deployed separately in this article.

Conclusion

Both MySQL/MariaDB and PostgreSQL can maintain a current copy on another server using features already present in the database itself, without installing additional software. The setup in both cases is a few parameters and one command to create the copy; the difference is that MariaDB connects to an existing database immediately, while PostgreSQL first copies it entirely. For a small website or store whose goal is “to have somewhere to switch manually in case of failure,” not “zero seconds of downtime,” this is a sensible minimum: cheaper than a cluster, but significantly more reliable than nothing.

Sources: MariaDB documentation (Standard Replication, GTID), PostgreSQL 17 documentation (Streaming Replication, pg_basebackup).