VPS Database HA: MySQL and PostgreSQL Replication

Running MySQL or PostgreSQL on a single VPS works fine until the first serious incident: a kernel panic, disk failure, data center network issue, or even a bad OS update. At that moment you do not care about benchmark numbers; you only care about how fast you can recover and how much data you might lose. That is exactly what database replication, high availability (HA) and automatic failover are designed to protect you from. In this guide, we will walk through how to design and implement MySQL and PostgreSQL replication on VPS servers in a way that is realistic, maintainable and testable. We will cover core concepts, practical topologies, MySQL and PostgreSQL specific settings, connection routing (proxies, VIP and DNS), and the operational side: backups, monitoring and disaster recovery. The goal is simple: after reading this, you should be able to sketch a concrete HA plan for your own VPS databases and know which tools and patterns to adopt first.

This guide uses MySQL 8.4 LTS and PostgreSQL 17, with supported minor releases and compatible clients. The MySQL commands are not MariaDB instructions. Validate backups, versions, certificate trust and administrative recovery before rollout. Standalone replication and a Patroni-managed cluster have different process owners and bootstrap procedures.

Why Database Replication and HA Matter on a VPS

CPU, RAM and storage capacity are only part of the design. Set an acceptable recovery time (RTO) and data-loss target (RPO), then evaluate replication, backups and failover against those needs.

Replication is the process of continuously copying data changes from one database instance (primary) to one or more others (replicas). High availability adds orchestration around that replication: health checks, automatic promotion, and a way for applications to reconnect to the appropriate node after failover.

Backups are essential, but restoring a database can take longer than promoting a ready replica. Data loss depends on which backups and transaction logs survive and can be recovered; it is not inevitable. Replication combined with off-site backups can help meet tighter RPO/RTO targets. For WooCommerce, SaaS dashboards and internal ERP systems, the choice between a single VPS and HA should follow the application’s recovery targets and operating budget.

If you are still deciding whether to scale vertically or build HA, it is worth reading our article High Availability vs one big server for e‑commerce and SaaS. A small HA setup on multiple VPSs can reduce downtime from individual node failures, but its cost-effectiveness depends on downtime costs, redundancy and operational overhead.

Core Concepts: Replication, Failover and Topologies

Primary / Replica (Master / Slave) Basics

Both MySQL and PostgreSQL follow the same high-level model:

• Primary: accepts all writes (INSERT/UPDATE/DELETE, DDL). Clients may also read from it.
• Replica: receives changes from the primary and replays them. Usually read-only, serving analytics or read-heavy traffic.

In MySQL, classic replication is statement-based or row-based using binary logs (binlog). Modern deployments typically use row-based binary logging with GTID (Global Transaction ID) for easier recovery and failover.

In PostgreSQL, HA commonly uses physical streaming replication: the primary writes WAL (Write-Ahead Log) segments, and replicas stream and replay them. You can also use logical replication for more advanced use cases (partial tables, cross-version migrations), but physical replication is the standard for HA.

Synchronous vs Asynchronous Replication

Replication timing directly affects your RPO:

• Asynchronous: the primary returns a local commit without waiting for a replica. Unreceived writes may be lost on failover; without a lag bound the loss is not limited to a few seconds.
• MySQL semisynchronous: the configured number of replicas acknowledges writing and flushing events to its relay log, not applying them. A timeout can revert the source to asynchronous replication.
• PostgreSQL synchronous: synchronous_commit=on waits for durable remote WAL flush, remote_write waits for the remote operating-system write, and remote_apply waits for durable flush and replay. Configure synchronous_standby_names and select an eligible failover target as well.

MySQL offers semi-synchronous replication plugins and group replication. PostgreSQL supports synchronous replication with synchronous_commit and synchronous_standby_names. Starting with asynchronous replication is reasonable only if its potential data loss fits your RPO. Evaluate synchronous options for stricter recovery targets, including their effects on latency and write availability.

Typical HA Topologies on VPS

On 2–4 VPS servers, these topologies are common:

