手把手教你如何进行postgresql数据库迁移 让你成为dba大佬

postgresql数据库 迁移方法

• • （记一次 postgresql，TimescaleDB数据库迁移过程）

前言

因工作需要 需要对数据库所在的linux服务器 进行迁移 更换到新的服务器

1.准备工作

环境：linux CentOS 6
数据库版本：postgresql 11.5

2.分析情况

针对这个情况进行分析 ，我发现可以对业务库采取 备份——还原 的方式进行迁移数据 毕竟数据量不大 ，但是对于时序库却不能采取这个方法 1. 是数据量大，2 .是TimescaleDB数据库含有大量的超表 备份还原是有一定的问题的 ，所有采取直接迁移postgres 下的data 文件 在修改配置的问题

一 业务库 迁移（postgresql数据库）

1.数据备份

1.1 对源数据库进行备份

pg_dump --file "/u01/pgsql-all.backup" --host "0.0.0.0" --port "5432" --username "postgres" --dbname "cs" --verbose --role "postgres" --format=c --blobs --encoding "UTF8" 

关于pg_dump 的用法 可以看这篇文章：链接直达

1.2传输备份文件

 cd /u01 scp pgsql-all.backup user@1.1.1.1:/tmp/ 

2.创建新的数据库

因为我这个项目1.1.1.1上已经预装了postgresql-11.5的版本了 ，

2.1创建新的数据库

直接用sql创建

CREATE DATABASE cs; 

或用使用createdb

createdb -h 1.1.1.1 -p 5432 -e -U postgres cs 

createdb 是一个 SQL 命令 CREATE DATABASE 的封装。

createdb 创建一个 PostgreSQL 数据库. 使用方法: createdb [选项]... [数据库名称] [描述] 选项: -D, --tablespace=TABLESPACE 数据库默认表空间 -e, --echo 显示发送到服务端的命令 -E, --encoding=ENCODING 数据库编码 -l, --locale=LOCALE 数据库的本地化设置 --lc-collate=LOCALE 数据库的LC_COLLATE设置 --lc-ctype=LOCALE 数据库的LC_CTYPE设置 -O, --owner=OWNER 新数据库的所属用户 -T, --template=TEMPLATE 要拷贝的数据库模板 -V, --version 输出版本信息, 然后退出 -?, --help 显示此帮助, 然后退出 联接选项: -h, --host=HOSTNAME 数据库服务器所在机器的主机名或套接字目录 -p, --port=PORT 数据库服务器端口号 -U, --username=USERNAME 联接的用户名 -w, --no-password 永远不提示输入口令 -W, --password 强制提示输入口令 --maintenance-db=DBNAME 更改维护数据库 默认情况下, 以当前用户的用户名创建数据库. 

2.2 安装数据库插件

CREATE EXTENSION "postgres_fdw"; Create extension "postgis"; Create extension "uuid-ossp" 

由于我们之前是全量备份整个库 它会自动帮你安装 如果只备份单模式或者备份部分的是需要安装插件的，需要保持新旧库一致的，要不之后的还原会报错的

3.数据迁移（整库还原）

这里使用pg_restore来还原备份做数据库迁移的

3.1 pg_restore用法

用法: pg_restore [选项]... [文件名] 一般选项: -d, --dbname=名字 连接数据库名字 -f, --file=文件名 输出文件名 -F, --format=c|d|t 备份文件格式(应该自动进行) -l, --list 打印归档文件的 TOC 概述 -v, --verbose 详细模式 -V, --version 输出版本信息, 然后退出 -?, --help 显示此帮助, 然后退出 恢复控制选项: -a, --data-only 只恢复数据, 不包括模式 -c, --clean 在重新创建之前，先清除（删除）数据库对象 -C, --create 创建目标数据库 -e, --exit-on-error 发生错误退出, 默认为继续 -I, --index=NAME 恢复指定名称的索引 -j, --jobs=NUM 执行多个并行任务进行恢复工作 -L, --use-list=FILENAME 从这个文件中使用指定的内容表排序 输出 -n, --schema=NAME 在这个模式中只恢复对象 -N, --exclude-schema=NAME 不恢复此模式中的对象 -O, --no-owner 不恢复对象所属者 -P, --function=NAME(args) 恢复指定名字的函数 -s, --schema-only 只恢复模式, 不包括数据 -S, --superuser=NAME 使用指定的超级用户来禁用触发器 -t, --table=NAME 恢复命名关系（表、视图等） -T, --trigger=NAME 恢复指定名字的触发器 -x, --no-privileges 跳过处理权限的恢复 (grant/revoke) -1, --single-transaction 作为单个事务恢复 --disable-triggers 在只恢复数据的过程中禁用触发器 --enable-row-security 启用行安全性 --if-exists 当删除对象时使用IF EXISTS --no-comments 不恢复注释 --no-data-for-failed-tables 对那些无法创建的表不进行 数据恢复 --no-publications 不恢复发行 --no-security-labels 不恢复安全标签信息 --no-subscriptions 不恢复订阅 --no-tablespaces 不恢复表空间的分配信息 --section=SECTION 恢复命名节 (数据前、数据及数据后) --strict-names 要求每个表和(或)schema包括模式以匹配至少一个实体 --use-set-session-authorization 使用 SESSION AUTHORIZATION 命令代替 ALTER OWNER 命令来设置所有权 联接选项: -h, --host=主机名 数据库服务器的主机名或套接字目录 -p, --port=端口号 数据库服务器的端口号 -U, --username=名字 以指定的数据库用户联接 -w, --no-password 永远不提示输入口令 -W, --password 强制口令提示 (自动) --role=ROLENAME 在恢复前执行SET ROLE操作 选项 -I, -n, -N, -P, -t, -T, 以及 --section 可以组合使用和指定 多次用于选择多个对象. 如果没有提供输入文件名, 则使用标准输入. 

3.2 还原命令

pg_restore --host "1.1.1.1" --port "5432" --username "postgres" --role "postgres" --dbname "cs" --verbose /tmp/pgsql-all.backup 

等还原完成后，业务库就已经还原完成了，下面开始迁移时序库

二 时序库 迁移（TimescaleDB数据库）

1 对源数据库进行处理

这里有2种方法，一种是直接传，一种是压缩后传

1.1 直接传

scp -r data user@1.1.1.2:/u01/

1.2 压缩传

[root@0.0.0.2 pgsql]# pwd /u01/pgsql [root@0.0.0.2 pgsql]# tar -zcvf data.tar.gz data [root@0.0.0.2 pgsql]#scp -r data user@1.1.1.2:/u01/ 

2.创建新的pg库，并改为TimescaleDB数据库

详情可以看安装 部署 postgresql数据库 搭建timescaledb数据库 （时序库）这篇文章

3.迁移数据

3.1复制新库之前配置的参数文件

3.2 停下数据库TimescaleDB

3.3 把迁移的data文件 移到pg目录下

删除时序库原来的data目录

如果害怕可以备份一下（或者改个名）

把新的data文件 移到pg目录下

[root@1.1.1.2 u01]# mv data /u01/pgsql/

3.4 还原之前复制的postgresql.conf文件

3.5 启动TimescaleDB数据库

如果遇到啥问题，请留言