使用关系型数据库PgSql

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介: 【5月更文挑战第13天】`PgSql`表空间允许管理员自定义数据库对象文件的位置,以控制磁盘布局和优化性能,如将高频索引放于SSD,归档数据的索引放于普通磁盘。PostgreSQL支持多种索引类型,如B-tree、Hash等,`CREATE INDEX`用于创建索引,`SET default_tablespace`可设置默认表空间。触发器则是在特定操作后自动执行的函数,可用于表或视图,分为行级和语句级。事件触发器则对DDL事件作出反应。

0 表空间

PgSql是开源关系型数据库中的新兴力量。PgSql表空间允许管理员自定义数据库对象文件的位置,以控制磁盘布局和优化性能,如将高频索引放于SSD,归档数据的索引放于普通磁盘。

question_ans.png

表空间就可以在创建数据库对象时通过名称引用。

    1, 如果初始化集簇所在分区 或 卷用完了,表空间可被创建在一个不同的分区上。
    2,表空间允许管理者根据数据库对象 的使用模式来优化性能
            一个频繁使用的索引放在 固态硬盘,一个归档数据的索引可放置在普通磁盘。

如果表空间放置在临时目录中,而临时目录被销毁了,那此表空间内的数据 将无法被访问。

创建一个表空间

djblog=# CREATE TABLESPACE fastspace LOCATION '/usr/local/pgsql/data';
    ***(Single step mode: verify command)*******************************************
    CREATE TABLESPACE fastspace LOCATION '/usr/local/pgsql/data';
    ***(press return to proceed or enter x and return to cancel)********************

    WARNING:  tablespace location should not be inside the data directory
    CREATE TABLESPACE

查看表空间

    \db

删除一个表空间

    djblog=# DROP TABLESPACE fastspace;
    ***(Single step mode: verify command)*******************************************
    DROP TABLESPACE fastspace;
    ***(press return to proceed or enter x and return to cancel)********************

    DROP TABLESPACE

创建另一个表空间

    djblog=# CREATE TABLESPACE fastinsert LOCATION '/usr/local/pgsql/';
    ***(Single step mode: verify command)*******************************************
    CREATE TABLESPACE fastinsert LOCATION '/usr/local/pgsql/';
    ***(press return to proceed or enter x and return to cancel)********************

    CREATE TABLESPACE
    djblog=#

设置默认的表空间

当default_tablespace被设置为非空字符串,那么它就为没有显式TABLESPACE子句的CREATE TABLE和CREATE INDEX命令提供一个隐式TABLESPACE子句。

djblog=# SET default_tablespace = fastinsert;
    ***(Single step mode: verify command)*******************************************
    SET default_tablespace = fastinsert;
    ***(press return to proceed or enter x and return to cancel)********************

    SET

设置表空间为 系统默认

djblog=# SET default_tablespace = pg_default;
    ***(Single step mode: verify command)*******************************************
    SET default_tablespace = pg_default;
    ***(press return to proceed or enter x and return to cancel)********************

    SET

2 在表空间创建索引

PostgreSQL 提供了多种索引类型: B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN
CREATE INDEX命令创建适合于大部分情况的B-tree 索引。

Hash索引只能处理简单等值比较。不论何时当一个索引列涉及到一个使用了=操作符的比较时,查询规划器将考虑使用一个Hash索引

多种二维几何数据类型的GiST操作符类,支持操作符的索引化查询:

        <<
        &<
        &>
        >>
        <<|
        &<|
        |&>
        |>>
        @>
        <@
        ~=
        &&

其查询规划器会在任何一种涉及到以下操作符的已索引列上考虑使用B-tree索引:

    <
    <=
    =
    >=
    >

将这些操作符组合起来,例如BETWEEN和IN,也可以用B-tree索引搜索实现。同样,在索引列上的IS NULL或IS NOT NULL条件也可以在B-tree索引中使用

  • 在几列定义索引

    CREATE INDEX test2_mm_idx ON test2 (major, minor);

