weixin_43084715的博客

为什么这里不直接爆出来owner of function multinstance_init()，是否可以理解为登录的不是ctsdb数据库。删除schema xc_test，这里还好是 xc_test用户创建的，不然就删掉了 xc_test模式下所有的对象。查询search_path，发现依赖于 xc_test用户下的 模式xc_test，修改ctsdb用户的search_path。重新删除ctsdb，还是同样的报错。切换重新删除user ctsdb;这时候找到了所依赖的对象。这里找不到任何对象。

DBD::Pg::db do failed: ERROR: column "transtype" is a generated columnsec., avg: 2520 recs/sec), REPORTSINTERMEDIATETABLE in progress.DETAIL: Generated columns cannot be used in COPY. at /usr/local/share/perl5/Ora2Pg.pm line 15125.FATAL: ERROR: column

通过pg_blocking_pids(1155840)查询持有锁的进程。通过pg_stat_activity视图查看进程对应的语句。granted为f表示pid=1155840被阻塞。

还允许用户在其他人的模式中创建对象。要允许这样做，需要授予模式上的CREATE特权。注意，默认情况下，每个人在公共模式上都有CREATE和USAGE特权。这允许所有能够连接到给定数据库的用户在其公共模式中创建对象。默认情况下，用户不能访问不属于他们的模式中的任何对象。要允许这样做，模式的所有者必须授予该模式的USAGE特权。为了允许用户使用模式中的对象，可能需要为对象授予额外的权限。

在上面的条件中，pg_database.datfrozenxid 是系统视图pg_database 中的列，并保存着每个数据库中最老的已冻结的事务标识。如果 CLOG 文件0002中包含最小的 pg_database.datfrozenxid，则可以删除旧文件（0000和0001），因为存储在这些文件中的所有事务在整个数据库集簇中已经被视为冻结了。注意，相应的CLOG页面也会被删除。它会扫描所有页面，检查表中的所有元组，更新相关的系统视图，并在可能时删除不必要的CLOG文件与页面。

ascii为0插入到oracle中，并不是null，它占用一个字节，要查询出来只能用=chr(0)尽量别插入ascii为0的字符到数据库中，可以用插入空字符串或者null代替，否则检索起来不方便碰到实在是不知道这个字段到底存的是什么的时候，可以使用dump函数去查看它具体的ascii码。可以看到第4行的第2列跟第3行的第2列的空格一样，都是占了一个字节，而且他的ASCII码为0，那么我们可以通过下面的方式将第4行查出来。那么这个第4行第2列到底存的是什么呢，我们可以通过dump函数去查看。

PostgreSQL的扩展系统是其强大功能的关键组成部分，因为它允许开发人员扩展其功能。在以前的版本中，新的扩展只能由数据库超级用户安装。为了更轻松地利用PostgreSQL的可扩展性，PostgreSQL 13添加了"可信扩展"的概念，该概念允许数据库用户使用安装超级用户标记为"受信任"的扩展。普通用户user1创建不可信扩展file_fdw,提示必须是超级用户才能 创建成功。普通用户user1创建可信扩展pgcrypto，可创建成功。必须在当前数据库上有CREATE权限才能创建此扩展。

不管我505之后提交了多少事务，但是backend_xmin所能见到的最大事务也还是505，如果505到510执行了大量的批量dml语句，无法对505到510之间的数据进行vacuum，这个时候就会导致表膨胀。当数据库中存在未结束的SQL语句或者未结束的持有事务ID的事务，在此事务过程中，或在此SQL执行时间范围内产生垃圾的话，这些垃圾无法回收，导致数据库膨胀。当我把505rollback之后，长事务才算结束，所以能看到的最大事务是510,即510之前的事务都可以做vacuum操作。...

依次启动node1、node2、node3节点的etcd。通过patroni来管理postgresql。通过VIP访问不同接口，实现读写分离和负载均衡。2、访问5001端口，连接读节点，实现负载均衡。三节点安装yuminstalletcd。node1节点VIP绑定eth0网卡设备。1、访问5000端口，连接读写主节点。查看patroni集群状态。postgresql主库状态。启动haproxy​​​。启动keepalived。手动切换Leader。...

