Ubuntu20.04 PostgreSQL 14 安装配置记录

Ubuntu20.04 PostgreSQL 14 安装配置记录
PostgreSQL 名称来源

It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.

In 1996, the project was renamed to PostgreSQL to reflect its support for SQL.

PostgreSQL 的发音为 [ˈpəʊsɡreˈsɪkl], 中间部分类似于 progress 的发音

服务端安装

参考官方安装说明 https://www.postgresql.org/download/linux/ubuntu/
```
# 创建软件源
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# 添加key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 安装
sudo apt update
sudo apt install postgresql
# 检查
sudo systemctl status postgresql
# 查看端口
sudo netstat -lnp
```
安装后, 系统中会增加一个 postgres 用户, 注意哦, 这个用户是可以登录的. 建议给这个用户设个密码
```
$ more /etc/passwd
...
postgres:x:113:121:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
```
这个用户可以直接访问 postgresql
```
$ sudo su postgres
[sudo] password for milton: 
postgres@ubuntu:/home/milton$ psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type "help" for help.

postgres=# 
postgres-# \q
postgres@ubuntu:/home/milton$
```
配置

查看数据库列表
```
sudo -u postgres psql -l 
```
创建用户

创建用户
```
sudo -u postgres createuser --interactive
[sudo] password for milton: 
Enter name of role to add: dbuser      
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
```
默认情况下, postgresql 会使用与linux用户同名的用户登录, 所以如果创建了用户为 milton, 如果再创建一个名为 milton 的 linux 用户, 可以直接用local方式登录. 如果不创建linux用户, 则只能通过host方式登录

User, Role, Group

在 PostgreSQL 中, user, group, role这三者可以认为是等价的, role和user唯一的区别在于user默认可以登录数据库. 在其他数据库例如Oracle, role只用于授权和分组不能用于登录, 而在 PostgreSQL 中, role 可以用于登录. 在 PostgreSQL 中, CREATE USER 和 CREATE GROUP 其实是 CREATE ROLE 的别名.

要创建一个 PostgreSQL 用户, 可以使用CREATE USER
```
CREATE USER myuser WITH PASSWORD 'secret_passwd';
```
也可以使用CREATE ROLE
```
CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd';
```
这两个语句创建的用户是完全一样的, 这样新创建的用户, 除了有puble role的权限以外没有任何其他权限. 所有的users和roles的权限, 都继承自public role

创建数据库
```
# 使用 postgres 用户
createdb milton
# 或者 sudo
sudo -u postgres createdb milton
# 指定用户
sudo -u postgres createdb testdb -O postgres
```
主配置

对应的配置文件在 /etc/postgresql//main, 当前的版本是14, 路径是 /etc/postgresql/14/main/postgresql.conf
```
sudo vi /etc/postgresql/14/main/postgresql.conf 
```
主配置文件说明 https://www.postgresql.org/docs/14/runtime-config-connection.html

服务IP listen_addresses
```
# 监听所有地址
listen_addresses = '*'
# 监听指定地址
listen_addresses = '192.168.10.20'
```
服务端口 port
```
port = 5432
```
密码加密方式 password_encryption
```
password_encryption = scram-sha-256    # scram-sha-256 or md5
```
用户名命名空间 db_user_namespace, 如果设置为on, 用户创建时可以使用 username@dbname 这样的格式, 用于与数据库绑定. 这时候依然可以创建全局用户, 但是连接时客户端必须加上 @
```
db_user_namespace = off
```
基于主机的认证配置

配置文件 pg_hba.conf, 配置说明 https://www.postgresql.org/docs/14/auth-pg-hba-conf.html

客户端认证由配置文件控制, 通常为名为 pg_hba.conf 的文件, 存储在集群的数据目录(HBA 代表 host-based authentication 的缩写). 当数据目录初始化时, 会生成一个默认的 pg_hba.conf 文件. 可以通过修改主配置文件, 将文件放到其他路径.

pg_hba.conf 文件通常的格式是按行组织的文本记录
- 使用#号标识注释
- 如果一行未结束需要换行, 使用\符号.
- 每行记录由一些空格或tab分隔的字段组成. 如果字段包含空格, 需要用双引号包围.
- 每行记录指定了: 连接类型, 客户端IP范围, 数据库名, 用户名, 验证方式.
- 匹配的第一个记录(匹配连接类型+客户端地址+数据库+用户名)将用于验证
- 没有缺省或再次验证, 只要一个记录被选中, 那么验证就只用这个记录处理, 如果没有命中的记录, 就返回拒绝.
初始配置示例
```
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               trust
# 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
```
记录格式说明

