Skip to main content

Pgpool Configuration & Failback

I would like to share the pgpool configuration, and it's failback mechanism in this post.

Hope it will be helpful to you in creating pgpool and it's failback setup.

Pgpool Installation & Configuration

1. Download the pgpool from below link(Latest version is 3.2.1).
    http://www.pgpool.net/mediawiki/index.php/Downloads



2. Untart the pgpool-II-3.2.1.tar.gz and goto pgpool-II-3.2.1 directory.


3. Install the pgpool by executing the below commands:
 
./configure ­­prefix=/opt/PostgreSQL92/ ­­--with­-pgsql­-includedir=/opt/PostgreSQL92/include/
--with­-pgsql­-libdir=/opt/PostgreSQL92/lib/
make
make install
4. You can see the pgpool files in /opt/PostgreSQL92/bin location.
/opt/PostgreSQL92/bin $ ls
clusterdb   droplang  pcp_attach_node  pcp_proc_count pcp_systemdb_info  pg_controldata  pgpool pg_test_fsync pltcl_loadmod  reindexdb createdb    dropuser  pcp_detach_node  pcp_proc_info createlang  ecpg      pcp_node_count   pcp_promote_node oid2name  pcp_pool_status  pcp_stop_pgpool 

/opt/PostgreSQL92/bin $ ./pgpool ­version
pgpool­II version 3.2.1 (namameboshi)

Pgpool Parameters

listen_addresses = '*'

# This parameter tells about, which clients can connect to the pgpool. If we mention the listen_addresses = 'ip1,ip2', then these two ip's only can access this pgpool.
 
port = 9999

# We can define the pgpool port using this parameter.
 
socket_dir = '/tmp'

# This parameter tells about the socket directory.
pcp_port = 9898

# PCP is pgpool admin utility port. Using pcp port, we can execute some node attach and detaches.
 
pcp_socket_dir = '/tmp' 
# PCP socket directory.


backend_hostname0 = '0.176.112.188' 
# Node 0 Host IP

backend_port0 = 5432
# Node 0 Port

backend_weight0 = 1

# Node 0 Weight in load balance mode.

backend_data_directory0 = '/opt/PostgreSQL92/data' 
# Node 0 Data Direcotry

backend_flag0 = 'ALLOW_TO_FAILOVER' 
# Node 0 can allow failover or not.

backend_hostname1 = '0.176.112.189'
# Node 1 Host IP
backend_port1 = 5432
# Node 1 Port

backend_weight1 = 1

# Node 1 Weight in load balance mode.

backend_data_directory1 = '/opt/PostgreSQL92/data' 
# Node 1 Data Directory

backend_flag1 = 'ALLOW_TO_FAILOVER' 
# Node 1 can allow failover or not.

enable_pool_hba = on

# Enabling pool hba authentication like pg_hba authentication. In any case, we need to have the same pg_hba.conf entries in pool_hba.conf.If we do have different values, then we some of the users can face connectivity issues. Hence, requesting you to make sure both values are same. If we enable the pool_hba, then we need to create "pool_passwd" file in the pgpool.conf paramter's location. pool_passwd must contain the values "username:md5password". You can get this username and passwords from "pg_shadow" postgres table.

authentication_timeout = 60

# Pgpool client authentication timeout.

num_init_children = 32

# This parameter tells about how many initial pg connections need to make while pgpool starts.

max_pool = 3

# This parameter defines about how many pools for each num_init_children connections.

pid_file_name = '/opt/PostgreSQL92/data/pgpool.pid'

# This parameter defines where the pgpool.pid file need to place.

replication_mode = off

# This parameter need to off, if we are using pgpool on top of Slony/Streaming replication. We can also enable this parameter, if you want the pgpool replication rather than Slony/Streaming.

load_balance_mode = off

# This parameter need to on, if we are doing the load balance. i.e, if you want to distribute the "SELECT" queries between the primary and slave servers, then we need to enable this parameter. By enabling this parameter, we can balance work load between the primary and slave.

white_function_list = ''

# Readonly functions/procedures we can mention in this list. 
black_function_list = 'nextval,setval'

# Readwrite functions/procedures we need to mention in this list.

recovery_user = 'postgres'

# We need to provide recovery user name.

recovery_password = 'adminedb'

# We need to provide recovery user password. The above two parameters works only to the pcp commands like pcp_recovery_command, pcp_attach_node, pcp_detach_node.

revery_1st_stage_command = 'basebackup'

# We need to provide recovery 1st command which is doing the failback.

You can find more details of these parameters from the below link.
http://www.pgpool.net/docs/latest/tutorial-en.html

Pgpool start and stop commands