在djblog_manager表的 roles 列 创建索引

  djblog=# CREATE INDEX roles_idx ON djblog_manager (roles);
    ***(Single step mode: verify command)*******************************************
    CREATE INDEX roles_idx ON djblog_manager (roles);
    ***(press return to proceed or enter x and return to cancel)********************

    CREATE INDEX
  • 唯一索引

    CREATE UNIQUE INDEX name ON table (column [, ...]);
    
  • 查看表的索引数量

    djblog=# \d djblog_manager
                         Table "public.djblog_manager"
        Column  |          Type          | Collation | Nullable | Default
      ----------+------------------------+-----------+----------+---------
       roles    | character varying(2)   |           | not null |
       projects | character varying(256) |           | not null |
       users    | character varying(256) |           | not null |
      Indexes:
          "djblog_manager_pkey" PRIMARY KEY, btree (roles)
          "djblog_manager_roles_4e7ad2cf_like" btree (roles varchar_pattern_ops)
          "roles_idx" btree (roles)
      Referenced by:
          TABLE "djblog_employeesign" CONSTRAINT "djblog_employeesign_role_id_c76106e9_fk_djblog_manager_roles" FOREIGN KEY (role_id) REFERENCES djblog_manager(roles) DEFERRABLE INITIALLY DEFERRED
    
  • 在工程表创建索引

      djblog=# CREATE INDEX projects_idx ON djblog_employeesign (id);
          ***(Single step mode: verify command)*******************************************
          CREATE INDEX projects_idx ON djblog_employeesign (id);
          ***(press return to proceed or enter x and return to cancel)********************
    
          CREATE INDEX
      djblog=#
      djblog=# CREATE INDEX projects_roleidx ON djblog_employeesign (role_id);
          ***(Single step mode: verify command)*******************************************
          CREATE INDEX projects_roleidx ON djblog_employeesign (role_id);
          ***(press return to proceed or enter x and return to cancel)********************
    
          CREATE INDEX
    
  • 在用户表创建索引

      djblog=# CREATE INDEX user_index ON auth_user (id);
      ***(Single step mode: verify command)*******************************************
      CREATE INDEX user_index ON auth_user (id);
      ***(press return to proceed or enter x and return to cancel)********************
    
      CREATE INDEX
    

3 触发器

触发器声明了如果执行了一种特定的操作,应该自动执行一个特殊函数,它可以被附加到表(分区的或者不分区的)、视图和外部表。

表和外部表中,触发器可被定义为 INSERT,UPDATE,DELETE之前或之后执行。
可为每个SQL语句执行一次,或者为每个被修改的行执行一次。
UPDATE触发器可针对 UPDATE的子句 set 特定列执行。

触发器也可被 TRUNCATE 语句触发。

在视图上,触发器可被定义用来取代 INSERT,UPDATE,DELETE操作之前或之后。
INSTEAD OF 触发器对视图中需要被修改的每一行触发一次。

视图触发器函数的职责是对 视图的 底层 基本表执行必要的修改。 并且在合适的时候返回被修改的行以便显式在视图中。

视图的触发器也可被 定义为每个SQL执行一次。 在INSERT/UPDATE/DELETE操作之前或之后。
只有在视图 包含一个 INSTEAD OF触发器时,上述触发器才会被触发。

否则该视图的任何语句都必须被重写 为一个影响其 底层基表的语句。 附加在基本表的 触发器将被触发。

触发器函数必须在 触发器本身创建之前定义好。 必须被定义成一个没有参数的函数。 返回类型为 trigger。

一个合适的触发器函数被创建,就可使用CREATE_TRGGER建立触发器。同一个触发器可被用于多个触发器。

POSTGRESQL 同时提供每行的触发器,和每个语句的触发器。

    对于每行的触发器,每一行被修改都会导致触发器被调用。 
            分为 BEFORE  AFTER  行被操作前 和被操作后执行。
    而语句的触发器,不论多少行被修改,语句触发器只调用一次。

    这些触发器只能被定义在非分区表 和 外部表上。
    不能定义在视图。

