がじぇ

お金と家電とプログラミングのブログ

【AWS】EC2インスタンスのPostgreSQLからRDSへデータを移行する

こんにちわ

がじぇったー (@hackmylife7) | Twitter

です。

結構ハマったのでEC2インスタンスからRDSへPostgreSQLのデータを移行する手順を書き残しておきます。

作業環境

移行元

  • EC2(CentOS6)
  • PostgreSQL 9.4.5
  • EC2(CentOS7 管理用サーバ)

移行先

前提

  • PostgreSQLのデータ移行はpg_dumpallでまるっとdumpファイルを作成するのが便利ですが、RDSにたいしその方法でデータを移行するとsuperuser権限が必要な箇所で失敗します。
  • https://forums.aws.amazon.com/thread.jspa?messageID=503791
  • 作業の流れとしてはRDSを移行元と同様の環境にするためにDB,Roleなどを先に設定し、その後、移行元のPostgreSQLでデータのダンプ処理を行い、RDSにデータを反映します。
  • 管理用サーバからRDSへのSecurityGroupの疎通設定は事前にされていることとします

TL;DR(要約)

  • dump_allコマンド取得したdumpファイルは使えないと思ったが、userやDB作成を事前にRDSで行っておけばデータの取り込みができた

PostgreSQLのローカルインストール

まずEC2(CentOS7 管理用サーバ)にRDSのPostgreSQLと同じバージョンの PostgreSQLをインストールします

# yum update
# yum -y install gcc readline-devel zlib-devel perl postgresql-contrib
# cd /usr/local/src/
# useradd -m -d /home/postgres postgres
# wget https://ftp.postgresql.org/pub/source/v9.5.2/postgresql-9.5.2.tar.gz
# tar xvfz postgresql-9.5.2.tar.gz
# cd postgresql-9.5.2
# ./configure
# make
# make check
# make install

RDSにDBとUser(Role)の作成

管理用サーバからRDSにログインします

psql -h hogehoge.ap-northeast-1.rds.amazonaws.com -U postgres

移行元サーバの環境に合わせDBの作成

postgres=> create database hogehoge;
CREATE DATABASE
postgres=> create database hugahuga;
CREATE DATABASE

移行元サーバの環境に合わせUser(Role)の作成

  • 厳密にsuperuserはRDSで作成することはできないが、マスターユーザは以下のコマンドで作成が可能
postgres=> create role testuser with password 'aaaa' login;
CREATE ROLE
postgres=> grant rds_superuser to testuser;
GRANT ROLE
postgres=> \du
                      ロール一覧
   ロール名    |        属性        |    メンバー
---------------+--------------------+-----------------
 testuser           |                    | {rds_superuser}
 postgres      | ロールを作成できる | {rds_superuser}
               : DBを作成できる
 rds_superuser | ログインできない   | {}
 rdsadmin      | スーパーユーザ     | {}
               : ロールを作成できる
               : DBを作成できる
 rdsrepladmin  | 継承なし           | {}
               : ログインできない
postgres=>

作成したDBの権限変更

postgres=> ALTER DATABASE testuser OWNER TO testuser;
ALTER DATABASE
postgres=> \l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |      アクセス権
-----------+----------+------------------+-------------+-------------------+-----------------------
 testuser       | testuser      | UTF8             | en_US.UTF-8 | en_US.UTF-8       |

移行元のPostgre DBのDump処理

cd /tmp
/usr/local/pgsql/bin/pg_dumpall -f "db_backupall_20200430.sql"

RDSへリストア処理

[root@ip-10-40-20-239 tmp]#  /usr/local/pgsql/bin/psql -f db_backupall_20200430.sql --host hogehoge.ap-northeast-1.rds.amazonaws.com -U postgres --port 5432  --username testuser
パスワードを入力

参考

Route 53 を使用中のドメインの DNS サービスにする - Amazon Route 53

postgresql - Moving a database with pg_dump and psql -U postgres db_name < ... results in "ERROR: relation "table_name" does not exist" - Stack Overflow

How to solve privileges issues when restore PostgreSQL Database - Stack Overflow

https://www.ibcscorp.com/aws/rds/upgrading-postgresql/

https://www.postgresql.jp/document/9.2/html/app-pgdump.html

django - Migrate postgres dump to RDS - Stack Overflow

https://stackoverflow.com/questions/3518218/moving-a-database-with-pg-dump-and-psql-u-postgres-db-name-results-in-er

database migration - Migrate postgresql schema AWS RDS - Stack Overflow

https://forums.aws.amazon.com/thread.jspa?messageID=503791

初心者にお勧めのAWS