本期问答如下:
- 扩展的可信标识
- drop schema与drop owned by的区别
- 跨字符集迁移表
Q1.create extesion必须使用superuser权限执行吗
问题描述
使用create extesion创建扩展时,是否必须使用superuser权限用户才能执行,而不能使用普通用户呢?
问题解答
在PG 13之前,extension只能使用superuser权限用户执行,在13版本里引入了“可信标识”,
系统视图pg_available_extension_versions里增加了trusted字段,如果extension有“可信标识”,就不需要superuser权限。
Q2.drop schema与drop owned by有什么区别
问题描述
通常情况下,一个用户的对象都在一个schema里,那drop schema (cascade)和drop owned by都是删除用户的对象,这两者是否没什么区别呢?
问题解答
drop schema是从schema(数据库对象的逻辑管理)的视角删除某个schema下的所有对象,如果schema下有多个用户的对象,都会被删除;drop owned by是从用户视角删除所有对象,而用户的对象可能分布在多个schema里,比如每个用户可能还有额外的对象在公共的public schema下,此时所有schema下用户的对象都会被删除。
从简单的场景观察两种操作的效果可能类似,实际由于schema与user是多对多的关系,因此有上面的差异。
Q3.如何跨字符集迁移表到不同的schema中
问题描述
从EUC_CN的库,导出一张大表到另外一个UTF-8编码的库里,目标端的schema也不相同,如果
使用pg_dump文本方式进行导出恢复,效率会很慢,使用tar格式时,导出文件里有设置search_path的命令,由于目标端schema名称不一致,恢复会找不到表。
问题解答
如果源端和目标端schema一致,可以使用二进制格式快速导出,命令如下:
pg_dump --file=data.dmp --format=c --schema=schema1 --no-subscriptions --username=user1 database1 pg_restore --username=user1 -d database2 data.dmp
当库的编码不同,schema也不同时,可以使用COPY二进制指定编码进行数据迁移。
源端使用如下命令导出数据:
\copy schema1_name.table_name to data.bin with(encoding 'UTF-8',format 'binary')
目标端使用如下命令恢复数据:
\copy schema2_name.table_name from data.bin with(encoding 'UTF-8',format 'binary')