|
pgpool-II is very useful tool for database replication and loadbalancing. It provides the following features. 1)Connection Pooling 2)Replication 3)Load Balance 4)Parallel Query Installation and configuration I used the pgpool-II-2.0.1.tar.gz for this configuration. You can also use the latest available version . Installing pgpool-II requires gcc 2.9 or higher, and GNU make. Also, pgpool-II links libpq library, so it must be installed on a machine used to build pgpool-II. After extracting the source tar ball, execute the configure script. 1) ./configure 2) make 3) make install This generally creats the required file under the directory /usr/local/etc/ Configuring pcp.conf pgpool-II provides the control interface where an administrator can collect pgpool-II status, and terminate pgpool-II processes via network. The pcp.conf is the user/password file for authentication with the interface. All modes require the pcp.conf file to be set. After installing pgpool-II, /usr/local/etc/pcp.conf.sample is created. Change the name of the file to pcp.conf and add your username and the password. cp /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf sysntax of pcp.conf username:[password encrypted in md5] We can generate md5 password using the command pg_md5 Configuring pgpool.conf cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf Rename the sample pgpool config file as mentiomed in above line. # pgpool-II configuration file sample listen_addresses = '*' port = 9999 pcp_port = 9898 socket_dir = '/tmp' pcp_socket_dir = '/tmp' /var/run/postgresql!
backend_socket_dir = '/tmp' recommended!
pcp_timeout = 10 num_init_children = 32 max_pool = 4 child_life_time = 300 connection_life_time = 0 child_max_connections = 0 client_idle_limit = 0 authentication_timeout = 60 logdir = '/tmp'
replication_mode = true replication_timeout = 5000 load_balance_mode = true replication_stop_on_mismatch = false replicate_select = false reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' print_timestamp = true master_slave_mode = false connection_cache = true health_check_timeout = 20 health_check_period = 10 health_check_user = 'postgres' failover_command = '' failback_command = ''
insert_lock = false ignore_leading_white_space = true log_statement = true log_connections = true log_hostname = false parallel_mode = false enable_query_cache = false pgpool2_hostname = '' system_db_hostname = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = '' enable_pool_hba = false recovery_user = 'postgres' recovery_password = 'abcd' recovery_1st_stage_command = 'pgpool_recovery' recovery_2nd_stage_command = 'pgpool_recovery'
recovery_timeout = 90
backend_hostname0 = '192.168.1.2'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/data'
backend_hostname1 = '192.168.1.3'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/data'
Start/Stop pgpool-II
pgpool -n -d > /tmp/pgpool.log 2>&1 & --- to start pgpoolpgpool stop ----- to stop the pgpool To reflect the changes in pgpool.conf, pgpool-II must be restarted First, we need to create a database to be replicated. We will name it "testdb".This database needs to be created on all the nodes. We can use createdb commands through pgpool-II, and the database will be created on all the nodes. createdb -p 9999 testdb (where 9999 is the port on which pgpool-II is ruuning) If database is created successfully on both the node,your configuration of pgpool is done. You can also explore the web base tool(pgpoolAdmin-2.1-beta1) for configuration and monitoring of pgpool-II . For more information you can check the following website. pgpool.projects.postgresql.org/tutorial-en.html
|