INSTED OF 触发器只能被定义在视图。 并且只能定义在行级。

  • 一个以继承或分区层次中 父表为目标的语句不会 导致受影响的子 表 语句触发器被 引发。

    受影响的子表行级触发器将被引发。

    如果一个INSERT 包含 ON CONFLICT DO UPDATE子句并且引用了EXCLUDED列。 同时有行级BEFORE INSERTED 和BEFORED UPDATED触发器时,需要考虑意外的结果。

    如果一个分区表的update导致一行移动到另一个分区。 它将从原始分区DELETE 然后 INSERT到新的分区。

          此时 原始分区的所有行级 BEFORE INSERTED 和 BEFORE UPDATE都将被触发。
          目标分区上的 行级 BEFORE INSERTED都将被触发。
    
          如果这些触发器将影响被移动的行,可能造成 令人惊讶的结果。 至于AFTER ROW触发器。
          跨分区的 UPDATE 被转换为 DELETE 和 INSERTED
    

    一个行级UPDATE OF 返回 null指示它没有修改任何底层数据。

    触发器的定义可 被指定一个 布尔的 WHEN 条件。 可被测试来看该触发器是否应该被触发。

  • 级联触发器

    触发器 调用了 触发器。

4 数据改变的可见性

如果触发器中包含 执行SQL命令,并且这些命令将访问触发器所在的表,那就需要注意数据的 可见性规则。

    * 语句级触发器遵循简单的可见性规则,一个语句 所作的改变对于语句级 BEFORE 触发器都不可见,所有修改对于语句级AFTER触发器都是可见的。

    * 导致触发器被引发的数据更改,插入,更新,删除。 自然对于在一个行级BEFORE触发器中执行的SQL指令不可见,因为它还没发生。

    * 但是在一个行级 BEFORE触发器中执行的SQL指令将会看见之前在同一个外层指令中所作的数据更改的效果。
    * 类似的 一个行级 INSTEAD OF 触发器将会看见之前在同一个外层指令中 INSTEAD OF 触发器引发所作的数据更改。
    * 当一个行级AFTER 触发器被引发时,所有外层指令所作的数据更改已经完成,并且对于该被调用的触发器函数时可见的。

触发器函数 如果使用 任何 标准过程语言编写(python,c),那么只有在该函数被声明为 VOLATILE时上述才适用。
STABLE 或 IMMUTABLE 的触发器函数不适用调用命令所作的更改。

3 自定义触发器模块

tcn 模块 提供一个触发器函数,通知监听者 有关它所附加的任意表的改变,必须用作一个 行级AFTER触发器
CREATE TRIGGER 语句 可以为该函数提供一个 可选参数,如果提供该参数,将被用作通知的频道名,如果忽略它,频道名将为tcn

通知的负载由表名,一个指示所执行的操作类型的字母 以及用于主键列名/值对 的构成,每一部分都用逗号与下一部分隔开。

表与列名总是被包裹在 双引号内,并且数据值总是被包裹在单引号内,嵌入的引号都被双写。

# CREATE TABLE
create table tcndata
   (
      a int not null,
      b date not null,
      c text,
      primary key (a,b)
    );
# CREATE TRIGGER
create trigger tcndata_tcn_trigger
  after insert or update or delete on tcndata
  for each row execute function triggered_change_notification();

# listen
listen tcn;
insert into tcndata values (1, date '2021-12-22', 'one'),
                           (1, date '2021-12-23', 'another'),
                           (2, date '2021-12-23', 'two');
  • 更新 update

    update tcndata set c = 'uno' where a = 1;
    
  • 删除 delete

     delete from tcndata where a = 1 and b = date '2021-12-22';                          
    

5 事件触发器 PostgreSQL提供

事件触发器。和常规触发器(附着在 一个表上并且只捕捉 DML事件)不同,
事件触发器对一个特定数据库来说是全局 的,并且可以捕捉 DDL 事件。

