Sep 24 2016

PostgreSQL Hot Standby (Streaming Replication) Database Installation and Failover Operations

Category: AdministrationFatih Acar @ 09:33

PostgreSQL has few feature for standby technology. I generally use streaming replication. This is very easy and confident. Streaming replication use a replication user for replication operations.Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the standby as they’re generated, without waiting for the WAL file to be filled.

Streaming replication is asynchronous by default, in which case there is a small delay between committing a transaction in the primary and the changes becoming visible in the standby.

If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. If you set up a WAL archive that’s accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments.

Test Systems Features

PG Version : 9.5
Primary DB IP : 192.168.10.11
Pimary DB Hostname : pg01
Hot Standby DB IP : 192.168.10.12
Host Standby DB Hostname : pg02
Virtual Service IP : 192.168.10.10
OS : Centos 7 x64
Data directory : /pgdata/data
Archive direcytory : /pgdata/ARCHIVELOG

streaming_replication

1- Keygen Generate And Copy

[postgres@pg01 ~] ssh-keygen -t rsa
[postgres@pg02 ~] ssh-keygen -t rsa
[postgres@pg01 ~] ssh-copy-id -i .ssh/id_rsa.pub postgres@pg02
[postgres@pg02 ~] ssh-copy-id -i .ssh/id_rsa.pub postgres@pg01

2- Create Streaming Replication User on Primary DB

psql# createuser -U postgres -p 5432 repuser -P -c 10 –replication

3- Primary DB pg_hba.conf configuration

[root@pg01 ~] vi /pgdata/data/pg_hba.conf

Add below line

host replication repuser 192.168.10.12/32 md5

[root@pg01 ~] systemctl reload postgresql.service

4- Create Archive Directory on Hot Standby Server

[root@pg02 ~] mkdir -p /pgdata/ARCHIVELOG

5- Postgresql.conf configuration on Primary DB

[root@pg01 ~] vi /pgdata/data/postgresql.conf

Parameters change like below

listen_addresses = ‘192.168.10.10’

wal_level = hot_standby # minimal, archive, hot_standby, or logical

archive_mode = on # enables archiving; off, on, or always
archive_command = ‘rsync -a %p postgres@192.168.10.12:/pgdata/ARCHIVELOG/%f’ # command to use to archive a logfile segment

max_wal_senders = 3 # max number of walsender processes
wal_keep_segments = 1000 # in logfile segments, 16MB each; 0 disables
max_replication_slots = 4 max number of replication slots # (change requires restart)

[root@pg01 ~] systemctl restart postgresql.service

6- Base Backup

You can use pg_basebackup when primary db is start or stop. Stop status is more confident.

[root@pg01 ~] systemctl stop postgresql.service

/pgdata/data directory has to be empty on hot standby server. pg_basebackup command will fill up that directory.

[root@pg02 ~] /usr/pgsql-9.5/bin/pg_basebackup -h 192.168.10.10 -D /pgdata/data -U repuser -p 5432 -v -P –xlog-method=stream

7- Postgresql.conf configuration on Hot Standby Server

Primary DB configuration has to be disabled with # mark.

#wal_level = hot_standby # minimal, archive, hot_standby, or logical

#archive_mode = on # enables archiving; off, on, or always
#archive_command = ‘rsync -a %p postgres@192.168.10.12:/pgdata/ARCHIVELOG/%f’ # command to use to archive a logfile segment

#max_wal_senders = 3 # max number of walsender processes
#wal_keep_segments = 1000 # in logfile segments, 16MB each; 0 disables
#max_replication_slots = 4

listen_addresses = ‘*’
hot_standby = on

8- Create Recovery.conf file on Hot Standby Server

[root@pg02 ~] vi /pgdata/data/recovery.conf

restore_command = ‘cp /pgdata/ARCHIVELOG/%f %p’ # e.g. ‘cp /mnt/server/archivedir/%f %p’
archive_cleanup_command = ‘/usr/pgsql-9.5/bin/pg_archivecleanup /pgdata/ARCHIVELOG/%r’
standby_mode = on
primary_conninfo = ‘host=192.168.10.10 port=5432 user=repuser password=repuserpass’
trigger_file = ‘/pgdata/data/failover.uygula’

