部署postgresql 12.3

# 安装基础依赖
wget -O /etc/yum.repos.d/epel.repo https://cloud.tencent.com/repo/epel-7.repo
yum clean all && yum makecache fast
yum -y install systemd-devel bash-completion bash-completion-extras \
               wxBase pgagent_11 gcc make perl-ExtUtils-Embed readline-devel \
               zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel \
               openssl-devel tcl-devel python-devel openssh-clients

# 内核优化
# 注: kernel.shmmax的值取决于你的系统配置，我机器是16G内存，故设置为13958643712 B（13G）
cat >> /etc/sysctl.conf << EOF
kernel.shmmax = 13958643712
kernel.shmall = 4194304
kernel.shmmni = 4096
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
kernel.sem = 50100 64128000 50100 1280
EOF

sysctl -p

# 最大可打开文件数及进程数等
$ cat >> /etc/security/limits.conf << EOF
*               soft    nofile          65536
*               hard    nofile          65536
*               soft    nproc           65536
*               hard    nproc           65536
*               soft    memlock         unlimited
*               hard    memlock         unlimited
EOF

# 创建数据库运行用户及数据存放目录
$ useradd postgres
echo Y51KEBzU | passwd --stdin postgres
mkdir -p /apps/usr/postgres/data

# 安装pgsql
wget https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v12.3/postgresql-12.3.tar.gz
curl https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v12.3/postgresql-12.3.tar.gz.md5 | md5sum -c

tmp_dir=$(mktemp -d)
tar zxf postgresql-12.3.tar.gz -C ${tmp_dir} --strip-components=1
cd ${tmp_dir}
./configure \
        --prefix=/apps/usr/postgres \
        --with-pgport=5432 \
        --with-systemd \
        --with-perl \
        --with-tcl \
        --with-python \
        --with-openssl \
        --with-pam \
        --with-libxml \
        --with-libxslt \
        --enable-thread-safety \
        --with-wal-blocksize=16 \
        --with-blocksize=8
gmake world && gmake install-world

cat >> /etc/profile << "EOF"
export PGPORT=5432
export PGDATA=/apps/usr/postgres/data
export PGHOME=/apps/usr/postgres
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.utf8
EOF
source /etc/profile 
chown -R postgres /apps/usr/postgres



# 查看sql版本
$ psql --version
psql (PostgreSQL) 12.3

$ su - postgres
# 设置postgresql默认用户密码，使用openssl指令生成密码文件
openssl rand -base64 20 | cut -c 6-15 > /home/postgres/.psqlpasswd
initdb -D /apps/usr/postgres/data/ --pwfile=/home/postgres/.psqlpasswd -A md5
# --pwfile: 指定密码文件
# -A: 指定登录验证方式，默认为trust，表示无需密码即可登录到数据库
# 注：data目录可以不存在，但绝对不允许其目录下有数据，这样将会初始化失败。

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data  # 注意这里替换为你的实际路径
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

cd /apps/usr/postgres/data
cp postgresql.conf{,.bak}   # 备份默认配置文件
cat /apps/usr/postgres/data/postgresql.conf   # 修改配置文件如下

listen_addresses = '*'   # *表示监听所有地址
port = 5432      # 监听端口
max_connections = 1000    # 指定最大连接数
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
shared_buffers = 1GB   # 设置共享内存缓冲区的内存量，建议值为系统总内存的25％
temp_buffers = 80MB    # 设置用于每个数据库会话中的临时缓冲区的最大内存量
max_files_per_process = 65535   # 设置允许每个服务器子进程同时打开的文件的最大数量。不能大于ulimt -n的数值
bgwriter_delay = 100ms 
bgwriter_lru_maxpages = 1000
bgwriter_flush_after = 0
synchronous_commit = off
wal_level = replica
archive_mode = on
# 以下路径替换为实际备份路径
archive_command = 'test ! -f /apps/usr/postgres/backup/incre/%f && cp %p /apps/usr/postgres/backup/incre/%f'
full_page_writes = on
wal_buffers = -1
wal_writer_delay = 100ms
wal_writer_flush_after = 256kB
checkpoint_timeout = 30min
max_wal_size = 5GB
min_wal_size = 1GB
log_destination = 'stderr'
logging_collector = on
log_directory = 'logs'
log_filename = 'postgresql-%Y-%m-%d_%H.log'
log_file_mode = 0640
log_rotation_age = 1d 
log_rotation_size = 100MB
log_truncate_on_rotation = off
log_min_messages = notice
log_min_error_statement = notice
log_min_duration_statement = 3s
log_checkpoints = on
log_connections = on
log_error_verbosity = verbose
log_line_prefix = '%m '
log_timezone = 'PRC'
track_activities = on
log_autovacuum_min_duration = 3s 
autovacuum_max_workers = 4
autovacuum_naptime = 45s
autovacuum_vacuum_scale_factor = 0.1 
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 1600000000 
autovacuum_multixact_freeze_max_age = 1600000000
vacuum_freeze_table_age = 1500000000
vacuum_multixact_freeze_table_age = 1500000000
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
default_text_search_config = 'pg_catalog.english'

$ vim /apps/usr/postgres/data/pg_hba.conf   # 添加访问权限
host    all             all             192.168.20.5/24           md5

$ pg_ctl start     # 启动数据库
$ ss -lnput | grep 5432     # 确定端口在监听
tcp    LISTEN     0      128       *:5432                  *:*                   users:(("postgres",pid=31928,fd=3))
tcp    LISTEN     0      128      :::5432                 :::*                   users:(("postgres",pid=31928,fd=4))


# 访问测试
$ cat ~/.psqlpasswd     # 查看密码
S1w4ktZhUV
$ psql -h 127.0.0.1
Password for user postgres:      # 输入查看到的密码
postgres=# \l    # 查看所有库
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

$ pg_pid=$(head -1 $PGDATA/postmaster.pid)
$ pmap ${pg_pid} | awk '/rw-s/ && /zero/ {print $2}'
1140976K
$ grep ^Hugepagesize /proc/meminfo
Hugepagesize:       2048 kB

$ echo "vm.nr_hugepages=558" >> /etc/sysctl.conf 
$ sysctl -p
$ grep Huge /proc/meminfo   # 验证大页面分配情况
AnonHugePages:     61440 kB
HugePages_Total:     558
HugePages_Free:      558
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB