在CentOS上安装PostgreSQL并配置扩展

PostgreSQL

选择安装版本

咱的服务器系统是centos7.9

# cat /etc/centos-release
CentOS Linux release 7.9.2009 (Core)

官网的安装命令：PostgreSQL: Linux downloads (Red Hat family)

选择好版本

把安装命令复制，执行

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:
sudo yum install -y postgresql14-server

# Optionally initialize the database and enable automatic start:
#命令依次是 数据集初始化、开机自启、启动服务
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

配置

通过以上步骤，自带了一个postgres管理员用户

su postgres 切换到此用户，psql登录

# su postgres
bash-4.2$ psql
psql (14.2)
Type "help" for help.
postgres=# 

配置文件路径

cd /var/lib/pgsql/14/data

修改监听IP和端口

vi postgresql.conf

第60行，注意去掉开头的#
listen_addresses = '*'          # what IP address(es) to listen on;

修改连接权限

pg_hba.conf最后面的

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             0.0.0.0/0            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

这里咱修改了IPv4的连接， 改成了0.0.0.0/0，允许远程访问。当然，服务器也要开放5432端口。

method是认证方式，9.2的默认是ident，通过登录账户的用户名匹配，需要改成md5或者scram-sha-256

重启服务

service postgresql-14 restart

参考：

(5条消息) PostgreSQL远程连接配置管理/账号密码分配（解决：致命错误: 用户 “postgres” Ident 认证失败）…_weixin_33851177的博客-CSDN博客
PostgreSQL新手入门 - 阮一峰的网络日志 (ruanyifeng.com)

使用GUI远程登录

因为咱是在服务器上配置好的，如果本地要使用命令行也行，GUI能更直观地显示一些信息

这里以pgAdmin为例，点击server，选择Add New Server

配置相关的信息

如果不记得postgres的密码，可以参考下面的用户管理，修改postgres的密码

用户管理

登录

psql -U postgres #用户名

显示用户信息

use the \du to list all user accounts (or roles) in the current PostgreSQL database server

The \du+ command adds an additional column called description.

更改密码

postgres=# alter role "username" WITH PASSWORD '*****';
ALTER ROLE
postgres=# 

添加用户

CREATE USER 用户名 WITH PASSWORD '*****';
CREATE role 用户名 WITH PASSWORD '*****';

使用user是带登录权限,role是不带登录权限

如果要授权登录

alter user 用户名 with login

授权

postgres=# grant all privileges on database 数据库名 to 用户名;

几个常用的命令

启动、重启、状态

service postgresql-14 start
service postgresql-14 restart
service postgresql-14 status

---

与MySQL对比

(1)列出所有的数据库

mysql: show databases
psql: \l或\list

(2)切换数据库

mysql: use dbname
psql: \c dbname

(3)列出当前数据库下的数据表

mysql: show tables
psql: \d

(4)列出指定表的所有字段

mysql: show columns from table name
psql: \d tablename

(5)查看指定表的基本情况

mysql: describe tablename
psql: \d+ tablename

(6)退出登录

mysql: quit 或者\q
psql:\q

(7)查看pgsl版本

pg_ctl --version

(8)命令行登陆数据库

psql -h 192.168.2.125 -p 5432 <dbname> <username>

(9)修改密码

psql登陆

然后， \password postgres

参考：

CentOS7安装PostgreSQL - Ryan.Miao - 博客园 (cnblogs.com)

卸载

如果配置错了，或者版本选错了，想重新来过

You can also use grep in conjunction with YUM’s list command to return a list of all package instances of PostgreSQL:

yum list installed | grep postgres

Uninstall the PostgreSQL package using YUM remove

yum remove {POSTGRESS-PACKAGE NAME}

How To Completely Uninstall PostgreSQL | ObjectRocket

PostGIS

如果安装PostgreSQL是安装上面步骤，那么仅需执行

yum install postgis31_14

31是postGIS的版本即3.1，14是对应的PostgreSql的版本（一定要对应，不然又会重新安装一个PostgreSql）

安装好的扩展会在/usr/pgsql-14/share/extension路径下

参考：

Installing PostGIS 3.1 and PostgreSQL 13 on CentOS 8 | Devrim Gunduz’s PostgreSQL Blog (planetpostgresql.org)

pgRouting

基本与PostGIS相同，仅需执行

yum install pgrouting_14

14对应PostgreSql的版本

当然，可以编译源文件，pgRouting/pgrouting: Repository contains pgRouting library. Development branch is “develop”, stable branch is “master” (github.com)

加载扩展（GUI）

选择需要增加扩展的数据库，点击菜单栏Tools->Query Tool，出现Query窗口，输入

create extension postgis;
create extension pgrouting;

如果执行成功