Skip to content

postgresql install how-to

postgresql install how-to published on 2 комментария к записи postgresql install how-to

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:

PGOPTS="-c config_file=/u01/postgres/main/conf/postgresql.conf"


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
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 :


enable database startup:

chkconfig postgresql-9.5 on
cat postgresql.conf | egrep -v (^#|^s*$|^s* *#)

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)


2 комментария

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar