PostgreSQL

PostgreSQL (后简称 pg)是用 C 语言实现的跨平台、功能完善、开源数据库管理系统。首个完善正式版本发布于 1997 年，它前身可追溯到 1982 年美国加州伯克利大学 Ingres 数据库管理系统项目，由于诞生自学院派，它对 SQL 标准实现更严谨。

安装二进制包

Ubuntu 上安装客户端 sudo apt install postgresql-client-14

Ubuntu 上安装服务端 sudo apt install postgresql

macOS Homebrew 安装服务端 brew install postgresql@16

Windows 通过 EDB installer 安装 12.22

解压 zip 到目录，如 c:\postgresql-12.22
初始化数据目录 c:\postgresql-12.22\pgsql\bin\initdb.exe --locale=C -E UTF-8 C:\data\pg12
启动服务 c:\postgresql-12.22\pgsql\bin\pg_ctl -D C:\data\pg12 -l c:\data\log\pg\main.log start
进入交互命令行 c:\postgresql-12.22\pgsql\bin\psql -d postgres

容器安装

shell

docker pull postgres:16

mkdir -p $HOME/v/data/pg
mkdir -p $HOME/v/log/pg

docker run -d \
--restart unless-stopped \
--name pg \
-e POSTGRES_PASSWORD=secret \
-e POSTGRES_HOST_AUTH_METHOD=md5 \
-e PGDATA=/var/lib/postgresql/data/pgdata \
-v $HOME/v/log/pg:/var/lib/postgresql/data \
-p 5432:5432 \
postgres

从源码编译安装

以 Ubuntu 系统为例从源码编译安装 PostgreSQL 服务端。

安装 GNU Readline 和 OpenSSL 依赖 sudo apt install libreadline-dev libssl-dev

下载源码编译安装

shell

wget https://ftp.postgresql.org/pub/source/v10.5/postgresql-10.5.tar.bz2
tar zxf postgresql-10.5.tar.bz2
cd postgresql-10.5

./configure --prefix=/opt/postgresql-10.5 --with-openssl && make && make install

sudo mkdir -p /data/
sudo chmod 777 /data/

sudo useradd postgres

PGDATA=/data/pg/5432
sudo mkdir -p $PGDATA/{data,var}
sudo chown -R postgres /data/pg

sudo passwd postgres
su postgres

./bin/initdb --locale=C -E UTF-8 --pgdata=$PGDATA/data

修改连接相关配置 $PGDATA/data/postgresql.conf

txt

listen_addresses = '*'
max_connections = 1000
port = 5432

管理员可临时改 ALTER SYSTEM SET max_connections = num_of_connections;

修改鉴权 $PREFIX/data/pg_hba.conf

启动进程 ./bin/pg_ctl -D $PGDATA/data/ -l $PGDATA/var/logfile start

创建名为数据库 d1 ，授权名为 user_ro 的账户只读(SELECT) 访问 metrics 表，密码为 secret

sql

CREATE DATABASE d1;
CREATE USER user_ro WITH ENCRYPTED PASSWORD 'secret';
CREATE TABLE metrics (name text);
GRANT select ON metrics TO user_ro;

测试连接 PGPASSWORD='secret' psql -h 127.0.0.1 -p 5432 -U postgres -c 'select version();'

初始化数据目录、创建数据库以及用户都可通过 $PREFIX/bin/ 下程序完成，也可通过 SQL 语句完成 ls -1 $PREFIX/bin/

txt

clusterdb
createdb
createuser
dropdb
dropuser
initdb
...

如创建数据库 PGPASSWORD='secret' ./bin/createdb -h 127.0.0.1 -p 5432 -U postgres mydb

术语

user
role
database \l
relation \d
object schema
- table
- view

schema -> floor plan database -> house table -> room

pg 使用术语 role （角色）指代其他数据库中的 user / account (用户/账号)：

user 可默认有 login 可登录，role 不可；
role 可以作为 group of accounts 概念使用。

常用配置

主配置文件路径 /etc/postgresql/<version>/main/postgresql.conf

认证配置文件路径 /etc/postgresql/<version>/main/pg_hba.conf

打印日志

txt

# 打印日志
logging_collector = on

# 日志打印级别
log_min_messages = info

慢查询日志

修改主配置文件 postgresql.conf ，全局记录执行超过 1000 毫秒(1 秒)的查询 log_min_duration_statement = 1000

动态重载配置 SELECT pg_reload_conf();

指定数据库记录执行超过 1000 毫秒(1 秒)的查询 ALTER DATABASE d1 SET log_min_duration_statement = 1000;

测试

sql

\c d1
SELECT pg_sleep(2);

输出类似

txt

grep duration /var/log/postgresql/postgresql-<version>-main.log

