本文介绍如何通过ECS实例将PostgreSQL数据迁移到目标RDS PostgreSQL实例中。

前提条件

准备一台可以连接到RDS和自建库的ECS(建议使用Linux系统)。

操作步骤

  1. 在ECS实例上安装PostgreSQL自带工具。
    说明 pg_dump版本必须与自建库一致,pg_restore版本建议与RDS PostgreSQL一致。本文示例为PostgreSQL 12,您也可根据业务需要自行选择其他版本。
    --安装RPM 
    #yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm    
        
    --安装PostgreSQL 
    #yum install -y postgresql12-*    
  2. 配置环境变量,保证导出导入时使用的PostgreSQL版本正确。
    #su - postgres    
        
    #vi .bash_profile     
        
    --追加内容    
    export PS1="$USER@`/bin/hostname -s`-> "              
    export LANG=en_US.utf8            
    export PGHOME=/usr/pgsql-12            
    export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH            
    export DATE=`date +"%Y%m%d%H%M"`            
    export PATH=$PGHOME/bin:$PATH:.            
    export MANPATH=$PGHOME/share/man:$MANPATH             
    alias rm='rm -i'            
    alias ll='ls -lh'            
    unalias vi       
  3. 导出用户。
    说明 一定要先迁移用户,否则可能因为对象的权限或Owner问题导致迁移失败。
    #pg_dumpall -g -h 127.0.0.1 -p 5432 -U postgres
    
    --
    -- PostgreSQL database cluster dump
    --
    
    SET default_transaction_read_only = off;
    
    SET client_encoding = 'UTF8';
    SET standard_conforming_strings = on;
    
    --
    -- Roles
    --
    
    CREATE ROLE postgres;
    ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md5d5df0dxxxxxxxc88a541fec598f';
    
    --
    -- PostgreSQL database cluster dump complete
    --
  4. 将上一步返回的Roles部分命令略做修改,在RDS PostgreSQL实例中执行命令导入用户,其中SUPERUSER需要修改为rds_superuser。
    #CREATE ROLE postgres;
    #ALTER ROLE postgres WITH rds_SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md5d5df0dxxxxxxxc88a541fec598f';
    RDS导入用户
  5. 连接到RDS PostgreSQL实例创建目标数据库。
    说明 encoding需要和自建库相同,本文示例为UTF8。
    #create database db1 with template template0 encoding 'UTF8' lc_ctype 'en_US.utf8' lc_collate 'C';
  6. 数据导出及导入。您可以使用如下三种方法:
    • 在线迁移

      当自建库和RDS PstgreSQL实例可以直接连接时,可以使用此方案。

      1. 配置密码文件。格式为:host:port:dbname:username:password
        说明 dbname和username必须小写,因为PostgreSQL元数据默认使用小写字母存储, 除非您的数据库和用户在创建时使用双引号包括并且大写。
        #vi ~/.pgpass    
        
        pgm-xxx.pg.rds.aliyuncs.com:1921:db1:用户:密码
        127.0.0.1:5432:postgres:用户:密码
        
        #chmod 400 ~/.pgpass  
      2. 用管道迁移数据。
        #nohup pg_dump -F p -h 127.0.0.1 -p 5432 -U postgres -d postgres --no-tablespaces | time psql -h pgm-bpxxxxx.pg.rds.aliyuncs.com -p 1921 -U postgres --single-transaction db1 > ./pg.dump.log 2>&1 & 
        说明 您可以查看pg.dump.log文件检查错误日志,因为设置了--single-transaction选项, 所以解决报错后可以直接重新导入。
    • 离线导出并导入

      如果自建库和RDS PstgreSQL实例无法直接连接时,可以先使用pg_dump导出文件,然后将文件拷贝到能连接RDS的主机,最后使用pg_restore导入。

      说明 导出时pg_dump版本需要与自建库一致,导入时pg_restore版本需要与RDS一致。
      1. 导出文件。
        #nohup pg_dump -F c -h 127.0.0.1 -p 5432 -U postgres -d postgres --no-tablespaces -f ./pg.dump > ./pg.dump.log 2>&1 &   
        #ll pg.dump    
        -rw-rw-r-- 1 digoal digoal 4.2M Aug 31 10:17 pg.dump    
        说明 等待导出完成,检查pg.dump.log文件,确认没有报错。
      2. 导入文件。
        #pg_restore -h pgm-bpxxxxx.pg.rds.aliyuncs.com -p 1921 -U postgres -d db1 --no-tablespaces --single-transaction  pg1.dump  >./pg1.restore.log
        说明 如果前面步骤设置的MD5密码,可能会报错,建议您在控制台重置postgres用户的密码。重置密码请参见重置密码

      耐心等待导入结束,您可以查看pg.dump.log文件检查错误日志,因为设置了--single-transaction选项, 所以解决报错后可以直接重新导入。

    • 离线导出并导入(并行迁移实现加速)

      与上一方法类似,只是在使用pg_restore导入时,采取并行的选项。

      nohup pg_restore -U postgres -d db1 --no-tablespaces -j 4 /tmp/pg.dump  >./pg.restore.log 2>&1 &
      说明 并行参数-j--single-transaction参数不能同时使用。