Docker：PostgreSQL开源关系型数据库指南-拾荒的小海螺

1、简述

PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系型数据库管理系统。POSTGRES的许多领先概念只是在比较迟的时候才出现在商业网站数据库中。PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。同样，PostgreSQL也可以用许多方法扩展，例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外，因为许可证的灵活，任何人都可以以任何目的免费使用、修改和分发PostgreSQL。
版本演进：

版本	演进
PostgreSQL-09	1.继承式分区， 2.手动添加触发器或规则
PostgreSQL-10	1.声明式分区，2.分区索引手动创建，不能基于分区父表创建
PostgreSQL-11	1.新增哈希分区，2.基于的分区表创建索引，3.支持update分区，4.会创建一个默认default分区，5.分区支持创建主键，外键，索引，触发器
PostgreSQL-12	1.新增哈希分区，2.alter table attach partitions不会阻塞查询
PostgreSQL-13	1.支持before trigger(不允许改变插入数据的目标分区)，2.支持逻辑复制
PostgreSQL-14	1.增加了许多并行查询功能，2.优化性能，减少了B-树索引的资源消耗
PostgreSQL-15	1.可以支持使用LZ4、Zstandard算法进行压缩，2.引入了 jsonlog数据格式用于日志记录

2、安装

拉取postgresql版本：

[root@localhost ~]# docker pull postgres:14.2
14.2: Pulling from library/postgres
214ca5fb9032: Pull complete 
e6930973d723: Pull complete 
aea7c534f4e1: Pull complete

创建映射的目录：

[root@localhost ~]# mkdir -p /data/postgres

通过docker run指令启动：

docker run --name postgres \
    --restart=always \
    -e POSTGRES_PASSWORD=root123 \
    -p 5432:5432 \
    -v /data/postgres:/var/lib/postgresql/data \
    -d postgres:14.2

run: 创建并运行一个容器：
--restart=always 表示容器退出时,docker会总是自动重启这个容器；
–name: 指定创建的容器的名字；
-e POSTGRES_PASSWORD=root123 : 设置环境变量，指定数据库的登录口令为root123 ；
-p 5432:5432: 端口映射将容器的5432端口映射到外部机器的5432端口；
-v /data/postgres:/var/lib/postgresql/data 将运行镜像的/var/lib/postgresql/data目录挂载到宿主机/data/postgres目录
-d postgres:14.2: 指定使用postgres:14.2作为镜像。

最后可以通过Navicat Premium连接：

3、常用指令

查看数据库版本：

select version();

查看数据库当前锁占用情况：

//查询当前进程锁
select pid,relation,mode,relname from pg_locks a,pg_class b where relation in (select oid from pg_class) and a.relation = b.oid

通过PID结束当前进程：

//杀死进程 
select pg_terminate_backend(pid); //pid为进程id号

取消后台执行，回滚当前事务：

//通过PID来回滚当前事务
select PG_CANCEL_BACKEND(PID);
//通过表名来查找结束
方式1:select 'select pg_terminate_backend('||pid||');' from pg_locks where relation in(select oid from pg_class where relname='table name');
方式2:select 'select pg_cancel_backend('||pid||');' from pg_locks where relation in(select oid from pg_class where relname='table name');

查看当前数据库最大连接数：

//查询连接数
select min_val, max_val from pg_settings where name='max_connections';
//最大连接数
select * from pg_settings where name = 'max_connections'

查看当前活跃的连接数：

select count(1) from pg_stat_activity;

处理当前模板和数据库无法删除的问题：

//模板数据库无法删除的问题
UPDATE pg_database SET datistemplate='false' WHERE datname='template_postgis'; 
//模板数据库被占用回话的无法删除问题
select * from pg_stat_activity where datname = 'ueccp'
select pg_terminate_backend(PID)

4、SQL

创建一个table语句：

CREATE SEQUENCE seq_ls_excel_task
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;

CREATE TABLE ls_excel_task (
	id int8 NOT NULL DEFAULT nextval('seq_ls_excel_task'::regclass),
	type int4 NOT NULL,
	status int4 NOT NULL DEFAULT 0,
	estimate_count int8 NOT NULL DEFAULT 0,
	total_count int8 NOT NULL DEFAULT 0,
	success_count int8 NOT NULL DEFAULT 0,
	failed_count int8 NOT NULL DEFAULT 0,
    file_name varchar(200) DEFAULT NULL,
    file_url varchar(500) DEFAULT NULL ,
    failed_file_url varchar(500) DEFAULT NULL ,
    failed_message varchar(255) DEFAULT NULL ,
    start_time timestamp(6) DEFAULT NULL ,
    end_time timestamp(6) DEFAULT NULL ,
	CONSTRAINT ls_excel_task_pkey PRIMARY KEY (id)
);

