比较 PostgreSQL 架构更改

管理 PostgreSQL 的常规操作之一是定期更新数据库系统的体系结构。PostgreSQL 做得很好，允许人们以可靠的方式通过添加和删除列以及更新列数据类型等来更新模式、添加类型、函数、触发器或更改表。但是，没有内置机制来帮助识别差异，更不用说生成必要的 SQL 来轻松完成从开发环境到生产环境的更新。

因此，让我们谈谈模式更改的可能方法。

使用逻辑转储清单

识别从一个数据库到另一个数据库的模式之间变化的最简单方法是比较模式转储清单。

以下示例演示了一种可以用来查找不同数据库上架构之间差异的方法：

例子：

-- create database schemas
create database db01
create database db01<br /><br /><br />

-- db01: version 1
create table t1 (
c1 int,
c2 text,
c4 date
);
create table t2(
c1 int,
c2 varchar(3),
c3 timestamp,
c4 date
);

-- db02: version 2
create table t1 (
c1 serial primary key,
c2 varchar(256),
c3 date default now()
);
create table t2(
c1 serial primary key,
c2 varchar(3),
c3 varchar(50),
c4 timestamp with time zone default now(),
c5 int references t1(c1)
);
create index on t2 (c5);

-- db01: version 1create table t1 (    c1 int,    c2 text,    c4 date);create table t2(    c1 int,    c2 varchar(3),    c3 timestamp,    c4 date);

# generate schema dumps
pg_dump -s db01 -Fc > db01.db
pg_dump -s db02 -Fc > db02.db

-- db02: version 2create table t1 (    c1 serial primary key,    c2 varchar(256),    c3 date default now());create table t2(    c1 serial primary key,    c2 varchar(3),    c3 varchar(50),    c4 timestamp with time zone default now(),    c5 int references t1(c1));create index on t2 (c5)

# generate manifests
pg_restore -l db01.db > db01_manifest.ini
pg_restore -l db02.db > db02_manifest.ini

# generate manifestspg_restore -l db01.db > db01_manifest.inipg_restore -l db02.db > db02_manifest.i

此代码段演示了通过比较 md5 校验和来查找差异：

# EX 1: generate checksums
md5sum \
<(tail +16 db01_manifest.ini | cut -d ' ' -f 4-) \
<(tail +16 db02_manifest.ini | cut -d ' ' -f 4-)
# output
$ 9d76c028259f2d8bed966308c256943e /dev/fd/63
$ ba124f9410ea623085c237dc4398388a /dev/fd/62

# EX 1: generate checksumsmd5sum \<(tail +16 db01_manifest.ini | cut -d ' ' -f 4-) \<(tail +16 db02_manifest.ini | cut -d ' ' -f 4-)# output$ 9d76c028259f2d8bed966308c256943e  /dev/fd/63$ ba124f9410ea623085c237dc4398388a  /dev/f

下一个片段区分了两个清单之间的差异，仅标识那些已更改的对象和属性。请注意，冗余信息（前 16 行）已被跳过：

# EX 2: perform diffdiff \> <(tail +16 db01_manifest.ini | cut -d ' ' -f 4-) \> <(tail +16 db02_manifest.ini | cut -d ' ' -f 4-)

这个结果差异显示了两个模式之间所做的更改：

1a2,3
> SEQUENCE public t1_c1_seq postgres
> SEQUENCE OWNED BY public t1_c1_seq postgres
2a5,12
> SEQUENCE public t2_c1_seq postgres
> SEQUENCE OWNED BY public t2_c1_seq postgres
> DEFAULT public t1 c1 postgres
> DEFAULT public t2 c1 postgres
"font-weight: 400;">> CONSTRAINT public t1 t1_pkey postgres</span>
></span>> CONSTRAINT public t2 t2_pkey postgresspan><span style="font-weight: 400;"><br />span><span style="font-weight: 400;">> INDEX public t2_c5_idx postgresspan><span style="font-weight: 400;"><br />span><span style="font-weight: 400;">> FK CONSTRAINT public t2 t2_c5_fkey postgresspan>

