RDSのpostgresqlからバックアップをとってローカルに反映する
本番環境のバックアップ&ローカルで使いたかったのでサクッとダウンロードしたかったので、手順まとめ。
1.pg_dumpでダンプ
pg_dump -U ユーザー名 -h ホスト -p ポート データベース名 -f ファイル名
例.
pg_dump -U hoge -h fuga-database.abcdefg1234.ap-northeast-1.rds.amazonaws.com -p 5432 hogefuga_database -f backup.sql
2.scpコマンドでローカルに持ってくる
scp -i xxx.pem ec2-user@ipアドレスorホスト名:ファイルのパス ローカルの保存先
例.
scp -i ~/.ssh/hoge.pem ec2-user@123.456.789.0:/usr/backup.sql ~/downloads
3.ローカルのpostgresqlでロード
psql -U ユーザー名 -h localhost -d データベース名 -p ポート -f ファイル名
例.
psql -U hoge -h localhost -d hogefuga_development -p 5432 -f backup.sql
補足1.pg_dump:aborting because of server version mismatch
手順1のダンプの時に、こんなエラーが出たインスタンスがあった。
pg_dump: server version: 9.6.11; pg_dump version: 9.2.23 pg_dump: aborting because of server version mismatch
サーバーとpg_dumpのバージョンが違うよ！っていうことみたい。 なのでpg_dumpのアップデートをする。
sudo rpm -Uvh https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6.9-x86_64/pgdg-ami201503-96-9.6-2.noarch.rpm Preparing... ################################# [100%] Updating / installing... 1:pgdg-ami201503-96-9.6-2 ################################# [100%] sudo yum install postgresql96 Loaded plugins: extras_suggestions, langpacks, priorities, update-motd pgdg96 | 4.1 kB 00:00:00 (1/2): pgdg96/x86_64/group_gz | 249 B 00:00:01 (2/2): pgdg96/x86_64/primary_db | 211 kB 00:00:01 28 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ---> Package postgresql96.x86_64 0:9.6.12-1PGDG.rhel6 will be installed --> Processing Dependency: postgresql96-libs(x86-64) = 9.6.12-1PGDG.rhel6 for package: postgresql96-9.6.12-1PGDG.rhel6.x86_64 --> Running transaction check ---> Package postgresql96-libs.x86_64 0:9.6.12-1PGDG.rhel6 will be installed --> Finished Dependency Resolution Dependencies Resolved ===================================================================================================================================================================================== Package Arch Version Repository Size ===================================================================================================================================================================================== Installing: postgresql96 x86_64 9.6.12-1PGDG.rhel6 pgdg96 1.4 M Installing for dependencies: postgresql96-libs x86_64 9.6.12-1PGDG.rhel6 pgdg96 290 k Transaction Summary ===================================================================================================================================================================================== Install 1 Package (+1 Dependent package) Total download size: 1.7 M Installed size: 8.4 M # 確認されるのでyを押してエンター Is this ok [y/d/N]: y Downloading packages: (1/2): postgresql96-libs-9.6.12-1PGDG.rhel6.x86_64.rpm | 290 kB 00:00:01 (2/2): postgresql96-9.6.12-1PGDG.rhel6.x86_64.rpm | 1.4 MB 00:00:03 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 540 kB/s | 1.7 MB 00:00:03 Running transaction check Running transaction test Transaction test succeeded Running transaction Warning: RPMDB altered outside of yum. Installing : postgresql96-libs-9.6.12-1PGDG.rhel6.x86_64 1/2 Installing : postgresql96-9.6.12-1PGDG.rhel6.x86_64 2/2 failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql exists and it is not a symlink failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb: /usr/bin/clusterdb exists and it is not a symlink failed to link /usr/bin/createdb -> /etc/alternatives/pgsql-createdb: /usr/bin/createdb exists and it is not a symlink failed to link /usr/bin/createlang -> /etc/alternatives/pgsql-createlang: /usr/bin/createlang exists and it is not a symlink failed to link /usr/bin/createuser -> /etc/alternatives/pgsql-createuser: /usr/bin/createuser exists and it is not a symlink failed to link /usr/bin/dropdb -> /etc/alternatives/pgsql-dropdb: /usr/bin/dropdb exists and it is not a symlink failed to link /usr/bin/droplang -> /etc/alternatives/pgsql-droplang: /usr/bin/droplang exists and it is not a symlink failed to link /usr/bin/dropuser -> /etc/alternatives/pgsql-dropuser: /usr/bin/dropuser exists and it is not a symlink failed to link /usr/bin/pg_basebackup -> /etc/alternatives/pgsql-pg_basebackup: /usr/bin/pg_basebackup exists and it is not a symlink failed to link /usr/bin/pg_dump -> /etc/alternatives/pgsql-pg_dump: /usr/bin/pg_dump exists and it is not a symlink failed to link /usr/bin/pg_dumpall -> /etc/alternatives/pgsql-pg_dumpall: /usr/bin/pg_dumpall exists and it is not a symlink failed to link /usr/bin/pg_restore -> /etc/alternatives/pgsql-pg_restore: /usr/bin/pg_restore exists and it is not a symlink failed to link /usr/bin/reindexdb -> /etc/alternatives/pgsql-reindexdb: /usr/bin/reindexdb exists and it is not a symlink failed to link /usr/bin/vacuumdb -> /etc/alternatives/pgsql-vacuumdb: /usr/bin/vacuumdb exists and it is not a symlink Verifying : postgresql96-9.6.12-1PGDG.rhel6.x86_64 1/2 Verifying : postgresql96-libs-9.6.12-1PGDG.rhel6.x86_64 2/2 Installed: postgresql96.x86_64 0:9.6.12-1PGDG.rhel6 Dependency Installed: postgresql96-libs.x86_64 0:9.6.12-1PGDG.rhel6 Complete!
これでインストール完了。 で、いざ実行！
pg_dump -U hoge -h fuga-database.abcdefg1234.ap-northeast-1.rds.amazonaws.com -p 5432 hogefuga_database -f backup.sql pg_dump: server version: 9.6.11; pg_dump version: 9.2.23 pg_dump: aborting because of server version mismatch
なんで？？ と頭をひねった結果、シンボリックリンクが変わってないっぽい。 とりあえずシンボリックリンクの変更とかはハマりそうだったので、直接実行ファイルを指定して実行。
cd /usr/pgsql-9.6/bin ./pg_dump -U hoge -h fuga-database.abcdefg1234.ap-northeast-1.rds.amazonaws.com -p 5432 hogefuga_database -f backup.sql
で、無事動いた！
補足2.psql:agri_system.sql:1632: ERROR: role "rdsadmin" does not existについて
3.の手順のコマンド走らせると、途中でエラーが出た。
# 処理が色々走る SET SET SET SET SET set_config ------------ (1 row) ︙ psql:agri_system.sql:1632: ERROR: role "rdsadmin" does not exist REVOKE GRANT GRANT
これは'rdsadmin'というroleがローカルに設定されていないため発生している模様。
この辺、1632行目でなんの処理をしているのかというところなんですが
-- -- Name: SCHEMA public; Type: ACL; Schema: -; Owner: hoge -- REVOKE ALL ON SCHEMA public FROM rdsadmin; REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO hoge; GRANT ALL ON SCHEMA public TO PUBLIC;
REVOKEは権限の削除の命令文。
REVOKE 権限の内容 ON レベル FROM ユーザー名;
REVOKE ALLで全ての権限を指定できる。
REVOKE ALL ON SCHEMA public FROM rdsadmin;
つまり、rdsadminからスキーマレベルの権限を全て削除、ということになる。
とりあえずはインポート自体は正常にできてるし、ここは権限の削除の処理なので、実行されなくてもそんなに問題ない部分だと思われる。（たぶん）
意外に簡単なので、もっと早くやってみるべきだったかな。
あと、バックアップとしてはみんなS3に定期で送信、っていう感じでやってるのかな？ そのあたりの運用も早めにやらないとな。
