前言
在云计算的时代,数据是公司最宝贵的资产,而数据库在数据的产生,存储,消费的各个阶段都扮演着不可或缺的角色。数据库迁移是实现业务上云最重要的环节。 本章节主要关注在数据库迁移前的环境摸排,对现有环境做到心里有数。也方便在后期进行数据校验。
预检查项
1. 时区
在进行数据库迁移之前,需要提前关注数据库时区,将目标数据库的时区和主库保持一致。
# 查看全局配置
postgres=# show timezone;
# 查看每一个数据库的单独配置
postgres=# select a.setdatabase, b.datname,a.setconfig from pg_db_role_setting a,pg_database b where a.setdatabase = b.oid;
setdatabase | datname | setconfig
-------------+---------+----------------
16572 | rudonx1 | {TimeZone=UTC}
(1 row)
# 查看用户级的配置
postgres=# select rolname,rolconfig from pg_roles where rolname='wanyix';
rolname | rolconfig
---------+----------------
wanyix | {TimeZone=UTC}
(1 row)
```
`
## 2. 数据库 & 表大小
查看数据库大小:
````undefined
rudonx=# select pg_database.datname, pg_size_pretty (pg_database_size(oid)) AS size from pg_database;
```
`
查看表大小:
````undefined
rudonx=# select relname, pg_size_pretty(pg_total_relation_size(relid)) as size from pg_stat_user_tables;
```
`
## 3. 数据库字符集信息
````undefined
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
```
`
## 4. 主键
建议全部表中有主键,在使用 DTS 或者其他外部工具,我们都建议有主键来减少发生数据重复的可能性[1]。
````undefined
# 查看无主键的表
SELECT
n.nspname AS "Schema",
C.relname AS "Table Name",
C.relhasindex AS "Has PK"
FROM
pg_catalog.pg_class
C JOIN pg_namespace n ON ( C.relnamespace = n.oid AND n.nspname NOT IN ( 'information_schema', 'pg_catalog' ) AND C.relkind = 'r' )
WHERE
C.relhasindex = 'f'
ORDER BY
C.relhasindex,
C.relname;
```
`
## 5. 搜索路径
需要提前查看数据库以及用户级别的搜索路径,避免迁移后出现找不到表的情况。
````undefined
# 数据库级别配置
rudonx=# show search_path;
search_path
-----------------
"$user", public
(1 row)
# 用户级别配置
postgres=# select rolname,rolconfig from pg_roles where rolname='wanyix';
rolname | rolconfig
---------+-----------------------------------
wanyix | {TimeZone=UTC,search_path=rudonx}
(1 row)
```
`
## 5. 检查已经安装的插件
需要在源库中检查现有的插件,并在目标库中进行确认,以免迁移后由于插件不可用/不支持导致不兼容。
````undefined
postgres=# dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+-------------------------------------------------------
pageinspect | 1.7 | public | inspect the contents of database pages at a low level
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
```
`
## 6. 关键参数
对于数据库的关键参数,应该在迁移准备工作之前在目标库进行配置好,避免在迁移完成之后产生任何性能问题。
# 参考文档
[1] [https://www.volcengine.com/docs/6390/79135](https://www.volcengine.com/docs/6390/79135)
**如果您有其他问题,欢迎您联系火山引擎**[技术支持服务](https://console.volcengine.com/ticket/createTicketV2/)