【PostgreSQL】下载安装PgSQL
官网下载地址：
1 | https://www.enterprisedb.com/downloads/postgres-postgresql-downloads |
Windows平台
官网直接提供exe安装包，没有手动安装的压缩包
1 | postgresql-14.4-1-windows-x64.exe |
几个重要的安装选项
- 安装界面会指定服务程序和库两个路径，可以自己手动选择
- 指定默认账户的密码
- 选择额外的安装项（这里我选了一个JDBC驱动包，方便DBeaver访问）
上述安装完成之后，使用navicat进行连接访问，发现乱码失败：
参考这篇文章，发现是配置在DB目录的配置
1 | https://blog.csdn.net/m0_59322443/article/details/122967482 |
配置文件位置：
1 | D:\pgsql-14\pg_hba.conf |
更改IPV4的连接的参数
1 2 3 | # IPv4 local connections:# host all all 127.0.0.1/32 scram-sha-256host all all 0.0.0.0/0 scram-sha-256 |
另外，关闭Windows的防火墙
再次连接时访问成功
Centos7平台安装：
Linux在红帽和Centos平台有直接对接的RPM资源，只需要执行官网上的yum命令
1 | https://www.postgresql.org/download/linux/redhat/ |
但是目前官方提供的yum源仅支持Centos6,7两个版本，没有8版本
这是我以Centos7为例安装的过程：
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 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 | [root@centos7-1 ~]# sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm已加载插件：fastestmirror, langpacksRepository base is listed more than once in the configurationRepository updates is listed more than once in the configurationRepository extras is listed more than once in the configurationRepository centosplus is listed more than once in the configurationpgdg-redhat-repo-latest.noarch.rpm | 8.1 kB 00:00:00 正在检查 /var/tmp/yum-root-bnVr0J/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-24.noarch/var/tmp/yum-root-bnVr0J/pgdg-redhat-repo-latest.noarch.rpm 将被安装正在解决依赖关系--> 正在检查事务---> 软件包 pgdg-redhat-repo.noarch.0.42.0-24 将被 安装--> 解决依赖关系完成base/7/x86_64 | 3.6 kB 00:00:00 epel/x86_64/metalink | 8.0 kB 00:00:00 epel/x86_64 | 4.7 kB 00:00:00 epel/x86_64/updateinfo | 1.1 MB 00:00:00 epel/x86_64/primary_db | 7.0 MB 00:00:02 extras/7/x86_64 | 2.9 kB 00:00:00 updates/7/x86_64 | 2.9 kB 00:00:00 updates/7/x86_64/primary_db | 16 MB 00:00:02 依赖关系解决============================================================================================================================================================================================================================================================================================================================================ Package 架构 版本 源 大小============================================================================================================================================================================================================================================================================================================================================正在安装: pgdg-redhat-repo noarch 42.0-24 /pgdg-redhat-repo-latest.noarch 11 k事务概要============================================================================================================================================================================================================================================================================================================================================安装 1 软件包总计：11 k安装大小：11 kDownloading packages:Running transaction checkRunning transaction testTransaction test succeededRunning transaction 正在安装 : pgdg-redhat-repo-42.0-24.noarch 1/1 验证中 : pgdg-redhat-repo-42.0-24.noarch 1/1已安装: pgdg-redhat-repo.noarch 0:42.0-24 完毕！[root@centos7-1 ~]# [root@centos7-1 ~]# sudo yum install -y postgresql14-server已加载插件：fastestmirror, langpacksRepository base is listed more than once in the configurationRepository updates is listed more than once in the configurationRepository extras is listed more than once in the configurationRepository centosplus is listed more than once in the configurationDetermining fastest mirrors * base: mirrors.aliyun.com * epel: mirror.sjtu.edu.cn * extras: mirrors.aliyun.com * updates: mirrors.aliyun.compgdg-common/7/x86_64/signature | 198 B 00:00:00 从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG 检索密钥导入 GPG key 0x442DF0F8: 用户ID : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" 指纹 : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 软件包 : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch) 来自 : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDGpgdg-common/7/x86_64/signature | 2.9 kB 00:00:00 !!! pgdg10/7/x86_64/signature | 198 B 00:00:00 从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG 检索密钥导入 GPG key 0x442DF0F8: 用户ID : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" 指纹 : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 软件包 : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch) 来自 : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDGpgdg10/7/x86_64/signature | 3.6 kB 00:00:00 !!! pgdg11/7/x86_64/signature | 198 B 00:00:00 从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG 检索密钥导入 GPG key 0x442DF0F8: 用户ID : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" 指纹 : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 软件包 : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch) 来自 : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDGpgdg11/7/x86_64/signature | 3.6 kB 00:00:00 !!! pgdg12/7/x86_64/signature | 198 B 00:00:00 从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG 检索密钥导入 GPG key 0x442DF0F8: 用户ID : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" 指纹 : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 软件包 : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch) 来自 : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDGpgdg12/7/x86_64/signature | 3.6 kB 00:00:00 !!! pgdg13/7/x86_64/signature | 198 B 00:00:00 从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG 检索密钥导入 GPG key 0x442DF0F8: 用户ID : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" 指纹 : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 软件包 : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch) 来自 : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDGpgdg13/7/x86_64/signature | 3.6 kB 00:00:00 !!! pgdg14/7/x86_64/signature | 198 B 00:00:00 从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG 检索密钥导入 GPG key 0x442DF0F8: 用户ID : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" 指纹 : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 软件包 : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch) 来自 : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDGpgdg14/7/x86_64/signature | 3.6 kB 00:00:00 !!! (1/11): pgdg11/7/x86_64/group_gz | 245 B 00:00:01 (2/11): pgdg10/7/x86_64/group_gz | 245 B 00:00:01 (3/11): pgdg12/7/x86_64/group_gz | 245 B 00:00:00 (4/11): pgdg13/7/x86_64/group_gz | 246 B 00:00:00 (5/11): pgdg-common/7/x86_64/primary_db | 163 kB 00:00:01 (6/11): pgdg14/7/x86_64/group_gz | 244 B 00:00:00 (7/11): pgdg14/7/x86_64/primary_db | 115 kB 00:00:00 (8/11): pgdg11/7/x86_64/primary_db | 411 kB 00:00:02 (9/11): pgdg12/7/x86_64/primary_db | 290 kB 00:00:01 (10/11): pgdg13/7/x86_64/primary_db | 194 kB 00:00:01 (11/11): pgdg10/7/x86_64/primary_db | 381 kB 00:00:07 正在解决依赖关系--> 正在检查事务---> 软件包 postgresql14-server.x86_64.0.14.4-1PGDG.rhel7 将被 安装--> 正在处理依赖关系 postgresql14-libs(x86-64) = 14.4-1PGDG.rhel7，它被软件包 postgresql14-server-14.4-1PGDG.rhel7.x86_64 需要--> 正在处理依赖关系 postgresql14(x86-64) = 14.4-1PGDG.rhel7，它被软件包 postgresql14-server-14.4-1PGDG.rhel7.x86_64 需要--> 正在处理依赖关系 libpq.so.5()(64bit)，它被软件包 postgresql14-server-14.4-1PGDG.rhel7.x86_64 需要--> 正在处理依赖关系 libicuuc.so.50()(64bit)，它被软件包 postgresql14-server-14.4-1PGDG.rhel7.x86_64 需要--> 正在处理依赖关系 libicui18n.so.50()(64bit)，它被软件包 postgresql14-server-14.4-1PGDG.rhel7.x86_64 需要--> 正在检查事务---> 软件包 libicu.x86_64.0.50.2-4.el7_7 将被 安装---> 软件包 postgresql14.x86_64.0.14.4-1PGDG.rhel7 将被 安装---> 软件包 postgresql14-libs.x86_64.0.14.4-1PGDG.rhel7 将被 安装--> 解决依赖关系完成依赖关系解决============================================================================================================================================================================================================================================================================================================================================ Package 架构 版本 源 大小============================================================================================================================================================================================================================================================================================================================================正在安装: postgresql14-server x86_64 14.4-1PGDG.rhel7 pgdg14 5.5 M为依赖而安装: libicu x86_64 50.2-4.el7_7 base 6.9 M postgresql14 x86_64 14.4-1PGDG.rhel7 pgdg14 1.5 M postgresql14-libs x86_64 14.4-1PGDG.rhel7 pgdg14 268 k事务概要============================================================================================================================================================================================================================================================================================================================================安装 1 软件包 (+3 依赖软件包)总下载量：14 M安装大小：55 MDownloading packages:(1/4): libicu-50.2-4.el7_7.x86_64.rpm | 6.9 MB 00:00:01 warning: /var/cache/yum/x86_64/7/pgdg14/packages/postgresql14-libs-14.4-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY 50% [======================================================================= ] 2.1 MB/s | 7.1 MB 00:00:03 ETA postgresql14-libs-14.4-1PGDG.rhel7.x86_64.rpm 的公钥尚未安装(2/4): postgresql14-libs-14.4-1PGDG.rhel7.x86_64.rpm | 268 kB 00:00:03 (3/4): postgresql14-server-14.4-1PGDG.rhel7.x86_64.rpm | 5.5 MB 00:00:23 (4/4): postgresql14-14.4-1PGDG.rhel7.x86_64.rpm | 1.5 MB 00:02:05 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------总计 115 kB/s | 14 MB 00:02:05 从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG 检索密钥导入 GPG key 0x442DF0F8: 用户ID : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" 指纹 : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 软件包 : pgdg-redhat-repo-42.0-24.noarch (@/pgdg-redhat-repo-latest.noarch) 来自 : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDGRunning transaction checkRunning transaction testTransaction test succeededRunning transaction 正在安装 : libicu-50.2-4.el7_7.x86_64 1/4 正在安装 : postgresql14-libs-14.4-1PGDG.rhel7.x86_64 2/4 正在安装 : postgresql14-14.4-1PGDG.rhel7.x86_64 3/4 正在安装 : postgresql14-server-14.4-1PGDG.rhel7.x86_64 4/4 验证中 : postgresql14-server-14.4-1PGDG.rhel7.x86_64 1/4 验证中 : postgresql14-14.4-1PGDG.rhel7.x86_64 2/4 验证中 : postgresql14-libs-14.4-1PGDG.rhel7.x86_64 3/4 验证中 : libicu-50.2-4.el7_7.x86_64 4/4已安装: postgresql14-server.x86_64 0:14.4-1PGDG.rhel7 作为依赖被安装: libicu.x86_64 0:50.2-4.el7_7 postgresql14.x86_64 0:14.4-1PGDG.rhel7 postgresql14-libs.x86_64 0:14.4-1PGDG.rhel7 完毕！[root@centos7-1 ~]# sudo /usr/pgsql-14/bin/postgresql-14-setup initdbInitializing database ... OK[root@centos7-1 ~]# sudo systemctl enable postgresql-14Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-14.service to /usr/lib/systemd/system/postgresql-14.service.[root@centos7-1 ~]# sudo systemctl start postgresql-14[root@centos7-1 ~]# |
在访问Centos平台的PGSQL也会出现此问题：
1 2 | [root@centos7-1 ~]# psql -p 5432 -U postgrespsql: 错误: 连接到套接字"/var/run/postgresql/.s.PGSQL.5432"上的服务器失败:致命错误: 对用户"postgres"的对等认证失败 |
同样，找到PGSQL的hba配置文件：
1 2 | [root@centos7-1 ~]# find / -name pg_hba.conf/var/lib/pgsql/14/data/pg_hba.conf |
编辑配置文件：
设置了Local本地访问PGSQL时直接进入（trust）
1 2 3 4 5 6 | # "local" is for Unix domain socket connections only#local all all peerlocal all all trust# IPv4 local connections:#host all all 127.0.0.1/32 scram-sha-256host all all 0.0.0.0/0 scram-sha-256 |
修改Postgres默认账号的密码
1 2 3 4 5 6 7 8 | # 之前已经设置了trust模式psql -U postgres# 进入后执行密码更改ALTER USER postgres WITH PASSWORD '123456';# 然后退出quit |
需要重启生效，但是发现 连接不支持授权方法
Autentication method 10 not supported
需要更改成全部trust方式
1 2 3 4 5 6 7 8 9 10 11 12 13 | # TYPE DATABASE USER ADDRESS METHOD# "local" is for Unix domain socket connections onlylocal all all trust# IPv4 local connections:host all all 0.0.0.0/0 trust# IPv6 local connections:host all all ::1/128 trust# Allow replication connections from localhost, by a user with the# replication privilege.local replication all trusthost replication all 127.0.0.1/32 trusthost replication all ::1/128 trust |
另外在pg配置文件中：
1 | /var/lib/pgsql/14/data/postgresql.conf |
监听主机改成任意可访问：
1 | listen_addresses = '*' |
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通