可以用任何包括了事件触发器支持的过程语言或者 C 编写 事件触发器。

当前支持的事件是

    ddl_command_start

在CREATE、 ALTER、DROP、SECURITY LABEL、 COMMENT、GRANT或者REVOKE 命令的执行之前发生,引发前不会做受影响对象是否存在的检查。

事件不会为目标是共享对象 — 数据库、角色 以及表空间 — 的 DDL 命令发生,也不会为目标是事件触发器的 DDL 命令发生
ddl_command_start也会在SELECT INTO 命令的执行之前发生,

因为这等价于 CREATE TABLE AS
ddl_command_end

    事件就在同一组命令的执行之后发生。为了 得到发生的DDL操作的更多细节,
    可以从 ddl_command_end事件触发器代码中使用集合返回函数 pg_event_trigger_ddl_commands()
    该触发器在动作以及发生之后引发,并且因此系统目录被读作已更改。

table_rewrite
事件在表被命令 ALTER TABLE 和 ALTER TYPE 的某些动作重写之前发生,虽然其他控制语句 CLUSTER VACUUM 也可以用来重写表,但是他们不会触发 table_rewrite事件。
不能在一个中止的事务中执行事件触发器 或 其他函数。因为如果一个 DDL指令 命令出现错误失败。
将不会执行任何相关的 ddl_command_end触发器。 如果一个 ddl_command_start触发器出现错误失败,将不会引发进一步的事件触发器,并且不会尝试执行该命令本身,类似的,如果一个 ddl_command_end触发器出现错误失败,DDL命令效果将被回滚,就像其他包含事务中止的情况那样。

sql_drop
为任何删除数据库对象的操作在ddl_command_end事件触发器之前发生。要列出以及被删除的对象,可以从sql_drop事件触发器代码中适用集合返回函数。 pg_event_trigger_dropped_objects().
该触发器在系统目录删除后执行,不能再查看它们。
未来可能会增加对更多事件的支持。

触发器定义 可以指定 一个WHEN条件,这样事件触发器 就可以只对用户 希望介入的特定命令触发。这类触发器通常用法是 限制用户可能执行的DDL操作范围。

为了在需要时能运行某些 DDL 命令,只在一个事务期间禁用该触发器会比较方便

  BEGIN;
ALTER EVENT TRIGGER noddl DISABLE;
CREATE TABLE foo (id serial);
ALTER EVENT TRIGGER noddl ENABLE;
COMMIT;

6 主从配置

主 IP:1.0.0.1 – 读/写
从 IP:1.0.0.2 – 只读

postgresql > 10
Hevo Data是一种无代码数据管道,完全自动化地将 数据从 从站移动到您选择的数据库或仓库。
Hevo 是简单的。界面直观。无需开发人员协助
它是可扩展的,借助Hevo,可以根据需要进行扩展和缩减,支持100多种集成。 包括营销,销售应用等
容错: Hevo 检测传入的数据是否异常,但您的分析业务将不会停止
安全: Havo 通过端到端加密和双重身份验证确保数据完整 安全。
24*7: Havo 通过聊天,电话为客户服务
实时数据传输: Hevo 可支持实时移动数据并获得有价值的见解。

  • 1 连接数据库 创建用户

      CREATE ROLE postgres
    
  • 2 修改配置文件 并添加一行 主 ip:30.129

      sudo vim /etc/postgresql/13/main/postgresql.conf   # 开启以下配置
              listen_addresses = '*'
              # 预写日志配置
              wal_level = replica
              archive_mode = on
              # archive_command 变量用于将 XLOG 从数据库实例归档到特定位置
              archive_command = 'rsync -a %p postgres@standbyhost:/var/lib/postgresql/13/main/archive/%f'
    
              max_wal_senders = 3
      mkdir -p /var/lib/postgresql/13/main/archive/
      chmod 700 /var/lib/postgresql/13/main/archive/
      chown -R postgres:postgres /var/lib/postgresql/13/main/archive/
    