1. Start the pgpool using below command:
./pgpool ­f /opt/PostgreSQL92/etc/pgpool.conf -­F /opt/PostgreSQL92/etc/pcp.conf -a /opt/PostgreSQL92/etc/pool_hba.conf -­d -D -n >/opt/PostgreSQL92/data/pgpool.log 2>&1 &
2. Check whether pgpool status:
postgres~/bin> ps ­ef|grep pgpool
postgres  4288  3754  0 07:55 pts/1    00:00:00 ./pgpool ­f
/opt/PostgreSQL92/etc/pgpool.conf -F /opt/PostgreSQL92/etc/pcp.conf
­a /opt/PostgreSQL92/etc/pool_hba.conf -d -­D -­n
postgres  4289  4288  0 07:55 pts/1
connection request
3. Connect to database using pgpool port 9999.
postgres@localhost:~/bin> psql ­p 9999
Password:
psql.bin (9.2.1)

Type "help" for help.
postgres=# show port;

 port
­­­­­­ 5432
(1 row)
4. You can stop the pgpool by using below command:
./pgpool ­f /opt/PostgreSQL92/etc/pgpool.conf -F /opt/PostgreSQL92/etc/pcp.conf -a /opt/PostgreSQL92/etc/pool_hba.conf ­-m fast stop
5. you can reload the pgpool by using below command:
./pgpool ­f /opt/PostgreSQL92/etc/pgpool.conf -F /opt/PostgreSQL92/etc/pcp.conf -a /opt/PostgreSQL92/etc/pool_hba.conf reload
Pgpool Failback

Failback is one of the features of PGPOOL which will re-initiate the failed master as a new slave server to the new master. For doing this operation, we need to follow the below steps.
1) Go to the Pgpool installer location on new master server.
cd /tmp/pgpool­II­3.2.1/sql/pgpool­recovery
export PATH=/opt/PostgreSQL92/bin:$PATH
make
make install
2) Then execute the pgpool-recovery.sql file in "template1" database of new master server.
3) Then prepare a script "basebackup" & "pgpool_remote_start" & "test1.sh" as like attached scripts and place in new master's data directory.
4) Then include the following parameters in pgpool.conf where the pgpool instance is running . Once the modifications done,then reload the pgpool using the following command. Reload Command ­­­­­­­­­­­­­­
/opt/PostgreSQL92/bin/pgpool -­f /opt/PostgreSQL92/etc/pgpool.conf -F /opt/PostgreSQL92/etc/pcp.conf -a /opt/PostgreSQL92/etc/pool_hba.conf reload
Pgpool Parameters
­­­­­­­­­­­­­­­­­
recovery_user = 'postgres'
recovery_password = 'adminedb'
recovery_1st_stage_command = 'basebackup'
**Note: recovery_user credentials must match with the pcp.conf credentials. 
5) As a final step, we need to execute the pcp_recovery_command from either new master or new slave as shown below.
/opt/PostgreSQL92/bin/pcp_recovery_node ­d 1 0.176.112.189 9898
postgres adminedb 0
DEBUG: send: tos="R", len=46
DEBUG: recv: tos="r", len=21, data=AuthenticationOK
DEBUG: send: tos="D", len=6
DEBUG: recv: tos="c", len=20, data=CommandComplete
DEBUG: send: tos="X", len=4
If we get the above kind of message from the recovery command, then our setup has been accomplished. pcp_recovery_node Explanation ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ ­d, ­debug : enable debug message (optional) timeout : connection timeout value in seconds. command exits on timeout hostname : pgpool­II hostname port# : PCP port number username : username for PCP authentication password : password for PCP authentication nodeID : ID of a node to recover test1.sh
#!/bin/sh
export PATH=/opt/PostgreSQL92/bin:$PATH
export LD_LIBRARY_PATH=/opt/PostgreSQL92/lib:$LD_LIBRARY_PATH
export PGPASSWORD=adminedb
mv /opt/PostgreSQL92/data1 /opt/PostgreSQL92/data_old
mv /opt/PostgreSQL92/backup /opt/PostgreSQL92/data1
echo "restore_command = 'cp /var/arch_test/%f %p'">/opt/PostgreSQL92/data1/recovery.conf
echo "standby_mode = 'on'">>/opt/PostgreSQL92/data1/recovery.conf
echo "primary_conninfo = 'host=0.176.112.188 port=5433 user=replication password=replication application_name=Async_Rep'">>/opt/PostgreSQL92/data1/recovery.conf
rm -f /opt/PostgreSQL92/data1/_Promote_Me_Primary_Is_Down_5433
echo "trigger_file = '/opt/PostgreSQL92/data1/_Promote_Me_Primary_Is_Down_5433'">>/opt/PostgreSQL92/data1/recovery.conf
basebackup
#!/bin/sh
export PATH=/opt/PostgreSQL92/bin:$PATH
export LD_LIBRARY_PATH=/opt/PostgreSQL92/lib:$LD_LIBRARY_PATH
export PGPASSWORD=adminedb
echo "archive_command = 'cp %p /var/arch_test/%f && scp %p postgres@0.176.112.189:/var/arch_test/%f'" >> /opt/PostgreSQL92/data1/postgresql.conf
pg_ctl reload
psql -c "select pg_start_backup('pgpool_recovery')" -p 5433
ssh postgres@0.176.112.189 mkdir /opt/PostgreSQL92/backup
rsync -C -a --exclude 'postmaster.pid' --exclude 'postmaster.opts' --exclude 'recovery.done' /opt/PostgreSQL92/data1/ postgres@0.176.112.189:/opt/PostgreSQL92/backup/
export PGPASSWORD=adminedb
psql -c "select pg_stop_backup()" -p 5433
rm -f /opt/PostgreSQL92/data/recovery.done
ssh postgres@0.176.112.189 sh /opt/PostgreSQL92/test1.sh
pgpool_remote_start
#/bin/bash
ssh postgres@0.176.112.189 pg_ctl -D /opt/PostgreSQL92/data1 start

