CONTENT
- CHANGES
Szukaj
#top Instalacja¶
Instalację serwera pgpool można wykonać na różne sposoby: od kompilacji ze źródeł poprzez instalację pojedynczych paczek rpm za pomocą polecenia rpm skończywszy na instalacja z użyciem menedżera pakietów, który zainstaluje wszystkie niezbędne paczki wraz zależnościami.
Celem niniejszego dokumentu nie jest rozważanie różnych metod instalacji tylko nieco usprawnienie użyteczności konfiguracji serwera pgpool po zainstalowaniu oraz wprowadzenie porządku / hierarchi położenia plików z których korzysta usługa w strukturze katalogowej.
Wszystko to zostało opisane poniżej.
#top Poinstalacyjna konfiguracja¶
#top keep oryginal start script¶
mv /etc/init.d/pgpool /etc/init.d/pgpool-rpm cp -av /etc/init.d/pgpool-rpm /etc/init.d/pgpool
#top move include sysconfig below config in init script¶
. /etc/sysconfig/pgpool [...] # Set defaults for configuration variables [...] PGPOOLLOG=/var/log/pgpool.log
. /etc/sysconfig/pgpool [...] # Set defaults for configuration variables [...] PGPOOLLOG=/var/log/pgpool.log if [ -r /etc/sysconfig/pgpool ]; then . /etc/sysconfig/pgpool fi
#top create log directory¶
Create
/var/log/pgpool log directory if does not existsmkdir /var/log/pgpool mv /var/log/pgpool.log /var/log/pgpool
#top chown run directory¶
Change owner run directory if it does not belong to postgres
# 2016-09-14 12:19:02 ERROR: pid 2883: could not open pid file as /var/run/pgpool/pgpool.pid. reason: Permission denied chown postgres:postgres /var/run/pgpool
#top update /etc/sysconfig/pgpool¶
sed 's,^OPTS,#OPTS,g' -i /etc/sysconfig/pgpool sed 's,^PGPOOLLOG,#PGPOOLLOG,g' -i /etc/sysconfig/pgpool echo 'OPTS="-a /etc/pgpool-II/pool_hba.conf -F /etc/pgpool-II/pcp.conf -n"' >> /etc/sysconfig/pgpool echo PGPOOLLOG=/var/log/pgpool/pgpool.log >> /etc/sysconfig/pgpool
after above changes config /etc/sysconfig/pgpool file should look like
# Options for pgpool # -n: don't run in daemon mode. does not detatch control tty # -d: debug mode. lots of debug information will be printed #OPTS=" -d -n" #OPTS=" -n" #PGPOOLLOG=/var/log/pgpool/pgpool.log OPTS="-a /etc/pgpool-II/pool_hba.conf -F /etc/pgpool-II/pcp.conf -n" PGPOOLLOG=/var/log/pgpool/pgpool.log
#top update config /etc/pgpool-II/pgpool.conf file¶
backup oryginal config file
cp -av /etc/pgpool-II/pgpool.conf /etc/pgpool-II/pgpool-rpm.conf
# Host name or IP address to listen on: # '*' for all, '' for no TCP/IP connections sed "s,^listen_addresses.*,listen_addresses = '*',g" -i /etc/pgpool-II/pgpool.conf # comment out default backend sed 's,^backend,#backend,g' -i /etc/pgpool-II/pgpool.conf # - Authentication - sed 's,^enable_pool_hba.*$,enable_pool_hba = on,g' -i /etc/pgpool-II/pgpool.conf # number of pre-forked child process sed 's,^num_init_children.*$,num_init_children = 5,g' -i /etc/pgpool-II/pgpool.conf # Semicolon separated list of queries # to be issued at the end of a session # The default is for 8.3 and later # reset_query_list = 'ABORT; DISCARD ALL' # because above causes below error in postgresql so disable it # ERROR: syntax error at or near "DISCARD" at character 2 sed "s,^reset_query_list,reset_query_list = ''\n#reset_query_list,g" -i /etc/pgpool-II/pgpool.conf # enable log info about connection and hostname sed 's,^\(log_connections.*\)$,log_connections = on\n#\1,g' -i /etc/pgpool-II/pgpool.conf sed 's,^\(log_hostname.*\)$,log_hostname = on\n#\1,g' -i /etc/pgpool-II/pgpool.conf
after above changes config /etc/pgpool-II/pgpool.conf file should look like
# - pgpool Connection Settings - listen_addresses = '*' # Host name or IP address to listen on: # '*' for all, '' for no TCP/IP connections # (change requires restart) port = 9999 # Port number # (change requires restart) socket_dir = '/tmp' # Unix domain socket path # The Debian package defaults to # /var/run/postgresql # (change requires restart) listen_backlog_multiplier = 2 # Set the backlog parameter of listen(2) to # num_init_children * listen_backlog_multiplier. # (change requires restart) serialize_accept = off # whether to serialize accept() call to avoid thundering herd problem # (change requires restart) # - pgpool Communication Manager Connection Settings - pcp_listen_addresses = '*' # Host name or IP address for pcp process to listen on: # '*' for all, '' for no TCP/IP connections # (change requires restart) pcp_port = 9898 # Port number for pcp # (change requires restart) pcp_socket_dir = '/tmp' # Unix domain socket path for pcp # The Debian package defaults to # /var/run/postgresql # (change requires restart) # - Backend Connection Settings - #backend_hostname0 = 'localhost' # Host name or IP address to connect to for backend 0 #backend_port0 = 5432 # Port number for backend 0 #backend_weight0 = 1 # Weight for backend 0 (only in load balancing mode) #backend_data_directory0 = '/var/lib/pgsql/data' # Data directory for backend 0 #backend_flag0 = 'ALLOW_TO_FAILOVER' # Controls various backend behavior # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER #backend_hostname1 = 'host2' #backend_port1 = 5433 #backend_weight1 = 1 #backend_data_directory1 = '/data1' #backend_flag1 = 'ALLOW_TO_FAILOVER' # - Authentication - enable_pool_hba = on # Use pool_hba.conf for client authentication pool_passwd = 'pool_passwd' # File name of pool_passwd for md5 authentication. # "" disables pool_passwd. # (change requires restart) authentication_timeout = 60 # Delay in seconds to complete client authentication # 0 means no timeout.
#------------------------------------------------------------------------------ # POOLS #------------------------------------------------------------------------------ # - Concurrent session and pool size - num_init_children = 5 # Number of concurrent sessions allowed # (change requires restart) max_pool = 4 # Number of connection pool caches per connection # (change requires restart)
#------------------------------------------------------------------------------ # CONNECTION POOLING #------------------------------------------------------------------------------ connection_cache = on # Activate connection pools # (change requires restart) # Semicolon separated list of queries # to be issued at the end of a session # The default is for 8.3 and later reset_query_list = '' #reset_query_list = 'ABORT; DISCARD ALL' # The following one is for 8.2 and before #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
#top start server¶
/etc/init.d/pgpool start
tail -f /var/log/pgpool/pgpool.log
#top Setting Your First Replication¶
Enable Repliation Mode
#------------------------------------------------------------------------------ # REPLICATION MODE #------------------------------------------------------------------------------ replication_mode = on #replication_mode = off
Enable Load Balancing Mode
#------------------------------------------------------------------------------ # LOAD BALANCING MODE #------------------------------------------------------------------------------ load_balance_mode = on #load_balance_mode = off
Setting Database Nodes
# - Backend Connection Settings - #backend_hostname0 = 'localhost' # Host name or IP address to connect to for backend 0 #backend_port0 = 5432 # Port number for backend 0 #backend_weight0 = 1 # Weight for backend 0 (only in load balancing mode) #backend_data_directory0 = '/var/lib/pgsql/data' # Data directory for backend 0 #backend_flag0 = 'ALLOW_TO_FAILOVER' # Controls various backend behavior # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER #backend_hostname1 = 'host2' #backend_port1 = 5433 #backend_weight1 = 1 #backend_data_directory1 = '/data1' #backend_flag1 = 'ALLOW_TO_FAILOVER' backend_hostname0 = '10.31.0.51' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/srv/pgsql/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = '10.31.0.52' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/srv/pgsql/data' backend_flag1 = 'ALLOW_TO_FAILOVER'
enable access do database for users /etc/pgpool-II/pool_hba.conf
host temp temp 10.0.0.3/32 md5 host temp temp 10.50.0.24/32 md5 host temp temp 10.55.0.24/32 md5
pg_md5 usage
pg_md5 PASSWORD --config-file, -f CONFIG-FILE Specify pgpool.conf. --md5auth, -m Produce md5 authentication password. --username, -u USER When producing a md5 authentication password,
create password for user
pg_md5 -f /etc/pgpool-II/pgpool.conf -m -u temp temp cat /etc/pgpool-II/pool_passwd
content /etc/pgpool-II/pool_passwd
temp:md5905c521e05ec8042631a912b71d0454e
/etc/init.d/pgpool reload
/var/log/pgpool/pgpool.log
2016-10-02 10:26:11: pid 4769: FATAL: pgpool is not accepting any new connections 2016-10-02 10:26:11: pid 4769: DETAIL: all backend nodes are down, pgpool requires at least one valid node 2016-10-02 10:26:11: pid 4769: HINT: repair the backend nodes and restart pgpool
/etc/init.d/pgpool restart
test connection
psql -p 9999 -U temp temp
show pool_status;
-----------------
item | value | description
--------------------------------------+---------------------------------------------+---------------------------------------------------------------------------------
listen_addresses | * | host name(s) or IP address(es) to listen on
port | 9999 | pgpool accepting port number
socket_dir | /tmp | pgpool socket directory
pcp_listen_addresses | * | host name(s) or IP address(es) for pcp process to listen on
pcp_port | 9898 | PCP port # to bind
pcp_socket_dir | /tmp | PCP socket directory
enable_pool_hba | 1 | if true, use pool_hba.conf for client authentication
pool_passwd | pool_passwd | file name of pool_passwd for md5 authentication
authentication_timeout | 60 | maximum time in seconds to complete client authentication
ssl | 0 | SSL support
ssl_key | | path to the SSL private key file
ssl_cert | | path to the SSL public certificate file
ssl_ca_cert | | path to a single PEM format file
ssl_ca_cert_dir | | directory containing CA root certificate(s)
num_init_children | 5 | # of children initially pre-forked
listen_backlog_multiplier | 2 | determines the size of the queue for pending connections
serialize_accept | 0 | whether to serialize accept() call
max_pool | 4 | max # of connection pool per child
child_life_time | 300 | if idle for this seconds, child exits
child_max_connections | 0 | if max_connections received, chile exits
connection_life_time | 0 | if idle for this seconds, connection closes
client_idle_limit | 0 | if idle for this seconds, child connection closes
log_destination | stderr | logging destination
log_line_prefix | %t: pid %p: | printf-style string to output at beginning of each log line
log_error_verbosity | 1 | controls how much detail about error should be emitted
client_min_messages | 18 | controls which message should be sent to client
log_min_messages | 19 | controls which message should be emitted to server log
log_connections | 1 | if true, print incoming connections to the log
log_hostname | 1 | if true, resolve hostname for ps and log print
log_statement | 0 | if non 0, logs all SQL statements
log_per_node_statement | 0 | if non 0, logs all SQL statements on each node
log_standby_delay | none | how to log standby delay
syslog_facility | LOCAL0 | syslog local faclity
syslog_ident | pgpool | syslog program ident string
debug_level | 0 | debug message level
pid_file_name | /var/run/pgpool/pgpool.pid | path to pid file
logdir | /var/log/pgpool | PgPool status file logging directory
connection_cache | 1 | if true, cache connection pool
reset_query_list | | queries issued at the end of session
replication_mode | 1 | non 0 if operating in replication mode
replicate_select | 0 | non 0 if SELECT statement is replicated
insert_lock | 1 | insert lock
lobj_lock_table | | table name used for large object replication control
replication_stop_on_mismatch | 0 | stop replication mode on fatal error
failover_if_affected_tuples_mismatch | 0 | failover if affected tuples are mismatch
load_balance_mode | 1 | non 0 if operating in load balancing mode
ignore_leading_white_space | 1 | ignore leading white spaces
white_function_list | | functions those do not write to database
black_function_list | nextval,setval,nextval,setval | functions those write to database
master_slave_mode | 0 | if true, operate in master/slave mode
master_slave_sub_mode | slony | master/slave sub mode
sr_check_period | 0 | sr check period
sr_check_user | nobody | sr check user
sr_check_database | postgres | sr check database
delay_threshold | 0 | standby delay threshold
follow_master_command | | follow master command
database_redirect_preference_list | | redirect by database name
app_name_redirect_preference_list | | redirect by application name
allow_sql_comments | 0 | allow SQL comments
health_check_period | 0 | health check period
health_check_timeout | 20 | health check timeout
health_check_user | nobody | health check user
health_check_database | | health check database
health_check_max_retries | 0 | health check max retries
health_check_retry_delay | 1 | health check retry delay
connect_timeout | 10000 | connect timeout
failover_command | | failover command
failback_command | | failback command
fail_over_on_backend_error | 1 | fail over on backend error
recovery_user | nobody | online recovery user
recovery_1st_stage_command | | execute a command in first stage.
recovery_2nd_stage_command | | execute a command in second stage.
recovery_timeout | 90 | max time in seconds to wait for the recovering node's postmaster
search_primary_node_timeout | 10 | max time in seconds to search for primary node after failover
client_idle_limit_in_recovery | 0 | if idle for this seconds, child connection closes in recovery 2nd statge
relcache_expire | 0 | relation cache expiration time in seconds
relcache_size | 256 | number of relation cache entry
check_temp_table | 1 | enable temporary table check
check_unlogged_table | 1 | enable unlogged table check
use_watchdog | 0 | non 0 if operating in use_watchdog
wd_ipc_socket_dir | /tmp | watchdog ipc socket directory
wd_lifecheck_method | heartbeat | method of watchdog lifecheck
clear_memqcache_on_escalation | 1 | If true, clear all the query caches in shared memory when escalation occurs
wd_escalation_command | | command executed when escalation occurs
wd_de_escalation_command | | command executed when master pgpool resigns occurs
trusted_servers | | upper server list to observe connection
delegate_IP | | delegate IP address of master pgpool
wd_hostname | netboot5.xen.wbcd.pl | Host name or IP address of this watchdog
wd_port | 9000 | watchdog port number
wd_priority | 1 | watchdog priority
wd_interval | 10 | life check interval (second)
ping_path | /bin | path to ping command
if_cmd_path | /sbin | path to interface up/down command
if_up_cmd | ip addr add $_IP_$/24 dev eth0 label eth0:0 | virtual interface up command with full parameters
if_down_cmd | ip addr del $_IP_$/24 dev eth0 | virtual interface down command with full parameters
arping_path | /usr/sbin | path to arping command
arping_cmd | arping -U $_IP_$ -w 1 | send ARP REQUESTi to neighbour host
wd_heartbeat_port | 9694 | port number for receiving heartbeat signal
wd_heartbeat_keepalive | 2 | interval time of sending heartbeat siganl (sec)
wd_heartbeat_deadtime | 30 | deadtime interval for heartbeat siganl (sec)
wd_life_point | 3 | retry times of life check
wd_lifecheck_query | SELECT 1 | lifecheck query to pgpool from watchdog
wd_lifecheck_dbname | template1 | database name connected for lifecheck
wd_lifecheck_user | nobody | watchdog user monitoring pgpools in lifecheck
wd_lifecheck_password | | password for watchdog user in lifecheck
wd_monitoring_interfaces_list | | interfaces to monitor by watchdog
memory_cache_enabled | 0 | If true, use the memory cache functionality, false by default
memqcache_method | shmem | Cache store method. either shmem(shared memory) or Memcached. shmem by default
memqcache_memcached_host | localhost | Memcached host name. Mandatory if memqcache_method=memcached
memqcache_memcached_port | 11211 | Memcached port number. Mondatory if memqcache_method=memcached
memqcache_total_size | 67108864 | Total memory size in bytes for storing memory cache. Mandatory if memqcache_met
memqcache_max_num_cache | 1000000 | Total number of cache entries
memqcache_expire | 0 | Memory cache entry life time specified in seconds. 60 by default
memqcache_auto_cache_invalidation | 1 | If true, invalidation of query cache is triggered by corresponding DDL/DML/DCL(
memqcache_maxcache | 409600 | Maximum SELECT result size in bytes
memqcache_cache_block_size | 1048576 | Cache block size in bytes. 8192 by default
memqcache_cache_oiddir | /var/log/pgpool/oiddir | Tempory work directory to record table oids
memqcache_stats_start_time | Thu Jan 1 01:00:00 1970 +| Start time of query cache stats
| |
memqcache_no_cache_hits | 0 | Number of SELECTs not hitting query cache
memqcache_cache_hits | 0 | Number of SELECTs hitting query cache
white_memqcache_table_list | | tables to memqcache
black_memqcache_table_list | | tables not to memqcache
backend_hostname0 | 10.31.0.51 | backend #0 hostname
backend_port0 | 5432 | backend #0 port number
backend_weight0 | 0.500000 | weight of backend #0
backend_data_directory0 | /srv/pgsql/data | data directory for backend #0
backend_status0 | 2 | status of backend #0
standby_delay0 | 0 | standby delay of backend #0
backend_flag0 | ALLOW_TO_FAILOVER | backend #0 flag
backend_hostname1 | 10.31.0.52 | backend #1 hostname
backend_port1 | 5432 | backend #1 port number
backend_weight1 | 0.500000 | weight of backend #1
backend_data_directory1 | /srv/pgsql/data | data directory for backend #1
backend_status1 | 2 | status of backend #1
standby_delay1 | 0 | standby delay of backend #1
backend_flag1 | ALLOW_TO_FAILOVER | backend #1 flag
heartbeat_device0 | | name of NIC device #0 for sending hearbeat
heartbeat_destination0 | host0_ip1 | destination host for sending heartbeat using NIC device 0
heartbeat_destination_port0 | 9694 | destination port for sending heartbeat using NIC device 0
(139 wierszy)
Zmodyfikowany ostatnio: 2016/10/03 14:25:18 (9 lat temu),
textsize: 28,1 kB,
htmlsize: 37,9 kB
Zapraszam do komentowania, zgłaszania sugestii, propozycji, własnych przykładów, ...
Dodaj komentarzKomentarze użytkowników