2049-06-20 19:00:48.497 CST [513908] postgres@d1 LOG:  duration: 2044.210 ms  statement: SELECT pg_sleep(2);

允许来自所有连接

修改主配置文件 listen_addresses = '*'

管理用户账号和权限

以名为 jane 用户为例。

修改默认账号 postgres 用户的密码

pg 使用了与系统分离的帐号管理，创建 db 需要使用一个 pg 内部授权帐号， initdb 执行后会自动在 pg 内创建同名 pg 授权帐号 postgres，密码为空。如果没有，可手动创建 createuser --superuser --login --no-password postgres

shell

sudo -u postgres psql
# 注：输入要包含以下单引号
ALTER USER postgres with encrypted password 'secret';

修改认证配置文件 pg_hba.conf ，将 local all postgres peer 为 local all postgres md5

重启服务后生效 sudo systemctl restart postgresql

测试连接

shell

sudo apt install postgresql-client
PGPASSWORD='secret' psql -h 127.0.0.1 -p 5432 -U postgres -l

添加或修改管理员用户

OPTION A: create a super user via SQL: CREATE ROLE jane WITH LOGIN SUPERUSER PASSWORD 'password';

OPTION B: create a super user via createuser

su postgres
createuser -s -i -d -r -l -w jane
psql -c "ALTER ROLE jane WITH PASSWORD 'secret';"

options:

-s, --superuser
-i, --inherit     role inherits privileges of roles it is a member of (default)
-d, --createdb    role can create new databases
-l, --login       role can login (default)
-w, --no-password never prompt for password

默认在 Linux 系统上安装并初始化数据库后，通过 postgres 登录不需要输入密码，可空密码登录后设置一个。

sudo -u postgres psql
\password 新密码

添加或修改普通用户(角色)

添加用户 CLI: createuser jane

删除用户 CLI: dropuser jane

添加登录权限： CREATE ROLE jane WITH LOGIN PASSWORD 'secret'

添加 REPLICATION 权限： ALTER USER jane REPLICATION

删除用户: DROP ROLE jane

注：用户查改增删表行为受到 schema 影响，具体见后「管理库表」章节。

添加用户对 t1 表查改增删记录权限： GRANT INSERT,SELECT,UPDATE,DELETE ON t1 TO jane;

添加用户对 t1 SEQUENCES 类表权限(如 pg_dump 会用到) ： GRANT USAGE,SELECT ON t1_id_seq TO jane;

添加用户对 t1 所有(含查改增删记录、TRUNCATE ，但不含 CREATE TABLE 、 DROP TABLE 等等) 权限：GRANT ALL ON t1 TO jane;

添加对 d1 库下 所有 schema 值为 public 的表 查改增删记录权限：

sql

\c d1
\d
GRANT ALL ON ALL TABLES IN SCHEMA public TO jane;

添加 只读全库和表 权限： GRANT pg_read_all_data TO jane;

删除指定权限：将上面 GRANT ... TO ... 替换为 REVOKE ... FROM ...

在初始化数据库实例后，数据库中默认就会存在一个名称为 public 的 schema，任何用户都有在 public schema 上的 create 权限，通常我们需要把这个权限回收回来: revoke create on schema public from public;

改 d1 库的属主为 jane 用户: ALTER DATABASE d1 OWNER TO jane

改 t1 表的属主为 jane 用户: ALTER TABLE t1 OWNER TO jane

查看所有用户系统权限信息 SELECT * FROM pg_roles;

SELECT * FROM information_schema.role_table_grants where table_name='t1';

临时切换从管理员用户为指定用户 SET ROLE jane

切换会原用户 RESET ROLE

管理库表

注意：默认当前用户查表搜索表路径为 $USER,public，如果 user=dumb_pg，则使用 psql -U dumb_pg < create_tables.sql 创建表后，会出现使用 \d 无法列出创建表情况。

查看 schema 搜索路径 SHOW search_path;

修改当前会话 schema 搜索路径 SET search_path TO my_db_name,'$USER',public;

修改 db 默认 schema 搜索路径 ALTER DATABASE my_db SET SEARCH_PATH TO my_db,public;

列出当前 db 下所有 schema \dnS select * from information_schema.schemata;

列出所有 table select * from information_schema.tables where table_schema not in ('information_schema', 'pg_catalog');

删除指定 schema 下所有表 DROP SCHEMA public CASCADE; CREATE SCHEMA public;

通过 TRUNCATE / DROP 删除库表后，缩减占用冗余空间 VACUUM FULL 1

数据类型

数值

smallint/2 bytes, integer/4 bytes, bigint/8 bytes
real/4 bytes, double precision/8 bytes

日期时间，三种类型均支持指定时区(SQL 标准中 timestamp 不带时区，但 pg 扩展支持)