If you want to delay to apply committed values. You can use recovery_min_apply_delay parameter in recovery.conf like below.

recovery_min_apply_delay = 5min

This parameter provide 5 minutes delay. When you commit a transaction at primary side, hot standby will apply this transaction 5 minutes later.

9- Start Hot Standby PostgreSQL Service

[root@pg02 ~] systemctl start postgresql.service

10- Test

Primary DB
Replications list
psql# select * from pg_stat_replication ;

Sender process i çalışıyor olmalıdır.
ps -ef | grep sender
postgres 18388 18298 0 17:04 ? 00:00:00 postgres: wal sender process repuser 192.168.10.12(33700) streaming 0/9000348

psql# create table test (name text);
psql# insert into test values(‘Fatih Acar’);

Hot Standby DB
Recovery mode check
psql# select pg_is_in_recovery();

Receiver process check
ps -ef | grep receiver
postgres 20936 20919 0 17:04 ? 00:00:00 postgres: wal receiver process streaming 0/9000268

psql# select * from test;

name
———–
Fatih Acar

Failover Operations

When Primary DB is down with crash, you have to perform failover operation.

Step 1

Create /pgdata/data/failover.uygula file that is wrote in recovery.conf file trigger_file parameter

Hot Standby DB will be primary db (read-write mode) when trigger_file exist.

Step 2

Take virtual service IP on Hot Standby Server

ifconfig eth0:0 192.168.10.10 netmask 255.255.255.0

Failover With Python Script

I wrote a python script for failover operation. You can use this script for failover operation.

You can download script from pg_failover.py.zip and upload your hot standby server.


You have to change parameter for your hot standby server.

[root@pg02 ~] vi pg_failover.py

#-*- coding: utf-8 -*-

############ Script Bilgisi ##########

# Versiyon 	    : 1

# Tarih		    : 2016-06-30

# Gelistirici  	    : Fatih Acar

# E-Mail   	    : fatih@fatihacar.com

# Aciklama : Bu Script Hot Standby olarak calisan PostgreSQL veritabainin
# primary veritabainda bozulma meydana geldigi durumlarda Hot Standby in 
# primary veritabani olarak calismasini saglamaktadir. 

# Anahtar Kelimeler : PostgreSQL Hot Standby Failover, Create Virtual IP, 
# Streaming Replication

############ Kutuphaneler #############

import os
import sys
import commands
import datetime

############# Degiskenler #############

# Virtual IP parametrelerine PROD veritabaninin Virtual ve Scan IP degerleri yazilmalidir.

data_dizini = '/pgdata/data'

failover_islem_dosyasi= 'failover.uygula'

hot_standby_ip = '192.168.10.12' # Hot Standby sunucusunun IP adresi

virtual_ip = '192.168.10.10' # Bos birakilirsa IP atamasi yapilmaz.

ethernet_adaptor = 'eth0' # Hot Standby sunucusu ethernet adaptor bilgisi

postgresql_service_name = 'postgresql-masterDB.service' # Hot Standby veritabaninin postgresql service ismi yazilmalidir.

#######################################