使用分布式key-value数据库作为数据存储，主节点故障时进行主节点重新选举，具有很强的健壮性。数据库信息记录到ETCD中，通过优化部署策略（多机房部署、增加实例数)可以避免脑裂。可以设置备库延迟主库WAL的字节数，当备库延迟大于指定值时不做故障切换。官方文档中解释不清晰，无搭建过程，对于新手来说没有第一把资料。支持switchover命令，能自动将老的主库进行角色转换。当主库实例关闭后，首先会尝试自动拉起;搭建繁琐，需要安装不同的组件，及配置项。当备库实例关闭后，支持自动拉起;......

PostgreSQL查询计划器依靠有关表内容的统计信息来生成良好的查询计划。这些统计信息由ANALYZE命令收集，该命令可以自行调用，也可以作为. 拥有相当准确的统计数据很重要，否则计划选择不当可能会降低数据库性能。如果启用了 autovacuum 守护程序，它将在表的内容发生充分变化时自动发出命令。但是，管理员可能更喜欢依赖手动调度的操作，特别是如果已知表上的更新活动不会影响“有趣”列的统计信息时。守护进程严格根据插入或更新的行数进行调度；它不知道这是否会导致有意义的统计变化。分区中更改的元组和继承子项不

如果您有一个定期删除全部内容的表，请考虑使用TRUNCATE,而不是使用后跟. 立即删除表的全部内容，无需后续或回收现在未使用的磁盘空间。缺点是违反了严格的 MVCC 语义。当一个表由于大量的更新或删除活动而包含大量的死行版本时，VACUUM可能不会令人满意。如果您有这样一个表，并且需要回收它所占用的多余磁盘空间，那么您将需要使用VACUUM FULL，或者CLUSTER，或者ALTER table的一种表重写变体。这些命令重写表的整个新副本，并为其构建新的索引。所有这些选项都需要一个ACCESS EXCL

以下三个函数等价如果改成select，则报以下错误ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead.

常用数据库对象存在性查询语句

postgresql模糊查询（like和~）引用变量，正则表达式～和like的模糊查询，这两种方式语义是一样的。 Select 'select pg_terminate_backend('||pid||');'from pg_stat_activity where state in ('active') and query ~ '%:querystr%';

以下是同事人员发过来的sql，让我帮他优化一下，clickhouse的语句，需要替换成在自己的数据库上执行，dth_diagnosis_all 这个表的数据量90217505 ，千万级的数据量，说是内存不足，sql执行不了。两个子查询的数据量 以下为优化后的sql，看这个sql子查询t1和t3，其实是同一张表，然后对同一张表做连接查询，对t3表做了where条件过滤（这个不用改），之后连接查询之后又对t1表进行了where条件过滤，如下： t1.org_province = #{provi

root用户安装postgresql和 postgres用户-bash问题，使用 root 进行源码编译安装，初始化数据库集簇 postgresql-setup initdb

准备文件：链接：https://pan.baidu.com/s/1xmUCDSJ2TPT17FuzhdMfog提取码：y1qj1. 将 uuid-ossp.so 文件放在 /usr/pgsql-12/lib 目录下2. 将 其他文件放在 /usr/pgsql-12/share/extension 目录下3.重启 postgresql 服务4. 安装 uuid-oss 插件即可create extension IF NOT EXISTS "uuid-ossp"—————

max_worker_processes：设置整个数据库实例层面允许支持的最大后台工作进程数，默认值为8。调整此参数需要重启数据库。数据库的一些系统进程如SysLogger、Bgwriter、WaLWriter、Pgarch、AutoVacuum、PgStat并不包含在这个参数控制的进程数内，即这些系统进程不是后台工作进程。而一些第三方插件产生的后台进程和并行的工作进程都算是后台工作进程，受这个参数的限制。max_parallel_workers：设置整个数据库实例层面允许用做并行的后台工作进程数。修

前段时间无法理解这个参数什么意思，今天又研究了一下。以下是官网给出的解释-Xmethod--xlog-method=method在备份中包含所需的事务日志文件（WAL文件）。这将包括所有在备份期间产生的事务日志。 如果声明了这个选项，那么直接在提取出的目录（不需要参考日志归档）中启动一个主进程是可能的。 因此使其成为一个完全独立的备份。支持下列收集事务日志的方法：-Xf , -X fetch , --xlog-method=fetch事务日志文件在备份结束时收集。因此，w...

