PG 数据库跨版本结构迁移注意事项

mahan

PG13 或者以上版本导出表结构然后去 PG13 以下版本来恢复记得 导出的时候添加上 --no-toast-compression
PG13 版本建表语法
[postgres@ ~/lxm/2024-03-06/test_data/public_schema]$ psql
psql (13.2)
Type "help" for help.

[postgres@cqbm301017:5779/(rasesql)postgres][03-06.17:22:51]M=# \h create table
Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ COMPRESSION compression_method ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )

实例:
CREATE TABLE public.test (
    created_by character varying(100) COMPRESSION pglz,
    created_date timestamp without time zone,
    updated_by character varying(100) COMPRESSION pglz,
    updated_date timestamp without time zone,
    id_test_qual_leak character varying(32) COMPRESSION pglz,
    id_test_quality_case character varying(32) COMPRESSION pglz,
    test_qual_link character varying(2) COMPRESSION pglz,
    id_leak_qual_link character varying(32) COMPRESSION pglz,
    link_leak_total_amount bigint,
    link_project_type character varying(50) COMPRESSION pglz
);

PG11 建表语法
[postgres@ ~/lxm/v2]$ psql
psql (11.5)
Type "help" for help.

[postgres@cnsz383429:6472/postgres][03-06.17:29:10]M=# \h create table
Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
结论
pg_dump 导出表结构的时候, 如果不添加 --no-toast-compression 会在生成的建表语句中带有 COMPRESSION 关键字, 但是在 PG13 以下库中恢复的时候, 会报错
pg_dump备份的时候, 如果只关注 public 模式下的对象, 记得要指定 -n publc
导入的时候 记得使用 -d <db_name> 来导入到指定库中, 否则会导入到 postgres 库中了
* 导出(备份)
pg_dump -U postgres -d test -n public   --no-toast-compression  -s  -f tbs_schema.sql  -T pgawr*  -T t11 -T t22 -T test*
* 导入(恢复)
psql -d test -f  tbs_schema.sql