记录可以是这些格式
```
local         database  user  auth-method [auth-options]
host          database  user  address     auth-method  [auth-options]
hostssl       database  user  address     auth-method  [auth-options]
hostnossl     database  user  address     auth-method  [auth-options]
hostgssenc    database  user  address     auth-method  [auth-options]
hostnogssenc  database  user  address     auth-method  [auth-options]
host          database  user  IP-address  IP-mask      auth-method  [auth-options]
hostssl       database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnossl     database  user  IP-address  IP-mask      auth-method  [auth-options]
hostgssenc    database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnogssenc  database  user  IP-address  IP-mask      auth-method  [auth-options]
```
连接方式
- local 使用本机Unix-domain sockets, 如果没有local开头的记录, 则不允许用Unix-domain sockets连接
- host 使用TCP/IP连接, 包含SSL和GSSAPI方式
- hostssl TCP/IP + 使用SSL
- hostnossl TCP/IP + 不使用SSL
- hostgssenc TCP/IP + GSSAPI 加密
- hostnogssenc TCP/IP + 不使用 GSSAPI 加密
数据库, 指定匹配的数据库
- 数据库名指定数据库, 多个数据库使用逗号连接
- all 匹配所有
- sameuser 与此数据库同名的用户, 必须是这个用户
- samerole 与此数据库同名的role, 用户必须属于这个role
- samegroup 以废弃
- replication
- @ 可以用@号指定文件
用户, 指定匹配的用户
- 用户名指定的用户, 多个用户用+号连接
- all 所有用户
- @ 可以用@号指定文件
客户端地址
- 172.20.143.89/32 IPv4地址或范围
- 172.20.1.1/255.0.0.0 IPv4地址范围的另一种格式
- fe80::7a31:c1ff:0000:0000/96 IPv6地址或范围
- all 所有地址
- samenet 同一子网的地址
- samehost 当前主机的所有地址
- .example.com 域名通配
验证方式
- trust 无条件通过
- reject 直接拒绝
- scram-sha-256 使用SCRAM-SHA-256验证
- md5 使用 Perform SCRAM-SHA-256 或 MD5 验证
- password 使用未加密的密码验证, 注意这种方式下, 密码在网络中是明文传输
- gss 使用 GSSAPI 验证, 仅适用于 TCP/IP 连接.
- sspi 使用 SSPI 验证, 仅适用于 Windows
- ident 通过ident服务器, 获取当前客户端操作系统用户名, 并与请求的数据库用户名进行校验, 仅适用于 TCP/IP 连接.
- peer 从操作系统获取用户名, 仅适用于 local 方式的连接
- ldap Authenticate using an LDAP server.
- radius Authenticate using a RADIUS server
- cert 使用 SSL 客户端证书进行验证
- pam 使用操作系统提供的 Pluggable Authentication Modules (PAM) 服务进行验证
- bsd 使用操作系统提供的 BSD Authentication service 进行验证
验证选项
- 根据不同的验证方式提供的选项
客户端安装

Windows下可以直接使用Navicat, pg自己的图形客户端是 pgAdmin, 基于python和javascript.

pgAdmin 4 is a complete rewrite of pgAdmin, built using Python and Javascript/jQuery. A desktop runtime written in NWjs allows it to run standalone for individual users

Ubuntu20.04, pgAdmin4
```
sudo vi /etc/apt/sources.list.d/pgadmin4.list 
# 写入以下内容
deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 main
# 更新并安装
sudo apt update
sudo apt install pgadmin4-desktop
```
数据类型

数值
- 整数 smallint, integer, bigint
- 自增整数 serial, bigserial
- 金额浮点 decimal, numeric
字符
- character(n) 定长字符串
- character varying(n) 相当于mysql的varchar, 有长度限制
- text 不定长,不限长字符串
日期
- timestamp, 这个相当于mysql的datetime
其它

pg还支持坐标, uuid, xml, json等字段格式

命令行操作

连接db
```
psql -h localhost -p 5432 -U postgres runoobdb
```
PostgreSQL 命令行下常用命令

管理相关

