Skip to main content

PostgreSQL Hot Standby Replication

#Install postgres in the primary and standby server as usual

root# wget http://yum.pgrpms.org/reporpms/9.1/pgdg-centos91-9.1-4.noarch.rpm

#install the repo

root# rpm -i pgdg-centos91-9.1-4.noarch.rpm

#We now need to edit the CentOS-Base.repo to exclude postgre.Simply edit CentOS-Base.repo and add 'exclude=postgresql*'  to the [base] and [updates] sections:

root# cd /etc/yum.repos.d

root# vi CentOS-Base.repo

    [base]

    name=CentOS-$releasever - Base

    mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os

    #baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/

    gpgcheck=1

    gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6

    exclude=postgresql*

    #released updates

    [updates]

    name=CentOS-$releasever - Updates

    mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates

    #baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/

    gpgcheck=1

    gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6

    exclude=postgresql*

  

  

root# yum list postgres*

root# yum install postgresql91-libs

root# yum install postgresql91

root# yum install postgresql91-server

root# yum install postgresql91-contrib

root# yum install postgresql91-devel

root# yum install postgresql91-plpython

#create postgresql data directory

root# mkdir -p /pgdata/data/

root# chown -R postgres.postgres /pgdata/

#We now need to edit /etc/init.d/postgresql file.

root# vi /etc/init.d/postgresql-9.1

    PGDATA=/pgdata/data

    PGLOG=/pgdata/pgstartup.log

  

#initialize Postgre and start server  

root# chkconfig postgresql-9.1 on

root# service postgresql-9.1 initdb

     Initializing database:                                     [  OK  ]

root# service postgresql-9.1 start

     Starting postgresql-9.1 service:                           [  OK  ]

-----#Hot_standby server

root# mkdir /pgdata/pg_arc/

root# chown postgres.postgres -R /pgdata/pg_arc/

service postgresql-9.1 stop

# install scp

root# yum provides */scp

root# yum -y install openssh-clients

#SSH Tunnel Creation

#On the master server do the following:

// sestatus | grep -i mode

// cat /etc/selinux/config

// #and change the SELINUX line to SELINUX=disabled

// vi /etc/selinux/config

// echo 0 >/selinux/enforce

// sestatus | grep -i mode

//       Current mode:                   permissive

//      Mode from config file:          disabled

root# su - postgres

$ ssh-keygen -t rsa

$ cd /var/lib/pgsql/.ssh

//$cat id_rsa.pub>>authorized_keys

$ ssh-copy-id -i id_rsa.pub postgres@postgresql2.mydomain.com

$ ssh postgres@postgresql2.mydomain.com

//ssh slave and see if you are able to login without password. On slave server do steps like this.

#On master server, connect to postgresql server. Create replication user on database

root# su - postgres

$ psql postgres

    psql (9.1.8)

    Type "help" for help.

postgres=# CREATE USER myuser_replicator WITH SUPERUSER ENCRYPTED PASSWORD 'a12345';

postgres=# \q

root# service postgresql-9.1 start

#Editing pg_hba.conf

#on mastser Edit /pgdata/data/pg_hba.conf. Give permission to repuser user on standby machine (IP = (postgresql2.mydomain.com)) to connect to the master.

$ vim /pgdata/data/pg_hba.conf

    #replication

    host    replication     myuser_replicator        10.1.1.118/32          md5

# you will need to enable streaming replication and WAL logging for your database in

$ vim pgdata/data/postgresql.conf

    listen_addresses = '*'

    shared_buffers = 2048MB   # ( ~= RAM/4)

    maintenance_work_mem = 256MB # (as much as you need)

    # Bu miktarda ihtiyaca göre artırılabilir.

    effective_cache_size = 4096MB ( ~= RAM/2 or ~= RAM3/4)

    wal_level = hot_standby

    archive_mode = on

    archive_command = 'scp %p  postgres@10.1.1.118:/pgdata/pg_arc/%f'

    max_wal_senders = 1 # (slave servers count)

# optionally, you can optimize server kernel parameter in /etc/sysctl.conf (Total system RAM = 8GB)

root#  echo 'kernel.shmmax = 68719476736' >> /etc/sysctl.conf

root#  echo 'kernel.shmall = 4294967296' >> /etc/sysctl.conf

root# sysctl –p

#In order for the standby to start replicating, the entire database needs to be archived and then reloaded into the standby.

#This process is called a “base backup”, and can be performed on the master and then transferred to the slave.

#Let’s create a snapshot to transfer to the slave by capturing a binary backup of the entire PostgreSQL data directory.

#Master

root# su - postgres

$ psql postgres

    psql (9.1.8)

    Type "help" for help.

postgres=# SELECT pg_start_backup('base_backup');

postgres=# \q

$ cd /pgdata

$ tar -czvf backupdata.tar.gz data

$ scp backupdata.tar.gz postgres@postgresql2.mydomain.com:/pgdata/

$ psql postgres

postgres=# SELECT pg_stop_backup();

postgres-# \q

# Standby Setup: First, shutdown the database on the standby

root# service postgresql-9.1 stop

root# cd /pgdata/

root# rm -rf data

root# tar -xzvf backupdata.tar.gz

root# chown -R postgres:postgres data

# modify /pgdata/data/postgresql.conf file on standby

$ vim /pgdata/data/postgresql.conf

    shared_buffers = 2048MB   # ( ~= RAM/4)

    effective_cache_size = 4096MB ( ~= RAM/2 or ~= RAM3/4)

    #wal_level = hot_standby

    #archive_mode = on

    #archive_command = 'scp %p  postgres@10.1.1.118:/pgdata/pg_arc/%f'

    #max_wal_senders = 1 # (slave servers count)

    hot_standby = on

