PostgreSQL 安装

[root@pgserver ~] cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.9 (Maipo)
[root@pgserver ~] uname -a
Linux pgserver 3.10.0-1160.el7.x86_64 #1 SMP Tue Aug 18 14:50:17 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux
gmake或者make要求至少3.80版本以上


[root@pgserver ~] gmake -v
GNU Make 3.82
Built for x86_64-redhat-linux-gnu
Copyright (C) 2010  Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.


[root@pgserver ~] which gmake
/bin/gmake


该库文件默认启用。用于在psql命令行工具下，可以通过键盘的上下箭头调出历史命令以及编辑之前的命令。如果不需要此功能的话，可以在configure的时候，带上--without-readline选项。
[root@pgserver ~] rpm -qa|grep readline
readline-devel-6.2-11.el7.x86_64
readline-6.2-11.el7.x86_64

[root@pgserver ~] systemctl status firewalld.service
[root@pgserver ~] systemctl stop firewalld.service
[root@pgserver ~] systemctl disable firewalld.service
[root@pgserver ~] setenforce 0


[root@pgserver ~] cp /etc/selinux/config /etc/selinux/config_`date +"%Y%m%d_%H%M%S"`&& sed -i 's/SELINUX\=enforcing/SELINUX\=disabled/g' /etc/selinux/config

[root@pgserver ~]# cat >> /etc/sysctl.conf << "EOF"
##############for postgresql###########
kernel.shmall =4294967296
kernel.shmmax=135497418752
kernel.shmmni =4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max =7672460
fs.aio-max-nr =1048576
net.ipv4.ip_local_port_range= 9000 65000
net.core.rmem_default= 262144
net.core.rmem_max= 4194304
net.core.wmem_default= 262144
net.core.wmem_max= 4194304
net.ipv4.tcp_max_syn_backlog= 4096
net.core.netdev_max_backlog= 10000
net.ipv4.tcp_timestamps= 0
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_timestamps=1
net.ipv4.tcp_keepalive_time= 72
net.ipv4.tcp_keepalive_probes= 9
net.ipv4.tcp_keepalive_intvl= 7
vm.zone_reclaim_mode=0
vm.dirty_background_bytes= 40960000
vm.dirty_ratio =80
vm.dirty_expire_centisecs= 6000
vm.dirty_writeback_centisecs= 50
vm.swappiness=0
vm.overcommit_memory= 0
vm.overcommit_ratio= 90
EOF


[root@pgserver ~]# sysctl -p

cat >> /etc/security/limits.conf << "EOF"
* soft    nofile 131072 
* hard    nofile 131072 
* soft    nproc  131072 
* hard    nproc  131072 
* soft    core   unlimited 
* hard    core   unlimited 
* soft    memlock 500000000 
* hard    memlock 500000000
EOF

more /etc/security/limits.d/20-nproc.conf
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.


*          soft    nproc     4096
root       soft    nproc     unlimited


echo "* - nproc unlimited" > /etc/security/limits.d/20-nproc.conf

rm -rf etc/yum.repos.d/redhat*
#配置本地源和网络源
cat >/etc/yum.repos.d/local.repo<<EOF
[local]
name=local
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF

[root@pgserver ~]# yum -y install libicu-devel.x86_64 libicu.x86_64 readline-devel zlib-devel gettext-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel perl perl-devel tcl-devel systemd-devel python-devel uuid-devel gcc gcc-c++ make perl-ExtUtils*

[root@pgserver ~] id postgres
id: postgres: no such user


[root@pgserver ~] groupadd postgres -g 1000
[root@pgserver ~] useradd postgres --gid 1000 --uid 1000 --create-home
[root@pgserver ~] echo "postgres" | passwd --stdin postgres
[root@pgserver ~] chown postgres.postgres /home/postgres/.bash_profile

[root@pgserver ~] mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg16,soft}
[root@pgserver ~] chown -R postgres:postgres /postgresql
[root@pgserver ~] chmod -R 775 /postgresql

下载文件并解压
[root@pgserver ~]# su - postgres
[postgres@pgserver ]$ cd postgresql/soft
[postgres@pgserver soft]$ wget https://ftp.postgresql.org/pub/source/v16.4/postgresql-16.4.tar.gz
校验安装包
[postgres@pgserver soft]$ md5sum postgresql-16.4.tar.gz
6849302e68ba1984cee67cd6eab2b930  postgresql-16.4.tar.gz
[postgres@pgserver soft]$ tar -zxvf postgresql-16.4.tar.gz


[postgres@pgserver soft]$ ll
total 31900
drwxrwxr-x. 6 postgres postgres     4096 Sep 11 23:46 postgresql-16.4
-rw-r--r--. 1 postgres postgres 32660355 Sep 11 23:42 postgresql-16.4.tar.gz


[postgres@pgserver soft]$ cd postgresql-16.4
编译
[postgres@pgserver postgresql-16.4]$ ./configure --prefix=/postgresql/pg16 --with-perl  --with-python       /默认端口5432


如果要修改默认端口号使用 --with-pgport
./configure


安装 
如果希望编译所有能编译的东西，包括文档（HTML和手册页）以及附加模块（contrib），这样键入：
[postgres@pgserver postgresql-16.4]$ make world -j 16 && make install-world


查看版本
[postgres@pgserver postgresql-16.4]$ postgresql/pg16/bin/postgres --version
postgres (PostgreSQL) 16.4

