不知道为什么突然想用PostgreSQL了，那就用一下吧

发表于 分类于 ， 阅读次数：

目前也没啥正经用途，感觉以后整活可能会用得上，先在机器上跑起来吧。

安装PostgreSQL

1
sudo apt-get install postgresql postgresql-client

安装完毕后，系统会创建一个数据库超级用户 postgres，密码为空。

访问PostgreSQL，并且可以进行交互，输入\q以退出。

1
2
3
4
5
yankf@overstars:~/db$ sudo -u postgres psql
psql (16.8 (Ubuntu 16.8-0ubuntu0.24.04.1))
Type "help" for help.

postgres=#

创建用户和数据库

新建一个用户kiriya

1
sudo su - postgres -c "createuser kiriya"

新建数据库straycat

1
sudo su - postgres -c "createdb straycat"

修改用户密码（postgres交互访问）

1
alter role kiriya with password '**********';

将数据库straycat授权给kiriya：

1
2
3
4
-- 授予数据库的所有权
grant all privileges on database straycat to kiriya;
-- 授予kiriya关于public的新建和访问权限
GRANT CREATE, USAGE ON SCHEMA public TO kiriya;

自动授权未来新建表的权限

1
2
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT ALL ON TABLES TO kiriya; -- 或指定 SELECT/INSERT 等细粒度权限

刷新权限

1
REASSIGN OWNED BY kiriya TO postgres;

新建schema

先\c确认下自己连的哪个数据库。

1
2
\c straycat  -- 切换到目标库
\dn          -- 检查straycat库中是否有kiriya_schema

连接到数据库

1
psql -U kiriya -d straycat -h localhost -W '*********'

AUTHORIZATION kiriya 指定 kiriya 用户为 Schema 的所有者

1
2
CREATE SCHEMA kiriya_schema AUTHORIZATION kiriya;
grant all privileges on schema kiriya_schema to kiriya;

授权

1
GRANT USAGE, CREATE ON SCHEMA kiriya_schema TO kiriya;

设置未来对象的默认权限

1
2
ALTER DEFAULT PRIVILEGES IN SCHEMA kiriya_schema 
GRANT ALL ON TABLES TO kiriya;

配置用户默认搜索路径

1
ALTER ROLE kiriya SET search_path TO kiriya_schema, public;

配置远程访问

需要修改两个配置文件：postgresql.conf和pg_hba.conf。

修改 postgresql.conf 监听地址

1
vim /etc/postgresql/{版本}/main/postgresql.conf

修改 listen_addresses 参数

1
2
listen_addresses = '*'  # 监听所有网络接口
port = 5432             # 默认端口无需修改

配置客户端访问权限（pg_hba.conf）

1
vim /etc/postgresql/{版本}/main/pg_hba.conf

添加一行

1
2
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             kiriya          0.0.0.0/0               md5

重启

1
systemctl restart postgresql

本地访问

1
psql -h localhost -p 5432 -d straycat -U kiriya

安装后测试连接TCP/IP connections失败解决方法

1
2
3
已失败
DBMS: PostgreSQL (版本 12.16 (Ubuntu 12.16-0ubuntu0.20.04.1))  区分大小写: 普通形式=lower，分隔形式=exact 驱动程序: PostgreSQL JDBC Driver (版本 42.6.0，JDBC4.2)
 Connection to xxx.xxx.xxx.xxx:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

TCP/IP连接建立失败，宿主机并没有监听其他的主机接口。

按如下操作进行设置：

1
vim /etc/postgresql/12/main/postgresql.conf

对配置文件进行编辑

1
2
3
4
5
6
7
8
9
58 # - Connection Settings -
59
60 listen_addresses = '*'      # what IP address(es) to listen on;
61                     # comma-separated list of addresses;
62                     # defaults to 'localhost'; use '*' for all
63                     # (change requires restart)
64 port = 5432             # (change requires restart)
65 max_connections = 100           # (change requires restart)
66 #superuser_reserved_connections = 3 # (change requires restart)

修改 允许访问配置pg_hba.conf

1
2
3
vim /etc/postgresql/12/main/pg_hba.conf
添加
host    all             postgres        0.0.0.0/0               md5

可能是PostgreSQL认为“postgres”不是“all”用户，所以USER里不应该填all……重启

1
systemctl restart postgresql

检查

1
2
netstat -lantp #查看端口是否启动
ps aux | grep postgres #查看数据库服务是否启动

测试链接

1
2
3
已成功
  复制
DBMS: PostgreSQL (版本 12.16 (Ubuntu 12.16-0ubuntu0.20.04.1))  区分大小写: 普通形式=lower，分隔形式=exact 驱动程序: PostgreSQL JDBC Driver (版本 42.6.0，JDBC4.2)  Ping: 14毫秒 SSL: yes