#modify or create new recovery settings on slave

root# vim /pgdata/data/recovery.conf

    standby_mode = 'on'

    primary_conninfo = 'host=10.1.1.183 port=5432 user=myuser_replicator password=a12345'  

    restore_command = 'cp /pgdata/pg_arc/%f %p'

    trigger_file = '/pgdata/trigger_file'    # /pgdata/trigger_file  parameter which tells the standby when to become the master.

    archive_cleanup_command = 'pg_archivecleanup /pgdata/pg_arc %r'

  

root# chown postgres.postgres /pgdata/data/recovery.conf

#You can also check certain processes on the machines to ensure replication is established:

postgres[master]$ ps -ef | grep sender

    postgres  1854  1725  0 11:09 pts/0    00:00:00 grep sender

postgres[slave]$ ps -ef | grep receiver

    postgres      1837  1752  0 11:00 pts/0    00:00:00 grep receiver

postgres(master)=# select * from pg_stat_replication;

 procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_st

ate

---------+----------+---------+------------------+-------------+-----------------+-------------+---------------+-------+---------------+----------------+----------------+-----------------+---------------+--------

----

(0 rows)

# Verify Setup

postgres=# CREATE DATABASE first_db;

postgres=# \l

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges 

-----------+----------+----------+-------------+-------------+-----------------------

 first_db  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

(4 rows)

postgres=# \c FIRST_DB

first_db=# CREATE table personel (id integer  PRIMARY KEY,name varchar(100),email varchar(255));

first_db=# INSERT into personel values (1, 'ercan', 'ercanxcan.gmail.com');

first_db=# INSERT into personel values (2, 'can', 'canercan04.yahoo.com');

first_db=# INSERT into personel values (3, 'eeee', 'eee.yahoo.com');

  

#Disaster recovery

Felaket kurtarma işlemi yapıldıktan ve eski master sunucuda sorunlar düzeltildiğinde,

eski master sunucunuzu tekrar master olarak devreye almak isterseniz.

Hali hazırda çalışan sunucuda sistemin full backup'ını alıp veritabanına dışarıdan erişimleri kapatmanız gerekir.

Master makinaya en son aldığınız backup kopyalanır ve sample şema yerine bu backup kullanılarak tüm adımlar tekrar

edilerek sistem başlanğıç konumuna dönmesi sağlanır.

SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

Comments

Popular posts from this blog

Offline AI Coding in VSCode: Continue + Ollama Complete Setup Guide

Want GitHub Copilot-level code assistance — but fully offline, air-gapped, and private? This guide walks you through setting up Continue (a VSCode extension) with Ollama (a local model runner) so you get chat, autocomplete, and inline editing powered entirely by models running on your own machine. No internet required at runtime. Table of Contents What Are Continue and Ollama? Prerequisites & Hardware Requirements Choosing the Right Models Step 1 — Installing Ollama & Pulling Models Step 2 — Installing Continue in VSCode (Offline) Step 3 — Configuring config.yaml Step 4 — Disabling Telemetry Step 5 — Usage & Shortcuts Troubleshooting What Are Continue and Ollama? Continue is an open-source VSCode (and JetBrains) extension that adds AI-powered coding features directly into your editor — think chat, inline edits, and tab autocomplete. By default it can connect to Claude, GPT-4, or any API. But it also supports local providers , which is exactly what we want here. Ollama i...

Local LLM on Ubuntu (CPU-Only): Ollama + Qwen Coder + Open WebUI + RAG Setup

Local LLM on Ubuntu (CPU-Only): Ollama + Qwen Coder + Open WebUI + RAG Run a private, high-performance internal AI coding assistant on your own hardware—no GPU required. Feb 13, 2026 Qwen 2.5 Coder RAG Enabled Enterprise Ready Keeping your code and documents on your own infrastructure is the gold standard for enterprise security. By combining Ollama , Qwen Coder , and Open WebUI , we can build a production-grade AI assistant that respects your privacy. Environment: CPU-only Ubuntu server (e.g., i9/Threadripper) with 64GB+ RAM for smooth 14B model inference. 1. System Preparation First, update your local environment and install necessary dependencies: sudo apt update && sudo apt upgrade -y sudo apt install -y curl git ca-certificates lscpu 2. Ollama & Model Tuning Install Ollama and optimize the configuration for CPU-only inference by setting the thread count: ...

Defining tomcat and mysql server Amazon EC2 instance

-- Create new amazon ec2 instance http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EC2_GetStarted.html -- Give permisson to pem file chmod 400 mycertificate.pem -- Connect to EC2 instance ssh -i mycertificate.pem ubuntu@ec2-51-51-51-51.eu-west-1.compute.amazonaws.com -- Installation Oracle Java 1.8 $ wget --no-check-certificate --no-cookies --header "Cookie: oraclelicense=accept-securebackup-cookie" http://download.oracle.com/otn-pub/java/jdk/8u92-b14/jdk-8u92-linux-x64.tar.gz $ tar -xzvf jdk-8u92-linux-x64.tar.gz $ sudo mkdir -p /usr/java/ $ sudo mv jdk1.8.0_92/ /usr/java/ $ sudo vim  /etc/profile         JAVA_HOME=/usr/java/jdk1.8.0_92         PATH=$PATH:$HOME/bin:$JAVA_HOME/bin         JRE_HOME=/usr/java/jdk1.8.0_92/jre         PATH=$PATH:$HOME/bin:$JRE_HOME/bin         export JAVA_HOME...