Part 1 : http://willyristanto.blogspot.com/2014/10/clustering-postgresql-with-pgpool-ii-92.html
5. Install pgpool
we can download pgpool from here :
http://www.pgpool.net/mediawiki/index.php/Downloads
[root@postgres1 pgp]# ls
pgpool-II-92-3.3.2-1.rhel6.x86_64.rpm
pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm
pgpool-II-92-devel-3.3.2-1.rhel6.x86_64.rpm
[root@postgres1 pgp]#yum install pgpool-II-92-3.3.2-1.rhel6.x86_64.rpm
[root@postgres1 pgp]#yum install pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm
[root@postgres1 pgp]#yum install pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm
6. Edit file pgpool-recovery.sql and pgpool-regclass.sql
[root@postgres1 pgp]# nano /usr/pgsql-9.2/share/extension/pgpool-recovery.sql
CREATE OR REPLACE FUNCTION pgpool_recovery(text, text, text)
RETURNS bool
AS '/usr/pgsql-9.2/lib/pgpool-recovery', 'pgpool_recovery'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION pgpool_remote_start(text, text)
RETURNS bool
AS '/usr/pgsql-9.2/lib/pgpool-recovery', 'pgpool_remote_start'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION pgpool_pgctl(text, text)
RETURNS bool
AS '/usr/pgsql-9.2/lib/pgpool-recovery', 'pgpool_pgctl'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION pgpool_switch_xlog(text)
RETURNS text
AS '/usr/pgsql-9.2/lib/pgpool-recovery', 'pgpool_switch_xlog'
LANGUAGE C STRICT;
make sure file pgpool-recovery.so is exist in the correct path
[root@postgres1 lib]# nano /usr/pgsql-9.2/share/extension/pgpool-regclass.sql
CREATE OR REPLACE FUNCTION pg_catalog.pgpool_regclass(cstring)
RETURNS oid
AS '/usr/pgsql-9.2/lib/pgpool-regclass', 'pgpool_regclass'
LANGUAGE C STRICT;
make sure file pgpool-regclass.so is exist in the correct path
7. Create Database
[root@postgres1 pgp]# su - postgres
-bash-4.1$ psql
psql (9.2.9)
Type "help" for help.
postgres=# create database mydb owner postgres;
CREATE DATABASE
postgres=#
8. Execute function on database
-bash-4.1$
psql -h localhost -U postgres -W -f
/usr/pgsql-9.2/share/extension/pgpool-recovery.sql mydb
Password for user postgres:
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
-bash-4.1$ psql -h localhost -U postgres -W -f /usr/pgsql-9.2/share/extension/pgpool-regclass.sql mydb
Password for user postgres:
CREATE FUNCTION
-bash-4.1$
9. Install pgpool on pgpool server
[root@pgpserv pgp]# ls
pgpool-II-92-3.3.2-1.rhel6.x86_64.rpm
pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm
pgpool-II-92-devel-3.3.2-1.rhel6.x86_64.rpm
[root@pgpserv pgp]#yum install pgpool-II-92-3.3.2-1.rhel6.x86_64.rpm
[root@pgpserv pgp]#yum install pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm
[root@pgpserv pgp]#yum install pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm
Login to server postgres database, we need to know passwd from pg_shadow from user postgres
mydb=# select passwd from pg_shadow where usename = 'postgres';
passwd
-------------------------------------
md53175bce1d3201d16594cebf9d7eb3f9d
back to pgpool server, edit file pcp.conf
#nano /etc/pgpool-II-92/pcp.conf
append line
postgres:3175bce1d3201d16594cebf9d7eb3f9d
please note that we get value 3175bce1d3201d16594cebf9d7eb3f9d from passwd_from pg_shadow (string "md5" is omitted)
10. Edit pgpool.conf
[root@pgpserv pgpool-II-92]# nano /etc/pgpool-II-92/pgpool.conf
listen_addresses = '*'
port = 9999
backend_hostname0 = '192.168.112.142'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.2/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.112.140'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.2/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
connection_cache = on
replication_mode = true
load_balance_mode = true
11. Start pgpool
we can start pgpool by simply command
#pgpool
stop pgpool
#pgpool stop
or we can stop immediately
#pgpool -m fast stop
Next Part 3 : http://willyristanto.blogspot.com/2014/10/clustering-postgresql-with-pgpool-ii-92_94.html
