{"id":2797,"date":"2024-05-12T21:10:09","date_gmt":"2025-02-23T08:57:12","guid":{"rendered":""},"modified":"2025-02-02T00:46:19","modified_gmt":"2025-02-01T23:46:19","slug":"how-to-set-up-multi-master-postgresql-replication-on-ubuntu-22-04","status":"publish","type":"post","link":"https:\/\/netcloud24.com\/knowledgebase\/how-to-set-up-multi-master-postgresql-replication-on-ubuntu-22-04\/","title":{"rendered":"How to Set Up Multi-Master PostgreSQL Replication on Ubuntu 22.04"},"content":{"rendered":"<p>\u00a0<\/p>\n<\/p>\n<header>\n<h1>\u00a0<\/h1>\n<\/header>\n<article>\n<section>\n<h2>Introduction<\/h2>\n<p>Multi-master replication in PostgreSQL allows for high availability and redundancy by enabling multiple database servers to accept write operations simultaneously. This guide will walk you through the steps to set up multi-master PostgreSQL replication on Ubuntu 22.04, which is especially effective when utilizing a  for your database management.<\/p>\n<\/section>\n<section>\n<h2>Prerequisites<\/h2>\n<ul>\n<li>Two or more Ubuntu 22.04 servers with root access<\/li>\n<li>Basic knowledge of PostgreSQL and Linux commands<\/li>\n<li>PostgreSQL installed on all nodes<\/li>\n<\/ul>\n<\/section>\n<section>\n<h2>Step 1: Install PostgreSQL<\/h2>\n<p>On each server, install PostgreSQL using the following commands:<\/p>\n<pre><code>sudo apt update\r\nsudo apt install postgresql postgresql-contrib -y<\/code><\/pre>\n<\/section>\n<section>\n<h2>Step 2: Configure PostgreSQL for Replication<\/h2>\n<p>Open the PostgreSQL configuration file on each server:<\/p>\n<pre><code>sudo nano \/etc\/postgresql\/14\/main\/postgresql.conf<\/code><\/pre>\n<p>Make the following changes:<\/p>\n<pre><code>listen_addresses = '*'\r\nwal_level = logical\r\nmax_wal_senders = 5\r\nmax_replication_slots = 5\r\n<\/code><\/pre>\n<p>Save the file and exit.<\/p>\n<\/section>\n<section>\n<h2>Step 3: Update pg_hba.conf<\/h2>\n<p>Edit the <code>pg_hba.conf<\/code> file to allow replication connections:<\/p>\n<pre><code>sudo nano \/etc\/postgresql\/14\/main\/pg_hba.conf<\/code><\/pre>\n<p>Add the following lines, replacing <code>node_ip<\/code> with the IP address of the other server:<\/p>\n<pre><code>host    replication     all             node_ip\/32            md5<\/code><\/pre>\n<p>Repeat this step for each server to allow connections from each other.<\/p>\n<\/section>\n<section>\n<h2>Step 4: Create Replication User<\/h2>\n<p>Log into PostgreSQL on one of the servers and create a replication user:<\/p>\n<pre><code>sudo -u postgres psql<\/code><\/pre>\n<pre><code>CREATE ROLE replicator WITH REPLICATION PASSWORD 'your_password' LOGIN;<\/code><\/pre>\n<p>Exit the PostgreSQL prompt:<\/p>\n<pre><code>EXIT;<\/code><\/pre>\n<\/section>\n<section>\n<h2>Step 5: Set Up Logical Replication<\/h2>\n<p>On each node, run the following commands to create a publication and subscription:<\/p>\n<pre><code>sudo -u postgres psql\r\nCREATE PUBLICATION my_pub FOR ALL TABLES;\r\nCREATE SUBSCRIPTION my_sub CONNECTION 'host=node_ip dbname=your_db user=replicator password=your_password' PUBLICATION my_pub;<\/code><\/pre>\n<p>Repeat this for each server, adjusting the connection string as necessary.<\/p>\n<\/section>\n<section>\n<h2>Step 6: Start PostgreSQL Service<\/h2>\n<p>Start the PostgreSQL service on each node:<\/p>\n<pre><code>sudo systemctl restart postgresql<\/code><\/pre>\n<\/section>\n<section>\n<h2>Step 7: Verify Replication Setup<\/h2>\n<p>Log into PostgreSQL and check the replication status:<\/p>\n<pre><code>sudo -u postgres psql\r\nSELECT * FROM pg_stat_replication;<\/code><\/pre>\n<p>This should show the status of connected replication clients.<\/p>\n<\/section>\n<section>\n<h2>Step 8: Configure Firewall<\/h2>\n<p>Ensure that your firewall allows traffic on PostgreSQL&#8217;s default port (5432):<\/p>\n<pre><code>sudo ufw allow 5432\/tcp<\/code><\/pre>\n<\/section>\n<section>\n<h2>Step 9: Conclusion<\/h2>\n<p>You have successfully set up multi-master PostgreSQL replication on Ubuntu 22.04, providing a robust solution for high availability. This configuration can be particularly beneficial when deployed on a . For additional options, explore various  solutions, including <a href=\"https:\/\/ie.netcloud24.com\">Windows VPSVirtual Private Server Hosting<\/a> and <a href=\"https:\/\/ie.netcloud24.com\">Windows VPS Hosting UK<\/a> for optimal performance.<\/p>\n<\/section>\n<\/article>\n<footer>\n<p>\u00a9 2024 Multi-Master PostgreSQL Replication Tutorial. All rights reserved.<\/p>\n<\/footer>\n<div class=\"post-author-box\" style=\"border-top:1px solid #ddd;margin-top:20px;padding-top:15px;\">\n<p><strong>Author:<\/strong> \u0141ukasz Bodziony<\/p>\n<p><strong>Website:<\/strong> <a href=\"https:\/\/ca.netcloud24.com\" target=\"_blank\" rel=\"dofollow\">Windows VPS<\/a><\/p>\n<p><em>\u0141ukasz Bodziony is the CEO and founder of <a href=\"https:\/\/netcloud24.com\" target=\"_blank\" rel=\"dofollow\">NETCLOUD24<\/a>, a global VPS hosting brand proudly originating from Poland. With extensive experience in cloud computing, virtualization, and server management, he delivers high-performance <strong>Windows VPS<\/strong> and <strong>Remote Desktop Services (RDS)<\/strong> solutions to clients across Europe, North America, and beyond.<\/em><\/p>\n<p><em>His expertise covers a wide range of technologies, including <strong>Microsoft Azure<\/strong>, <strong>Proxmox VE<\/strong>, <strong>Amazon Web Services (AWS)<\/strong>, and numerous other virtualization and cloud platforms.<\/em><\/p>\n<p><em>Beyond running his hosting business, \u0141ukasz also provides <strong>professional paid server configuration and optimization services<\/strong> for companies and individuals. Outside of work, he is dedicated to caring for his children and building a secure future for them.<\/em><\/p>\n<p><em>If you are interested in working with him or need expert assistance with your hosting, cloud environment, or server setup, feel free to reach out via <a href=\"https:\/\/ca.netcloud24.com\" target=\"_blank\" rel=\"dofollow\">Windows VPS<\/a>.<\/em><\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>\u00a0 \u00a0 Introduction Multi-master replication in PostgreSQL allows for high availability and redundancy by enabling multiple database servers to accept write operations simultaneously. This guide will walk you\u2026<\/p>\n","protected":false},"author":1,"featured_media":3421,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_seopress_robots_primary_cat":"","_seopress_titles_title":"","_seopress_titles_desc":"","_seopress_robots_index":"","footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[],"tags":[14,12,11,23,20,21,22,17,7,8,6,10,18,19,15,24,16,5,13,9],"class_list":["post-2797","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","tag-cheapvps","tag-cloudvps","tag-hostingvps","tag-rds","tag-rdscal","tag-remotedesktop","tag-remotedesktopvps","tag-servervps","tag-ukvps","tag-virtualserver","tag-vpshosting","tag-vpsserver","tag-vpssolutions","tag-vpswindows","tag-vpswithwindows","tag-windowsrds","tag-windowsserver","tag-windowsvps","tag-windowsvpshosting","tag-windowsvpsuk"],"jetpack_publicize_connections":[],"_links":{"self":[{"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/posts\/2797","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/comments?post=2797"}],"version-history":[{"count":0,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/posts\/2797\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/media\/3421"}],"wp:attachment":[{"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/media?parent=2797"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/categories?post=2797"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/tags?post=2797"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}