I will split material of how-to install pgpool on several logical parts
here is the first one:
start install postgres with packages:
yum install postgresql95-devel postgresql95 postgresql95-test postgresql95-libs postgresql95-server postgresql95-contrib pgbouncer.x86_64 repmgr95* pgpool-II-95 pgpool-II-95-extensions
for 9.6
yum install postgresql96-devel postgresql96 postgresql96-test postgresql96-libs postgresql96-server postgresql96-contrib pgbouncer.x86_64 repmgr96*
for 10
yum install postgresql10-devel postgresql10 postgresql10-test postgresql10-libs postgresql10-server postgresql10-contrib pgbouncer.x86_64
create directories and change home directory of postgres user:
mkdir /var/log/postgres chown postgres: /var/log/postgres usermod -m -d /u01 postgres chown postgres: /u01 -R su - postgres cp /etc/skel/.bash* $HOME mkdir -p /u01/postgres/main/pgdata/ mkdir -p /u01/postgres/main/pgdatats/ mkdir -p /u01/postgres/main/recovery mkdir -p /u01/postgres/main/conf mkdir /u01/postgres/main/recovery/backup mkdir /u01/postgres/main/recovery/walarchive mkdir /u01/postgres/main/recovery/dmp
configure bash_profile:
echo "export PGDATA=/u01/postgres/main/pgdata ">> ~/.bash_profile echo "export PGTBS=/u01/postgres/main/pgdatats ">> ~/.bash_profile echo "export PGCFG=/u01/postgres/main/conf ">> ~/.bash_profile echo "export HISTTIMEFORMAT='%Y-%m-%d %H:%M:%S ' ">> ~/.bash_profile echo "export HISTFILESIZE=9000 ">> ~/.bash_profile echo "export PROMPT_COMMAND='history -a' ">> ~/.bash_profile echo "export PS1='\[\e]0;\u@\h: \w\a\][\u@\h \W]$ ' ">> ~/.bash_profile
create dummy database:
/usr/pgsql-9.5/bin/initdb --locale=ru_RU.utf8 --data-checksums
configure init scripts ( or systemd daemon ) to work with our external config directory:
RH6:
/etc/init.d/postgresql-9.5 PGDATA=/u01/postgres/main/pgdata PGOPTS="-c config_file=/u01/postgres/main/conf/postgresql.conf"
RH7:
vim /usr/lib/systemd/system/postgresql-9.5.service
# Note: changing PGDATA will typically require adjusting SELinux
# Note: do not use a PGDATA pathname containing spaces, or you will
Environment=PGDATA=/u01/postgres/main/pgdata
Environment=PGCONF=/u01/postgres/main/conf
ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-10/bin/postmaster -D ${PGDATA} -c config_file=${PGCONF}/postgresql.conf
don’t forget about:
systemctl daemon-reload
Create starter database:
/usr/pgsql-9.5/bin/initdb --lc-collate=ru_RU.UTF8 --locale=en_US.UTF8 --encoding=UTF8 /usr/pgsql-9.5/bin/pg_ctl stop
Move config file /u01/postgres/main/conf/postgresql.conf to $PGCFG directory and modify it :
data_directory='/u01/postgres/main/pgdata' hba_file='/u01/postgres/main/conf/pg_hba.conf' ident_file='/u01/postgres/main/conf/pg_ident.conf'
enable database startup:
chkconfig postgresql-9.5 on
[code]
data_directory=’/u01/postgres/main/pgdata’
hba_file=’/u01/postgres/main/conf/pg_hba.conf’
ident_file=’/u01/postgres/main/conf/pg_ident.conf’
listen_addresses = ‘*’ # what IP address(es) to listen on;
max_connections = 500 # (change requires restart)
shared_buffers = 8GB # min 128kB
work_mem = 64MB # min 64kB
maintenance_work_mem = 2GB # min 1MB
dynamic_shared_memory_type = posix # the default is the first option
wal_level = hot_standby # minimal, archive, hot_standby, or logical
max_wal_size = 3GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 — 1.0
archive_mode = on # enables archiving; off, on, or always
archive_command = ‘cp -i %p /u01/postgres/main/recovery/walarchive/%f < /dev/null’ # command to use to archive a logfile segment
max_wal_senders = 4 # max number of walsender processes
max_replication_slots = 4 # max number of replication slots
hot_standby = on # "on" allows queries during recovery
hot_standby_feedback = on # send info from standby to prevent
effective_cache_size = 24GB
log_destination = ‘stderr’ # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = ‘/var/log/postgres’ # directory where log files are written,
log_filename = ‘postgresql-%Y-%m-%d.log’ # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file with the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
log_min_duration_statement = 300 # -1 is disabled, 0 logs all statements
log_checkpoints = on
log_connections = on
log_disconnections = on
log_line_prefix = ‘%t [%p]: [%l-1] db=%d,appname=%q,user=%u,client=%h’ # special values:
log_statement = ‘ddl’ # none, ddl, mod, all
log_temp_files = 0 # log temporary files equal or larger
log_timezone = ‘Europe/Moscow’
cluster_name = ‘p00nrd’ # added to process titles if nonempty
track_io_timing = on
autovacuum_vacuum_scale_factor = 0.02 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.01 # fraction of table size before analyze
autovacuum_vacuum_cost_delay = 30ms # default vacuum cost delay for
datestyle = ‘iso, mdy’
timezone = ‘Europe/Moscow’
lc_messages = ‘en_US.UTF8’ # locale for system error message
lc_monetary = ‘en_US.UTF8’ # locale for monetary formatting
lc_numeric = ‘en_US.UTF8’ # locale for number formatting
lc_time = ‘en_US.UTF8’ # locale for time formatting
default_text_search_config = ‘pg_catalog.english’
shared_preload_libraries = ‘pg_stat_statements’ # (change requires restart)
pg_stat_statements.max=10000
pg_stat_statements.track=top
pg_stat_statements.track_utility=off
[/code]
2 комментария к “postgresql install how-to”