[postgres@pgserver ~]$ cat >> ~/.bash_profile <<"EOF"
export PGPORT=5432
export PGDATA=/postgresql/pgdata
export PGHOME=/postgresql/pg16
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
EOF


[postgres@pgserver ~]$ source  ~/.bash_profile

[postgres@pgserver ~]$ postgresql/pg16/bin/initdb -D postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres

[postgres@pgserver ~]$ cat >> postgresql/pgdata/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5432
cluster_name='pg16_5432'
max_connections=200
unix_socket_directories='/postgresql/pgdata'
unix_socket_group = 'postgres'
unix_socket_permissions = 0700
wal_level=logical
archive_mode=always
archive_command='cp %p postgresql/archive/%f'
min_wal_size=1GB
max_wal_size=2GB
wal_keep_segments=128
max_wal_senders=10
hot_standby=on
logging_collector = on
log_directory = 'pg_log'
log_destination=csvlog
log_filename='pg_log_%u.log'
log_file_mode=0600
log_truncate_on_rotation = on
log_rotation_age=1d
log_min_messages=warning
log_min_duration_statement=30s
log_checkpoints=on
log_duration=on
log_lock_waits=on
log_statement='mod'
EOF




[postgres@pgserver ~]$ cat >> postgresql/pgdata/pg_hba.conf << EOF
 # TYPE  DATABASE    USER    ADDRESS       METHOD
 host      all       all    0.0.0.0/0        md5
 EOF

su - postgres
--启动数据库
[postgres@pgserver ~]$ pg_ctl start &     或者  pg_ctl -D /postgresql/pgdata start &
--查看数据库运行状态
[postgres@pgserver ~]$ pg_ctl status
--停止数据库
[postgres@pgserver ~]$ pg_ctl stop
[postgres@pgserver ~]$ pg_ctl stop -m fast


[postgres@pgserver ~]$ pg_ctl status
pg_ctl: server is running (PID: 10705)
/postgresql/pg16/bin/postgres


[postgres@pgserver ~]$ ps -ef |grep 10705
postgres  10705      1  0 15:46 ?        00:00:00 /postgresql/pg16/bin/postgres
postgres  10706  10705  0 15:46 ?        00:00:00 postgres: logger
postgres  10707  10705  0 15:46 ?        00:00:00 postgres: checkpointer
postgres  10708  10705  0 15:46 ?        00:00:00 postgres: background writer
postgres  10710  10705  0 15:46 ?        00:00:00 postgres: walwriter
postgres  10711  10705  0 15:46 ?        00:00:00 postgres: autovacuum launcher
postgres  10712  10705  0 15:46 ?        00:00:00 postgres: archiver
postgres  10713  10705  0 15:46 ?        00:00:00 postgres: logical replication launcher
postgres  10825  10275  0 15:54 pts/0    00:00:00 grep --color=auto 10705

-- 配置系统服务
[postgres@pgserver ~]$ cat > /etc/systemd/system/PG16.service <<"EOF"
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres
After=network.target


[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/postgresql/pgdata
OOMScoreAdjust=-1000
ExecStart=/postgresql/pg16/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/postgresql/pg16/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/postgresql/pg16/bin/pg_ctl reload -D ${PGDATA} -s
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0


[Install]
WantedBy=multi-user.target
EOF

systemctl reload PG16
systemctl enable PG16
systemctl start PG16
systemctl status PG16

[postgres@pgserver log]$ psql
psql: error: connection to server on socket "/postgresql/pgdata/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?


[postgres@pgserver log]$ vim /postgresql/pgdata/postgresql.conf
unix_socket_directories = '/tmp'
修改为
unix_socket_directories = '/postgresql/pgdata/'




[postgres@pgserver log]$ pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped
[postgres@pgserver log]$ pg_ctl start
waiting for server to start.... done
server started
[postgres@pgserver log]$ psql
psql (16.4)
Type "help" for help.


postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)


postgres=# select * from pg_config;
       name        |                                                                           setting
-------------------+-----------------------------------------------------------------------------------
 BINDIR            | /postgresql/pg16/bin
 DOCDIR            | /postgresql/pg16/share/doc
 HTMLDIR           | /postgresql/pg16/share/doc
 INCLUDEDIR        | /postgresql/pg16/include
 PKGINCLUDEDIR     | /postgresql/pg16/include
 INCLUDEDIR-SERVER | /postgresql/pg16/include/server
 LIBDIR            | /postgresql/pg16/lib
 PKGLIBDIR         | /postgresql/pg16/lib
 LOCALEDIR         | /postgresql/pg16/share/locale
 MANDIR            | /postgresql/pg16/share/man
 SHAREDIR          | /postgresql/pg16/share
 SYSCONFDIR        | /postgresql/pg16/etc
 PGXS              | /postgresql/pg16/lib/pgxs/src/makefiles/pgxs.mk
 CONFIGURE         |  '--prefix=/postgresql/pg16'
 CC                | gcc -std=gnu99
 CPPFLAGS          | -D_GNU_SOURCE
 CFLAGS            | -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wm
issing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
 CFLAGS_SL         | -fPIC
 LDFLAGS           | -Wl,--as-needed -Wl,-rpath,'/postgresql/pg16/lib',--enable-new-dtags
 LDFLAGS_EX        |
 LDFLAGS_SL        |
 LIBS              | -lpgcommon -lpgport -lz -lreadline -lpthread -lrt -ldl -lm
 VERSION           | PostgreSQL 16.4