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
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 segmentmax_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.
[postgres@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 = 4listen_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/9000348psql# 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/9000268psql# 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
Please check line 3 in your diagram. It’s the pg_hba.conf file you should be vi’ing not postgres.conf
I have a hot standby server that has a lock on a table. The lock has not been released for a day now.
I think it was because I created an index on a referenced table on the main server.
I searched a bit but could not find what to do, so I dropped the index on the main server. One hour and the lock is still in place.
It is either the standby is corrupt somehow or the cause of all this is the index.
How do I get the slave up to speed – or recover?
Thanks