• Single primary + one replica: basic database redundancy. A ready replica can shorten recovery through promotion (often manual), offload reads and move backup work off the primary.
• Single primary + two replicas: better survivability (one replica can remain available while another is down), room for analytics, and flexibility to do rolling OS/database upgrades where version compatibility permits.
• Primary/replica with quorum and fencing: usually with a coordination layer (etcd/Consul for PostgreSQL Patroni, or orchestrator-style tools for MySQL) to select a new primary, with fencing to prevent the old primary from continuing to accept writes.

MySQL Group Replication and InnoDB Cluster are separate cluster designs. InnoDB Cluster defaults to single-primary; multi-primary is optional. PostgreSQL can use Patroni or a separately evaluated repmgr design. Each approach still needs supported versions, membership, safe promotion and client routing; a proxy does not turn ordinary replication into a complete cluster.

Related guide: Beyond backups: choosing between MariaDB Galera Cluster and MySQL Group Replication for real HA.

Choosing the Right VPS Architecture for HA

How Many VPS Servers Do You Really Need?

Plan capacity and redundancy around three roles:

• Database nodes: two or more independently placed VPSs with measured disk/network capacity. Two data nodes leave only one copy after a node failure.
• Controller quorum: for the Patroni example, three actual etcd members, one each on DB1, DB2 and a helper, with Patroni on both database nodes.
• Routing and application: redundant proxies and application instances where end-to-end availability requires them. PgBouncer provides pooling, not leader election.

If budgets are tight, you can start with 2 database VPSs + 1 app/proxy VPS, then grow towards fully separated roles as traffic increases. The shared app/proxy VPS remains a single point of failure, so this provides database redundancy rather than end-to-end HA. Our article When to separate database and application servers for MySQL and PostgreSQL goes deeper into this decision.

The etcd quorum is two of three voting members, not two PostgreSQL servers plus an informal witness. Installing etcd only on the helper leaves a single DCS member. Colocation is a capacity tradeoff: test DB I/O pressure against etcd latency, and verify independent underlying failure domains. Requiring every write on two durable database copies blocks new writes when only one data node remains.

Single Region vs Multi-Region Replication

Most HA setups start inside a single data center or region: lower latency, simpler networking and fewer moving parts. Once your RTO/RPO requirements include regional disasters or long outages, you can extend replication across regions.

For that, we like patterns similar to those described in multi‑region architectures with DNS geo‑routing and database replication: keep a primary cluster in one region, stream replication to another region, and use DNS or traffic manager logic to fail over clients. This design requires VPS placement in separate regions, alongside a backup and DR plan.

Network and Storage Considerations

Replication streams WAL/binlog data over the network; under heavy write load, that traffic can be significant. On a VPS, ensure:

• For local HA, your nodes share a low-latency network while occupying independent failure domains, avoiding a shared physical host or storage dependency where possible. Cross-region replicas trade higher latency for protection against regional outages.
• Measure storage latency, write endurance and sustained I/O under the combined database, replication and backup workload. NVMe may help, but the interface alone does not guarantee performance. Our NVMe SSD vs SATA SSD vs HDD comparison compares the storage options and their tradeoffs.

MySQL Replication on VPS: Practical Setup and HA Patterns

1. Preparing MySQL for Replication

Assume MySQL 8.4 LTS on both hosts, with GTID already enabled on the source. Converting a running non-GTID topology needs a separate migration plan. db1.internal.example.com resolves to 10.0.0.1, and db2.internal.example.com to 10.0.0.2; CA-issued server certificates must match those names. Validate the package source, effective configuration, administrative access and unique server_id/server_uuid on each host.

1. Configure unique server IDs in my.cnf on each node:

