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

释放双眼,带上耳机,听听看~!

postgresql数据库 迁移方法

  • 记一次 postgresql,TimescaleDB数据库迁移过程)

文章目录

  • postgresql数据库 迁移方法
  • 前言
    • 1.准备工作
    • 2.分析情况
  • 一 业务库 迁移(postgresql数据库)
    • 1.数据备份
      • 1.1 对源数据库进行备份
      • 1.2传输备份文件
    • 2.创建新的数据库
      • 2.1创建新的数据库
      • 2.2 安装数据库插件
    • 3.数据迁移(整库还原)
      • 3.1 pg_restore用法
      • 3.2 还原命令
  • 二 时序库 迁移(TimescaleDB数据库)
    • 1 对源数据库进行处理
      • 1.1 直接传
      • 1.2 压缩传
    • 2.创建新的pg库,并改为TimescaleDB数据库
    • 3.迁移数据
      • 3.1复制新库之前配置的参数文件
      • 3.2 停下数据库TimescaleDB
      • 3.3 把迁移的data文件 移到pg目录下
        • 删除时序库原来的data目录
        • 把新的data文件 移到pg目录下
      • 3.4 还原之前复制的postgresql.conf文件
      • 3.5 启动TimescaleDB数据库

前言

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

1.准备工作

因本次迁移是需要迁移2个数据库,分别是:
分业务库:postgresql数据库
时序库 :TimescaleDB 数据库

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

数据量 业务库 80G
时序库 500G

2.分析情况

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


一 业务库 迁移(postgresql数据库)

1.数据备份

源数据库(业务库)
ip:0.0.0.0 端口 5432 用户 postgres 数据库名:cs
新数据库(业务库)
ip:1.1.1.1 端口 5432 用户 postgres 数据库名:cs2

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传输备份文件

首先这俩个服务器之间能够要ping同,这样才能传输文件
通过Lina上的scp 命令 传输

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

2.创建新的数据库

查看1.1.1.1服务器上是否装上postgresql数据库 ,没有的话需要自己装
创建数据库的可以看这篇文章安装 部署 postgresql数据库 搭建主从节点 (业务库)

因为我这个项目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 安装数据库插件

一般根据工作需要需要安装不同的postgres 插件
我这里只大体举个例子:

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数据库)

源数据库(业务库)
ip:0.0.0.2 端口 5432 用户 postgres 数据库名:cst
新数据库(业务库)
ip:1.1.1.2 端口 5432 用户 postgres 数据库名:cst2

由于时序库比较大 无法使用之前的方法,我们采用直接迁移postgres下的数据文件的办法
由于postgresql数据库的特性 我才去迁移pg目录下的data 文件夹的方式

1 对源数据库进行处理

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

1.1 直接传

同样需要网通
从0.0.0.2服务器进入数据库的目录下,我的是在/u01下
cd /u01/pg11.5

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/ 

解压
[root@1.1.1.2 ~]# cd /u01/pgsql/
[root@1.1.1.2 pgsql]# tar -zxvf data

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

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

3.迁移数据

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

[root@1.1.1.2 data]# cp pg_hba.conf /home/
[root@1.1.1.2 data]# cp postgresql.conf /home/

3.2 停下数据库TimescaleDB

[root@1.1.1.2 ~]# su - postgres
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D /u01/pgsql/data/ stop
waiting for server to shut down… done
server stopped

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

删除时序库原来的data目录

删除时序库原来的data目录
[root@1.1.1.2 ~]# cd /u01/pgsql/
[root@1.1.1.2 pgsql]# ls
data
[root@1.1.1.2 pgsql]# rm -fr data/

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

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

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

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

[root@1.1.1.2 data]# cp /home/postgresql.conf .
[root@1.1.1.2 data]# cp /home/pg_hba.conf .

3.5 启动TimescaleDB数据库

[root@1.1.1.2 ~]# su - postgres
-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D /u01/pgsql/data/ start
如果没有启动systemctl status postgresql-11或者log日志查看具体问题


如果遇到啥问题,请留言

为TA充电
共{{data.count}}人
人已赞赏
数码科技

红米Note10系列发布,小米继续“下沉”之路

2021-5-27 0:15:36

聊福利

Epic免费喜+1《Among Us》

2021-5-29 20:05:37

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索