PostgreSQL(01): Ubuntu20.04/22.04 PostgreSQL 安装配置记录

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
# 安装之前可以看一下有哪些版本
apt-cache search postgresql
# 安装, 20.04默认是postgresql-14, 22.40是postgresql-15
sudo apt install postgresql
# 检查
sudo systemctl status postgresql
# 查看端口
sudo netstat -lnp

主配置

对应的配置文件在 /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

客户端安装

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

登录验证

如果是同系统上的postgres用户, 直接在命令行下运行 psql 就可以进入pg的命令行, 这是peer类型的访问
如果是其他用户, 必须在pg中存在同名role/user才能进行peer类型访问
非postgres的其他用户, 可以通过host方式访问, 访问时必须指定数据库, 命令如下, 输入完需要按提示输入口令

psql -h localhost -U dbuser -d testdb

访问验证机制

pg的验证和权限是分开的, 一个用户首先要能通过验证进行访问, 然后才是根据授权访问对应的数据库
pg的访问验证设置, 默认的配置文件位于 /etc/postgresql/[版本号]/main/pg_hba.conf, 版本号可能是14, 15等
只有当这个role/user在pg中存在, 才能对pg进行访问. pg安装后, 默认的role为postgres, 是一个superuser

验证配置文件说明

配置文件 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范围, 数据库名, 用户名, 验证方式.
匹配的第一个记录(匹配连接类型+客户端地址+数据库+用户名)将用于验证
没有缺省或再次验证, 只要一个记录被选中, 那么验证就只用这个记录处理, 如果没有命中的记录, 就返回拒绝.

注意, 验证只要匹配了一个记录(方式), 就用这个方式处理, 不会有多次匹配.

初始配置示例

这是默认的配置, 注意几点

因为local ... peer是本地socket, 优先级最高, 用户不是postgres就是all, 如果在命令行运行psql, 会基于用户的linux用户名, 用这些规则进行验证
linux下的postgres用户, 因为在pg中的同名user/role存在, 因此可以通过验证
如果用其它用户访问psql, 例如ubuntu用户运行psql -l, 会报failed: FATAL: role "ubuntu" does not exist错误, 通过sudo -u postgres createuser --interactive创建一个名为ubuntu的user之后, 就可以运行psql -l了.

# "local" is for Unix domain socket connections only
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
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 进行验证

验证选项

根据不同的验证方式提供的选项

开放所有用户从所有网络, 用加密口令访问所有数据库

在 pg_hba.conf 添加下面这行 It allows access to all databases for all users with an encrypted password:

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 scram-sha-256

默认的 postgres 用户

安装后, Ubuntu系统中会增加一个 postgres 用户

$ more /etc/passwd
...
postgres:x:113:121:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

因为pg中也存在 postgres 这个superuser, 这个用户可以直接访问 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

创建用户

创建用户, 这个 createuser 命令只是一个perl脚本, 专门用于 postgresql 创建用户, 准确的说是对应 local peer的用户, 因为里面不设置密码

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

在命令行下执行psql或postgres时, postgresql 会用linux用户名进行验证, 所以如果创建了用户为 milton, 如果再创建一个名为 milton 的 linux 用户就可以直接访问. 如果不对应linux用户, 则只能通过host方式登录

User, Role, Group

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

CREATE USER = CREATE ROLE + LOGIN PERMISSION

也可以使用 CREATE USER 创建一个 PostgreSQL 用户

CREATE USER myuser;
CREATE USER myuser WITH PASSWORD 'secret_passwd';

也可以使用以下等价的 CREATE ROLE 命令

CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd';

这两个语句创建的用户是完全一样的, 这样新创建的用户, 除了有public role的权限以外没有任何其他权限. 所有的users和roles的权限, 都继承自public role

创建数据库

如果是系统命令行, 用createdb命令

# 使用 postgres 用户
createdb milton
# 或者 sudo
sudo -u postgres createdb milton
# 指定用户
sudo -u postgres createdb testdb -O postgres

如果是在数据库命令行, 用这样的sql

create database testdb

创建数据库后, 修改数据库的owner

# entering sql console using 'psql'
$ psql
psql (16.3 (Ubuntu 16.3-1.pgdg20.04+1), server 14.12 (Ubuntu 14.12-1.pgdg20.04+1))
Type "help" for help.
# then input 'ALTER DATABASE testdb OWNER TO dbuser;'
postgres=# ALTER DATABASE testdb OWNER TO dbuser;

数据类型

数值

整数 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

备份和恢复

备份数据库

pg_dump -h localhost -p 5432 -U username -d database_name -f "path-to-file.dump"

恢复数据库

pg_restore -h localhost -p 5432 -U username -d new_database_name -1 "path-to-file.dump"

备份指定的表

pg_dump --host localhost --port 5432 --username dbuser --format plain --verbose --file "path-to-file.dump" --table public.table_name dbname
# or
pg_dump -h localhost -p 5432 -U dbuser -F plain -v -f "path-to-file.dump" -t public.table_name dbname

对应的恢复要用 psql, 如果用pg_restore, 会提示pg_restore: error: input file appears to be a text format dump. Please use psql.

psql -U dbuser -d dbname -1 -f path-to-file.dump

如果提示psql:psit.sql:27485: invalid command \N, 加上-v ON_ERROR_STOP=1参数

psql -v ON_ERROR_STOP=1 -U dbuser -d dbname -1 -f path-to-file.dump

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

posted on 2022-02-15 21:01 Milton 阅读(2640) 评论(0) 编辑收藏举报

刷新页面返回顶部

Milton