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

前提条件

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

操作步骤

  1. 在ECS实例上安装PostgreSQL自带工具。
    说明 pg_dump版本必须与自建库一致，pg_restore版本建议与RDS PostgreSQL一致。本文示例为PostgreSQL 12，您也可根据业务需要自行选择其他版本。
    1
    2
    3
    4
    5
    --安装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版本正确。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    #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问题导致迁移失败。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    #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。
    1
    2
    #CREATE ROLE postgres;
    #ALTER ROLE postgres WITH rds_SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md5d5df0dxxxxxxxc88a541fec598f';
    RDS导入用户
  5. 连接到RDS PostgreSQL实例创建目标数据库。
    说明 encoding需要和自建库相同，本文示例为UTF8。
    1
    #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元数据默认使用小写字母存储， 除非您的数据库和用户在创建时使用双引号包括并且大写。
        1
        2
        3
        4
        5
        6
        #vi ~/.pgpass   
         
        pgm-xxx.pg.rds.aliyuncs.com:1921:db1:用户:密码
        127.0.0.1:5432:postgres:用户:密码
         
        #chmod 400 ~/.pgpass
      2. 用管道迁移数据。
        1
        #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. 导出文件。
        1
        2
        3
        #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. 导入文件。
        1
        #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导入时，采取并行的选项。

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