Dinesh Kumar

Comments

  1. Valuable for information if there is any other regarding this kindly revert me back on this Jobs in PHP

    ReplyDelete
  2. how to make slave as a new master using pgpool

    ReplyDelete
  3. Agar aap Noida Greater Noida mai Property Kharidna ya Bechna Chahte hain to Propman portal par visit karen yahan aap apni property free mai advertise kar sakte hain

    ReplyDelete
  4. bOBBY KATARIA BIOGRAPJHY

    BOBBY KARARIA BIOGRAPHY PADHEN KE LIYE DEKHEN

    Bobby Kataria

    ReplyDelete

Post a Comment

Popular posts from this blog

Parallel Operations With pl/pgSQL

Hi, I am pretty sure that, there will be a right heading for this post. For now, i am going with this. If you could suggest me proper heading, i will update it :-) OK. let me explain the situation. Then will let you know what i am trying to do here, and how i did it. Situation here is, We have a table, which we need to run update on “R” no.of records. The update query is using some joins to get the desired result, and do update the table.  To process these “R” no.of records, it is taking “H” no.of hours. That too, it’s giving load on the production server. So, we planned to run this UPDATE as batch process.  Per a batch process, we took “N” no.or records. To process this batch UPDATE, it is taking “S” no.of seconds. With the above batch process, production server is pretty stable, and doing great. So, we planned to run these Batch updates parallel.  I mean, “K” sessions, running different record UPDATEs. Of-course, we can also increase the Batch size here.  But

How To Send E-Mail From PostgreSQL

Hi , If you want to send E-Mails from PostgreSQL, then use the below Python 3.2 Script as below. I have used ActivePython 3.2 with PostgreSQL 9.1 for sending E-Mails from PostgreSQL. If you want to configure the Python 3.2 with PostgreSQL 9.1 then, please refer the below steps. http://manojadinesh.blogspot.in/2012/06/fatal-python-error-pyinitialize-unable.html Once, your Python 3.2 successful then follow the below steps to send an e-mail. Step 1 ===== postgres=# CREATE OR REPLACE FUNCTION public.send_email(_from Text,_password Text,smtp Text,port INT,receiver text, subject text, send_message text) RETURNS TEXT  LANGUAGE plpython3u AS $function$ import smtplib sender = _from receivers = receiver message = ("From: %s\nTo: %s\nSubject: %s\n\n %s"  % (_from,receiver,subject,send_message)) try:   smtpObj = smtplib.SMTP(smtp,port)   smtpObj.starttls()   smtpObj.login(_from, _password)   smtpObj.sendmail(sender, receivers,message)   print ('Successf

::Pipelined in Oracle as well in PostgreSQL::

Pipelined Table Functions:- [ORACLE] =========================== If you want to return multiple rows to the calling environment, then piplined table functions is prefred. It will increase the dbperformance as well. Ex:- Step 1: ----------- CREATE TABLE EMP(EMPNO INT,ENAME VARCHAR2(10),SAL INT); Step 2: ----------- Insert sample data. Step 3: ----------- Create an object for the row type casting. CREATE OR REPLACE TYPE emp_row AS OBJECT ( empno INT, ename VARCHAR2(20), SAL INT ); Step 4: ----------- Create a Return Type for the pipelined function. CREATE OR REPLACE TYPE emp_table_type AS TABLE OF emp_row; Step 5: ----------- CREATE OR REPLACE FUNCTION emp_pipe_function RETURN emp_table_type PIPELINED IS BEGIN FOR rec in (select * from emp) LOOP PIPE ROW (emp_row(rec.empno,rec.ename,rec.sal)); END LOOP; RETURN; END; Step 6: ---------- SQL> select * from table(emp_pipe_function); EMPNO ENAME SAL ---------- ----