Docker：PostgreSQL安装和常用的数据库指令

女爷i 2024-04-20 12:02 48阅读 0赞

#### 1、简述 ####

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

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

#### 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连接：  
![在这里插入图片描述][bc4b4260479d4a0f9bb92da4bb6b4919.png]

#### 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')

[bc4b4260479d4a0f9bb92da4bb6b4919.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/51201ae534e24fdfa50b20ab0a725bb2.png