一、准备
1、创建系统用户postgres
useradd postgres
2、创建目录
磁盘检查，查看磁盘空间
mkdir -p /data/postgres/pgdata
mkdir /data/arch
chown -R postgres:postgres /data/postgres
chmod -R 700 /data/postgres/pgdata
二、步骤
1、安装PG11
# Install the repository RPM:
yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum -y install postgresql14 postgresql14-server
yum install -y postgresql14-contrib.x86_64
2、设置系统参数文件
2.1、PG初始化文件
mv /usr/lib/systemd/system/postgresql-14.service /usr/lib/systemd/system/postgresql-14.service.bak
vim /usr/lib/systemd/system/postgresql-14.service
[Unit]
Description=PostgreSQL 14 database server
Documentation=https://www.postgresql.org/docs/14/static/
After=syslog.target
After=network.target
[Service]
Type=notify
User=postgres
Group=postgres
# 自己定义的数据路径
Environment=PGDATA=/data/postgres/pgdata
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
ExecStart=/usr/pgsql-14/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
2.2、加载配置文件
sudo systemctl daemon-reload
3、初始化PG
# 初始化
su - postgres
initdb -W -D $PGDATA
sudo systemctl enable postgresql-14
注意：如果要接入CLUP管理平台，不要以服务的方式启动，要用pg_ctl start -D $PGDATA 启动
4、修改PG配置文件
4.1、postgresql.conf文件
vim /data/postgres/pgdata/postgresql.conf
listen_addresses = '0.0.0.0' # what IP address(es) to listen on;
port = 1921 # (change requires restart)
max_connections = 2000 # (change requires restart)
superuser_reserved_connections = 13 # (change requires restart)
unix_socket_directories = '.' # comma-separated list of directories
unix_socket_permissions = 0700 # begin with 0 to use octal notation
tcp_keepalives_idle = 60 # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 10 # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 10 # TCP_KEEPCNT;
shared_buffers = 2048MB # min 128kB
maintenance_work_mem = 512MB # min 1MB
shared_preload_libraries = 'pg_stat_statements，wal2json' # (change requires restart)
vacuum_cost_delay = 10 # 0-100 milliseconds
vacuum_cost_limit = 10000 # 1-10000 credits
bgwriter_delay = 10ms # 10-10000ms between rounds
wal_level = hot_standby # minimal, archive, or hot_standby
synchronous_commit = off # synchronization level;
wal_buffers = 16384kB # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10ms # 1-10000 milliseconds
#checkpoint_segments = 128 # in logfile segments, min 1, 16MB each
max_wal_size=800MB
min_wal_size=320MB
#wal_keep_segments=32
archive_mode = on # allows archiving to be done
archive_command = '/bin/date' # command to use to archive a logfile segment
max_wal_senders = 32 # max number of walsender processes
hot_standby = on # "on" allows queries during recovery
max_standby_archive_delay = 300s # max delay before canceling queries
max_standby_streaming_delay = 300s # max delay before canceling queries
wal_receiver_status_interval = 1s # send replies at least this often
hot_standby_feedback = on # send info from standby to prevent
effective_cache_size = 14000MB
log_destination = 'csvlog' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%a.log'
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 = 1s # -1 is disabled, 0 logs all statements
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_line_prefix = '< %m >' # special values:
log_lock_waits = on # log lock waits >= deadlock_timeout
log_statement = 'ddl' # none, ddl, mod, all
log_timezone = 'PRC'
autovacuum = on # Enable autovacuum subprocess? 'on'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
pg_stat_statements.max = 1000
pg_stat_statements.track = all
4.2、PG白名单
vim /data/postgres/pgdata/pg_hba.conf
host replication all 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5
4.3、postgres用户环境变量
vim /home/postgres/.bash_profile
export PGPORT=1921
export PGHOME=/usr/pgsql-14
export PGDATA=/data/postgres/pgdata
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.UTF-8
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
三 相关命令
1、启动PG
# 启动
sudo systemctl start postgresql-14
2、PG连接方式
psql -h localhost -U postgres -d postgres -p 1921
说明：
psql -h [ip] -U [用户名] -d [库名] -p [端口号]
3、启停脚本
# ###
# 配置好postgres用户环境变量后
# ###
# 配置重启，需要配置环境变量
pg_ctl reload
# 重启
pg_ctl restart
# 启动
pg_ctl start
# 停止 -m fast 快速模式
pg_ctl stop -m fast
# ###
#
# ###
# 重启PG
sudo systemctl start postgresql-14
