数据库微观案例第47期

原创彭冲 2024-08-16

339

本期内容如下：

SQL如何使用OS环境变量
FDW是否可以访问函数或存过
createdb属性无法创建数据库
分区表DDL引起pg_dump等待
pgcopydb工具介绍

1.SQL里使用OS变量

例如服务端设置的PGDATA环境变量，打印其值

$ echo $PGDATA
/opt/pgdata1700
复制

在psql里使用时，先需要把OS的环境变量传递给psql变量

postgres=# \getenv pg_data PGDATA

postgres=# \echo :pg_data
/opt/pgdata1700
复制

\getenv命令可以获取OS环境变量

接着在SQL里使用冒号 + psql变量名称

postgres=# select cast(:pg_data as text);
ERROR:  syntax error at or near "/"
LINE 1: select cast(/opt/pgdata1700 as text);
复制

变量为字符串类型时，注意SQL里引用需要嵌套单引号

postgres=# select cast(:'pg_data' as text);
      text       
-----------------
 /opt/pgdata1700
(1 row)
复制

2.FDW是否可以访问函数或存过

postgres_fdw可以远程执行db里面的存储过程或函数吗？一些客户做异构数据库迁移时有这样的需求。

postgres_fdw是以外部表映射的方式来访问远端的对象，外部表还不支持配置存过或函数。不过远端的函数可以先使用视图进行封装，外部表再映射视图来使用：

CREATE OR REPLACE VIEW public.my_view AS
 SELECT id,
    info
   FROM f1() f1(id, info)
;
复制

远端的存储过程或函数还可以使用dblink方式来调用，以存储过程为例：

select * from dblink( 'mydblink', 'call my_proc1()') as foo(a text);
复制

如果存储过程有入参，单引号需要复写一次

select * from dblink( 'mydblink', 'call my_proc2(''para1'', ''para1'')') as foo(a text,b text);
复制

3.createdb属性无法创建数据库

问题来自微信公众号AustinDatabases：<<PostgreSQL 具有createdb的用户无法创建数据库的原因>>

https://mp.weixin.qq.com/s/TxpzOxbTBaYqgpdJp5n9tw

通常情况下用户有createdb属性是可以创建数据库的，下面进行测试：

postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# create extension pg_stat_statements ;
CREATE EXTENSION
template1=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE USER admin createdb password 'XXX';
CREATE ROLE
postgres=# \c - admin
You are now connected to database "postgres" as user "admin".
postgres=> 
postgres=> CREATE DATABASE mydb;
CREATE DATABASE
复制

上面的代码使用postgres用户在template1模版库下创建了pg_stat_statements插件，admin用户新建数据库mydb成功，mydb也继承了pg_stat_statements插件。

最后定位原因是template1模版库的系统属性datistemplate被修改了：

UPDATE pg_database SET datistemplate = false WHERE datname = 'template1'; 
复制

template1模版库的datistemplate值修改为false后，再使用admin创建数据库时复现错误：

postgres=> CREATE DATABASE mydb2;
ERROR:  permission denied to copy database "template1"
复制

其实仔细观察错误信息，并不是用户没有创建数据库的权限，如果没有createdb属性，报错信息是这样的：

ERROR:  permission denied to create database
复制

错误提示差别一个是copy，一个是create：并非用户的createdb属性有问题，而是create的过程中不能copy默认模版库，因为模版库系统属性datistemplate被修改了。

4.分区表执行DDL引起pg_dump等待

问题来自墨天轮仙人掌：<<postgresql分区表DDL操作时导致pg_dump等待>>

https://www.modb.pro/db/1823654958807982080

使用pg_dump进行逻辑导出时，在PG 15之前即便不导出分区表，普通表导出也会受pg_dump查询分区函数的影响，而此时任意某个分区表有DDL操作时都会导致pg_dump被卡。

该问题在PG 15做了优化，从源码"bin/pg_dump/pg_dump.c"注释中可以看到相关说明

/*
 * Find all the tables and table-like objects.
 *
 * We must fetch all tables in this phase because otherwise we cannot
 * correctly identify inherited columns, owned sequences, etc.
 *
 * We include system catalogs, so that we can work if a user table is
 * defined to inherit from a system catalog (pretty weird, but...)
 *
 * Note: in this phase we should collect only a minimal amount of
 * information about each table, basically just enough to decide if it is
 * interesting.  In particular, since we do not yet have lock on any user
 * table, we MUST NOT invoke any server-side data collection functions
 * (for instance, pg_get_partkeydef()).  Those are likely to fail or give
 * wrong answers if any concurrent DDL is happening.
 */
复制

从PG 15开始，这个场景pg_dump可以正常执行完成，不会被卡。

5.关于pgcopydb工具

这篇文章介绍了使用pgcopydb工具可以更快的进行逻辑备份恢复:

https://www.mydbops.com/blog/faster-logical-backuprestore-using-pgcopydb-postgresql

相比pg_dump/pg_restore内置工具，pgcopydb封装并调用pg_dump/pg_restore工具。

pgcopydb is a tool that automates running pg_dump | pg_restore between two running Postgres servers.

pgcopydb工具主要解决了两个痛点：

表数据不经过中间商，即不落地消耗存储
基于大模型经验拆分创建索引及添加表约束，极大提高了性能

第一个问题：pg_dump -Fd --jobs=N和pg_restore -Fd --jobs=N，虽然能使用多个进程来并行备份及恢复，但需要使用本地目录文件先落地存储。

第二个问题：pg_dump/pg_restore对唯一约束和主键的处理不够机灵

Those indexes are exported using the ALTER TABLE command directly. This is fine because the command creates both the constraint and the underlying index, so the schema in the end is found as expected.

That said, those ALTER TABLE ... ADD CONSTRAINT commands require a level
of locking that prevents any concurrency.

Although most forms of ADD table_constraint require an ACCESS EXCLUSIVE
lock, ADD FOREIGN KEY requires only a SHARE ROW EXCLUSIVE lock. Note that
ADD FOREIGN KEY also acquires a SHARE ROW EXCLUSIVE lock on the referenced
table, in addition to the lock on the table on which the constraint is
declared.

The trick is then to first issue a CREATE UNIQUE INDEX statement and when
the index has been built then issue a second command in the form of ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY USING INDEX ..., as in the
following example taken from the logs of actually running pgcopydb:

除此以外，pgcopydb还可以做CDC数据捕获，虽然这并不是什么new fresh技能：基于数据库的逻辑解码 + wal2json解码插件

简单做了下编译验证和测试：

$ pgcopydb copy db \
--source="postgres://postgres@ip1:port/postgres" \
--target="postgres://postgres@ip2:port/mydb" \
--table-jobs=2 --index-jobs=1 \
--no-owner --no-acl \
--fail-fast
复制

上面的命令可快速将源端的某个db拷贝到目标端的db，整体的帮助命令和在线文档非常详细。

目前遇到的问题是最新的v0.17不能编译成功，v0.15版本与PG 16可以编译成功，pgcopydb编译后支持的PG版本为11到16，但操作时源端与目标端的大版本必须一致，而pg_dump/pg_restore没有这个限制，因为向下兼容，相对更灵活些。

与我联系

微信公众号：象楚之行
墨天轮：https://www.modb.pro/u/15675
微信：skypkmoon

勤耕细作，用心积微；静待花开，量变质成。

墨力计划 postgresql

最后修改时间：2024-08-31 20:22:34

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

PostgreSQL乐知乐享（共177篇）

PostgreSQL工作实践分享