{"id":3565,"date":"2025-12-28T13:09:58","date_gmt":"2025-12-28T10:09:58","guid":{"rendered":"https:\/\/www.dchost.com\/blog\/mysql-and-postgresql-replication-on-vps-high-availability-and-automatic-failover\/"},"modified":"2025-12-28T13:09:58","modified_gmt":"2025-12-28T10:09:58","slug":"mysql-and-postgresql-replication-on-vps-high-availability-and-automatic-failover","status":"publish","type":"post","link":"https:\/\/www.dchost.com\/blog\/en\/mysql-and-postgresql-replication-on-vps-high-availability-and-automatic-failover\/","title":{"rendered":"MySQL and PostgreSQL Replication on VPS: High Availability and Automatic Failover"},"content":{"rendered":"<div class=\"dchost-blog-content-wrapper\"><p>Running MySQL or PostgreSQL on a single <a href=\"https:\/\/www.dchost.com\/vps\">VPS<\/a> 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, using patterns we also apply on dchost.com infrastructure. 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.<\/p>\n<div id=\"toc_container\" class=\"toc_transparent no_bullets\"><p class=\"toc_title\">\u0130&ccedil;indekiler<\/p><ul class=\"toc_list\"><li><a href=\"#Why_Database_Replication_and_HA_Matter_on_a_VPS\"><span class=\"toc_number toc_depth_1\">1<\/span> Why Database Replication and HA Matter on a VPS<\/a><\/li><li><a href=\"#Core_Concepts_Replication_Failover_and_Topologies\"><span class=\"toc_number toc_depth_1\">2<\/span> Core Concepts: Replication, Failover and Topologies<\/a><ul><li><a href=\"#Primary_Replica_Master_Slave_Basics\"><span class=\"toc_number toc_depth_2\">2.1<\/span> Primary \/ Replica (Master \/ Slave) Basics<\/a><\/li><li><a href=\"#Synchronous_vs_Asynchronous_Replication\"><span class=\"toc_number toc_depth_2\">2.2<\/span> Synchronous vs Asynchronous Replication<\/a><\/li><li><a href=\"#Typical_HA_Topologies_on_VPS\"><span class=\"toc_number toc_depth_2\">2.3<\/span> Typical HA Topologies on VPS<\/a><\/li><\/ul><\/li><li><a href=\"#Choosing_the_Right_VPS_Architecture_for_HA\"><span class=\"toc_number toc_depth_1\">3<\/span> Choosing the Right VPS Architecture for HA<\/a><ul><li><a href=\"#How_Many_VPS_Servers_Do_You_Really_Need\"><span class=\"toc_number toc_depth_2\">3.1<\/span> How Many VPS Servers Do You Really Need?<\/a><\/li><li><a href=\"#Single_Region_vs_Multi-Region_Replication\"><span class=\"toc_number toc_depth_2\">3.2<\/span> Single Region vs Multi-Region Replication<\/a><\/li><li><a href=\"#Network_and_Storage_Considerations\"><span class=\"toc_number toc_depth_2\">3.3<\/span> Network and Storage Considerations<\/a><\/li><\/ul><\/li><li><a href=\"#MySQL_Replication_on_VPS_Practical_Setup_and_HA_Patterns\"><span class=\"toc_number toc_depth_1\">4<\/span> MySQL Replication on VPS: Practical Setup and HA Patterns<\/a><ul><li><a href=\"#1_Preparing_MySQL_for_Replication\"><span class=\"toc_number toc_depth_2\">4.1<\/span> 1. Preparing MySQL for Replication<\/a><\/li><li><a href=\"#2_Semi-Synchronous_Replication_Optional\"><span class=\"toc_number toc_depth_2\">4.2<\/span> 2. Semi-Synchronous Replication (Optional)<\/a><\/li><li><a href=\"#3_Handling_Failover_for_MySQL\"><span class=\"toc_number toc_depth_2\">4.3<\/span> 3. Handling Failover for MySQL<\/a><\/li><\/ul><\/li><li><a href=\"#PostgreSQL_Streaming_Replication_on_VPS\"><span class=\"toc_number toc_depth_1\">5<\/span> PostgreSQL Streaming Replication on VPS<\/a><ul><li><a href=\"#1_Preparing_PostgreSQL_for_Streaming_Replication\"><span class=\"toc_number toc_depth_2\">5.1<\/span> 1. Preparing PostgreSQL for Streaming Replication<\/a><\/li><li><a href=\"#2_Base_Backup_and_Standby_Configuration\"><span class=\"toc_number toc_depth_2\">5.2<\/span> 2. Base Backup and Standby Configuration<\/a><\/li><li><a href=\"#3_Synchronous_Replication_Optional\"><span class=\"toc_number toc_depth_2\">5.3<\/span> 3. Synchronous Replication (Optional)<\/a><\/li><li><a href=\"#4_Automatic_Failover_with_Patroni_or_repmgr\"><span class=\"toc_number toc_depth_2\">5.4<\/span> 4. Automatic Failover with Patroni or repmgr<\/a><\/li><\/ul><\/li><li><a href=\"#Automatic_Failover_Patterns_Proxy_VIP_and_DNS\"><span class=\"toc_number toc_depth_1\">6<\/span> Automatic Failover Patterns: Proxy, VIP and DNS<\/a><ul><li><a href=\"#1_Proxy-Led_HA_HAProxy_ProxySQL_PgBouncer\"><span class=\"toc_number toc_depth_2\">6.1<\/span> 1. Proxy-Led HA (HAProxy, ProxySQL, PgBouncer)<\/a><\/li><li><a href=\"#2_Floating_IP_VRRP_with_keepalived\"><span class=\"toc_number toc_depth_2\">6.2<\/span> 2. Floating IP \/ VRRP with keepalived<\/a><\/li><li><a href=\"#3_DNS-Based_Failover\"><span class=\"toc_number toc_depth_2\">6.3<\/span> 3. DNS-Based Failover<\/a><\/li><li><a href=\"#4_Avoiding_Split-Brain\"><span class=\"toc_number toc_depth_2\">6.4<\/span> 4. Avoiding Split-Brain<\/a><\/li><\/ul><\/li><li><a href=\"#Operational_Best_Practices_Backups_Monitoring_and_DR\"><span class=\"toc_number toc_depth_1\">7<\/span> Operational Best Practices: Backups, Monitoring and DR<\/a><ul><li><a href=\"#Replication_Is_Not_a_Backup\"><span class=\"toc_number toc_depth_2\">7.1<\/span> Replication Is Not a Backup<\/a><\/li><li><a href=\"#Monitoring_Replication_Lag_and_Health\"><span class=\"toc_number toc_depth_2\">7.2<\/span> Monitoring Replication Lag and Health<\/a><\/li><li><a href=\"#Disaster_Recovery_and_Failover_Drills\"><span class=\"toc_number toc_depth_2\">7.3<\/span> Disaster Recovery and Failover Drills<\/a><\/li><\/ul><\/li><li><a href=\"#When_Replication_on_a_VPS_Is_and_Is_Not_the_Right_Choice\"><span class=\"toc_number toc_depth_1\">8<\/span> When Replication on a VPS Is (and Is Not) the Right Choice<\/a><ul><li><a href=\"#When_HA_Replication_Makes_Sense\"><span class=\"toc_number toc_depth_2\">8.1<\/span> When HA Replication Makes Sense<\/a><\/li><li><a href=\"#When_a_Single_VPS_Strong_Backups_Is_Enough\"><span class=\"toc_number toc_depth_2\">8.2<\/span> When a Single VPS + Strong Backups Is Enough<\/a><\/li><\/ul><\/li><li><a href=\"#Wrapping_Up_A_Practical_Path_to_HA_MySQLPostgreSQL_on_VPS\"><span class=\"toc_number toc_depth_1\">9<\/span> Wrapping Up: A Practical Path to HA MySQL\/PostgreSQL on VPS<\/a><\/li><\/ul><\/div>\n<h2><span id=\"Why_Database_Replication_and_HA_Matter_on_a_VPS\">Why Database Replication and HA Matter on a VPS<\/span><\/h2>\n<p>On a VPS, CPU, RAM and NVMe storage are just the starting point. For any application beyond a hobby project, the real questions quickly become: how much downtime can we tolerate (RTO), and how much data can we afford to lose (RPO)? Replication and HA are your main tools to reduce both.<\/p>\n<p><strong>Replication<\/strong> is the process of continuously copying data changes from one database instance (primary) to one or more others (replicas). <strong>High availability<\/strong> adds orchestration around that replication: health checks, automatic promotion, and a way for applications to always connect to the right node.<\/p>\n<p>Without replication, your only protection is backups. Backups are essential but slow to restore, and they always imply some data loss. With replication, you can combine near real-time copies of your data with off-site backups to reach much tighter RPO\/RTO targets. We have seen this especially for WooCommerce, SaaS dashboards and internal ERP systems: once revenue or operations depend on the database, a single VPS is simply not enough.<\/p>\n<p>If you are still deciding whether to scale vertically or build HA, it is worth reading our article <a href=\"https:\/\/www.dchost.com\/blog\/en\/yuksek-erisilebilirlik-mi-guclu-tek-sunucu-mu\/\">High Availability vs one big server for e\u2011commerce and SaaS<\/a>. For most growing projects, a small HA setup on multiple VPSs becomes safer and often more cost-effective than one oversized machine.<\/p>\n<h2><span id=\"Core_Concepts_Replication_Failover_and_Topologies\">Core Concepts: Replication, Failover and Topologies<\/span><\/h2>\n<h3><span id=\"Primary_Replica_Master_Slave_Basics\">Primary \/ Replica (Master \/ Slave) Basics<\/span><\/h3>\n<p>Both MySQL and PostgreSQL follow the same high-level model:<\/p>\n<ul>\n<li><strong>Primary<\/strong>: accepts all writes (INSERT\/UPDATE\/DELETE, DDL). Clients may also read from it.<\/li>\n<li><strong>Replica<\/strong>: receives changes from the primary and replays them. Usually read-only, serving analytics or read-heavy traffic.<\/li>\n<\/ul>\n<p>In MySQL, classic replication is <strong>statement-based<\/strong> or <strong>row-based<\/strong> using <strong>binary logs (binlog)<\/strong>. Modern deployments typically use row-based binary logging with GTID (Global Transaction ID) for easier recovery and failover.<\/p>\n<p>In PostgreSQL, HA uses <strong>physical streaming replication<\/strong>: the primary writes WAL (Write-Ahead Log) segments, and replicas stream and replay them. You can also use <strong>logical replication<\/strong> for more advanced use cases (partial tables, cross-version migrations), but physical replication is the standard for HA.<\/p>\n<h3><span id=\"Synchronous_vs_Asynchronous_Replication\">Synchronous vs Asynchronous Replication<\/span><\/h3>\n<p>Replication timing directly affects your RPO:<\/p>\n<ul>\n<li><strong>Asynchronous<\/strong>: primary commits locally and returns success to the client; replicas catch up in the background. Fast, but a sudden primary failure might lose the last few transactions.<\/li>\n<li><strong>Synchronous<\/strong>: primary waits for at least one replica to confirm it has received (and sometimes flushed) the change before acknowledging the commit. Slower, but RPO can be close to zero.<\/li>\n<\/ul>\n<p>MySQL offers semi-synchronous replication plugins and group replication. PostgreSQL supports synchronous replication with <code>synchronous_commit<\/code> and <code>synchronous_standby_names<\/code>. We often recommend starting with asynchronous replication on VPS setups, and then selectively enabling synchronous replication for the most critical workloads once you understand the performance impact.<\/p>\n<h3><span id=\"Typical_HA_Topologies_on_VPS\">Typical HA Topologies on VPS<\/span><\/h3>\n<p>On 2\u20134 VPS servers, these topologies are common:<\/p>\n<ul>\n<li><strong>Single primary + one replica<\/strong>: minimal HA. You get fast failover (often manual), plus offloaded reads and safer backups from the replica.<\/li>\n<li><strong>Single primary + two replicas<\/strong>: better survivability (you can lose one node), room for analytics, and flexibility to do rolling OS\/database upgrades.<\/li>\n<li><strong>Primary\/replica with quorum and fencing<\/strong>: usually with a coordination layer (etcd\/Consul for PostgreSQL Patroni, or orchestrator-style tools for MySQL) to safely decide who becomes primary.<\/li>\n<\/ul>\n<p>MySQL also has <strong>Group Replication<\/strong> and MySQL InnoDB Cluster (multi-primary). PostgreSQL has Patroni or repmgr-based clusters. For many VPS-based workloads, a well-tuned primary + replica topology with a proxy in front is simpler to operate than a full multi-primary cluster. If you are curious about more advanced MySQL clustering, we share our experience in <a href=\"https:\/\/www.dchost.com\/blog\/en\/yedekten-oteyi-konusalim-mariadb-galera-cluster-ve-mysql-group-replication-ile-kesintisizlige-sicak-bir-yolculuk\/\">Beyond backups: choosing between MariaDB Galera Cluster and MySQL Group Replication for real HA<\/a>.<\/p>\n<h2><span id=\"Choosing_the_Right_VPS_Architecture_for_HA\">Choosing the Right VPS Architecture for HA<\/span><\/h2>\n<h3><span id=\"How_Many_VPS_Servers_Do_You_Really_Need\">How Many VPS Servers Do You Really Need?<\/span><\/h3>\n<p>At dchost.com we usually start the conversation around three roles:<\/p>\n<ul>\n<li><strong>Database nodes<\/strong>: 2\u20133 VPSs running MySQL or PostgreSQL.<\/li>\n<li><strong>Proxy\/load balancer<\/strong>: 1\u20132 VPSs running HAProxy\/ProxySQL\/PgBouncer, optionally with VRRP\/keepalived for a floating IP.<\/li>\n<li><strong>Application nodes<\/strong>: your web\/app servers, ideally separate from the database VPSs for cleaner scaling and isolation.<\/li>\n<\/ul>\n<p>If budgets are tight, you can start with <strong>2 database VPSs + 1 app\/proxy VPS<\/strong>, then grow towards fully separated roles as traffic increases. Our article <a href=\"https:\/\/www.dchost.com\/blog\/en\/veritabani-sunucusunu-uygulama-sunucusundan-ayirmak-ne-zaman-mantikli\/\">When to separate database and application servers for MySQL and PostgreSQL<\/a> goes deeper into this decision.<\/p>\n<h3><span id=\"Single_Region_vs_Multi-Region_Replication\">Single Region vs Multi-Region Replication<\/span><\/h3>\n<p>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.<\/p>\n<p>For that, we like patterns similar to those described in <a href=\"https:\/\/www.dchost.com\/blog\/en\/cok-bolgeli-mimariler-nasil-kurulur-dns-geo%e2%80%91routing-ve-veritabani-replikasyonu-ile-korkusuz-felaket-dayanikliligi\/\">multi\u2011region architectures with DNS geo\u2011routing and database replication<\/a>: keep a primary cluster in one region, stream replication to another region, and use DNS or traffic manager logic to fail over clients. On dchost.com, you can place VPSs in different locations and layer this on top of a proper backup and DR plan.<\/p>\n<h3><span id=\"Network_and_Storage_Considerations\">Network and Storage Considerations<\/span><\/h3>\n<p>Replication traffic is mostly sequential writes (WAL\/binlog streaming), but under heavy write load it can be significant. On a VPS, ensure:<\/p>\n<ul>\n<li>Your nodes share a <strong>low-latency network<\/strong> (same region, preferably same data center).<\/li>\n<li>You use <strong>NVMe SSD<\/strong> or at least high-quality SSD for the databases, as replication intensifies disk IO. Our <a href=\"https:\/\/www.dchost.com\/blog\/en\/nvme-ssd-sata-ssd-ve-hdd-karsilastirmasi-web-hosting-yedek-ve-arsiv-icin-dogru-disk-secimi\/\">NVMe SSD vs SATA SSD vs HDD comparison<\/a> explains why NVMe is usually the right choice for write-heavy databases.<\/li>\n<\/ul>\n<h2><span id=\"MySQL_Replication_on_VPS_Practical_Setup_and_HA_Patterns\">MySQL Replication on VPS: Practical Setup and HA Patterns<\/span><\/h2>\n<h3><span id=\"1_Preparing_MySQL_for_Replication\">1. Preparing MySQL for Replication<\/span><\/h3>\n<p>Assume two VPSs (primary and replica) running a recent MySQL 8.x or compatible server, on Linux (Ubuntu\/Debian\/AlmaLinux\/Rocky).<\/p>\n<ol>\n<li><strong>Configure unique server IDs<\/strong> in <code>my.cnf<\/code> on each node:<\/li>\n<\/ol>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">[mysqld]\nserver_id = 1           # primary\nlog_bin = mysql-bin\nbinlog_format = ROW\ngtid_mode = ON\nenforce_gtid_consistency = ON\n<\/code><\/pre>\n<p>On the replica, use <code>server_id = 2<\/code> and similar binlog settings (if you also want it to act as a source later).<\/p>\n<ol start=\"2\">\n<li><strong>Create a replication user<\/strong> on the primary:<\/li>\n<\/ol>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">CREATE USER 'repl'@'10.%' IDENTIFIED BY 'strong-password';\nGRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.%';\nFLUSH PRIVILEGES;\n<\/code><\/pre>\n<ol start=\"3\">\n<li><strong>Take a consistent backup<\/strong> of the primary and restore it on the replica.<\/li>\n<\/ol>\n<p>You can use <code>mysqldump --single-transaction<\/code>, physical backup tools, or an LVM snapshot. For a deeper dive into backup choices, see <a href=\"https:\/\/www.dchost.com\/blog\/en\/mysql-veritabani-yedekleme-stratejileri-mysqldump-percona-xtrabackup-ve-snapshot-nasil-secilir\/\">MySQL backup strategies: mysqldump, XtraBackup and snapshots<\/a>.<\/p>\n<ol start=\"4\">\n<li><strong>Point the replica at the primary<\/strong> using GTID:<\/li>\n<\/ol>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">CHANGE MASTER TO\n  MASTER_HOST = '10.0.0.1',\n  MASTER_USER = 'repl',\n  MASTER_PASSWORD = 'strong-password',\n  MASTER_AUTO_POSITION = 1;\nSTART SLAVE;\n<\/code><\/pre>\n<p>Check status with <code>SHOW SLAVE STATUSG<\/code> and ensure <code>Seconds_Behind_Master<\/code> stays low under normal load.<\/p>\n<h3><span id=\"2_Semi-Synchronous_Replication_Optional\">2. Semi-Synchronous Replication (Optional)<\/span><\/h3>\n<p>If you want stronger consistency, enable MySQL semi-synchronous replication:<\/p>\n<ul>\n<li>Install and enable the semi-sync plugin on both nodes.<\/li>\n<li>On the primary, set <code>rpl_semi_sync_master_enabled=ON<\/code>.<\/li>\n<li>On the replica, set <code>rpl_semi_sync_slave_enabled=ON<\/code>.<\/li>\n<\/ul>\n<p>This does not make replication fully synchronous, but it reduces the window of unreplicated transactions. Be sure to test the latency impact on your workload.<\/p>\n<h3><span id=\"3_Handling_Failover_for_MySQL\">3. Handling Failover for MySQL<\/span><\/h3>\n<p>Replication alone does not give you HA. You need a way to:<\/p>\n<ul>\n<li>Detect primary failure.<\/li>\n<li>Promote a replica to primary (<code>STOP SLAVE; RESET SLAVE ALL; SET GLOBAL read_only = OFF;<\/code> and adjust users\/GTID as needed).<\/li>\n<li>Redirect application connections to the new primary.<\/li>\n<\/ul>\n<p>Common patterns on VPS setups:<\/p>\n<ul>\n<li><strong>Application-level failover<\/strong>: apps know both nodes, try primary first, then replica. Simple but pushes complexity into every app.<\/li>\n<li><strong>HAProxy or ProxySQL in front<\/strong>: apps connect to a proxy, which knows which backend is primary and which are replicas.<\/li>\n<li><strong>Floating IP (VRRP\/keepalived)<\/strong>: a virtual IP always points to the current primary; proxies\/apps connect to that IP.<\/li>\n<\/ul>\n<p>For MySQL, <strong>ProxySQL<\/strong> is particularly powerful: it can route writes to the primary and reads to replicas, handle connection pooling, and integrate with MySQL monitoring. We shared a complete, real-world scenario in <a href=\"https:\/\/www.dchost.com\/blog\/en\/proxysql-ile-mysql-read-write-split-ve-baglanti-havuzu-woocommerce-laravel-icin-gercek-dunya-rehberi\/\">ProxySQL read\/write split and pooling for WooCommerce and Laravel<\/a>. The same patterns apply on a VPS-based HA setup.<\/p>\n<h2><span id=\"PostgreSQL_Streaming_Replication_on_VPS\">PostgreSQL Streaming Replication on VPS<\/span><\/h2>\n<h3><span id=\"1_Preparing_PostgreSQL_for_Streaming_Replication\">1. Preparing PostgreSQL for Streaming Replication<\/span><\/h3>\n<p>Assume two VPSs (primary and standby) running PostgreSQL 14+ on Linux.<\/p>\n<ol>\n<li><strong>Configure WAL and replication settings<\/strong> on the primary&#8217;s <code>postgresql.conf<\/code>:<\/li>\n<\/ol>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">wal_level = replica\nmax_wal_senders = 10\nmax_replication_slots = 10\narchive_mode = on\narchive_command = 'cp %p \/var\/lib\/postgresql\/wal_archive\/%f'\n<\/code><\/pre>\n<p>Adjust paths and numbers to your needs. Set <code>hot_standby = on<\/code> on the standby so it can accept read-only queries.<\/p>\n<ol start=\"2\">\n<li><strong>Allow replication connections<\/strong> in <code>pg_hba.conf<\/code> on the primary:<\/li>\n<\/ol>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">host replication replicator 10.0.0.0\/24 md5\n<\/code><\/pre>\n<ol start=\"3\">\n<li><strong>Create a replication role<\/strong>:<\/li>\n<\/ol>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong-password';\n<\/code><\/pre>\n<h3><span id=\"2_Base_Backup_and_Standby_Configuration\">2. Base Backup and Standby Configuration<\/span><\/h3>\n<p>On the standby:<\/p>\n<ol>\n<li>Stop PostgreSQL and clear the data directory.<\/li>\n<li>Run <code>pg_basebackup<\/code> from the primary:<\/li>\n<\/ol>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">pg_basebackup -h 10.0.0.1 -D \/var\/lib\/postgresql\/data \n  -U replicator -Fp -Xs -P -R\n<\/code><\/pre>\n<p>The <code>-R<\/code> flag creates a <code>standby.signal<\/code> (or <code>recovery.conf<\/code> on older versions) and a <code>primary_conninfo<\/code> entry that tells PostgreSQL how to connect back to the primary for streaming.<\/p>\n<p>After starting PostgreSQL on the standby, check <code>pg_stat_replication<\/code> on the primary to confirm replication is working.<\/p>\n<h3><span id=\"3_Synchronous_Replication_Optional\">3. Synchronous Replication (Optional)<\/span><\/h3>\n<p>To enable synchronous replication (for near-zero RPO), on the primary set:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">synchronous_commit = on\nsynchronous_standby_names = '1 (standby1)'\n<\/code><\/pre>\n<p>Replace <code>standby1<\/code> with the actual application_name of your standby (see <code>primary_conninfo<\/code>). PostgreSQL will now wait for that standby to confirm before completing commits. Test carefully; network issues can block writes if the synchronous standby is unavailable.<\/p>\n<h3><span id=\"4_Automatic_Failover_with_Patroni_or_repmgr\">4. Automatic Failover with Patroni or repmgr<\/span><\/h3>\n<p>Manual failover is fine for internal apps, but customer-facing workloads usually need automatic promotion. For PostgreSQL on VPS, two popular approaches are:<\/p>\n<ul>\n<li><strong>Patroni<\/strong>: uses a distributed configuration store (etcd, Consul, or even PostgreSQL itself) to manage who is primary, handle automatic failover, and keep configuration in sync.<\/li>\n<li><strong>repmgr<\/strong>: a lighter-weight tool that manages replication, monitoring and failover, often combined with external tools for VIP\/DNS changes.<\/li>\n<\/ul>\n<p>Both integrate well with PgBouncer or HAProxy in front of the cluster, so applications simply connect to a stable endpoint. On dchost.com, we see many teams pair Patroni with PgBouncer on a small VPS, then direct app traffic to PgBouncer instead of connecting to PostgreSQL nodes directly.<\/p>\n<p>If you are planning a serious PostgreSQL deployment on a VPS, also look at our performance-focused article <a href=\"https:\/\/www.dchost.com\/blog\/en\/vpste-postgresqli-ucurmak-shared_buffers-work_mem-wal-ve-pgbounceri-ne-zaman-nasil-ayarlariz\/\">The friendly VPS playbook for PostgreSQL performance<\/a> and our autovacuum tuning guide. Poorly tuned vacuum and WAL settings can silently damage replication lag and failover times.<\/p>\n<h2><span id=\"Automatic_Failover_Patterns_Proxy_VIP_and_DNS\">Automatic Failover Patterns: Proxy, VIP and DNS<\/span><\/h2>\n<h3><span id=\"1_Proxy-Led_HA_HAProxy_ProxySQL_PgBouncer\">1. Proxy-Led HA (HAProxy, ProxySQL, PgBouncer)<\/span><\/h3>\n<p>In this model, your applications talk to one or two proxy endpoints:<\/p>\n<ul>\n<li>For MySQL: ProxySQL or HAProxy.<\/li>\n<li>For PostgreSQL: PgBouncer and\/or HAProxy.<\/li>\n<\/ul>\n<p>The proxy periodically checks backends, knows which one is primary, and routes traffic accordingly. During failover, your orchestration tool (Orchestrator\/Patroni\/repmgr\/custom scripts) promotes a replica; the proxy detects the change and updates its backend roles.<\/p>\n<p>This centralizes connection logic and lets you change the cluster topology without touching application code. The cost is an extra VPS (or two, if you want redundant proxies) but in practice this is one of the cleanest VPS HA architectures.<\/p>\n<h3><span id=\"2_Floating_IP_VRRP_with_keepalived\">2. Floating IP \/ VRRP with keepalived<\/span><\/h3>\n<p>A floating IP (virtual IP) always points to the current primary. keepalived uses VRRP to move this IP between nodes based on health checks. Applications connect to that IP via standard MySQL\/PostgreSQL ports.<\/p>\n<p>Advantages:<\/p>\n<ul>\n<li>Very simple for applications: they see only one IP.<\/li>\n<li>Can be combined with proxies (VIP pointing at the active proxy instead of the database).<\/li>\n<\/ul>\n<p>Disadvantages:<\/p>\n<ul>\n<li>Requires L2\/L3 network support for VRRP, which every VPS platform implements differently.<\/li>\n<li>Failover is at the IP level; you still need application-level reconnection logic and safe promotion steps to avoid split-brain.<\/li>\n<\/ul>\n<h3><span id=\"3_DNS-Based_Failover\">3. DNS-Based Failover<\/span><\/h3>\n<p>Another approach is to have a DNS name (e.g. <code>db.example.com<\/code>) that points to the current primary. When failover happens, automation updates the DNS record.<\/p>\n<p>Pros:<\/p>\n<ul>\n<li>Simpler from the network perspective (no VRRP).<\/li>\n<li>Works well for <strong>multi-region<\/strong> or multi-data-center replication where IP-level failover is harder.<\/li>\n<\/ul>\n<p>Cons:<\/p>\n<ul>\n<li>TTL and caching mean failover is not instant.<\/li>\n<li>Clients must honor DNS TTLs and reconnect properly.<\/li>\n<\/ul>\n<p>For high-end architectures, combining DNS geo\u2011routing with multi-region replication is powerful; our article <a href=\"https:\/\/www.dchost.com\/blog\/en\/hic-kesilmeden-yayinda-kalmak-mumkun-mu-anycast-dns-ve-otomatik-failover-ile-nasil-saglanir\/\">How Anycast DNS and automatic failover keep your site up<\/a> explains how DNS\u2011level strategies complement database HA.<\/p>\n<h3><span id=\"4_Avoiding_Split-Brain\">4. Avoiding Split-Brain<\/span><\/h3>\n<p>Split-brain happens when two nodes both believe they are primary and accept writes independently. Merging that data is painful or impossible.<\/p>\n<p>To avoid it:<\/p>\n<ul>\n<li>Always use <strong>quorum-based decisions<\/strong> when promoting a new primary (e.g. Patroni with etcd, or orchestration tools that check majority).<\/li>\n<li>Ensure the old primary is <strong>fenced<\/strong> (shut down, or blocked from accepting connections) before advertising the new primary.<\/li>\n<li>Keep health checks conservative; treat network partitions as serious incidents, not something to automatically heal without checks.<\/li>\n<\/ul>\n<h2><span id=\"Operational_Best_Practices_Backups_Monitoring_and_DR\">Operational Best Practices: Backups, Monitoring and DR<\/span><\/h2>\n<h3><span id=\"Replication_Is_Not_a_Backup\">Replication Is Not a Backup<\/span><\/h3>\n<p>Replication will happily replicate <em>mistakes<\/em>: dropped tables, bad migrations, or mass updates. You still need proper backups with versioning and point\u2011in\u2011time recovery.<\/p>\n<p>We strongly recommend:<\/p>\n<ul>\n<li>Regular logical or physical backups from a replica (to lighten load on the primary).<\/li>\n<li>Off\u2011site \/ off\u2011VPS backups, ideally to S3\u2011compatible object storage.<\/li>\n<li>Periodic <strong>restore drills<\/strong>, not just \u201cbackup completed\u201d checks.<\/li>\n<\/ul>\n<p>Two resources that pair perfectly with this article:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.dchost.com\/blog\/en\/uygulama%e2%80%91tutarli-yedekler-nasil-alinir-lvm-snapshot-ve-fsfreeze-ile-mysql-postgresqli-usutmeden-dondurmak\/\">Application\u2011consistent hot backups with LVM snapshots for MySQL and PostgreSQL<\/a> \u2014 for low\u2011impact, consistent backups.<\/li>\n<li><a href=\"https:\/\/www.dchost.com\/blog\/en\/vps-uzerinde-postgresql-yedekleme-ve-pitr-pgbackrest-ile-wal-arsivleme-adim-adim\/\">PostgreSQL backups and PITR with pgBackRest<\/a> \u2014 if you are serious about Postgres.<\/li>\n<\/ul>\n<h3><span id=\"Monitoring_Replication_Lag_and_Health\">Monitoring Replication Lag and Health<\/span><\/h3>\n<p>Good HA is impossible without good observability. At minimum, monitor:<\/p>\n<ul>\n<li>Replication delay (Seconds_Behind_Master in MySQL, WAL replay lag in PostgreSQL).<\/li>\n<li>Replica health (IO\/SQL thread status in MySQL, <code>pg_stat_replication<\/code> in PostgreSQL).<\/li>\n<li>Disk space, IOPS, CPU, RAM and connection counts on each node.<\/li>\n<\/ul>\n<p>You can build this with Prometheus + exporters + Grafana on a small VPS. We have a friendly starting point in <a href=\"https:\/\/www.dchost.com\/blog\/en\/vps-izleme-ve-alarm-kurulumu-prometheus-grafana-ve-uptime-kuma-ile-baslangic\/\">VPS monitoring and alerts with Prometheus, Grafana and Uptime Kuma<\/a>. Alert not only on hard failures, but on <strong>trend warnings<\/strong> like increasing replication lag or WAL archive backlog.<\/p>\n<h3><span id=\"Disaster_Recovery_and_Failover_Drills\">Disaster Recovery and Failover Drills<\/span><\/h3>\n<p>HA that is never tested is just a diagram. At dchost.com, we encourage customers to schedule small \u201cgame days\u201d where they:<\/p>\n<ul>\n<li>Simulate primary failure (stop the service, block the IP, or power off the VPS in a controlled window).<\/li>\n<li>Observe how orchestration tools behave: who becomes primary, how proxies react.<\/li>\n<li>Measure RTO\/RPO against your targets.<\/li>\n<\/ul>\n<p>Our general DR planning article <a href=\"https:\/\/www.dchost.com\/blog\/en\/felaket-kurtarma-plani-nasil-yazilir-rto-rpoyu-kafada-netlestirip-yedek-testleri-ve-runbooklari-gercekten-calisir-hale-getirmek\/\">How I write a no\u2011drama DR plan<\/a> is a good companion read if you want to formalize this process.<\/p>\n<h2><span id=\"When_Replication_on_a_VPS_Is_and_Is_Not_the_Right_Choice\">When Replication on a VPS Is (and Is Not) the Right Choice<\/span><\/h2>\n<h3><span id=\"When_HA_Replication_Makes_Sense\">When HA Replication Makes Sense<\/span><\/h3>\n<p>On a VPS, database replication and automatic failover are worth the complexity when:<\/p>\n<ul>\n<li>Your application generates direct revenue (e\u2011commerce, SaaS, booking platforms).<\/li>\n<li>Downtime during working hours is not acceptable for customers or internal teams.<\/li>\n<li>You already invested in caching and query optimization but still hit single-node limits.<\/li>\n<li>You need maintenance windows for OS\/database upgrades without full outage.<\/li>\n<\/ul>\n<p>In those scenarios, starting with a <strong>2\u2011node cluster + proxy<\/strong> on dchost.com VPSs is often the right balance. As requirements grow, you can move database roles to dedicated NVMe VPSs or even <a href=\"https:\/\/www.dchost.com\/dedicated-server\">dedicated server<\/a>s and keep the same logical architecture.<\/p>\n<h3><span id=\"When_a_Single_VPS_Strong_Backups_Is_Enough\">When a Single VPS + Strong Backups Is Enough<\/span><\/h3>\n<p>For small blogs, internal tools with low business impact, or early MVPs, HA may be overkill. In those cases:<\/p>\n<ul>\n<li>Run MySQL or PostgreSQL on a single, well\u2011sized VPS.<\/li>\n<li>Invest in <strong>good backups<\/strong>, automated restore tests, and careful schema migration practices.<\/li>\n<li>Plan an upgrade path: know when to add a read replica or separate database and application servers.<\/li>\n<\/ul>\n<p>The key is to design with <em>future HA<\/em> in mind: use connection strings, environment variables and configuration management that allow you to introduce proxies and replicas later without rewriting everything.<\/p>\n<h2><span id=\"Wrapping_Up_A_Practical_Path_to_HA_MySQLPostgreSQL_on_VPS\">Wrapping Up: A Practical Path to HA MySQL\/PostgreSQL on VPS<\/span><\/h2>\n<p>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.<\/p>\n<p>For MySQL, start with GTID-based replication between two dchost.com VPSs, add ProxySQL or HAProxy in front, and practice manual promotion before introducing full automatic failover. For PostgreSQL, start with physical streaming replication, then evaluate Patroni or repmgr plus PgBouncer once you are comfortable with the basics. In both worlds, never forget that replication does not replace backups; pair your HA setup with robust, tested backup pipelines and, if your risk profile requires it, cross-region replicas as described in our multi\u2011region and Anycast DNS articles.<\/p>\n<p>If you are planning an HA rollout and you are not sure how to map these patterns onto dchost.com VPS, dedicated or colocation infrastructure, our team can help you choose the right mix of servers, storage and network features. The important part is to start with a design you can understand, operate and test\u2014not a fragile diagram that only looks good on slides. From there, scaling your MySQL or PostgreSQL clusters on VPS becomes a matter of iteration, not a leap of faith.<\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":3566,"comment_status":"","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[],"class_list":["post-3565","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-teknoloji"],"_links":{"self":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts\/3565","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/comments?post=3565"}],"version-history":[{"count":0,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts\/3565\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/media\/3566"}],"wp:attachment":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/media?parent=3565"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/categories?post=3565"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/tags?post=3565"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}