基于源码包安装PostgreSQL

源码包下载地址：https://www.postgresql.org/download/

一、修改系统参数

1.关闭防火墙

[root@postgresql ~]# systemctl status firewalld.service
[root@postgresql ~]# systemctl stop firewalld.service
[root@postgresql ~]# systemctl disable firewalld.service

[root@postgresql ~]# setenforce 0
[root@postgresql ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

2.修改sysctl.conf

[root@postgresql ~]# cat > /etc/sysctl.conf <<"EOF"
vm.swappiness=10
vm.zone_reclaim_mode=0
fs.aio-max-nr = 1048576
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
kernel.shmmax = 1288490188
kernel.shmall = 314572
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
EOF

-- 生效
[root@postgresql ~]# sysctl -p

3.修改limits.conf参数

[root@postgresql ~]# 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 50000000
* hard memlock 50000000
EOF

4.修改用户最大进程数

[root@postgresql ~]# echo "* - nproc unlimited" > /etc/security/limits.d/90-nproc.conf
[root@postgresql ~]# rm -f /etc/security/limits.d/*20*

5.配置PAM

[root@postgresql ~]# echo "session required pam_limits.so" >> /etc/pam.d/login

6.关闭THP

-- root用户下，修改文件/etc/rc.local
[root@postgresql ~]# cat > /etc/rc.local <<"EOF"
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
EOF

-- redhat 7以上，需要修改权限
[root@postgresql ~]# chmod +x /etc/rc.d/rc.local

二、基于源码安装PostgreSQL

1.创建用户

[root@postgresql ~]# groupadd -g 60000 pgsql
[root@postgresql ~]# useradd -u 60000 -g pgsql pgsql
[root@postgresql ~]# echo "pgsql" | passwd --stdin pgsql

2. 创建安装目录

[root@postgresql ~]# mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg13,soft}
[root@postgresql ~]# chown -R pgsql:pgsql /postgresql
[root@postgresql ~]# chmod -R 775 /postgresql

3.上传并解压安装包

-- 注意安装包的权限
[pgsql@postgresql soft]$ ll
-rw-r--r-- 1 pgsql pgsql 27548921 Jan 29 11:14 postgresql-13.2.tar.gz
[pgsql@postgresql soft]$ tar -zxvf postgresql-13.2.tar.gz 

4.安装依赖包

[root@postgresql ~]# yum install -y cmake make gcc zlib gcc-c++ perl readline readline-devel zlib zlib-devel perl python36 tcl openssl ncurses-devel openldap pam

5.编译安装

[root@postgresql ~]# cd postgresql-13.2
[root@postgresql ~]# ./configure --prefix=/postgresql/pg13 --without-readline
[root@postgresql ~]# make -j 8 && make install

• –without-readline 默认时将自动使用GNU Readline库。它允许psql（PostgreSQL的命令行 SQL 解释器）记住你输入的每一个命令并且允许你使用箭头键来找回和编辑之前的命令。如果你不想用它，那么你必需给configure声明–without-readline选项。

6.配置环境变量

[pgsql@postgresql ~]$ cat >>  ~/.bash_profile <<"EOF"
 export LANG=en_US.UTF-8
 export PS1="[\u@\h \W]\$ "
 export PGPORT=5432
 export PGDATA=/postgresql/pgdata
 export PGHOME=/postgresql/pg13
 export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
 export PATH=$PGHOME/bin:$PATH:.
 export DATE=`date +"%Y%m%d%H%M"`
 export MANPATH=$PGHOME/share/man:$MANPATH
 export PGHOST=$PGDATA
 export PGUSER=postgres
 export PGDATABASE=postgres
 EOF
[pgsql@postgresql ~]$ source  ~/.bash_profile

7.初始化数据库

-- pgsql用户下执行：
[pgsql@postgresql ~]$ /postgresql/pg13/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /postgresql/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /postgresql/pg13/bin/pg_ctl -D /postgresql/pgdata -l logfile start

8.修改参数文件

-- 备份原来的参数文件
[pgsql@postgresql ~]$ cp /postgresql/pgdata/postgresql.conf /postgresql/pgdata/postgresql.conf.bak
[pgsql@postgresql ~]$ cp /postgresql/pgdata/pg_hba.conf /postgresql/pgdata/pg_hba.conf.bak
[pgsql@postgresql ~]$ cat >> /postgresql/pgdata/postgresql.conf <<"EOF"
 listen_addresses = '*'
 port=5432
 unix_socket_directories='/postgresql/pgdata'
 logging_collector = on 
 log_directory = 'pg_log'
 log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
 log_truncate_on_rotation = on
 EOF

-- 添加连接规则
[pgsql@postgresql ~]$ cat   > /postgresql/pgdata/pg_hba.conf << EOF
 # TYPE  DATABASE    USER    ADDRESS       METHOD
 local     all       all                    trust
 host      all       all    ::1/128         trust
 host      all       all   127.0.0.1/32     trust
 host      all       all    0.0.0.0/0        md5
 host   replication  all    0.0.0.0/0        md5
 EOF
[pgsql@postgresql ~]$ 

9.启动PG数据库

[pgsql@postgresql ~]$ pg_ctl start
waiting for server to start....2023-02-11 11:21:50.745 CST [11052] LOG:  redirecting log output to logging collector process
2023-02-11 11:21:50.745 CST [11052] HINT:  Future log output will appear in directory "pg_log".
 done
server started

-- 检查状态
[pgsql@postgresql ~]$ pg_ctl status
pg_ctl: server is running (PID: 11052)
/postgresql/pg13/bin/postgres

10.登录PG

[pgsql@postgresql ~]$ psql 
psql (13.2)
Type "help" for help.

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)

postgres=# 

11.停止PG

[pgsql@postgresql ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped

[pgsql@postgresql ~]$ pg_ctl status
pg_ctl: no server running
[pgsql@postgresql ~]$