[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log_replica_updates = ON
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
read_only = OFF
super_read_only = OFF

The future source uses these replica settings:

[mysqld]
server_id = 2
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log_replica_updates = ON
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
read_only = ON
super_read_only = ON
relay_log = relay-bin
event_scheduler = OFF

Binary logging and log_replica_updates are enabled so the replica can later serve downstream replicas; GTID itself does not require binary logging on every replica. Apply startup changes through the distribution’s actual service/configuration path in a maintenance window and verify the effective values.

Create a dedicated replication account for the replica’s exact source IP as observed by MySQL, accounting for NAT. Use a separate account for monitoring. Keep MySQL port 3306 off the public Internet and restrict internal firewall access to authorized application, replication and administration hosts. Replace the secret in a protected SQL session; do not put actual passwords into shell arguments, shared command histories or logs.

CREATE USER 'repl'@'10.0.0.2'
  IDENTIFIED WITH caching_sha2_password BY 'REPLACE_WITH_UNIQUE_SECRET'
  REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.2';

REPLICATION SLAVE is sufficient for this account; REPLICATION CLIENT belongs to monitoring/backup needs when required. CREATE USER/GRANT does not require FLUSH PRIVILEGES. The default caching_sha2_password mechanism works over the verified TLS channel below. Replication credentials are stored in the replica connection metadata, so protect the data directory and backups.

Seed a new isolated replica from one consistent snapshot. The example has one application schema, appdb; list every application schema together if there are more. A partial dump records the source’s complete GTID set, so omitting a schema can mark missing data as already applied. Inventory and provision users, roles, grants and routine DEFINER accounts separately, with their intended privileges; do not blindly restore mysql system tables as part of a GTID seed.

During the dump, application tables must use InnoDB; suspend DDL, schema deployments and account/grant changes. Nontransactional tables need a different consistency plan. The pre-created /secure directory must be restricted to the authorized backup operator. Verify option-specific backup privileges, including SELECT, SHOW VIEW, TRIGGER and the relevant RELOAD, REPLICATION CLIENT, EVENT/routine permissions.

umask 077
mysqldump --host=db1.internal.example.com --user=backup --password \
  --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/mysql/ca.pem \
  --single-transaction --quick --source-data=2 --set-gtid-purged=ON \
  --routines --triggers --events --hex-blob --no-tablespaces \
  --databases appdb > /secure/replica-seed.sql

–source-data=2 briefly takes a global read lock at the start and writes the matching File/Position as a comment. –set-gtid-purged=ON records the source GTIDs and disables session binary logging during import. –no-tablespaces omits tablespace definitions; general tablespaces need a separate restore plan. Check the exit status/stderr, hash the completed dump, transfer it over an encrypted channel and test its restoration. A raw LVM snapshot requires database-consistent coordination; it is not automatically a valid seed.

Use only a fresh, application-fenced target with reviewed GTID state. Existing data or overlapping GTIDs require a separate migration plan; do not clear data or reset GTID history. Temporarily opening read_only/super_read_only for the authorized import must not expose the target to application writers. The seed_admin account needs privileges for every statement in the reviewed dump, including its GTID/session settings.

mysql --host=db2.internal.example.com --user=seed_admin --password \
  --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/mysql/ca.pem \
  < /secure/replica-seed.sql

Stop on any import error. Validate schema/data, grants and DEFINER accounts, then restore read_only=ON and super_read_only=ON and verify both. This is an application-schema seed, not an unreviewed –all-databases system-table import. Retain a documented account/configuration inventory for promotion.

File/Position and manual read-lock alternative

A separate file-position channel must use coordinates from the same snapshot. MySQL 8.4 uses SHOW BINARY LOG STATUS. If taking a manual global read lock, keep the issuing SQL client open while a second session reads coordinates and takes the coordinated backup:

FLUSH TABLES WITH READ LOCK;

In the second SQL session while the lock is held:

SHOW BINARY LOG STATUS\G

After the backup completes or is safely abandoned, release the lock in the original lock-owning session:

UNLOCK TABLES;

A mysql -e client that exits releases its lock before a later dump. File/position mode uses SOURCE_AUTO_POSITION=0 and the matching SOURCE_LOG_FILE/SOURCE_LOG_POS; do not combine it with the GTID path below. Retain the full dataset, account inventory and source history required by the chosen method.

Related guide: MySQL backup strategies: mysqldump, XtraBackup and snapshots.

Separately preparing accounts and DEFINERs on the isolated target must not introduce unintended local GTIDs; the authorized operator must review that provisioning session’s sql_log_bin policy and the final GTID set. If roles later reverse, provision the replication account for the new replica’s exact observed IP rather than widening it to a wildcard.

Before seeding, verify event_scheduler=OFF on the replica and disable local scheduled jobs. Fencing application connections does not stop local event execution. Enable imported event definitions only through the promotion runbook after proving that the new primary is the sole job owner.

On the replica, both receiver and applier threads must be stopped before changing to auto-position if a channel is already running. The CA file must be readable by MySQL, and SOURCE_HOST must match the certificate identity. In a protected SQL session configure:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'db1.internal.example.com',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'REPLACE_WITH_UNIQUE_SECRET',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1,
  SOURCE_SSL_CA = '/etc/mysql/ca.pem',
  SOURCE_SSL_VERIFY_SERVER_CERT = 1;
START REPLICA;
SHOW REPLICA STATUS\G

Check Replica_IO_Running/Replica_SQL_Running, Last_IO_Error, Last_SQL_Error and all Performance Schema applier worker errors. Verify Auto_Position=1 and the effective TLS/CA/identity settings. Seconds_Behind_Source=0 is not proof of catch-up or zero possible data loss; the receiver can be behind on a slow network. Compare GTID history and application invariants. If required GTIDs have been purged, recover the missing trusted history or take a new seed; disabling auto-position does not recover missing data.

2. Semi-Synchronous Replication (Optional)

Semisynchronous replication requires compatible plugins on the participating source and replicas. Load only the libraries shipped for the installed MySQL version; verify that the plugin is installed before enabling its variable.

• Source: rpl_semi_sync_source plugin; rpl_semi_sync_source_enabled=ON.
• Replica: rpl_semi_sync_replica plugin; rpl_semi_sync_replica_enabled=ON.
• When enabling a running replica, reconnect its receiver with STOP REPLICA IO_THREAD and START REPLICA IO_THREAD so it registers its semisynchronous capability.
• Verify plugin status, acknowledgement count, timeout/fallback and configured wait point under failure and load; persist intended settings through the appropriate option files.

An acknowledgement means the events reached a replica relay log and were flushed to disk, not that SQL replay completed. Timeout can return the source to asynchronous operation. Wait point, durable local settings and promotion of a replica holding the required acknowledged transactions matter; this is not an unconditional RPO=0 guarantee. A failed old source may contain divergent/unacknowledged work and must be reconciled or rebuilt before reuse.

3. Handling Failover for MySQL

Replication alone does not give you HA. You need a way to:

• Confirm failure and suspend application writes; fence the old primary so it cannot accept writes, including direct clients and scheduled jobs. read_only/super_read_only alone is not infrastructure fencing.
• Select an eligible replica using GTID history, apply state, errors and the accepted data-loss budget; apply the available required relay-log work before promotion.
• Record replication metadata and recovery evidence. Do not use a generic RESET REPLICA ALL recipe that discards channel information before diagnosis.
• Complete promotion through the chosen runbook/controller, prove that exactly one writer is reachable, then update routing and validate application recovery.

Common patterns on VPS setups:

• Application-aware failover requires supported driver behavior, role checks, reconnects and transaction recovery. Trying a read-only replica is not promotion.
• HAProxy or ProxySQL can route traffic according to a tested role policy; they do not independently fence an old writer.
• Orchestrator/Raft quorum belongs to the Orchestrator controllers, for example two of three controllers, not the database replicas. A single helper/controller is still a controller failure point.
• A provider-supported floating IP or redundant proxy endpoint can stabilize addressing, but existing sessions still need recovery.

For MySQL, ProxySQL is particularly powerful: it can route writes to the primary and reads to replicas, handle connection pooling, and integrate with MySQL monitoring. A lagging replica can return stale data immediately after a successful write. Route reads that require read-after-write consistency to the primary or to a replica verified to have applied the required transaction. For a related configuration guide, see ProxySQL read/write split and pooling for WooCommerce and Laravel. The same patterns apply on a VPS-based HA setup.

The openark/orchestrator repository is archived; its README directs maintenance collaboration to the Percona fork. Check the selected distribution’s maintenance, support and MySQL 8.4 compatibility before adoption. InnoDB Cluster/MySQL Router uses its own membership and routing design; do not apply a classic replication runbook to it unchanged.

PostgreSQL Streaming Replication on VPS

1. Preparing PostgreSQL for Streaming Replication

Use compatible supported PostgreSQL 17 minor releases on both nodes. Physical streaming is not a cross-major upgrade mechanism. pg1.internal.example.com / 10.0.0.1 is the primary; pg2.internal.example.com / 10.0.0.2 is the standby. Prepare CA-issued server certificates, correct key ownership, private DNS and the client CA file first.

1. Configure WAL and replication settings on the primary’s postgresql.conf:

listen_addresses = '10.0.0.1'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
max_slot_wal_keep_size = '10GB'
hot_standby = on
password_encryption = 'scram-sha-256'
ssl = on
ssl_cert_file = '/etc/postgresql/tls/server.crt'
ssl_key_file = '/etc/postgresql/tls/server.key'

Change the standby listen address and node certificate to its own identity; preserve required standby-compatible resource settings. 10GB is an illustrative slot-retention budget to size from write rate and tolerated outages. A slot can fill the disk if retention is unlimited; a limited slot can lose required WAL and require a new seed. Apply startup-only settings in a planned window.

WAL archiving is separate from streaming replication. For PITR, after configuring a tested pgBackRest repository and app stanza, an archive configuration can use:

archive_mode = on
archive_command = 'pgbackrest --stanza=app archive-push %p'

Provision the off-host repository, credentials/ownership and retention first; run stanza-create, check, a complete backup and a restore drill. The check command can trigger a WAL switch. A bare cp or success merely because the destination exists is not a complete reliable archive policy: retries, identical-file verification, conflicting files and full disks need defined handling. Archived WAL is not a base backup.

2. Allow replication connections in pg_hba.conf on the primary:

hostssl replication repl 10.0.0.2/32 scram-sha-256

pg_hba.conf uses the first matching record; earlier broad rules must not bypass hostssl/SCRAM. Verify ssl=on, readable certificate/key files and the effective rule. Permit port 5432 only from the required internal sources.

In an authorized psql session create the role and use the password prompt. If the role already exists, inspect it and plan any password rotation rather than recreating it.

SET password_encryption = 'scram-sha-256';
CREATE ROLE repl WITH LOGIN REPLICATION;
\password repl

2. Base Backup and Standby Configuration

On the standby:

• Stop the standalone PostgreSQL process that would use the target directory. Use a new empty target; never blindly clear an existing data directory. Preserve existing data and configuration first.
• Run pg_basebackup as the postgres operating-system account. The example data directory is owned by postgres with mode 0700.
• Prepare /var/lib/postgresql/.pgpass, owned by that account with mode 0600, containing a protected pg1.internal.example.com:5432:replication:repl:REAL_SECRET entry; escape colons/backslashes as needed.
• replica1 must not already exist when using –create-slot. Allow two walsender connections for the backup plus streamed WAL, sufficient slot capacity, and correctly mapped paths for additional tablespaces.

pg_basebackup \
  --dbname="host=pg1.internal.example.com port=5432 user=repl application_name=standby1 sslmode=verify-full sslrootcert=/etc/postgresql/tls/ca.crt passfile=/var/lib/postgresql/.pgpass" \
  --pgdata=/var/lib/postgresql/17/replica \
  --format=plain --wal-method=stream --write-recovery-conf \
  --create-slot --slot=replica1 --progress

–write-recovery-conf creates standby.signal and writes connection and slot settings to postgresql.auto.conf. PostgreSQL 17 does not use recovery.conf. Inspect leftover slots and retained WAL after an interrupted backup; do not delete an active consumer’s slot. Review copied settings plus distribution configuration outside the data directory, including listen address, certificate, hot_standby and the actual service data path before starting.

Start the correct standalone instance and inspect pg_stat_replication on the primary; on the standby verify pg_is_in_recovery(), pg_stat_wal_receiver and receive/replay LSNs. Test a TLS/SCRAM replication connection and application-level data invariants. Reaching streaming state alone is not a complete failover test.

3. Synchronous Replication (Optional)

For a standalone synchronous example, require the named standby and durable remote flush on the primary:

synchronous_commit = on
synchronous_standby_names = 'FIRST 1 (standby1)'

standby1 matches application_name in the basebackup connection above. If the required standby is unavailable, affected commits wait. on waits for durable remote WAL flush; remote_write is only an operating-system write and remote_apply additionally waits for replay. An empty synchronous_standby_names does not require a remote acknowledgement. Per-transaction synchronous_commit overrides and choosing an ineligible promotion target change the protection. With only two database nodes, losing one means two-copy synchronous writes cannot continue.

4. Automatic Failover with Patroni or repmgr

Choose manual or automatic failover from the tested RTO and operational capacity, not merely whether an application is customer-facing. Patroni owns PostgreSQL process and leader lifecycle; repmgr is a separate approach that needs its own supported configuration and failure policy.

• Run Patroni on each PostgreSQL node and three actual etcd members across DB1, DB2 and the helper; quorum is two of three. PostgreSQL itself is not the DCS backend in this example.
• Use the etcd3 section for the v3 API. Provision etcd cluster membership, peer/client TLS, authentication and constrained key-prefix permissions separately.
• Do not let both the distribution’s standalone service and Patroni start the same database. Existing-data adoption or fresh bootstrap needs an explicit migration plan.
• Test leader lease loss, lag/timeline eligibility and fencing/watchdog behavior. synchronous_mode does not always require two durable copies; synchronous_mode_strict can block writes without a suitable standby. Manual override and cancelled/uncertain commits retain important limits.

A role-aware proxy can check Patroni /primary, which returns 200 only for a running primary holding the leader lock. PgBouncer is a pooler with no internal failover detection; use an external role-aware routing or target-update mechanism. Plan redundant proxies, pool reconnection and application recovery.

Patroni architecture fragment

The YAML below is not a launch-ready cluster recipe. Pin compatible Patroni/etcd versions and separately complete etcd peer/client bootstrap, database initdb or existing-data adoption, PostgreSQL HBA/TLS settings, backup/restore, watchdog and client trust configuration. REPLACE_WITH_* values are literal placeholders; distribute actual secrets through a controlled process and restrict file ownership/permissions. The YAML keys follow the Patroni 4.1.0 reference; compare them with the documentation for your selected supported Patroni release.

scope: app-ha
namespace: /service/
name: pg1

restapi:
  listen: 10.0.0.1:8008
  connect_address: pg1.internal.example.com:8008
  certfile: /etc/patroni/tls/server.crt
  keyfile: /etc/patroni/tls/server.key
  authentication:
    username: patroni_api
    password: REPLACE_WITH_API_SECRET

etcd3:
  hosts:
    - etcd1.internal.example.com:2379
    - etcd2.internal.example.com:2379
    - etcd3.internal.example.com:2379
  protocol: https
  cacert: /etc/patroni/tls/etcd-ca.crt
  cert: /etc/patroni/tls/etcd-client.crt
  key: /etc/patroni/tls/etcd-client.key
  username: patroni_dcs
  password: REPLACE_WITH_DCS_SECRET

postgresql:
  listen: 10.0.0.1:5432
  connect_address: pg1.internal.example.com:5432
  data_dir: /var/lib/postgresql/17/patroni
  bin_dir: /usr/lib/postgresql/17/bin
  authentication:
    superuser:
      username: postgres
      password: REPLACE_WITH_ADMIN_SECRET
    replication:
      username: repl
      password: REPLACE_WITH_REPLICATION_SECRET
      sslmode: verify-full
      sslrootcert: /etc/postgresql/tls/ca.crt

For pg2, review name, every listen/connect_address, node-specific certificate/key, data path and binary path. The three DCS endpoints must already form the intended cluster. v2 keys do not become visible to v3 by renaming the config section; etcdv3 gRPC gateway does not turn a TLS common name into DCS user authorization. Configure REST server-certificate verification for all Patroni peers, proxies and administrative clients. Restrict API access to authorized network sources and authenticate mutation endpoints.

If you are planning a serious PostgreSQL deployment on a VPS, also look at our performance-focused article The friendly VPS playbook for PostgreSQL performance and our autovacuum tuning guide. Poorly tuned vacuum and WAL settings can increase replication lag and delay recovery.

Automatic Failover Patterns: Proxy, VIP and DNS

1. Proxy-Led HA (HAProxy, ProxySQL, PgBouncer)

In this model, your applications talk to one or two proxy endpoints:

• For MySQL: ProxySQL or HAProxy.
• For PostgreSQL: PgBouncer and/or HAProxy.

The orchestrator selects and safely promotes an eligible replica; the role-aware proxy follows that decision. For PostgreSQL use a Patroni /primary check with verified TLS, rather than a mere open TCP port. PgBouncer needs an external failover/target-update mechanism and a tested server-connection refresh procedure. MySQL proxy routing must likewise follow the selected single-writer policy.

This centralizes endpoint management. Redundant proxies add resource and operational costs; test that old direct-to-database writers cannot bypass the policy. A stable endpoint does not transfer open TCP sessions or in-flight transactions; reconnect, identify uncertain commits and use idempotent retries or reconciliation.

2. Floating IP / VRRP with keepalived

A floating IP can be moved to the selected primary or active proxy when the provider network supports the chosen mechanism. keepalived/VRRP requires suitable network, ARP and anti-spoof behavior; some platforms require a provider API instead. Couple IP movement to safe promotion and fencing: the address does not inherently know which database is primary.

Advantages:

• Very simple for applications: they see only one IP.
• Can be combined with proxies (VIP pointing at the active proxy instead of the database).

Disadvantages:

• Requires L2/L3 network support for VRRP, which every VPS platform implements differently.
• Failover is at the IP level; you still need application-level reconnection logic and safe promotion steps to avoid split-brain.

3. DNS-Based Failover

Another approach is to have a DNS name (e.g. db.example.com) that points to the current primary. When failover happens, automation updates the DNS record.

Pros:

• Simpler from the network perspective (no VRRP).
• Works well for multi-region or multi-data-center replication where IP-level failover is harder.

Cons:

• TTL and caching mean failover is not instant.
• Clients must honor DNS TTLs and reconnect properly.

Changing DNS does not move existing connections. Driver caches and pool reuse can extend recovery beyond the authoritative TTL; verify the actual client resolver and retry behavior. Repeating an operation whose commit result is unknown can duplicate business effects.

For high-end architectures, combining DNS geo‑routing with multi-region replication is powerful; our article How Anycast DNS and automatic failover keep your site up explains how DNS‑level strategies complement database HA.

4. Avoiding Split-Brain

Split-brain happens when two nodes both believe they are primary and accept writes independently. Merging that data is painful or impossible.

To avoid it:

• Use the chosen controller’s tested membership/lease and candidate eligibility rules. etcd/Orchestrator controller quorum is distinct from data replication acknowledgements.
• Fence the old writer before advertising the new one. Block privileged jobs and direct clients as well as proxy traffic; database read-only settings alone are not external fencing.
• Keep the recovered old primary isolated. In MySQL compare GTID history and divergent writes, then reconcile or rebuild from a verified seed; do not automatically merge two writable histories.
• For PostgreSQL pg_rewind, the target must be stopped and have pre-existing checksums or wal_log_hints, plus full_page_writes and the required WAL history. Preserve the old data and verify standby configuration after rewind. A failed rewind can leave the target unusable; use a new base backup when prerequisites or recovery fail.

Operational Best Practices: Backups, Monitoring and DR

Replication Is Not a Backup

Replication will happily replicate mistakes: dropped tables, bad migrations, or mass updates. You still need proper backups with versioning and point‑in‑time recovery.

We strongly recommend:

• Regular logical or physical backups from a replica (to lighten load on the primary).
• Off‑site / off‑VPS backups, ideally to S3‑compatible object storage.
• Periodic restore drills, not just “backup completed” checks.

Two resources that pair perfectly with this article:

• Application‑consistent hot backups with LVM snapshots for MySQL and PostgreSQL — for low‑impact, consistent backups.
• PostgreSQL backups and PITR with pgBackRest — if you are serious about Postgres.

Monitoring Replication Lag and Health

Good HA is impossible without good observability. At minimum, monitor:

• MySQL: Seconds_Behind_Source with its limitations, receiver/applier state, all worker errors and GTID progress.
• PostgreSQL: pg_stat_replication state/sync_state and write/flush/replay LSN, standby receive/replay state, retained WAL and slot availability.
• Archive failures, off-host backups and successful restores; WAL/binlog disk capacity and disk latency.
• DCS quorum/leader lease, fencing, proxy role checks and actual application write/read/reconnect probes.
• CPU, RAM, network loss and connection-pool pressure.

You can build this with Prometheus + exporters + Grafana on a small VPS. We have a friendly starting point in VPS monitoring and alerts with Prometheus, Grafana and Uptime Kuma. Alert not only on hard failures, but on trend warnings like increasing replication lag or WAL archive backlog.

Disaster Recovery and Failover Drills

Validate in an isolated environment first; production drills need an approved maintenance/recovery plan. Re-run relevant cases when versions, capacity or network assumptions change.

• Exercise primary loss, network partitions, DCS majority loss, lagging replicas and full disks; stopping a service alone does not model a partition.
• Prove that the old primary cannot write and only one new writer is reachable; inspect controller and proxy behavior.
• Track unique transaction IDs and acknowledged commits to measure actual RPO; measure RTO through successful application operations, including pool reconnection and uncertain-commit handling.
• Restore a backup and reintegrate the old primary as a safe replica, checking schemas, routines, accounts and business invariants at corresponding GTID/LSN points.

pt-table-checksum writes checksum work on the source and has privilege, statement-logging and topology restrictions; validate support for the selected MySQL 8.4/tool version before using it on a ROW topology. PostgreSQL pg_checksums inspects physical page checksums on a cleanly stopped cluster; it does not compare logical table contents between primary and standby.

Our general DR planning article How I write a no‑drama DR plan is a good companion read if you want to formalize this process.

When Replication on a VPS Is (and Is Not) the Right Choice

When HA Replication Makes Sense

Reasons to evaluate HA replication include:

• Your application generates direct revenue (e‑commerce, SaaS, booking platforms).
• Downtime during working hours is not acceptable for customers or internal teams.
• You already invested in caching and query optimization but still hit single-node limits.
• You need to reduce maintenance downtime, subject to database version compatibility and a tested upgrade path.

Select the number of database, DCS, proxy and application nodes from the actual failure and durability targets. Two database nodes plus one helper can illustrate the design but do not guarantee end-to-end HA. When evaluating DCHost VPS, dedicated or colocation options, verify measured I/O, independent failure domains, private networking, fencing/VIP support and operational responsibilities for the chosen service.

Related guide: dedicated server.

When a Single VPS + Strong Backups Is Enough

For small blogs, internal tools with low business impact, or early MVPs, HA may be overkill. In those cases:

• Run MySQL or PostgreSQL on a single, well‑sized VPS.
• Invest in good backups, automated restore tests, and careful schema migration practices.
• Plan an upgrade path: know when to add a read replica or separate database and application servers.

The key is to design with future HA in mind: use connection strings, environment variables and configuration management that allow you to introduce proxies and replicas later without rewriting everything.

Wrapping Up: A Practical Path to HA MySQL/PostgreSQL on VPS

High availability on a VPS does not mean you need an enterprise-scale cluster from day one. It means being honest about your RPO/RTO needs, designing a simple but solid topology, and gradually adding pieces: replication, a proxy, backup automation, monitoring, and tested failover procedures.

For MySQL, validate the GTID seed, TLS channel, durability settings and manual recovery before adding automatic promotion. For PostgreSQL, validate streaming and recovery first, then plan a Patroni-managed lifecycle with genuine DCS quorum. In both cases test fencing, routing, old-primary return and application transaction recovery against explicit RPO/RTO targets.

Map data volume, recovery targets, off-host backups and independent failure domains to the actual DCHost infrastructure service you select. Add replicas, proxies and automation when measured needs and tested recovery behavior justify them.

Primary Technical Sources

• MySQL 8.4: mysqldump
• MySQL 8.4: encrypted replication
• MySQL 8.4: semisynchronous replication
• MySQL Shell 8.4: InnoDB Cluster
• PostgreSQL 17: pg_basebackup
• PostgreSQL 17: synchronous_commit
• Patroni: replication modes
• Patroni: configuration
• etcd: quorum and failure tolerance
• PgBouncer: failover