【kong 2.0.2研究】系列二：在CentOS 7上安装&配置PostgreSQL 12，以支持Kong

程序员文章站 2022-06-03 21:15:58

...

1、前言

最近研究Kong，但是发现Kong不支持Mysql(哎，这就是Kong的鸡肋啊，抓紧时间开发个组件支持吧！)，只支持
postgres, cassandra, 或者off（不适用数据库，信息记录到内存里）.

参考：

https://github.com/Kong/kong/issues/4209

https://github.com/Kong/kong/issues/1867

https://github.com/Kong/kong/issues/4209

好吧，网上查了查资料，自己也记录一下，这里就说怎样安装和使用postgres吧。

1.1、本文主要内容

PostgreSQL 12 安装(yum)
PostgreSQL 12 基础配置
PostgreSQL 12 远程访问配置
PostgreSQL 基础管理

1.2、本文环境信息与适用范围

环境信息

软件	版本
CentOS	7.5 Release
PostgreSQL	12.x

适用范围

软件	版本
CentOS	CentOS 7.x
PostgreSQL	9.x-12.x

2、PostgreSQL安装

2.1、导入yum源

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

2.2、安装PostgreSQL服务

sudo yum install -y postgresql12 postgresql12-server

安装PostgreSQL 11就是 yum install postgresql12 postgresql12-server
安装PostgreSQL 9.5就是 yum install postgresql95 postgresql95-server
依此类推

2.3、初始化数据库

sudo /usr/pgsql-12/bin/postgresql-12-setup initdb 
#Initializing database ... OK

2.4、启动PostgreSQL服务

#启动PostgreSQL服务
sudo systemctl start postgresql-12

#设置PostgreSQL服务为开机启动
sudo systemctl enable postgresql-12
#Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.

9.x版本的服务名是postgresql-9.x

3、修改postgres账号密码

PostgreSQL安装成功之后，会默认创建一个名为postgres的Linux用户，初始化数据库后，会有名为postgres的数据库，来存储数据库的基础信息，例如用户信息等等，相当于MySQL中默认的名为mysql数据库。

PostgreSQL数据库创建了一个超级postgres用户作为数据库的管理员，密码随机，所以需要修改密码，方式如下：

3.1 步骤一：登录PostgreSQL

sudo -u postgres psql

进入postgres，提示符变成： postgres=#

3.2 步骤二：修改登录PostgreSQL密码

postgres=# ALTER USER postgres WITH PASSWORD 'postgres123';
ALTER ROLE
postgres=# \q

4、配置远程访问

4.1、开放端口

sudo firewall-cmd --permanent --add-port=5432/tcp  
sudo firewall-cmd --permanent --add-port=80/tcp  
sudo firewall-cmd --reload

4.2、修改IP绑定

#修改配置文件
vi /var/lib/pgsql/12/data/postgresql.conf

#将监听地址修改为*
#默认listen_addresses配置是注释掉的，所以可以直接在配置文件开头加入该行
listen_addresses='*'

当然，此处‘*’也可以改为任何你想开放的服务器IP

4.3、允许所有IP访问

#修改配置文件
vi /var/lib/pgsql/12/data/pg_hba.conf

#设置trust信任和白名单IP
# IPv4 local connections:
host    all            all      127.0.0.1/32      trust
host    all            all      192.168.157.1/32（需要连接的服务器IP）  trust


#在文件尾部加入
host  all  all 0.0.0.0/0 md5

4.4、重启PostgreSQL服务

#重启PostgreSQL服务
sudo systemctl restart postgresql-12

配置完成后即可使用客户端进行连接

4.5 查看链接状态

netstat -apn | grep 5432

tcp6 0 0 :::5432 :::* LISTEN -
unix 2 [ ACC ] STREAM LISTENING 217614522 - /tmp/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 217614520 - /var/run/postgresql/.s.PGSQL.5432

5、Postgre常用命令

5.1 重启PostgreSQL服务

#重启PostgreSQL服务
sudo systemctl start postgresql-12
sudo systemctl stop postgresql-12

sudo systemctl restart postgresql-12

5.2 登录

sudo -u postgres psql

5.3 控制台命令

\h：查看SQL命令的解释，比如\h select。
\?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。

5.4 SQL常用命令

# 创建新表 
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
# 插入数据 
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');
# 选择记录 
SELECT * FROM user_tbl;
# 更新数据 
UPDATE user_tbl set name = '李四' WHERE name = '张三';
# 删除记录 
DELETE FROM user_tbl WHERE name = '李四' ;
# 添加栏位 
ALTER TABLE user_tbl ADD email VARCHAR(40);
# 更新结构 
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# 更名栏位 
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# 删除栏位 
ALTER TABLE user_tbl DROP COLUMN email;
# 表格更名 
ALTER TABLE user_tbl RENAME TO backup_tbl;
# 删除表格 
DROP TABLE IF EXISTS backup_tbl;

