1、安装概述
PG安装方法很多，和MySQL类似，给用户提供很大的选择空间。如：RPM包安装(在线、离线)、源码编译安装、系统自带、二进制、NDB安装等。
官网：https://www.postgresql.org/
rpm包：https://yum.postgresql.org/rpmchart.php
yum源：https://yum.postgresql.org/repopackages.php
源码包：https://www.postgresql.org/ftp/source/
打开 PostgreSQL 官网 https://www.postgresql.org/，点击菜单栏上的 Download ，可以看到这里包含了很多平台的安装包，包括 Linux、Windows、Mac OS等 。
生产库建议源码安装，以下方法已在9.4到15版本测试过。
2、下载源码包
源码下载地址：https://www.postgresql.org/ftp/source/
image-20210527164155508
代码语言：javascript复制-- 下载源码包
wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz
wget https://ftp.postgresql.org/pub/source/v14.9/postgresql-14.9.tar.gz
wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz
wget https://ftp.postgresql.org/pub/source/v12.6/postgresql-12.6.tar.gz
wget https://ftp.postgresql.org/pub/source/v11.11/postgresql-11.11.tar.gz
wget https://ftp.postgresql.org/pub/source/v10.16/postgresql-10.16.tar.gz
wget https://ftp.postgresql.org/pub/source/v9.6.21/postgresql-9.6.21.tar.gz
wget https://ftp.postgresql.org/pub/source/v9.4.26/postgresql-9.4.26.tar.gz
3、创建用户和安装目录
代码语言：javascript复制-- 创建用户
groupadd -g 60000 pgsql
useradd -u 60000 -g pgsql pgsql
echo "lhr" | passwd --stdin pgsql
-- 创建目录
mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg13,soft}
chown -R pgsql:pgsql /postgresql
chmod -R 775 /postgresql
4、编译PG
代码语言：javascript复制-- 安装一些依赖包
yum install -y cmake make gcc zlib gcc-c perl readline readline-devel zlib zlib-devel
perl python36 tcl openssl ncurses-devel openldap pam perl-IPC-Run libicu-devel
-- 编译
su - pgsql
cd /postgresql/soft
tar zxvf postgresql-13.3.tar.gz
cd postgresql-13.3
./configure --prefix=/postgresql/pg13
make -j 8 && make install
make world -j 8 && make install-world
5、配置环境变量
代码语言：javascript复制-- 配置环境变量
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
source ~/.bash_profile
6、初始化数据库
代码语言：javascript复制-- 初始化
su - pgsql
/postgresql/pg13/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres
7、配置远程登陆
代码语言：javascript复制-- 修改参数
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-%a.log'
log_truncate_on_rotation = on
EOF
cat >> /postgresql/pgdata/pg_hba.conf << EOF
# TYPE DATABASE USER ADDRESS METHOD
local all all 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
-- 启动
su - pgsql
pg_ctl start
pg_ctl status
pg_ctl stop
-- 修改密码
pg_ctl start
psql
alter user postgres with password 'lhr';
exit
-- 或：
nohup /postgresql/pg13/bin/postgres -D /postgresql/pgdata > /postgresql/pg13/pglog.out 2>&1 &
8、配置系统服务
代码语言：javascript复制-- 配置系统服务
cat > /etc/systemd/system/PG13.service <<"EOF"
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network.target
[Service]
Type=forking
User=pgsql
Group=pgsql
Environment=PGPORT=5432
Environment=PGDATA=/postgresql/pgdata
OOMScoreAdjust=-1000
ExecStart=/postgresql/pg13/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/postgresql/pg13/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/postgresql/pg13/bin/pg_ctl reload -D ${PGDATA} -s
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
EOF
使用systemctl启动PG：
代码语言：javascript复制systemctl daemon-reload
systemctl enable PG13
systemctl start PG13
systemctl status PG13
已启动：
代码语言：javascript复制[root@lhrpg postgresql]# systemctl status PG13
● PG13.service - PostgreSQL database server
Loaded: loaded (/etc/systemd/system/PG13.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2021-05-26 21:32:26 CST; 1s ago
Docs: man:postgres(1)
Process: 12473 ExecStart=/postgresql/pg13/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
Main PID: 12475 (postgres)
CGroup: /docker/ea25b97cfa732f1ec6f188098898e8f26b64cd1bf1e9b408898d722c8a5917fb/system.slice/PG13.service
├─12475 /postgresql/pg13/bin/postgres -D /postgresql/pgdata -p 5433
├─12476 postgres: logger
├─12478 postgres: checkpointer
├─12479 postgres: background writer
├─12480 postgres: walwriter
├─12481 postgres: autovacuum launcher
├─12482 postgres: stats collector
└─12483 postgres: logical replication launcher
May 26 21:32:26 lhrpg systemd[1]: Starting PostgreSQL database server...
May 26 21:32:26 lhrpg pg_ctl[12473]: 2021-05-26 21:32:26.617 CST [12475] LOG: redirecting log output to logging collector process
May 26 21:32:26 lhrpg pg_ctl[12473]: 2021-05-26 21:32:26.617 CST [12475] HINT: Future log output will appear in directory "pg_log".
May 26 21:32:26 lhrpg systemd[1]: Started PostgreSQL database server.
9、登陆测试
代码语言：javascript复制-- 远程登陆
psql -U postgres -h 192.168.66.35 -d postgres -p5432
-- 从Postgresql 9.2开始，还可以使用URI格式进行远程连接：psql postgresql://myuser:mypasswd@myhost:5432/mydb
psql postgresql://postgres:lhr@192.168.66.35:5432/postgres
其中-h参数指定服务器地址，默认为127.0.0.1，默认不指定即可，-d指定连接之后选中的数据库，默认也是postgres，-U指定用户，默认是当前用户，-p 指定端口号，默认是"5432"，其它更多的参数选项可以执行： ./bin/psql --help 查看。
代码语言：javascript复制C:Userslhrxxt>psql -U postgres -h 192.168.66.35 -d postgres -p5432
Password for user postgres:
psql (13.3)
Type "help" for help.
postgres=# l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------- ---------- ---------- ------------------- ------------------- -----------------------
postgres | postgres | UTF8 | Chinese_China.936 | Chinese_China.936 |
template0 | postgres | UTF8 | Chinese_China.936 | Chinese_China.936 | =c/postgres
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Chinese_China.936 | Chinese_China.936 | =c/postgres
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# CREATE DATABASE lhrdb WITH OWNER=postgres ENCODING='UTF-8';
CREATE DATABASE
postgres=# c lhrdb
You are now connected to database "lhrdb" as user "postgres".
lhrdb=#
lhrdb=# create table student (
lhrdb(# id integer not null,
lhrdb(# name character(32),
lhrdb(# number char(5),
lhrdb(# constraint student_pkey primary key (id)
lhrdb(# );
CREATE TABLE
lhrdb=#
lhrdb=# d student
Table "public.student"
Column | Type | Collation | Nullable | Default
-------- --------------- ----------- ---------- ---------
id | integer | | not null |
name | character(32) | | |
number | character(5) | | |
Indexes:
"student_pkey" PRIMARY KEY, btree (id)
lhrdb=#
lhrdb=# INSERT INTO student (id, name, number) VALUES (1, '张三', '1023');
INSERT 0 1
lhrdb=# SELECT * FROM student WHERE id=1;
id | name | number
---- ------------------------------------ --------
1 | 张三 | 1023
(1 row)
安装配置完成，若有不懂，可以私聊麦老师。
源码编译脚本汇总
代码语言：javascript复制groupadd pg15
useradd -g pg15 pg15
echo "lhr" | passwd --stdin pg15
mkdir -p /pg15/{pgdata,archive,scripts,backup,pg15,soft}
chown -R pg15:postgres /pg15
chmod -R 775 /pg15
yum install -y cmake make gcc zlib gcc-c perl readline readline-devel zlib zlib-devel
perl python36 tcl openssl ncurses-devel openldap pam
su - pg15
cd /pg15/soft
tar zxvf postgresql-14.9.tar.gz
cd postgresql-14.9/
./configure --prefix=/pg15/pg15
make -j 16 && make install
make world -j 16 && make install-world
cat >> ~/.bash_profile <<"EOF"
export LANG=en_US.UTF-8
export PS1="[u@h W]$ "
export PGPORT=5440
export PGDATA=/pg15/pgdata
export PGHOME=/pg15/pg15
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
source ~/.bash_profile
/pg15/pg15/bin/initdb -D /pg15/pgdata -E UTF8 --locale=en_US.utf8 -U postgres
cat >> /pg15/pgdata/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5440
unix_socket_directories='/pg15/pgdata'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF
cat >> /pg15/pgdata/pg_hba.conf << EOF
# TYPE DATABASE USER ADDRESS METHOD
local all all 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
su - pg15
pg_ctl start
pg_ctl status
psql
alter user postgres with password 'lhr';
exit
pg_ctl stop
-- 或：
nohup /pg15/pg15/bin/postgres -D /pg15/pgdata > /pg15/pg15/pglog.out 2>&1 &
cat > /etc/systemd/system/pg15.service <<"EOF"
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network.target
[Service]
Type=forking
User=pg15
Group=postgres
Environment=PGPORT=5440
Environment=PGDATA=/pg15/pgdata
OOMScoreAdjust=-1000
ExecStart=/pg15/pg15/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/pg15/pg15/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/pg15/pg15/bin/pg_ctl reload -D ${PGDATA} -s
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl enable pg15
systemctl start pg15
systemctl status pg15
psql -U postgres -h 192.168.66.35 -d postgres -p5432
PG环境汇总
麦老师的镜像中包括了PG 9.4、9.6、10、11、12、13、14、15各个版本，都是采用源码安装，可以直接使用，满足各类测试要求：
代码语言：javascript复制docker rm -f lhrpgall
docker run -itd --name lhrpgall -h lhrpgall
-p 25432-25445:5432-5445 -p 122:22 -p 189:3389
-v /sys/fs/cgroup:/sys/fs/cgroup
--restart=always
--privileged=true lhrbest/lhrpgall:3.0
/usr/sbin/init
docker exec -it lhrpgall bash
systemctl status pg94 pg96 pg10 pg11 pg12 pg13 pg14 pg15
systemctl status postgresql-13.service
[root@lhrpgall /]# ps -ef|grep postgres | grep bin
pg15 229 1 0 12:11 ? 00:00:00 /pg15/pg15/bin/postgres -D /pg15/pgdata -p 5440
pg10 231 1 0 12:11 ? 00:00:00 /pg10/pg10/bin/postgres -D /pg10/pgdata -p 5436
pg13 232 1 0 12:11 ? 00:00:00 /pg13/pg13/bin/postgres -D /pg13/pgdata -p 5433
pg14 235 1 0 12:11 ? 00:00:00 /pg14/pg14/bin/postgres -D /pg14/pgdata -p 5439
pg94 243 1 0 12:11 ? 00:00:00 /pg94/pg94/bin/postgres -D /pg94/pgdata -p 5438
pg11 244 1 0 12:11 ? 00:00:00 /pg11/pg11/bin/postgres -D /pg11/pgdata -p 5435
pg96 247 1 0 12:11 ? 00:00:00 /pg96/pg96/bin/postgres -D /pg96/pgdata -p 5437
pg12 249 1 0 12:11 ? 00:00:00 /pg12/pg12/bin/postgres -D /pg12/pgdata -p 5434