date
time
timestamp 大部分时间函数相关都返回此类型

基础语法

进入交互终端 PGPASSWORD='secret' psql -h 127.0.0.1 -p 5432 -U postgres

查看版本 PGPASSWORD='secret' psql -h 127.0.0.1 -p 5432 -U postgres -c 'select version()'

pg SQL 方言解释要点

sql 里面忽略空白符(空格，tab，换行)和两个减号一个空格后的内容;
两个减号一个空格后，直到行尾都是注释；
以分号结束一个 sql 语句。

查看版本 select version();

查看当前使用配置文件路径 show config_file;

常见内置命令以反斜杠(\)开始，后缀加号(+)为详情模式

查看帮助

sql

\h
\h create table

连接到指定数据库 \c mydb

显示对应列(column)，相当于 mysql 里面的 \G \x

显示查询耗时 \timing

列出用户/角色/数据库/schema/表/表结构/表索引

sql

\du+
\l+
\dnS+
\dt+
\d+ t1
\dp t1
\di+

注：如果表名首字母是大写，则需要加双引号(")，如 \dt+ "t1" 。

通过 SQL 列出用户/角色/数据库/表/表结构

sql

select * from pg_catalog.pg_user;

select * from pg_catalog.pg_roles;

select * from pg_catalog.pg_database;

select * from pg_catalog.pg_tables;

select
    column_name, data_type, character_maximum_length, column_default, is_nullable
from INFORMATION_SCHEMA.COLUMNS t where t.table_catalog = d1 and t.table_name = t1

查看表索引

sql

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 't1%' -- `t1` is table name
order by
    t.relname,
    i.relname;

创建数据库 CREATE DATABASE d1; 或 PGPASSWORD='secret' createdb -h 127.0.0.1 -p 5432 -U postgres d1

重命名数据库 ALTER DATABASE d1 RENAME TO d2;

重命名表 ALTER TABLE t1 RENAME TO t1_bak;

修改表结构

添加字段

sql

ALTER TABLE t1 ADD COLUMN c1 text;
ALTER TABLE t1 ADD if not exists c2 bool default false not null;
alter table t1 add if not exists c3 TEXT[] default '{}';

删除字段 ALTER TABLE t1 DROP COLUMN c1;

重命名字段 ALTER TABLE t1 RENAME COLUMN addr TO city;

修改字段类型属性

sql

-- from text to integer
alter table t1 alter rating type integer using rating::integer;

alter table t1 ALTER COLUMN c1 TYPE varchar(150), ALTER COLUMN c1 set default '';

ALTER TABLE t1 ALTER COLUMN c2 SET NOT NULL;
ALTER TABLE t1 ALTER COLUMN c3 SET DEFAULT 7.77;
ALTER TABLE t1 ALTER COLUMN c4 TYPE numeric(10,2);

修改索引

添加自增主键约束 ALTER TABLE t1 ADD COLUMN id SERIAL PRIMARY KEY; 或 ALTER TABLE t1 ADD PRIMARY KEY (id);

删除主键约束/主键索引

sql

\d t1
    "t1_pkey" PRIMARY KEY, btree (id)

alter table t1 drop CONSTRAINT t1_pkey;

创建索引

sql

CREATE UNIQUE INDEX t1_idx ON t1 (c1);
CREATE INDEX t1_idx ON t1 (c1);

删除索引 DROP INDEX t1_idx;

增删引用约束（外键） REFERENCES Constraint (Foreign Key)

ALTER TABLE products
ADD CONSTRAINT fk_products_group_id
FOREIGN KEY (group_id)
REFERENCES product_groups(id)
ON DELETE CASCADE;

ALTER TABLE t1 DROP CONSTRAINT fk_products_group_id;

常用查询

插入或更新 insert into t1 (id,name) values ($1,$2) ON CONFLICT ("id") DO UPDATE SET "name"="excluded"."name";

n 小时/天/月/年前

sql

-- 支持单位 years, months, weeks, days, hours, minutes, seconds
-- 支持单位简写 days => d
select now() - interval '30 days';
select now() - interval '30 d';

select * from t1 where to_timestamp(updated) > to_timestamp('2049-08-29', 'YYYY-MM-DD');

bigint 转日期字符串 select TO_CHAR(TO_TIMESTAMP(1725465276), 'YYYY-MM-DD');

当前时刻 select now();

当前时刻 unix 秒数 select cast(extract(epoch from current_timestamp) as integer);

Query current hour/minute/second

SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS current_hour,
       EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS current_minute,
       EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS current_second;

时间转换

sql

select to_timestamp(1284352323);

-- timestamp from 1970-01-01_00:00:00 UTC
select extract(epoch from now()) ;

select to_date('2049-09-29', 'YYYY-MM-DD');
select to_timestamp ('2049-09-29 20:51', 'YYYY-MM-DD HH24:MI');
select to_timestamp ('2049-09-29', 'YYYY-MM-DD');

字符串数组是否包含字符串

sql

-- item 类型是 text，items 类型是 text[]

-- 元素在不在数组中
select item from t1 where item = ANY(array(( select items from t2))::text[]);

-- 数组中不包含元素
select item from t1 where NOT(item = ANY(array(( select items from t2))::text[]));

-- 空数组
select id,items from t1 where array_upper(items, 1) is null;

使用 array_position 来排序和数组元素为主键的关联的记录 https://stackoverflow.com/questions/866465/order-by-the-in-value-list

查询字符串数组并去重 SELECT DISTINCT UNNEST(langs::text[]) FROM t1;

通过子查询计算百分比除法

sql

update t2 set rate=(
select (
    (
        count(*) filter (
            where deleted = false and approved = true and id = '123456'
        )
    )::float /
        count(*) filter (
            where deleted = false and id = '123456'
        )
    ) * 100 as p
from t1
) where id = '123456';

Python 库访问

Python

shell

sudo apt install libpq-dev
sudo pip install psycopg2

索引和查询优化

要点

explain 是执行计划( execution plan )显示语句如何扫描引用的表；
explain 后的语句会实际执行修改数据，通过 BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; 可以避免；
设置选项例子 explain (ANALYZE,VERBOSE,BUFFERS) select ...；
cost 值说明， cost=float 值 1..float 值 2，值 1 是 planer 预估返回第一行耗时，值 2 是返回所有行耗时；越大越慢，单位是？
同样查询，第二次时会走缓存加速

其他关键概念

plain sequential scan., index scan
execution cost => disk page fetches

备份和恢复数据

pg_dump 和 pg_restore 是 postgresql-client 软件包带的备份和恢复数据命令行工具。

纯文本 SQL 比压缩文件目录体积大，但不同版本 postgres 客户端和服务端兼容性好。

以纯文本 SQL 格式备份数据

备份数据 PGPASSWORD='secret' pg_dump -h localhost -U postgres -p 5432 -t t1 -d d1 -f d1.t1.sql

恢复数据 PGPASSWORD='secret' psql -h localhost -U postgres -p 5432 -d d1 < d1.t1.sql

其他可能需要的参数

-O, --no-owner       skip restoration of object ownership in
-a, --data-only      dump only the data, not the schema
-s, --schema-only    dump only the schema, no data
-x, --no-privileges  do not dump privileges (grant/revoke)

注：同名表如果已存在需要先删除

以压缩文件和目录格式备份数据

备份数据 PGPASSWORD="secret" pg_dump -h localhost -U postgres -p 5432 -d d1 -t t1 -f d1.t1 -Fd -j 5

参数说明

-F, --format=c|d|t|p  output file format (custom, directory, tar, plain text (default))
-j, --jobs=NUM        use this many parallel jobs to dump

恢复数据 PGPASSWORD="secret" pg_restore -h localhost -U postgres -p 5432 -d d1 d1.t1

注：同名表如果已存在需要先删除

常见问题

`current transaction is aborted, commands ignored until end of transaction block`

在一个事物执行多个操作，前面一个查询/修改出错后，会影响后续查询/修改。
解决：修改逻辑，要么全对，或出错后中止后续逻辑。

PostgreSQL ​

安装二进制包 ​

容器安装 ​

从源码编译安装 ​

术语 ​

常用配置 ​

打印日志 ​

慢查询日志 ​

允许来自所有连接 ​

管理用户账号和权限 ​

修改默认账号 postgres 用户的密码 ​

添加或修改管理员用户 ​

添加或修改普通用户(角色) ​

管理库表 ​

数据类型 ​

基础语法 ​

修改表结构 ​

修改索引 ​

常用查询 ​

Python 库访问 ​

索引和查询优化 ​

备份和恢复数据 ​

以纯文本 SQL 格式备份数据 ​

以压缩文件和目录格式备份数据 ​

常见问题 ​

current transaction is aborted, commands ignored until end of transaction block ​

PostgreSQL

安装二进制包

容器安装

从源码编译安装

术语

常用配置

打印日志

慢查询日志

允许来自所有连接

管理用户账号和权限

修改默认账号 postgres 用户的密码

添加或修改管理员用户

添加或修改普通用户(角色)

管理库表

数据类型

基础语法

修改表结构

修改索引

常用查询

Python 库访问

索引和查询优化

备份和恢复数据

以纯文本 SQL 格式备份数据

以压缩文件和目录格式备份数据

常见问题

`current transaction is aborted, commands ignored until end of transaction block`