会话一：txdb_default=# insert into zz values(3);INSERT 0 1wal文件查看如下所示rmgr: Heap len (rec/tot): 54/ 222, tx: 504, lsn: 0/12012360, prev 0/12012328, desc: INSERT off 4 flags 0x00, blkref #0: rel 1663/13603/16392 blk 0 FPWrmgr: Tran...

脏数据（未提交）和脏页（已经被修改的页）脏数据是指事务对缓冲池中行记录的修改，并且还没有提交。注意脏数据和脏页是完全不同的两种概念，脏页指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页的数据不一致。脏页的读取是非常正常的，脏页是数据库实例内存和磁盘异步造成的，这并不影响数据的一致性，脏页最终会被刷新到磁盘中。wal_buffers：控制wal buffer的大小,默认值为shared_buffers大小的1/32。wal_writer_delay：控

work_meminteger类型，为每个进程单独分配的内存，声明内部排序操作和Hash表在开始使用临时磁盘文件之前使用的内存数目。这个内存也是本地内存，以千字节为单位，默认是1024 KB（1MB）。请注意，对于复杂的查询，可能会同时并发运行多个排序或散列（Hash）操作；每个排序或散列操作都会分配该参数声明的内存来存储中间数据，只有存不下时才会使用临时文件。同样，多个正在运行的会话可能会同时进行排序操作，因此使用的总内存可能是work_mem的好几倍。ORDER BY、DISTINCT和MERGE.

create table public.t1_trgm(f1 int not null,f2 varchar(2000),primary key(f1));create extension pg_trgm;create index t1_trgm_f2_idx on public.t1_trgm using gin(f2 gin_trgm_ops);使用方法select * from public.t1_trgm where f2 like '%xxxxx%';对于上述的%xxx

PostgreSQL中如果查询需要连接两个或更多表，在所有扫描单个表的可能计划都被找到后，连接计划将会被考虑。和很多数据库一样，可供选择的三种表连接方式为：nested loop join、merge join、hash join。nested loop join：对左表中找到的每一行都要扫描右表一次。这种策略最容易实现但是可能非常耗时(不过，如果右表可以通过索引扫描，这将是一个不错的策略。因为可以用左表当前行的值来作为右表上索引扫描的键)。merge join：在连接开始之前，每一个表都按照连

使用方法select * from public.t1_like where f2 like '111%’;select * from public.t1_like where f2 COLLATE "C" = 'AAA';索引本身是以排序为基础的，而每个索引只能支持一种排序规则，这个排序规则由Collate指定。例如：在zh_CN的区域下中文按拼音排序，其它区域按字符编码排序。因此，如果用一种Collate进行排序建立索引，用另一种Collate查询，索引会失效。...

插件btree_gin任意字段查询走索引create extension btree_gin;create index idx_t_gin3_1on t_gin3 using gin(c1, c2, c3, c4, c5, c6, c7, c8, c9); postgres=# \d t_gin3 数据表 "public.t_gin3"栏位 | 类型 | 校对规则 | 可空的 | 预设------+---------+----------+--------+--...

之前一直以为select放在begin；commit中才会消耗事务，今天做了验证发现select语句在begin；commit中也不会消耗事务。pg中默认的查询是不消耗事务的，如下图所示如果把select放在begin中，会消耗事务吗，答案是不会消耗在事务中包含有insert语句，才会消耗事务...

unlogged表：创建一个不记录wal日志的表，对表的增删改都不会被记录到wal中。介于正常表和临时表之间的类型表，称之为unlogged表，在该表新建的索引也属于unlogged，该表在写入数据时候并不将数据写入到持久的write-ahead log文件中，在数据库异常关机或者异常崩溃后该表的数据会被truncate掉，但是在写入性能上会比正常表快几倍。创建语法：create unlogged table t（id int）;unlogged table 的几个特点：1、unlog

创建临时表时不同选项create tmp table tmp_t (id int)on commit delete rows |on commit drop |on commit preserve rowson commit delete rows选项，就是事务结束，数据就消失（事务级别）on commit drop选项，可以设置事务结束临时表就消失（事务级别）on commit preserve rows选项，事务结束时，数据依然还会存在，直到会话结束为止(会话级别)...

PostgreSQL中如果查询需要连接两个或更多表，在所有扫描单个表的可能计划都被找到后，连接计划将会被考虑。和很多数据库一样，可供选择的三种表连接方式为：nested loop join、merge join、hash join。nested loop join：对左表中找到的每一行都要扫描右表一次。这种策略最容易实现但是可能非常耗时(不过，如果右表可以通过索引扫描，这将是一个不错的策略。因为可以用左表当前行的值来作为右表上索引扫描的键)。merge join：在连接开始之前，每一个表都按照连

