记录一次pg整库的数据库迁移
迁库注意的事项(概念性的)
1. 需要判断是否能够进行整库迁移。
2. 对线上程序的影响。
3. 迁库要进行彩排(或者是预演),这样才对未知问题、消耗时间等有预期。
4. 对迁移方式进行综合考虑
迁库方式
使用navicat迁移
优点:操作简便、可视
缺点:
1.对于较大的库迁移耗费时间过长
2.无主键的表无法对比其数据间的差异
3.对表数据的新增、修改、删除,无法选择某张表对比。只能整库对比,才能发现哪个表存在数据差异。
4.迁移过程中,主表、从表等迁移顺序等原因,会造成迁移报错。
5.迁移过程中人工操作的内容太多
6.迁库索引不是迁移的索引创建语句,而是迁移的索引数据,导致会很缓慢并且很多余。使用程序迁移
优点:能够快速的对比数据的差异,适用于很短的时间内完成库的迁移工作(仍需停服务)。
缺点:
1.需要进行开发
2.其他未知情形存在判断不全的情况
3.要想停服务的时间短,需要迁两次,一次迁移绝大部分,第二次迁移变更部分。
4.源库的表,需要新增,修改,版本号等字段支持。主从方式迁移
优点:简单、方便、高效
缺点:对新旧库的数据库版本,操作系统版本有限制要求。使用导入、导出的方式进行迁移
优点:
1.快速、高效、稳定
2.如果其他迁移方式或多或少都不合适的话,那就采用这种方式。
缺点:
1.需要进行彩排,防止出现迁移过程中意外报错的情况
2.迁移需要停应用服务
3.需要执行相关命令
"导入、导出” 方式的具体操作步骤 (概念性)
1.准备前工作:
准备好迁库后需要部署的程序分支(含新的数据库链接地址等)
确认新库的磁盘是否足够
2.迁移时工作:
导出时停服务(若不能停服务则挂出:系统维护中界面,需要kafka等消息队列支持)
记录报错信息,进行单独处理
确认将要进行导入操作的数据库为主库
3.迁移后工作:
抽查几张表,确认数据条数是否一致
确认主从复制是否正常
确认pg高可用,主从复制、回卷等数据库各项设置是否正常
4.启动应用:
启动服务,验证程序操作数据库情况是否正常
DCL语句
#创建用户
create user opbizuser with password 'opbizuser@2111';
#创建数据库赋予用户
create DATABASE opbizbasedb2 ENCODING ='utf-8' TABLESPACE = pg_default owner opbizuser;
#将数据库的权限赋予用户
GRANT ALL ON DATABASE opbizbasedb2 TO opbizuser;
导入、导出及传输(库,用户自建)
1.登录旧库主库的195
su - postgres
2-1.导出
pg_dump -h 10.13.198.19 -p 5432 -d postgres -Fc -v -f /backup/20211029_db_hk.bak
2-2.输入源库密码
pg@123456
3.远程传输(此次此方式不行,需要源端密码,源端密码不知道)
scp -r /backup/20211028_db_hk.bak root@10.13.58.19:/pgdb/1028
4.导入数据库
pg_restore -p 10001 -d opbizbasedb2 -j 5 /pgdb/1029/20211029_db_hk.bak
导入、导出及传输(库,用户整体导)
注意:
1.自建的隐式转换需要单独导
2.用户需要pg_dumpall单独导
3.导出用户权限
pg_dumpall -g -h 10.13.198.19 -p 5432 -U postgres
4.退出,进行操作系统,执行导出(建目录,为目录授权)
pg_dump -h 10.13.198.19 -p 5432 -d postgres -Fc -v -f /backup/20211028_db_hk.bak
如果 不想看导出过程 把-v去掉
数据库表行数统计sql
- 表中的所有记录(有系统表)
select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' order by rowCounts desc
- 表中的所有记录(无系统表)
select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public') order by rowCounts desc;
其他相关命令
1. 数据库主从查看
ps aux | grep postgres
出现:wal receiver process 为从服务器
2. 查看数据库的字符版本信息
su - postgres
psql \l+ 查看数据库的基本信息,9.6.19
删掉测试用户等信息的相关sql
select pid,datname,usename,application_name,client_hostname,backend_start,state from pg_stat_activity
where datname = '要删除的数据库名字';
drop owned by opbizbaseuser1 cascade;
drop user opbizbaseuser1 cascade;