日々Derailment

技術的なことの備忘録。脱線事故が起きまくってるので、なるべくrails wayを守れるよう頑張ります。

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に定期で送信、っていう感じでやってるのかな? そのあたりの運用も早めにやらないとな。