mysql两表更新中可以使用join语句，pg两表更新中只能用语法update set from语法。mysql两表更新update dzys_User_CallList_Self a, dzys_user bset a.Opp_CustID=b.CUST_IDwhere a.OPP_NUMBER=b.USER_NUMBER；postgresql两表更新UPDATE dzys_User_CallList_Self as a set Opp_CustID=b.CUST_I...

Extract 属于 SQL 的 DML（即数据库管理语言）函数，同样，InterBase 也支持 Extract，它主要用于从一个日期或时间型的字段内抽取年、月、日、时、分、秒数据，因此，它支持其关健字 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、WEEKDAY、YEARDAY。计算时间差天数select extract(day FROM (age('2017-12-10'::date , '2017-12-01'::date)));计算时间差秒数select ex

执行建表语句卡住查询是否是有锁，结果显示，并没有锁，从结果显示，当前pid应该是51284 这种情况下考虑是否是同步复制导致的备库无法同步，结果查看之后，果然是备库没打开导致。杀死进程果然警告信息如我所料，没有同步备库。...

定时任务 问题提出： 生产环境怎么备份，开发测试环境怎么备份，使用全备，归档备，什么时候备，保留多 长时间？ 个人看法： 生产环境每天全备一次，归档打开保存。 开发测试，可以 3~5 天一次全备，归档打开保存。 具体根据生产环境，开发测试环境以及应用沟通的结果去制定相应脚本和策略。 远程备份脚本 [postgres@node2 ~]$ cat pg_basebackup.sh #! /bin/sh DEST_NODE_HOST=node1 PRIMARY_

物理备份 备份分为冷备和热备。冷备是正常关闭服务后拷贝文件。热备是服务正常运行中拷贝文 件。热备由于采用数据缓冲区机制，拷贝的文件数据会不一致。根据数据库恢复基本原理，只要 确定某 lsn 之前的日志已经全部写入了数据文件，则在拷贝后的数据文件上，应用该 lsn 号 之后的日志文件，可将数据恢复到一致的状态，所以前提是保证基础备份节点之后的 wal 文件必须存在，不能覆盖，一种方法是归档打开。通过热备份的方式生成基础备份的方法有两种，一种是通过底层 API 的方式一步一步地

WAL 日志和归档配置 WAL 日志归档，就是把在线的已写完的 WAL 日志复制出来。使用复制 WAL 文件的方式 来同步主、备数据库之间的数据，会导致备库落后主库一个 WAL 日志文件，具体落后多长 时间取决于主库上生成一个完整的 WAL 文件所需要的时间。 在 PostgreSQL 中配置归档的方法是在配置文件 postgresql.conf 中配置参数 archive_mode 和 archive_command，命令如下： wal_level = rep...

逻辑备份 逻辑备份在恢复时，介于逻辑备份与故障时间点之间的数据难以恢复，而且如果表比较 大时，备份时间会很长，而且恢复时间会更长，故一般不采取逻辑备份方式进行数据库备份； 对于小表和跨平台跨版本 的数据迁移，适合用逻辑备份。并且逻辑备份可以当作物理备份的 辅助备份手段。 逻辑备份主要以下三种： pg_dump 命令可以选择一个数据库或部分表进行备份，只能备份单个数据库，而且恢复 的时候需要创建空数据库。 pg_dumpall 是将一个 PostgreSQL 数据库集群全..

查询pg_locks表，granted为f的pid进程（47340）被transactionid事务id为14362060阻塞了，通过pg_locks表transactionid这一列相同的事务id，可以查到transactionid事务id为14362060的pid进程为46962，同时也能看到47340进程对应的事务id为14362061。所以通过pg_stat_activity表可以查看，46962进程阻塞了47340进程。...

备库中会在wal_log 里面有backup 的标记，这说明这个WAL log 之前的数据已经备份了，若是清理这个WAL LOG 以前的log 是安全的。PostgreSQL9.2之后支持级连复制，所以在9.2及以上的版本中pg_basebackup也可以从另一个Standby库上做基础备份，但从Stamdby备份时不会创建备份历史文件（.backup的文件）。...