非mysql的另一个数据库选项
简介
这里介绍pgsql数据库中相关概念和常用使用操作。
1 表空间
表空间允许管理员 在文件系统中定义用来存放表示数据库对象的文件的位置。
从而控制磁盘布局。
表空间就可以在创建数据库对象时通过名称引用。
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 的触发器函数不适用调用命令所作的更改。
5 自定义触发器模块
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';
6 事件触发器 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;
7主从配置
主 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;
8 配置从节点 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
现在查看 两边数据都一样了。
9 创建订阅 来复制数据。
通常使用 异步复制，实时性要求更高的 需要同步复制
CREATE SUBSCRIPTION my_sub CONNECTION ‘dbname = message_synchronize host = 192.168.30.129 user = postgre password = post.2021’ PUBLICATION my_pub;
10 小结
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
- 点赞
- 收藏
- 关注作者
评论（0）