创建和密码
```
-- 查看用户列表
\du
\du+
-- 查看role列表和口令, pg通过host登录, 验证的是role的密码
select rolname,rolpassword from pg_authid;
-- 创建用户
CREATE USER myuser WITH PASSWORD 'secret_passwd';
CREATE USER myuser WITH encrypted password 'mypass';
-- 或者使用CREATE ROLE
CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd';
-- 修改用户的数据库口令
ALTER ROLE myuser WITH password 'secret_passwd';
```
可以直接将一个用户的权限赋给另一个用户(以及收回)
```
GRANT myuser TO myuser1;
REVOKE myuser FROM myuser1;
```
通过这个sql查看用户权限之间的引用关系
```
SELECT 
      r.rolname, 
      ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT IN ('pg_signal_backend','rds_iam',
                        'rds_replication','rds_superuser',
                        'rdsadmin','rdsrepladmin')
ORDER BY 1;
```
DATABASE 相关
```
-- 数据库列表
\l
-- 选中数据库
\c [dbname]
-- 创建数据库
CREATE DATABASE dbname;
-- 创建并指定owner
CREATE DATABASE testdb1 OWNER dbuser;
-- 删除数据库
DROP DATABASE dbname
DROP DATABASE IF EXISTS dbname
```
授权数据库给用户(不是owner)
```
GRANT CONNECT ON DATABASE mydatabase TO myuser;
-- 全部权限
GRANT all privileges ON DATABASE mydatabase TO myuser;
```
SCHEMA 相关

每个database都包含一个缺省的schema, 名称为 public, 如果不指定, 则使用这个缺省的 schema.

除了public和用户创建的schema之外, 每个数据库都包含一个pg_catalog的schema, 它包含系统表和所有内置数据类型、函数、操作符. pg_catalog 总是搜索路径中的一部分. 如果它没有明确出现在路径中, 那么它隐含地在所有路径之前搜索. 这样就保证了内置名字总是可以被搜索. 不过, 你可以明确地把pg_catalog放在搜索路径之后, 如果你想使用用户自定义的名字覆盖内置的名字的话.
```
-- 新增
CREATE SCHEMA aStock;
CREATE SCHEMA schema_name AUTHORIZATION user_name;
-- 删除空schema
DROP SCHEMA aStock; 
-- 递归删除非空 schema
DROP SCHEMA aStock CASCADE;

-- 显示搜索路径
SHOW search_path;
-- 变更搜索路径：
SET search_path TO aStock, public;
SET search_path TO myschema;
```
授权schema给用户
```
GRANT USAGE ON SCHEMA myschema TO myuser;
-- 如果用户需要建表权限
GRANT USAGE, CREATE ON SCHEMA myschema TO myuser;
```
TABLE 相关

授权table给用户
```
GRANT SELECT ON TABLE mytable1, mytable2 TO myuser;
-- 如果需要包含myschema下所有table和view
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO myuser;
-- 如果需要增删改
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mytable1, mytable2 TO myuser;
-- 如果需要包含myschema下所有table和view
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO myuser;
```
注意上面的命令, 如果schema下创建了新table, myuser并不能访问, 如果要新建的table也自动授权, 需要使用下面的语句
```
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO myuser;
-- 带增删改
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;
```
- OIDs demoted to normal columns: a glance at the past
  https://postgresql.verite.pro/blog/2019/04/24/oid-column.html
- 表中隐藏的系统字段, 应该是没有网上一些文章说的自带版本控制字段用于乐观锁了
  https://www.postgresql.org/docs/14/ddl-system-columns.html
SEQUENCE 相关
```
GRANT USAGE ON SEQUENCE myseq1, myseq2 TO readwrite;
-- You can also grant permission to all sequences using the following SQL statement:
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
-- To automatically grant permissions to sequences added in the future:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;
```
Compare To MySQL
- Pg 支持多个表从同一个序列中取 id
- Pg 支持 OVER 子句, OVER 子句能简单的解决 "每组取 top 5" 这类问题
- Pg 不存在需要 utf8mb4 显示 emoji 的问题
参考
- 管理user和role https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/
- MySQL PostgreSQL Difference
  - https://www.cnblogs.com/geekmao/p/8541817.html
  - https://www.cnblogs.com/sbj-dawn/p/8053549.html
相关阅读:
列表以及字典的练习
 基于Web的个人网页响应式页面设计与实现 HTML+CSS+JavaScript（web前端网页制作课作业）
vue手动搭建脚手架（保姆式教案）
C. Medium Design Codeforces Round 904 (Div. 2)
Java安全之CC3
进程切换及一些常见概念（面试必问）
mysql8.0数据库中explain查询及优化方案
 知识图谱下的关联交易
 day18 java 集合Collection的List和Set
angular1.0常用总结
原文地址：https://www.cnblogs.com/milton/p/15898117.html

PostgreSQL 名称来源

服务端安装

配置

查看数据库列表

创建用户

User, Role, Group

创建数据库

主配置

基于主机的认证配置

初始配置示例

记录格式说明

客户端安装

Ubuntu20.04, pgAdmin4

数据类型

数值

字符

日期

其它

命令行操作

PostgreSQL 命令行下常用命令

管理相关

DATABASE 相关

SCHEMA 相关

TABLE 相关

SEQUENCE 相关

Compare To MySQL

参考