Configuring PgPool
Table Of Content
0.0 Getting Started
1.0 Installing PgPool
2.0 Copying PgPool Files
3.0 Configuring PgPool Files
4.0 Configuring PostGres Files
5.0 Assigning a password to user ‘posrgres’
6.0 Real Time Test
0.0 Getting Started
Assumptions:
PostgreSQL is properly configured and it’s up-n-running in all machines.
Versions:
Fedora 9
PostgreSQL 8.3.1
pgpool-II version2.1-beta2(inamiboshi)
For this tutorial,
Postgres data folder will be under /var/lib/pgsql/data
PgPool will be under /usr/share/pgpool-II
PostgreSQL database’s(both machines) Username: postgres / Password: postgres
For this example, we use 2 servers.
Server 01
IP – 192.168.xxx.yyy
This machine contains PostgreSQL and PgPool.
Server 02
IP – 192.168.xxx.zzz
This machine contains PostgreSQL.
Command Prompts
# - Fedora command terminal. [root@localhost ~]
$ - PostgreSQL bash. ~bash -3.25$
& - Inside the particular database’s sql bash. (databasename)=#
1.0 Installing PgPool
First of all, you need install PgPool to the fedora machine (192.168.xxx.yyy). That can be done through Add/Remove Software.
**ImportantYou cannot install this software from the “root” login. For that you need to re-login from un-privileged user.
Path:
System >> Administration >> Add/Remove Software
In that Add/Remove software window type the word pgpool and search for pgpool.
This will take some time depending on the bandwidth of the internet link.
From the search results look for,
PostgreSQL-pgpool-II-2.1-0.2.beta2.fc9(i386)
Click “Install” by selecting the particular PgPool version. This will install PgPool to your system.
Once installation is done, you can check the installed versions by giving following commands in a terminal.
# postgres --version
# pgpool –version
2.0 Copying PgPool Files
Next we need to copy some configuration files to a source destination. These file copying should be done under “root” login.
PgPool will be installed under /usr/share/pgpool-II directory.
Copy following 3 files to the /etc from /usr/share/pgpool-II directory.
pcp.conf.sample
pgpool.conf.sample
pool_hba.conf.sample
Rename those 3 files under /etc to as follows. (Simply remove the word “sample”)
pcp.conf
pgpool.conf
pool_hba.conf
So after this copying process, inside the folder /etc there must be 3 files with following names.
1. pcp.conf
2. pgpool.conf
3. pool_hba.conf
3.0 Configuring PgPool Files
3.1 pgpool.conf
Edit the pgpool.conf file as follows.
Edit Sec: 01
**Under “Host name or IP address to listen on” section
listen_addresses = '*'
Edit Sec: 02
**Under “System DB info” section
backend_hostname0 = ‘192.168.xxx.yyy'
backend_port0 = 5432;
backend_weight0 = 1;
backend_hostname1 = ‘192.168.xxx.zzz’
backend_port1 = 5432;
backend_weight1 = 1;
3.2 pcp.conf
Create a username and password to enter in to this file.
Username: postgres
Password: postgres
The password should be in md5 hash format. To convert password “postgres” to md5 type bellow command in a terminal.
# /usr/bin/pg_md5 postgres
The result will be…
e8a48653851e28c69d0508fb27fc5
Append the following line in to pcp.conf
postgres: e8a48653851e28c69d0508fb27fc5
3.3 pool.hba
No changes required in this file.
4.0 Configuring PostGres Files
4.1 postgresql.conf
Uncomment the following lines and change as follows.
**Under “Connection Settings” section
listen_addresses = '*'
port = 5432
4.2 pg_hba.conf
Append following 2 lines at the end.
Host all all 192.168.xxx.0 255.255.255.0 password
Host all all 192.168.xxx.0/24 password
5.0 Assigning a password to user ‘posrgres’
Run following commands to assign a password to user “postgres”.
# su - postgres
$ psql postgres
& alter user postgres password ‘postgres’;
6.0 Real Time Test
Example 01: Replication
Step 01
To enable database replication function, edit following lines in pgpool.conf
**Under “if non 0, run in a parallel query mode” section
parallel_mode = false
replication_mode = true
load_balance_mode = true
After editing the pgpool.conf file restart PostgreSQL and PgPool.
PostgreSQL - ReStart
# /etc/init.d/PostgreSQL restart
PgPool - Stop
# pgpool stop
PgPool - Start
# pgpool –n
Checking the result:
To test whether PgPool/PostgreSQL properly configured for replication, run following sql command.
# su – postgres
$ createdb sam_test –p 9999 –h 192.168.xxx.yyy –U postgres
When password is prompt… type “postgres”
Above command will create the new database.
If replication is working properly, in both machines a new database called “sam_test” should exist.
To list the database list,
$ psql –l –p 5432 –h 192.168.xxx.yyy –U postgres
$ psql –l –p 5432 –h 192.168.xxx.zzz –U postgres
This means replication is working fine!!!!
Example 02: Parallel Query
Step 01
To enable parallel query, edit following lines in pgpool.conf
**Under “if non 0, run in a parallel query mode” section
parallel_mode = true
replication_mode = false
load_balance_mode = true
Step 02
Now we need to create a user called “pgpool” and a database called “pgpool”.
Run following commands.
# su – postgres
$ createuser -p 5432 pgpool
$ createdb -p 5432 -O pgpool pgpoolcreatedb -p 5432 -O pgpool pgpool
Step 03
Now we need to install dblink to the system.
That can be done through Add/Remove Software.
**ImportantYou cannot install this software from the “root” login. For that you need to re-login from un-privileged user.
Path:
System >> Administration >> Add/Remove Software
In that Add/Remove software window type the word contrib and search for contrib.
From the search results look for,
PostgreSQL-contrib-8.3.1-1.fc9(i386)
Click “Install” by selecting the particular contrib version. This will install contrib to your system.
Step 04
After installing dblink, run the following command.
# su – postgres
$ psql –f /usr/share/pgsql/contrib/dblink.sql –p 5432 pgpool
This command will create 38 functions and 1 composite type in pgpool database.
Step 05
Next run the following command.
# su – postgres
$ psql –f /usr/share/pgpool-II/system_db.sql –p 5432 –U pgpool pgpool
This will create a new catalog called “pgpool_catalog” on “pgpool” database.
Checking the result:
Create a new folder called “sql” inside pgpool-II folder. Inside that folder, create 4 new sql files with following content.
insert01.sql
insert into dt_tb (dt_no, dt_ch_no, dt_val, dt_time) values (1, 1, 1,'2006-01-01 00:00:00');
insert into dt_tb (dt_no, dt_ch_no, dt_val, dt_time) values (2, 2, 2,'2006-01-01 00:00:00'); insert into dt_tb (dt_no, dt_ch_no, dt_val, dt_time) values (3, 3, 3,'2006-01-01 00:00:00');
insert into dt_tb (dt_no, dt_ch_no, dt_val, dt_time) values (4, 4, 4,'2006-01-01 00:00:00'); insert into dt_tb (dt_no, dt_ch_no, dt_val, dt_time) values (5, 1, 1,'2006-01-01 00:01:00');
insert02.sql
insert into dt_tb (dt_no, dt_ch_no, dt_val, dt_time) values(6, 4, 4,'2006-12-31 23:58:00');
insert into dt_tb (dt_no, dt_ch_no, dt_val, dt_time) values (7, 4, 4,'2006-12-31 23:58:00');
insert into dt_tb (dt_no, dt_ch_no, dt_val, dt_time) values(8, 1, 1, '2006-12-31 23:59:00');
insert into dt_tb (dt_no, dt_ch_no, dt_val, dt_time) values (9, 1, 1,'2006-12-31 23:59:00');
insert into dt_tb (dt_no, dt_ch_no, dt_val, dt_time) values(10, 2, 2, '2006-12-31 23:59:00');
make_dt_tb.sql
DROP TABLE dt_tb;
CREATE TABLE dt_tb(dt_no integer, dt_ch_no integer, dt_val integer, dt_time timestamp);
data_dist_rule.sql
CREATE OR REPLACE FUNCTION pgpool_catalog.dist_rule (timestamp)
RETURNS INTEGER AS $$
SELECT CASE
WHEN EXTRACT(month FROM $1)>6 THEN 1
ELSE 0
END;
$$ LANGUAGE SQL;
INSERT INTO pgpool_catalog.dist_def VALUES(
'data',
'public',
'dt_tb',
'dt_time',
ARRAY['dt_no', 'dt_ch_no', 'dt_val', 'dt_time'],
ARRAY['integer', 'integer', 'integer', 'timestamp'],
'pgpool_catalog.dist_rule'
);
First we need to create a new database called “data”.
# su - postgres
$ createdb data –p 9999 –h 192.168.xxx.yyy –U postgres
This will create database called “data” in both machines.
Next run following command.
# su – postgres
$ psql –f /usr/share/pgpool-II/sql/data_dist_rule.sql –p 5432 –U pgpool pgpool
Above sql command will create the distribution rule for the database pgpool.
Then run following sql commands.
# su – postgres
$ psql –p 5432 –h 192.168.xxx.yyy–U postgres data –f /usr/share/pgpool-II/sql/make_dt_tb.sql
$ psql –p 5432 –h 192.168.xxx.zzz–U postgres data –f /usr/share/pgpool-II/sql/make_dt_tb.sql
Above commands will create a new table “dt_tb” in “data” database.
$ psql –p 5432 –h 192.168.xxx.yyy –U postgres data –f /usr/share/pgpool-II/sql/insert01.sql
$ psql –p 5432 –h 192.168.xxx.zzz –U postgres data –f /usr/share/pgpool-II/sql/insert02.sql
Above commands will insert records to the table “dt_tb”.
Now we need to run some sql’s and check the results.
Run bellow sql commands.
# su – postgres
$ psql –p 5432 –h 192.168.xxx.yyy data –c “select * from dt_tb;”
This will give you a result containing 5 rows.
$ psql –p 5432 –h 192.168.xxx.zzz data –c “select * from dt_tb;”
This will give you a result containing 5 rows.
Finally run bellow sql.
$ psql –p 9999 –h 192.168.xxx.yyy data –c “select * from dt_tb;”
This sql command must return 10 records.
(5 from 192.168.xxx.yyy + 5 from 192.168.xxx.zzz)
This means parallel query is working fine !!!! Time to have a good cup of TEA !!
References:
Tutorial on - How to configure PgPool II/PostgreSQL on Fedora 9
About Me
Blog Archive
View comments