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等 。
2、yum在线安装
申请环境：
代码语言：javascript复制docker rm -f pg13
docker run -itd --name pg13 -h pg13
-p 5436:5432 -p 34389:3389
-v /sys/fs/cgroup:/sys/fs/cgroup
--privileged=true lhrbest/lhrcentos76:9.2
/usr/sbin/init
docker exec -it pg13 bash
开始安装：
代码语言：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
-- 删除已存在的PG
yum remove -y postgresql* && rm -rf /var/lib/pgsql && rm -rf /usr/pgsql* && userdel -r postgres && groupdel postgres
yum install -y sysbench
-- 安装yum源
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum repolist all | grep pgdg
yum repolist enabled | grep pgdg
sed -i 's/gpgcheck=1/gpgcheck=0/g' /etc/yum.repos.d/pgdg-redhat-all.repo
sed -i 's/repo_gpgcheck = 1/repo_gpgcheck = 0/g' /etc/yum.repos.d/pgdg-redhat-all.repo
-- 安装pg
yum install -y postgresql13 postgresql13-server postgresql13-contrib postgresql13-libs
# yum install -y postgresql9.6 postgresql9.6-server
# yum install postgresql10-server postgresql10-contrib postgresql10 postgresql10.x86_64
-- 验证
[root@lhrpg /]# rpm -aq| grep postgres
postgresql13-server-13.3-1PGDG.rhel7.x86_64
postgresql13-13.3-1PGDG.rhel7.x86_64
postgresql13-libs-13.3-1PGDG.rhel7.x86_64
postgresql13-contrib-13.3-1PGDG.rhel7.x86_64
-- 环境变量
echo "export PATH=/usr/pgsql-13/bin:$PATH" >> /etc/profile
-- （建议安装）安装开发包，若后期需要编译一些插件，例如pg_recovery、pg_dirtyread等都需要该包
-- 必须先安装centos-release-scl-rh
-- 安装成功会在目录/etc/yum.repos.d/下产生文件CentOS-SCLo-scl-rh.repo，若安装不成功则会报错 Requires: llvm-toolset-7-clang >= 4.0.1
yum install -y centos-release-scl-rh
yum install -y postgresql13-devel
# 最终一共需要5个rpm包
[root@lhrpgall /]# rpm -aq| grep postgres
postgresql13-13.12-1PGDG.rhel7.x86_64
postgresql13-libs-13.12-1PGDG.rhel7.x86_64
postgresql13-devel-13.12-1PGDG.rhel7.x86_64
postgresql13-server-13.12-1PGDG.rhel7.x86_64
postgresql13-contrib-13.12-1PGDG.rhel7.x86_64
3、初始化PG，并启动PG
代码语言：javascript复制/usr/pgsql-13/bin/postgresql-13-setup initdb
systemctl enable postgresql-13
systemctl start postgresql-13
systemctl status postgresql-13
4、修改密码
代码语言：javascript复制-- 本地登陆
su - postgres
psql
-- 安装插件
create extension pageinspect;
create extension pg_stat_statements;
select * from pg_extension ;
select * from pg_available_extensions order by name;
-- 修改postgres密码
alter user postgres with password 'lhr'; 或 password
select * from pg_tables;
select version();
5、开放防火墙
代码语言：javascript复制-- 开放防火墙
firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-port
6、配置允许PG远程登录
代码语言：javascript复制-- 配置允许PG远程登录，注意版本：
cat >> /var/lib/pgsql/13/data/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5432
unix_socket_directories='/var/lib/pgsql/13/data'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF
cat << EOF > /var/lib/pgsql/13/data/pg_hba.conf
# 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
systemctl restart postgresql-13
7、登陆测试
代码语言：javascript复制-- 远程登陆
psql -U postgres -h 192.168.66.35 -d postgres -p54327
-- 从Postgresql 9.2开始，还可以使用URI格式进行远程连接：psql postgresql://myuser:mypasswd@myhost:5432/mydb
psql postgresql://postgres:lhr@192.168.66.35:54327/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 -p54327
Password for user postgres:
psql (13.3)
Type "help" for help.
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.3 (Debian 13.3-1.pgdg100 1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
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)
8、配置环境变量
代码语言：javascript复制mkdir -p /home/postgres
chown postgres.postgres /home/postgres -R
sed -i 's|/var/lib/pgsql|/home/postgres|' /etc/passwd
echo "lhr" |passwd --stdin postgres
cat > /home/postgres/.bash_profile <<"EOF"
export PGPORT=5432
export PGHOME=/usr/pgsql-13
export PGDATA=/var/lib/pgsql/13/data
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.UTF-8
export DATE='date "%Y%m%d%H%M"'
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGPASSWORD=lhr
export PGDATABASE=postgres
export PS1="[u@h W]$ "
EOF
chown postgres.postgres /home/postgres/.bash_profile
安装配置完成，若有不懂，可以私聊麦老师。
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
