{"id":119985,"date":"2026-03-21T15:41:04","date_gmt":"2026-03-21T12:41:04","guid":{"rendered":"https:\/\/computingforgeeks.com\/?p=119985"},"modified":"2026-03-21T15:41:05","modified_gmt":"2026-03-21T12:41:05","slug":"postgresql-replication-rocky-almalinux","status":"publish","type":"post","link":"https:\/\/computingforgeeks.com\/postgresql-replication-rocky-almalinux\/","title":{"rendered":"Configure PostgreSQL 17 Streaming Replication on Rocky Linux 10 \/ AlmaLinux 10"},"content":{"rendered":"\n<p>PostgreSQL streaming replication continuously ships Write-Ahead Log (WAL) records from a primary server to one or more standby servers. The standby replays these records in near real-time, providing high availability and read scaling. If the primary fails, a standby can be promoted to take over.<\/p>\n\n\n\n<p>This guide covers setting up PostgreSQL 17 streaming replication on Rocky Linux 10 \/ AlmaLinux 10 with one primary and one standby server. For the official documentation, see the <a href=\"https:\/\/www.postgresql.org\/docs\/17\/warm-standby.html\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL replication docs<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Prerequisites<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>2 servers running Rocky Linux 10 or AlmaLinux 10<\/li>\n\n<li>Primary server: 10.0.1.10<\/li>\n\n<li>Standby server: 10.0.1.11<\/li>\n\n<li>Root or sudo access on both servers<\/li>\n\n<li>Port 5432\/tcp open between both servers<\/li>\n\n<li><a href=\"https:\/\/computingforgeeks.com\/install-postgresql-rocky-almalinux\/\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL 17 installed<\/a> on both servers<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Step 1: Install PostgreSQL 17 on Both Servers<\/h2>\n\n\n\n<p>Run these commands on both the primary and standby servers. Install the PGDG repository and PostgreSQL 17.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo dnf install -y https:\/\/download.postgresql.org\/pub\/repos\/yum\/reporpms\/EL-10-x86_64\/pgdg-redhat-repo-latest.noarch.rpm\nsudo dnf install -y postgresql17-server postgresql17<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 2: Initialize and Configure the Primary Server<\/h2>\n\n\n\n<p>On the <strong>primary server<\/strong> (10.0.1.10), initialize the database cluster.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo \/usr\/pgsql-17\/bin\/postgresql-17-setup initdb<\/code><\/pre>\n\n\n\n<p>Start and enable PostgreSQL.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl enable --now postgresql-17<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Create a Replication User<\/h3>\n\n\n\n<p>Create a dedicated user for replication with the <code>REPLICATION<\/code> privilege.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql<\/code><\/pre>\n\n\n\n<p>Run the following SQL:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'StrongReplPass2026';\n\\q<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Configure postgresql.conf<\/h3>\n\n\n\n<p>Edit the main configuration file.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vi \/var\/lib\/pgsql\/17\/data\/postgresql.conf<\/code><\/pre>\n\n\n\n<p>Set these replication parameters:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># Listen on all interfaces\nlisten_addresses = '*'\n\n# WAL settings for replication\nwal_level = replica\nmax_wal_senders = 5\nwal_keep_size = 512MB\n\n# Optional: synchronous replication (uncomment for sync mode)\n# synchronous_standby_names = 'standby1'<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Configure pg_hba.conf<\/h3>\n\n\n\n<p>Allow the standby server to connect for replication.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vi \/var\/lib\/pgsql\/17\/data\/pg_hba.conf<\/code><\/pre>\n\n\n\n<p>Add this line at the end:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># Replication connection from standby\nhost    replication     replicator      10.0.1.11\/32        scram-sha-256<\/code><\/pre>\n\n\n\n<p>Restart PostgreSQL to apply the changes.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl restart postgresql-17<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3: Configure Firewall on Both Servers<\/h2>\n\n\n\n<p>Open port 5432\/tcp on both servers to allow PostgreSQL traffic.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo firewall-cmd --permanent --add-port=5432\/tcp\nsudo firewall-cmd --reload<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 4: Set Up the Standby Server<\/h2>\n\n\n\n<p>On the <strong>standby server<\/strong> (10.0.1.11), stop PostgreSQL if it&#8217;s running and remove any existing data directory.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl stop postgresql-17\nsudo rm -rf \/var\/lib\/pgsql\/17\/data\/*<\/code><\/pre>\n\n\n\n<p>Take a base backup from the primary server using <code>pg_basebackup<\/code>. This copies the entire database cluster and sets up the standby configuration automatically.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres pg_basebackup -h 10.0.1.10 -U replicator -D \/var\/lib\/pgsql\/17\/data -Fp -Xs -P -R<\/code><\/pre>\n\n\n\n<p>The flags:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>-h 10.0.1.10<\/code> &#8211; primary server address<\/li>\n\n<li><code>-U replicator<\/code> &#8211; replication user<\/li>\n\n<li><code>-D \/var\/lib\/pgsql\/17\/data<\/code> &#8211; target data directory<\/li>\n\n<li><code>-Fp<\/code> &#8211; plain format (not tar)<\/li>\n\n<li><code>-Xs<\/code> &#8211; stream WAL during backup<\/li>\n\n<li><code>-P<\/code> &#8211; show progress<\/li>\n\n<li><code>-R<\/code> &#8211; create standby.signal and write connection info to postgresql.auto.conf<\/li>\n<\/ul>\n\n\n\n<p>Enter the replication password when prompted. The <code>-R<\/code> flag is the key &#8211; it automatically creates the <code>standby.signal<\/code> file and adds the <code>primary_conninfo<\/code> to <code>postgresql.auto.conf<\/code>.<\/p>\n\n\n\n<p>Verify the standby configuration was created.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ cat \/var\/lib\/pgsql\/17\/data\/postgresql.auto.conf\nprimary_conninfo = 'user=replicator password=StrongReplPass2026 channel_binding=prefer host=10.0.1.10 port=5432 sslmode=prefer'\n\n$ ls \/var\/lib\/pgsql\/17\/data\/standby.signal\n\/var\/lib\/pgsql\/17\/data\/standby.signal<\/code><\/pre>\n\n\n\n<p>Start PostgreSQL on the standby.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl enable --now postgresql-17<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 5: Verify Replication Status<\/h2>\n\n\n\n<p>On the <strong>primary server<\/strong>, check the replication status.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ sudo -u postgres psql -c \"SELECT client_addr, state, sent_lsn, replay_lsn FROM pg_stat_replication;\"\n client_addr |   state   |  sent_lsn  | replay_lsn\n-------------+-----------+------------+------------\n 10.0.1.11   | streaming | 0\/3000148  | 0\/3000148\n(1 row)<\/code><\/pre>\n\n\n\n<p>The <code>state<\/code> should show <code>streaming<\/code> and <code>sent_lsn<\/code> should match <code>replay_lsn<\/code> (meaning the standby is caught up).<\/p>\n\n\n\n<p>On the <strong>standby server<\/strong>, confirm it&#8217;s in recovery mode.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ sudo -u postgres psql -c \"SELECT pg_is_in_recovery();\"\n pg_is_in_recovery\n--------------------\n t\n(1 row)<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 6: Test Replication<\/h2>\n\n\n\n<p>On the <strong>primary<\/strong>, create a test database and table.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql -c \"CREATE DATABASE testdb;\"\nsudo -u postgres psql -d testdb -c \"CREATE TABLE test_repl (id serial PRIMARY KEY, data text); INSERT INTO test_repl (data) VALUES ('replication works');\"<\/code><\/pre>\n\n\n\n<p>On the <strong>standby<\/strong>, verify the data appears (standby is read-only).<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ sudo -u postgres psql -d testdb -c \"SELECT * FROM test_repl;\"\n id |       data\n----+-------------------\n  1 | replication works\n(1 row)<\/code><\/pre>\n\n\n\n<p>Clean up the test database on the primary.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql -c \"DROP DATABASE testdb;\"<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Promoting a Standby to Primary<\/h2>\n\n\n\n<p>If the primary fails, promote the standby to become the new primary.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres pg_ctl promote -D \/var\/lib\/pgsql\/17\/data<\/code><\/pre>\n\n\n\n<p>Or use SQL on the standby.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql -c \"SELECT pg_promote();\"<\/code><\/pre>\n\n\n\n<p>After promotion, the standby exits recovery mode and accepts writes. The <code>standby.signal<\/code> file is automatically removed.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>PostgreSQL 17 streaming replication is running between the primary and standby on Rocky Linux 10 \/ AlmaLinux 10. For automated failover in production, consider tools like Patroni or repmgr. Monitor replication lag with <a href=\"https:\/\/computingforgeeks.com\/monitoring-mysql-mongodb-prometheus-grafana\/\" target=\"_blank\" rel=\"noreferrer noopener\">Prometheus and Grafana<\/a> using the postgres_exporter, and manage your databases with <a href=\"https:\/\/computingforgeeks.com\/install-pgadmin4-ubuntu\/\" target=\"_blank\" rel=\"noreferrer noopener\">pgAdmin<\/a>.<\/p>\n\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL streaming replication continuously ships Write-Ahead Log (WAL) records from a primary server to one or more standby servers. The standby replays these records in near real-time, providing high availability and read scaling. If the primary fails, a standby can be promoted to take over. This guide covers setting up PostgreSQL 17 streaming replication on &#8230; <a title=\"Configure PostgreSQL 17 Streaming Replication on Rocky Linux 10 \/ AlmaLinux 10\" class=\"read-more\" href=\"https:\/\/computingforgeeks.com\/postgresql-replication-rocky-almalinux\/\" aria-label=\"Read more about Configure PostgreSQL 17 Streaming Replication on Rocky Linux 10 \/ AlmaLinux 10\">Read more<\/a><\/p>\n","protected":false},"author":3,"featured_media":4457,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36301,461,299,50,35910],"tags":[37713,37715,37714],"class_list":["post-119985","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-almalinux","category-databases","category-how-to","category-linux-tutorials","category-rocky-linux","tag-postgresql-replication","tag-postgresql-replication-on-almalinux-8","tag-postgresql-replication-on-rocky-linux-8"],"_links":{"self":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/119985","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/comments?post=119985"}],"version-history":[{"count":1,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/119985\/revisions"}],"predecessor-version":[{"id":163083,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/119985\/revisions\/163083"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/media\/4457"}],"wp:attachment":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/media?parent=119985"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/categories?post=119985"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/tags?post=119985"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}