5.5 用户与访问授权语法示例

#新建用户
CREATE USER test WITH PASSWORD 'test';

#赋予指定账户指定数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;

#移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb TO test

6 问题

6.1 错误：psql: FATAL: Peer authentication failed for user "postgres"

解决办法如下:

1）. 运行下面的命令编辑pg_hba.conf文件 sudo vi /var/lib/pgsql/12/data/pg_hba.conf

2）. 将

# Database administrative login by Unix domain socket

local all postgres peer

改为

# Database administrative login by Unix domain socket

local all postgres trust

3）. 保存后执行下面的命令重新加载配置文件: sudo /etc/init.d/postgresql reload

6.2 错误：FATAL: password authentication failed for user "postgres"

错误：

conn = psycopg2.connect(database="testdb", user="postgres", password="nopasswd", host="127.0.0.1", port="5432")
  File "/usr/lib/python2.7/dist-packages/psycopg2/__init__.py", line 179, in connect
    connection_factory=connection_factory, async=async)
psycopg2.OperationalError: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

用jdbc连接Postgresql数据库时经常出现这个错误，这主要是由于用户密码认证方式引起的，Postgresql数据库安装好后默认采用md5密码加密认证方式。
解决方法：
1）. 运行下面的命令编辑pg_hba.conf文件 sudo vi /var/lib/pgsql/12/data/pg_hba.conf

2）. 将

# IPv4 local connections:
host all all 127.0.0.1/32 md5

更改为
# IPv4 local connections:
host all all 127.0.0.1/32 trust
3）. 保存后执行下面的命令重新加载配置文件: sudo /etc/init.d/postgresql reload

6.3 错误：psql: FATAL: Ident authentication failed for user "root"

create index for some fields
psql: FATAL: Ident authentication failed for user "root"

解决方法：
1）. 运行下面的命令编辑pg_hba.conf文件 sudo vi /var/lib/pgsql/12/data/pg_hba.conf

2）. 将

# "local" is for Unix domain socket connections only
local all all peer //旧版本该处为ident

更改为
# "local" is for Unix domain socket connections only
local all all trust
3）. 保存后执行下面的命令重新加载配置文件: sudo /etc/init.d/postgresql reload

6.4 错误：psql: FATAL: role root does not exist

在postgresql中创建一个名为root的角色即可：
[email protected]:~$ createuser root
[email protected]:~$ psql
psql (9.3.3)
Type "help" for help.

postgres=# \du
List of roles
Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
long      |                                                | {}
postgres  | Superuser, Create role, Create DB, Replication | {}
root      |                                                | {}

创建完角色之后如果出现以下错误：
createdb: database creation failed: ERROR: permission denied to create database
你需要为postgresql进行一些操作的授权（并非所有用户都经过了创建新数据库的授权。）
在创建角色时就赋予角色一些属性，可以使用下面的方法。
首先切换到postgres 用户。
创建角色bella 并赋予其CREATEDB 的权限。
postgres=# CREATE ROLE bella CREATEDB ;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}

创建角色并赋予多个属性时属性之间要用空格：

postgres=# create role bellaaa CREATEDB superuser Createrole Replication login; //要添加的属性一般要包括如下。
CREATE ROLE

postgres=# \du
                              List of roles
Role name |                   Attributes                    | Member of
-----------+-------------------------------------------------+-----------
bella     | Superuser, Create DB, Cannot login              | {}
bellaa    | Superuser, Create role, Create DB, Cannot login | {}
bellaaa   | Superuser, Create role, Create DB, Replication| {}
postgres | Superuser, Create role, Create DB, Replication | {}
root      | Superuser, Create DB                            | {}

6.5 错误：role 'root' is not permitted to log in.

在执行以下操作后提示FATAL： role 'root' is not permitted to log in.

后直接将postgres所有权限都给root后，仍报错

google后，由于create role时不为用户赋予login权限，赋予login权限即可
postgres=# alter user root login;
ALTER ROLE

6.6 错误：FATAL: database "wangye" does not exist

没有设置PGDATABASE这个环境变量时，使用psql进行登录，默认的数据库是与操作系统用户名一致的，这时候会报错：
[email protected]:~$ psql
psql: FATAL: database "wangye" does not exist

然后设置（export）环境变量PGDATABASE=testDB，这样就默认登录wangye数据库：

[email protected]:~$ su postgres
Password:
[email protected]:/home/wangye$ psql
psql (9.1.14)
Type "help" for help.

[email protected]:/home/wangye$ createdb wangye -E utf-8
[email protected]:/home/wangye$ psql wangye
psql (9.1.14)
Type "help" for help.

参考：

https://www.runoob.com/postgresql/postgresql-syntax.html

https://ken.io/note/centos7-postgresql12-install-and-configuration

https://blog.csdn.net/wangyezi19930928/article/details/20358369