记录一次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;