作者
digoal
日期
2018-06-20
标签
PostgreSQL , 迁移 , 阿里云RDS PG
背景
用户如果需要将线下的PG数据库迁移到阿里云RDS PG，应该评估哪些东西，如何迁移？
1 规格、性能评估
主要评估线下PG实例所在主机的性能指标
1、CPU主频
2、CPU核数
3、磁盘使用容量
4、网络带宽
5、磁盘读写IOPS
6、磁盘读写带宽
这些指标应该尽量与阿里云RDS PG对齐。
如果线下PG开启了异步提交时，对应的阿里云RDS PG IOPS可能需要更多（与每秒提交的写事务相关）。
2 兼容性评估
1、数据库版本
如果版本不一致，请参考PostgreSQL 对应版本的 release notes，看看有哪些不兼容的地方，一般不兼容的地方，PG会在release notes里面给出migration 方法。
2、插件
用户需要的插件，在RDS PG中是否存在，如果在RDS PG中没有，那么两种选择1. 提工单看是否可以加上插件，2. 看看业务上是否可以修正，去掉这个插件的依赖。
3、插件版本
线下PG与RDS PG的插件版本不一样时，建议根据插件的RELEASE NOTES，判断是否需要修改业务？
4、业务需要的数据库用户权限
（超级用户、OR 普通用户，。。。）
RDS PG给的最大权限是rds_superuser，权限介于数据库的superuser 与 普通用户之间。
5、本土化参数
这个主要与数据库有关，建库时，使用的是C还是其他本地化collation。collation决定了排序、货币格式等。
创建数据库时可以指定，如果模板库不是你要的，那么你需要用template0来创建。
postgres=# create database db1 with template=template0 encoding='SQL_ASCII' LC_COLLATE='C' LC_CTYPE='C' ;
CREATE DATABASE
https://www.postgresql.org/docs/10/static/charset.html
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | | 127 GB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | =c/postgres +| 7561 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | =c/postgres +| 7561 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 rows)
同时对于wchar的模糊查询，也需要用collate 非 C设置。
《PostgreSQL 模糊查询最佳实践 - (含单字、双字、多字模糊查询方法)》
6、数据库字符集
如果线上线下字符集不一致，需要字符集转换。建议使用一致的字符集。创建数据库时可以指定。
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | | 127 GB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | =c/postgres +| 7561 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | =c/postgres +| 7561 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 rows)
postgres=# create database db1 with template=template0 encoding='SQL_ASCII' LC_COLLATE='C' LC_CTYPE='C' ;
CREATE DATABASE
7、CLIENT相关的参数
这些参数决定了一些使用上的风格，建议迁移前后做一下对比，保证兼容。
比如bytea_output，可以选择为十六进制输出，也可以选择为转义输出格式。
```
------------------------------------------------------------------------------
CLIENT CONNECTION DEFAULTS
------------------------------------------------------------------------------
- Statement Behavior -
search_path = '"$user", public' # schema names
row_security = on
default_tablespace = '' # a tablespace name, '' uses the default
temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace复制
check_function_bodies = on
default_transaction_isolation = 'read committed'
default_transaction_read_only = off
default_transaction_deferrable = off
session_replication_role = 'origin'
statement_timeout = 0 # in milliseconds, 0 is disabled
lock_timeout = 0 # in milliseconds, 0 is disabled
idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000
vacuum_multixact_freeze_min_age = 5000000
vacuum_multixact_freeze_table_age = 150000000
bytea_output = 'hex' # hex, escape
xmlbinary = 'base64'
xmloption = 'content'
gin_fuzzy_search_limit = 0
gin_pending_list_limit = 4MB
- Locale and Formatting -
datestyle = 'iso, mdy'
intervalstyle = 'postgres'
timezone = 'PRC'
timezone_abbreviations = 'Default' # Select the set of available time zone
# abbreviations. Currently, there are # Default # Australia (historical usage) # India # You can create your own file in # share/timezonesets/.复制
extra_float_digits = 0 # min -15, max 3
client_encoding = sql_ascii # actually, defaults to database
# encoding复制
These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF8' # locale for monetary formatting
lc_numeric = 'en_US.UTF8' # locale for number formatting
lc_time = 'en_US.UTF8' # locale for time formatting
default configuration for text search
default_text_search_config = 'pg_catalog.english'
- Shared Library Preloading -
shared_preload_libraries = '' # (change requires restart)
local_preload_libraries = ''
session_preload_libraries = ''
- Other Defaults -
dynamic_library_path = '$libdir'
jit = on # allow JIT compilation
jit_provider = 'llvmjit' # JIT implementation to use
```
```
------------------------------------------------------------------------------
VERSION AND PLATFORM COMPATIBILITY
------------------------------------------------------------------------------
- Previous PostgreSQL Versions -
array_nulls = on
backslash_quote = safe_encoding # on, off, or safe_encoding
default_with_oids = off
escape_string_warning = on
lo_compat_privileges = off
operator_precedence_warning = off
quote_all_identifiers = off
standard_conforming_strings = on
synchronize_seqscans = on
- Other Platforms and Clients -
transform_null_equals = off
```
```
------------------------------------------------------------------------------
CUSTOMIZED OPTIONS
------------------------------------------------------------------------------
Add settings for extensions here
```
3 迁移
大致步骤如下
1、测试迁移，全量数据迁移
2、全链路测试
3、清除数据，正式迁移数据
4、比对数据一致性
5、迁移业务
建议
1、如果线下数据库是9.4会以上版本，并且可以安装SO插件，则可以选择增量迁移服务，DTS服务，或者采用社区提供的pglogical工具。
2、如果线下数据库不允许安装so，建议1使用一次性的全量迁移，使用pg_dump或rds_dbsync一次性迁移都可以，参考文档：
《PostgreSQL 大版本升级方法之一 - 不落地并行导出导入》
《MySQL 不落地迁移、导入 PostgreSQL - 推荐 rds_dbsync》
3、如果线下数据库不允许安装so，建议2，如果数据库是9.4或以上版本，可以搭建一个物理从库，在物理从库上安装ali_decode.so，就可以支持通过这个物理从库来实现增量迁移了。
4、正式割接业务前建议的动作：
检查数据库参数配置是否符合预期，HA模块是否正常，备库延迟是否符合预期，备库是否正常，数据库规格是否符合预期，数据库所在服务器、网络、存储等硬件是否正常，数据库所在机器操作系统是否正常等。
检查索引是否与源库一致、垃圾回收、统计信息收集、FREEZE、检查点、预热热点数据。
灰度引流，查看数据库是否有报错，业务是否有报错，是否符合预期，检查TOP SQL是否需要优化，优化SQL，逐步切流量。
参考
https://www.postgresql.org/docs/10/static/charset.html
《PostgreSQL 模糊查询最佳实践 - (含单字、双字、多字模糊查询方法)》
《[未完待续] PostgreSQL pglogical 逻辑复制实现跨版本升级》
《MySQL准实时同步到PostgreSQL, Greenplum的方案之一 - rds_dbsync》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