1a2,3> SEQUENCE public t1_c1_seq postgres> SEQUENCE OWNED BY public t1_c1_seq postgres2a5,12> SEQUENCE public t2_c1_seq postgres> SEQUENCE OWNED BY public t2_c1_seq postgres> DEFAULT public t1 c1 postgres> DEFAULT public t2 c1 postgres<span style="font-weight: 400;">> CONSTRAINT public t1 t1_pkey postgres</span><span style="font-weight: 400;"><br /></span><span style="font-weight: 400;">> CONSTRAINT public t2 t2_pkey postgres</span><span style="font-weight: 400;"><br /></span><span style="font-weight: 400;">> INDEX public t2_c5_idx postgres</span><span style="font-weight: 400;"><br /></span><span style="font-weight: 400;">> FK CONSTRAINT public t2 t2_c5_fkey postgres</span>

好消息是，有许多现有工具可以调和提议的模式设计和目标模式之间的差异：

商业产品可以以优雅高效的方式区分数据库之间的模式。研究，如谷歌，产生了人们可以使用的最流行的技术。
关于开源解决方案，有许多项目能够区分 Postgres 数据库模式。

使用 apgdiff 扩展

以下是开源工具apgdiff的示例实现。

Apgdiff 可以在 Postgres 社区存储库中找到。它比较两个模式转储文件并创建一个 SQL 输出文件，该文件在很大程度上适用于旧模式的升级：

Package: apgdiff
Version: 2.7.0-1.pgdg18.04+1
Architecture: all
Maintainer: Debian PostgreSQL Maintainers 
Installed-Size: 173
Depends: default-jre-headless | java2-runtime-headless
Homepage: https://www.apgdiff.com/
Priority: optional
Section: database
Filename: pool/main/a/apgdiff/apgdiff_2.7.0-1.pgdg18.04+1_all.deb
Size: 154800
SHA256: 9a83fcf54aed00e1a28c3d00eabe1c166977af1e26e91035e15f88b5215b181b
SHA1: ea713acb55898f07374dadd1bebb09ec2fa4b589
MD5sum: e70a97903cb23b8df8a887da4c54e945

Package: apgdiffVersion: 2.7.0-1.pgdg18.04+1Architecture: allMaintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>Installed-Size: 173Depends: default-jre-headless | java2-runtime-headlessHomepage: https://www.apgdiff.com/Priority: optionalSection: databaseFilename: pool/main/a/apgdiff/apgdiff_2.7.0-1.pgdg18.04+1_all.debSize: 154800SHA256: 9a83fcf54aed00e1a28c3d00eabe1c166977af1e26e91035e15f88b5215b181bSHA1: ea713acb55898f07374dadd1bebb09ec2fa4b589MD5sum: e70a97903cb23b8df8a887da4c54e945

以下示例演示了如何使用 apgdiff 更新开发环境和生产数据库架构之间的差异。

例子：

apt install -y apgdiff

# EX 1: dump as SQL statements
pg_dump -s db01 -Fp > db01.sql
pg_dump -s db02 -Fp > db02.sql
createdb db03 --template=db01
apgdiff --ignore-start-with db01.sql db02.sql > db01-db02.sql
# “psql -1” encapsulates statements within a transaction
psql -1 -f db01-db02.sql db03

# EX 2: uses logical dumps
# notice the dumps are standard logical dumps and includes data
pg_dump db01 -Fc > db01.db
pg_dump db02 -Fc > db02.db
createdb db03 --template=db01
# this invocation assumes the resultant diff doesn’t require editing
apgdiff --ignore-start-with \
<(pg_restore -s -f - db01.db) \
<(pg_restore -s -f - db02.db) \
| psql -1 db03

# EX 2: uses logical dumps# notice the dumps are standard logical dumps and includes datapg_dump db01 -Fc > db01.dbpg_dump db02 -Fc > db02.dbcreatedb db03 --template=db01# this invocation assumes the resultant diff doesn’t require editingapgdiff --ignore-start-with \    <(pg_restore -s -f - db01.db) \    <(pg_re

通过这些简单的方法，您可以完成更多的事情。通过结合这些的变体，可以用很少的代码创建相当复杂的 shell 脚本，而且运气好的话，不需要那么多努力。

原文标题：Diffing PostgreSQL Schema Changes
原文作者：Robert Bernier
原文链接：https://www.percona.com/blog/diffing-postgresql-schema-changes/

比较 PostgreSQL 架构更改

使用逻辑转储清单

使用 apgdiff 扩展

评论