sudo vim /etc/postgresql/13/main/pg_hba.conf

    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    host    all             postgres        192.168.30.130/32       md5
  • 3 配置wal_level
    配置wal_level。wal_level 决定写入 WAL 的信息量。它需要从默认的“副本”更改为“逻辑”。这将向 wal_log 添加必要的信息量,这将允许 slave_node 访问 master_node 并复制表格数据

      vim /etc/postgresql/13/main/postgresql.conf 
      wal_level = logical        # minimal, replica, or logical
    

重启 pgsql服务

   sudo service postgresql restart
  • 4 为表创建 PUBLICATION 关联

以使得 从数据库 可以从数据库中复制数据

   CREATE PUBLICATION my_pub FOR TABLE mytable;

授予 user_name 对 mytable的所有权限
GRANT ALL ON mytable TO user_name;

5, 配置从节点 30.130

   sudo vim /etc/postgresql/13/main/postgresql.conf
    hot_standby = on

编辑 sudo cat path/of/data/pg_hba.conf
确保可以 允许 postgre 账户 连接 此 数据库

继续 master_node 并输入以下命令以使用仅模式转储
以获取 my_table 中数据的结构。我们执行此步骤是因为 PostgreSQL 也需要该表存在于 192.168.30.130( slave_node) 的pgsql中。 在 30.130 从机中 执行

    pg_dump talkdb –t message_synchronize –s

在主(30.129) 数据库机 通过管道 把表结构传输到 从机 30.130

    postgre@usercom:/data$ pg_dump talkdb -t message_synchronize -s | psql talkdb -h 192.168.30.130

此命令将 message_synchronize 的表架构通过管道传输到从机 –slave_node。

但是没有附带表中的数据,因为我们已经指定我们只使用 -s 管道模式。
现在可以看到 主机和从机都有 表message_synchronize,但是 从机的表为空。

pg_dump talkdb | psql talkdb -h 192.168.30.130
pg_dump talkdb | psql talkdb -h 172.31.4.210 -p 5433

再次执行 不带 -s 将把数据也复制到从机

 pg_dump talkdb -t message_synchronize -s | psql talkdb -h 192.168.30.130

现在查看 两边数据都一样了。

6 创建订阅 来复制数据。

通常使用 异步复制,实时性要求更高的 需要同步复制

   CREATE SUBSCRIPTION my_sub CONNECTION ‘dbname = message_synchronize host = 192.168.30.129 user = postgre password = post.2021’ PUBLICATION my_pub;

7 小结

python连接 pgsql 报错

psycopg2.OperationalError: SCRAM authentication requires libpq version 10 or above

需要导入官方资源

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
1月前
|
Oracle 关系型数据库 MySQL
数据库中对时间的操作(mySql、Oracle、pgSql)
数据库中对时间的操作(mySql、Oracle、pgSql)
|
1月前
|
SQL 存储 关系型数据库
数据库迁移mssql to pgsql之函数转换
数据库迁移mssql to pgsql之函数转换
|
存储 监控 关系型数据库
PgSQL · 特性分析 · 数据库崩溃恢复(上)
背景 为了合并I/O提高性能,PostgreSQL数据库引入了共享缓冲区,当数据库非正常关闭,比如服务器断电时,共享缓冲区即内存中的数据就会丢失,这个时候数据库操作系统重启时就需要从非正常状态中恢复过来,继续提供服务。本文将具体分析在这种情况下,PostgreSQL数据库如何从崩溃状态中恢复。 上期月报PgSQL · 特性分析 · checkpoint机制浅析中介绍了PostgreSQL中的c
3594 0
|
存储 关系型数据库 数据库
PgSQL · 特性分析 · 数据库崩溃恢复(下)
背景 在上期月报PgSQL · 特性分析 · 数据库崩溃恢复(上),我们分析了PostgreSQL在数据库非正常退出后(包括通过recovery.conf用户主动恢复)的处理,概括起来分为以下几步: 1.
2125 0
|
监控 关系型数据库 数据库