行业知识
Linux系统之安装PostgreSQL数据库
2026-11-08 14:21:26
来源：互联网转载
Linux系统之安装PostgreSQL数据库
- 一、PostgreSQL介绍
- 1.PostgreSQL简介
- 2.PostgreSQL特点
- 二、本次实践介绍
- 1.本次实践介绍
- 2.实践环境介绍
- 三、配置PostgreSQL的yum仓库源
- 1.检查本地是否部署PostgreSQL
- 2.配置镜像源
- 3.检查yum仓库镜像源状态
- 四、安装PostgreSQL
- 1.安装PostgreSQL
- 2.初始化数据库
- 3.启动PostgreSQL数据库
- 4.检查PostgreSQL数据库状态
- 五、PostgreSQL的基本命令
- 1.查看PostgreSQL相关文件
- 2.PostgreSQL的相关命令
- 六、修改用户密码
- 1.切换postgres用户
- 2.登录PostgreSQL数据库
- 3.修改PostgreSQL数据库用户密码
- 七、配置PostgreSQL远程访问
- 1.配置远程访问
- 2.重启PostgreSQL服务
- 3.远���连接PostgreSQL数据库
一、PostgreSQL介绍
1.PostgreSQL简介
PostgreSQL (也叫 Postgres) 是一个自由的对象 - 关系数据库服务器 (数据库管理系统)，它在灵活的 BSD - 风格许可证下发行。它提供了相对其他开放源代码数据库系统 (比如 MySQL 和 Firebird)，和对专有系统比如 Oracle、Sybase、IBM 的 DB2 和 Microsoft SQL Server 的一种选择。
2.PostgreSQL特点
- 它是ORDBMS（面向对象的关系数据库管理系统）。
- 外键引用完整性
- 用户自定义的类型
- 表的继承性
- 视图、规则、子查询
- 多版本并发控制(MVCC)
- 复杂的锁定机制
- 异步复制
- 流式复制（从9.0开始）
- 嵌套事务（保存点）
二、本次实践介绍
1.本次实践介绍
从小白视角，以最快速度在LInux系统上部署一个简单的PostgreSQL数据库。
2.实践环境介绍
hostname IP地址 操作系统版本 PostgreSQL版本 jeven 192.168.3.166 centos 7.6 13.10 三、配置PostgreSQL的yum仓库源
1.检查本地是否部署PostgreSQL
检查本地环境是否部署过PostgreSQL
[root@jeven ~]# rpm -qa | grep postgres[root@jeven ~]#
2.配置镜像源
配置PostgreSQL的yum仓库源，使用的是阿里提供的镜像源。
[root@jeven ~]# rpm -Uvh https://mirrors.aliyun.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpmRetrieving https://mirrors.aliyun.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpmwarning: /var/tmp/rpm-tmp.kcnTVa: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEYPreparing... ################################# [100%]Updating / installing... 1:pgdg-redhat-repo-42.0-32 ################################# [100%][root@jeven ~]# sed -i "s@https://download.postgresql.org/pub@https://mirrors.aliyun.com/postgresql@g" /etc/yum.repos.d/pgdg-redhat-all.repo[root@jeven ~]#
sed -i "s@https://download.postgresql.org/pub@https://mirrors.aliyun.com/postgresql@g" /etc/yum.repos.d/pgdg-redhat-all.repo
生成缓存
yum clean cacheyum makecache
3.检查yum仓库镜像源状态
[root@jeven yum.repos.d]# yum repolist all |grep enable base/7/x86_64 CentOS-7 - Base - mirr enabled: 10,072docker-ce-stable/7/x86_64 Docker CE Stable - x86 enabled: 211elrepo ELRepo.org Community E enabled: 157epel/x86_64 Extra Packages for Ent enabled: 13,770extras/7/x86_64 CentOS-7 - Extras - mi enabled: 515mysql-connectors-community/x86_64 MySQL Connectors Commu enabled: 213mysql-tools-community/x86_64 MySQL Tools Community enabled: 96mysql57-community/x86_64 MySQL 5.7 Community Se enabled: 642pgdg-common/7/x86_64 PostgreSQL common RPMs enabled: 396pgdg11/7/x86_64 PostgreSQL 11 for RHEL enabled: 1,453pgdg12/7/x86_64 PostgreSQL 12 for RHEL enabled: 1,071pgdg13/7/x86_64 PostgreSQL 13 for RHEL enabled: 823pgdg14/7/x86_64 PostgreSQL 14 for RHEL enabled: 552pgdg15/7/x86_64 PostgreSQL 15 for RHEL enabled: 263remi-safe Safe Remi's RPM reposi enabled: 5,134updates/7/x86_64 CentOS-7 - Updates - m enabled: 4,857
四、安装PostgreSQL
1.安装PostgreSQL
使用yum直接PostgreSQL13的版本
[root@jeven yum.repos.d]# yum -y install postgresql13-serverLoaded plugins: fastestmirror, langpacksLoading mirror speeds from cached hostfile * elrepo: hkg.mirror.rackspace.com * remi-safe: ftp.riken.jpResolving Dependencies--> Running transaction check---> Package postgresql13-server.x86_64 0:13.10-1PGDG.rhel7 will be installed--> Processing Dependency: postgresql13-libs(x86-64) = 13.10-1PGDG.rhel7 for package: postgresql13-server-13.10-1PGDG.rhel7.x86_64--> Processing Dependency: postgresql13(x86-64) = 13.10-1PGDG.rhel7 for package: postgresql13-server-13.10-1PGDG.rhel7.x86_64--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql13-server-13.10-1PGDG.rhel7.x86_64--> Running transaction check---> Package postgresql13.x86_64 0:13.10-1PGDG.rhel7 will be installed---> Package postgresql13-libs.x86_64 0:13.10-1PGDG.rhel7 will be installed--> Finished Dependency ResolutionDependencies Resolved================================================================================================================================================== Package Arch Version Repository Size==================================================================================================================================================Installing: postgresql13-server x86_64 13.10-1PGDG.rhel7 pgdg13 5.4 MInstalling for dependencies: postgresql13 x86_64 13.10-1PGDG.rhel7 pgdg13 1.4 M postgresql13-libs x86_64 13.10-1PGDG.rhel7 pgdg13 385 kTransaction Summary==================================================================================================================================================Install 1 Package (+2 Dependent packages)Total download size: 7.1 MInstalled size: 31 MDownloading packages:warning: /var/cache/yum/x86_64/7/pgdg13/packages/postgresql13-libs-13.10-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEYPublic key for postgresql13-libs-13.10-1PGDG.rhel7.x86_64.rpm is not installed(1/3): postgresql13-libs-13.10-1PGDG.rhel7.x86_64.rpm | 385 kB 00:00:00 (2/3): postgresql13-13.10-1PGDG.rhel7.x86_64.rpm | 1.4 MB 00:00:01 (3/3): postgresql13-server-13.10-1PGDG.rhel7.x86_64.rpm | 5.4 MB 00:00:02 --------------------------------------------------------------------------------------------------------------------------------------------------Total 2.6 MB/s | 7.1 MB 00:00:02 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDGImporting GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project " Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-32.noarch (installed) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDGRunning transaction checkRunning transaction testTransaction test succeededRunning transactionWarning: RPMDB altered outside of yum. Installing : postgresql13-libs-13.10-1PGDG.rhel7.x86_64 1/3 Installing : postgresql13-13.10-1PGDG.rhel7.x86_64 2/3 Installing : postgresql13-server-13.10-1PGDG.rhel7.x86_64 3/3 Verifying : postgresql13-server-13.10-1PGDG.rhel7.x86_64 1/3 Verifying : postgresql13-13.10-1PGDG.rhel7.x86_64 2/3 Verifying : postgresql13-libs-13.10-1PGDG.rhel7.x86_64 3/3 Installed: postgresql13-server.x86_64 0:13.10-1PGDG.rhel7 Dependency Installed: postgresql13.x86_64 0:13.10-1PGDG.rhel7 postgresql13-libs.x86_64 0:13.10-1PGDG.rhel7 Complete!
2.初始化数据库
初始化PostgreSQL数据库
[root@jeven ~]# postgresql-13-setup initdbInitializing database ... OK
3.启动PostgreSQL数据库
启动PostgreSQL数据库，并设置开机自启。
[root@jeven ~]# systemctl enable postgresql-13[root@jeven ~]# systemctl start postgresql-13[root@jeven ~]#
4.检查PostgreSQL数据库状态
检查当前PostgreSQL服务状态
[root@jeven ~]# systemctl status postgresql-13.service ● postgresql-13.service - PostgreSQL 13 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled) Active: active (running) since Mon 2023-03-27 21:07:33 CST; 1min 34s ago Docs: https://www.postgresql.org/docs/13/static/ Process: 13256 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 13264 (postmaster) Tasks: 8 Memory: 17.6M CGroup: /system.slice/postgresql-13.service ├─13264 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/ ├─13266 postgres: logger ├─13268 postgres: checkpointer ├─13269 postgres: background writer ├─13270 postgres: walwriter ├─13271 postgres: autovacuum launcher ├─13272 postgres: stats collector └─13273 postgres: logical replication launcher Mar 27 21:07:33 jeven systemd[1]: Starting PostgreSQL 13 database server...Mar 27 21:07:33 jeven postmaster[13264]: 2023-03-27 21:07:33.325 CST [13264] LOG: redirecting log output to logging collector processMar 27 21:07:33 jeven postmaster[13264]: 2023-03-27 21:07:33.325 CST [13264] HINT: Future log output will appear in directory "log".Mar 27 21:07:33 jeven systemd[1]: Started PostgreSQL 13 database server.五、PostgreSQL的基本命令
1.查看PostgreSQL相关文件
检查PostgreSQL服务端的所有相关文件所在目录
rpm -ql postgresql13-server
检查PostgreSQL客户端的所有相关文件所在目录
rpm -ql postgresql13
2.PostgreSQL的相关命令
PostgreSQL的所有相关命令文件
[root@jeven ~]# /usr/pgsql-13/bin/clusterdb pg_basebackup pg_dumpall pg_test_timing postmastercreatedb pgbench pg_isready pg_upgrade psqlcreateuser pg_checksums pg_receivewal pg_verifybackup reindexdbdropdb pg_config pg_resetwal pg_waldump vacuumdbdropuser pg_controldata pg_restore postgres initdb pg_ctl pg_rewind postgresql-13-check-db-dir pg_archivecleanup pg_dump pg_test_fsync postgresql-13-setup
六、修改用户密码
1.切换postgres用户
切换postgres用户
[root@jeven ~]# su - postgres-bash-4.2$
2.登录PostgreSQL数据库
登录后，提示符变为 ‘postgres=#’
[root@jeven ~]# su - postgres-bash-4.2$ psql -U postgrespsql (13.10)Type "help" for help.postgres=#
3.修改PostgreSQL数据库用户密码
修改PostgreSQL数据库用户密码
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';ALTER ROLE
七、配置PostgreSQL远程访问
1.配置远程访问
修改/var/lib/pgsql/13/data/postgresql.conf配置文件，开启远程访问。
删除localhost，改为监听所有，或者自己指定的服务器IP地址。
-bash-4.2$ grep listen /var/lib/pgsql/13/data/postgresql.conflisten_addresses = '*'# what IP address(es) to listen on;
修改/var/lib/pgsql/13/data/pg_hba.conf文件，新增以下两行：
host all all 127.0.0.1/32 identhost all all 0.0.0.0/0 md5
2.重启PostgreSQL服务
重启PostgreSQL服务
systemctl restart postgresql-13.service
3.远程连接PostgreSQL数据库
使用psql命令远程连接PostgreSQL数据库
[root@jeven ~]# psql -h192.168.3.166 -Upostgres -WPassword: psql (13.10)Type "help" for help.postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres(3 rows)postgres=#
上一篇：宝塔面板怎么测网速？