def failover_db():
	
	try:
		print ('\033[92mSUCCESS : '+data_dizini+'/'+failover_islem_dosyasi+' dosyasi olusturuluyor..\033[0m')
		file = open(''+data_dizini+'/'+failover_islem_dosyasi,'w')
		file.write('PostgreSQL Hot Standby Failover islemi icin olusturulmustur !\n')
        	file.write('Lutfen silmeyiniz !\n')
		file.write('Tarih : ' +datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
		file.close()
		os.system('chown postgres.postgres '+data_dizini+'/'+failover_islem_dosyasi)
		print ('\033[92mSUCCESS : '+data_dizini+'/'+failover_islem_dosyasi+' dosyasi olusturuldu.\033[0m')
	except:
		print ('\033[91mERROR : '+data_dizini+'/'+failover_islem_dosyasi+' dosyasi olusturulamadi !\033[0m')
	
	try:
		if os.path.isfile("/tmp/failover_query.sql"):
			print ('\033[92mSUCCESS : Failover islemleri basliyor..\033[0m')
			print ('\033[92mSUCCESS : PostgreSQL servisi restart ediliyor..\033[0m')
			try:
				if(os.system('systemctl restart '+postgresql_service_name)) == 0:
 					print ('\033[92mSUCCESS :  '+postgresql_service_name+ 'servisi restart edildi.\033[0m')
				else:
					print ('\033[91mERROR : '+postgresql_service_name+ 'servisi restart edilemedi !\033[0m')
			except:
				print "\033[91mERROR : Failover islemleri sirasinda hata alindi.\033[0m"

	except:
			print "\033[91mERROR : Failover islemleri sirasinda hata alindi.\033[0m"
	
	print ('\033[92mSUCCESS : PostgreSQL servisinin durumu goruntuleniyor. \033[0m')
	
	print os.system('systemctl status '+postgresql_service_name)
	
        print ('\033[93mWARNING : Eger PostgreSQL servis durumu running olarak gozukmediyse failover islem adimlarinda problem meydana gelmistir !\033[0m')

	print "\033[92mSUCCESS : IP alma ve failover islemleri tamamlandi.\033[0m"

def take_virtual_ip():

	print "\033[92mSUCCESS : Virtual IPler aliniyor..\033[0m"
	if virtual_ip <> '':
		try:
			if(os.system('ifconfig '+ethernet_adaptor+':0 ' +virtual_ip+ ' netmask 255.255.255.0')) == 0:
				print ('\033[92mSUCCESS : ' +virtual_ip+ ' alindi.\033[0m')
			else:
				print ('\033[91mERROR : ' +virtual_ip+ ' alinamadi !\033[0m')
		except:
			print ('\033[91mERROR : ' +virtual_ip+ ' alinamadi !\033[0m')
	else:
		print ('\033[93mWARNING : virtual_ip parametresi bos oldugu icin IP alma islemi yapilmadi !\033[0m')

	print "IP adreslerinin son durumu listeleniyor.."
	print os.system('ifconfig')
	print "IP alma islemleri tamamlandi."

def main():

	print "\n\n ###################### PostgreSQL Hot Standby Failover ###################### \n\n"
	print "DIKKAT ! Bu uygulama PostgreSQL Hot Standby veritabanini PROD veritabani olarak aktif hale getirir ! \n"
	print "###### Parametreler ######\n"
	print ('data_dizini = ' +data_dizini)
	print ('failover_islem_dosyasi = ' +failover_islem_dosyasi)
	print ('hot_standby_ip = ' +hot_standby_ip)
	print ('virtual_ip = ' +virtual_ip)
	print ('ethernet_adaptor = ' +ethernet_adaptor)
	print ('postgresql_service_name = ' +postgresql_service_name)
	print ('\n#########################\n')
	if raw_input("Parametreler dogru ise devam etmek icin evet yazip enter tusuna basiniz : ") == 'evet': 
		
		secim1 = raw_input("Sadace IP alma islemi icin 1 e, hem IP alma hem de failover islemleri icin 2 ye basiniz : ")
		if secim1 == '1':
                	take_virtual_ip()
		elif secim1 == '2':
         		take_virtual_ip()

			print "\033[92mSUCCESS : Failover islemi basliyor..\n\033[0m"
						
			failover_db()

			print ('\033[93mWARNING : Failover islemlerinin ardindan FULL yedek alinmasi onerilir ! \033[0m')
		else:
			print "Virtual IP olusturma ve failover islemleri yapilmadi !"
	else:
		print "Uygulama calistirilmadan sonlandirildi."

main()


Execute 

[root@pg02 ~] python pgfailover.py

Source : www.postgresql.org

10,205 total views, 16 views today

Tags: Database Administration, PostgreSQL Administration, PostgreSQL High Availability, PostgreSQL Hot Standby

facebook comments:

One Response to “PostgreSQL Hot Standby (Streaming Replication) Database Installation and Failover Operations”

  1. Kunle says:

    Please check line 3 in your diagram. It’s the pg_hba.conf file you should be vi’ing not postgres.conf

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.