ALTER TABLE "ls_excel_task" 
  OWNER TO "postgres";
COMMENT ON COLUMN "ls_excel_task"."id" IS '增长主键';
COMMENT ON COLUMN "ls_excel_task"."type" IS '类型：1-导入,2-导出';
COMMENT ON COLUMN "ls_excel_task"."status" IS '状态：0-初始,1-进行中,2-完成,3-失败';
COMMENT ON COLUMN "ls_excel_task"."estimate_count" IS '预估总记录数';
COMMENT ON COLUMN "ls_excel_task"."total_count" IS '实际总记录数';
COMMENT ON COLUMN "ls_excel_task"."success_count" IS '成功记录数';
COMMENT ON COLUMN "ls_excel_task"."failed_count" IS '失败记录数';
COMMENT ON COLUMN "ls_excel_task"."file_name" IS '文件名';
COMMENT ON COLUMN "ls_excel_task"."file_url" IS '文件路径';
COMMENT ON COLUMN "ls_excel_task"."failed_file_url" IS '失败文件路径';
COMMENT ON COLUMN "ls_excel_task"."failed_message" IS '失败消息';
COMMENT ON COLUMN "ls_excel_task"."start_time" IS '开始时间';
COMMENT ON COLUMN "ls_excel_task"."end_time" IS '结束时间';
COMMENT ON TABLE  "ls_excel_task" IS 'Excel任务管理表';

重置序列号：

ALTER SEQUENCE seq_ls_excel_task RESTART WITH 1505090;

创建临时表：

create TEMPORARY table lsk20011134
as
select * from rs_excel_user

With as查询语句运用：

WITH ts AS ( SELECT * FROM rs_excel_user WHERE uuid = '1' ) SELECT
f.* 
FROM
	rs_excel_user f
	INNER JOIN ts T ON f.uuid = T.uuid

对表分区运用：

//根据当前月份分区
CREATE TABLE ls_read_historym_202207 PARTITION of ls_read_history for VALUES in ( '2022-07')

5、特性和优势

高度可扩展性
PostgreSQL 提供了多种扩展和插件机制，使其能够轻松地集成到不同规模和需求的应用程序中。通过分区表、并行查询、逻辑复制等功能，PostgreSQL 能够有效地处理大规模数据和高并发访问。
数据完整性和安全性
PostgreSQL 支持多种约束（如主键、外键、唯一约束）和触发器，确保数据的完整性和一致性。此外，它提供了丰富的安全特性，包括角色管理、SSL 加密连接、行级安全和列级权限控制，保护数据免受未经授权的访问。
强大的扩展性
PostgreSQL 社区活跃，并有众多的第三方扩展可用，这些扩展提供了各种额外的功能，如全文搜索、地理空间支持、时间序列处理等。开发人员可以根据具体需求选择并集成这些扩展，以扩展 PostgreSQL 的功能。
JSON 和 NoSQL 支持
随着非结构化数据的增加，PostgreSQL 提供了对 JSON 数据类型的本地支持，并支持 NoSQL 数据库的特性。这使得开发者可以在一个数据库中处理传统的结构化数据和半结构化数据，而无需依赖额外的 NoSQL 解决方案。

6、适用场景

Web 应用程序和电子商务
由于其可靠性和性能，PostgreSQL 在大多数 Web 应用程序和电子商务平台中广泛使用。它能够处理大量的事务和复杂的查询，同时提供高度可靠性和数据安全。
数据分析和数据仓库
PostgreSQL 通过支持复杂的查询和窗口函数，适用于数据分析和数据仓库场景。其丰富的数据类型和索引支持，使其能够有效地处理大数据量和复杂数据模型。
地理信息系统（GIS）和地理空间应用
PostgreSQL 提供了对地理空间数据的原生支持，包括地理坐标和地理图形的索引和查询。这使得它成为构建地理信息系统和地理空间应用的理想选择。

7、结语

作为一个成熟而强大的开源数据库管理系统，PostgreSQL 在各种应用场景中都表现出色。它不仅提供了标准的关系型数据库功能，还支持许多先进的特性和扩展，使其能够适应不断变化的数据管理需求。无论是小型项目还是大规模企业应用，PostgreSQL 都是一个可靠且功能强大的选择。

通过不断的社区支持和更新，PostgreSQL 的未来看起来更加光明。对于那些寻求一个稳定、高性能和灵活扩展的数据库解决方案的人来说，PostgreSQL 无疑是一个不可忽视的选择。

希望本文能够帮助您更好地了解和利用 PostgreSQL 的优势，为您的项目选择提供参考和指导。

目录CONTENT

Docker：PostgreSQL开源关系型数据库指南