合 使用yum来安装PostgreSQL数据库（从PG9.4到PG16各个版本通用）
Tags： PGPostgreSQL安装部署yum安装统信UOSRockyLinux
👉 本文共约1681个字，系统预计阅读时间或需7分钟。
目录
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在线安装
若是PG16，不能使用centos 7，请参考：https://www.dbaup.com/postgresql-16shujukudegezhonganzhuangfangshihuizongyumbianyidockerdeng.html
申请环境：
1 2 3 4 5 6 7 | docker rm -f pg15 docker run -itd --name pg15 -h pg15 \ -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 pg15 bash |
开始安装：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | -- 一些依赖包 yum install -y cmake make gcc gcc-c++ perl readline readline-devel zlib zlib-devel \ perl python36 tcl openssl ncurses-devel openldap pam perl-IPC-Run libicu-devel libarchive libzstd -- 删除已存在的PG yum remove -y postgresql* && rm -rf /var/lib/pgsql && rm -rf /usr/pgsql* && userdel -r postgres && groupdel postgres yum install -y sysbench -- 安装yum源：https://www.postgresql.org/download/linux/redhat/ # yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm 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 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 -- 其它国产OS，需要修改yum源的配置 # sudo sed -i 's/\$releasever/8/g' /etc/yum.repos.d/pgdg-redhat-all.repo -- 若是CentOS8和9，则需要禁用内置的PostgreSQL模块，否则会报错 All matches were filtered out by modular filtering for argument sudo dnf -qy module disable postgresql yum repolist all | grep pgdg yum repolist enabled | grep pgdg -- 安装pg yum install -y postgresql15 postgresql15-server postgresql15-contrib postgresql15-libs # yum install -y postgresql16 postgresql16-server postgresql16-contrib postgresql16-libs # 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 -- 环境变量 echo "export PATH=/usr/pgsql-15/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 postgresql15-devel # 最终一共需要5个rpm包 [root@lhrpgall /]# rpm -aq| grep postgres postgresql15-server-15.4-1PGDG.rhel7.x86_64 postgresql15-15.4-1PGDG.rhel7.x86_64 postgresql15-contrib-15.4-1PGDG.rhel7.x86_64 postgresql15-libs-15.4-1PGDG.rhel7.x86_64 postgresql15-devel-15.4-1PGDG.rhel7.x86_64 |
离线安装
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | rpm -ivh sudo*.rpm lrzsz*.rpm rpm -ivh net-tools-*.rpm rpm -ivh libicu*.rpm --nodeps rpm -ivh libzstd*.rpm rpm -ivh systemd-sysv-*.rpm --nodeps rpm -ivh perl*.rpm --nodeps rpm -ivh python3*.rpm --nodeps rpm -ivh libxslt*.rpm rpm -ivh openssl*.rpm --nodeps rpm -ivh llvm*.rpm devtoolset*.rpm scl-utils*.rpm libedit-devel*.rpm --nodeps rpm -ivh cpp*.rpm libmpc*.rpm mpfr*.rpm glibc-devel*.rpm automake*.rpm autoconf*.rpm libtool*.rpm --nodeps rpm -ivh cmake*.rpm make*.rpm gcc*.rpm \ tcl*.rpm ncurses-devel*.rpm libarchive*.rpm --nodeps yum localinstall -y *.rpm --skip-broken /* wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-8-x86_64/postgresql15-15.5-2PGDG.rhel8.x86_64.rpm wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-8-x86_64/postgresql15-server-15.5-2PGDG.rhel8.x86_64.rpm wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-8-x86_64/postgresql15-contrib-15.5-2PGDG.rhel8.x86_64.rpm wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-8-x86_64/postgresql15-devel-15.5-2PGDG.rhel8.x86_64.rpm wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-8-x86_64/postgresql15-libs-15.5-2PGDG.rhel8.x86_64.rpm dnf localinstall ./*.rpm */ */ rpm -ivh postgresql15-libs-15.4-1PGDG.rhel7.x86_64.rpm rpm -ivh postgresql15-15.4-1PGDG.rhel7.x86_64.rpm --nodeps rpm -ivh postgresql15-server-15.4-1PGDG.rhel7.x86_64.rpm rpm -ivh postgresql15-contrib-15.4-1PGDG.rhel7.x86_64.rpm rpm -ivh postgresql15-devel-15.4-1PGDG.rhel7.x86_64.rpm --nodeps echo "export PATH=/usr/pgsql-15/bin:$PATH" >> /etc/profile |
3、初始化PG，并启动PG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | localedef -c -f UTF-8 -i zh_CN zh_CN.utf8 localedef -c -f UTF-8 -i zh_CN zh_CN.gbk localedef -c -f UTF-8 -i zh_CN zh_CN.gb18030 localedef -c -f UTF-8 -i zh_CN zh_CN.gb2312 locale -a | grep zh_CN /usr/pgsql-15/bin/postgresql-15-setup initdb -- 重新初始化中文 --locale=en_US.utf8 su - postgres rm -rf /var/lib/pgsql/15/data/ /usr/pgsql-15/bin/initdb -D /var/lib/pgsql/15/data -E UTF8 --locale=zh_CN.UTF-8 -U postgres --data-checksums systemctl enable postgresql-15 systemctl restart postgresql-15 systemctl status postgresql-15 |
4、修改密码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- 本地登陆 su - postgres psql -- 安装插件 create extension pageinspect; create extension pg_stat_statements; select * from pg_extension ; select * from pg_available_extensions order by name limit 2; -- 修改postgres密码 alter user postgres with password 'lhr'; 或 \password create user lhr with password 'lhr' superuser; select * from pg_tables; select version(); |
5、开放防火墙
1 2 3 4 5 6 7 8 9 10 11 12 13 | -- 开放防火墙 firewall-cmd --add-port=5432/tcp --permanent firewall-cmd --reload firewall-cmd --list-port systemctl status firewalld.service systemctl stop firewalld.service echo 'export LANG=en_US.UTF-8' >> /etc/profile sed -i s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config setenforce 0 |
6、配置允许PG远程登录
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | -- 配置允许PG远程登录，注意版本： cat >> /var/lib/pgsql/15/data/postgresql.conf <<"EOF" listen_addresses = '*' port=5432 unix_socket_directories='/var/lib/pgsql/15/data' logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on EOF cat << EOF > /var/lib/pgsql/15/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-15 systemctl status postgresql-15 |
7、配置环境变量
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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 echo "postgres ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers cat > /home/postgres/.bash_profile <<"EOF" export PGPORT=5432 export PGHOME=/usr/pgsql-15 export PGDATA=/var/lib/pgsql/15/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 systemctl restart postgresql-15 systemctl status postgresql-15 |
8、登陆测试
1 2 3 4 5 6 | -- 远程登陆 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 查看。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
