AWSのEC2上にPostgresをインストールし、EC2上でtsvファイルをPostgresに読み込みたい。

3621 ワード
SSH PostgreSQL tsv scp EC2 SSH テキストリンク

AWSにインスタンスを作成する。

・AWSのリージョンはバージニア北部を使用
・インスタンスサイズは、t2.micro を使用
・秘密鍵を作成

秘密鍵の権限を600にchange mode(権限変更)する

chmod 600 /Users/[ユーザー名]/.ssh/[秘密鍵]

awsのサーバーに入る

ssh -i [秘密鍵のパス] ec2-user@[ipアドレス]

ミドルウェアのインストール

Postgresqlのインストール

$  sudo amazon-linux-extras install postgresql9.6
$  sudo yum install postgresql-server postgresql-devel postgresql-contrib

初期設定

$  sudo postgresql-setup initdb　　　← これで初期DBが作られる？

$  sudo systemctl start postgresql　　← 起動（.serviceは付けない）

$  sudo su - postgres　　← postgresユーザーにチェンジ

$  psql　　← postgresqlに接続

postgresユーザのパスワードが設定されていないので、以下でpostgresに設定

postgres=# alter role postgres with password 'postgres';

psqlを一旦抜けて、さらにpostgresユーザも抜けて　ec2-user に一旦戻る

$  sudo su -    でroot ユーザにスイッチ

★/var/lib/pgsql/data/pg_hba.conf をviで編集し、認証方法を変更する

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

設定ファイル変更後はpostgresの再起動を忘れずに

$ exit　　　ec2-user に戻る

$  sudo systemctl restart postgresql　　　　←　postgres 再起動

$ psql -U postgres　　　　←　ec2-user でpsql 実行

ここまでいけば、ローカルからのpgAdminでSSHトンネル設定でもつながるはず。

SSHトンネル設定

トンネルホスト
　　→EC2インスタンスのパブリックIPに読み替える

識別情報ファイル
　　→鍵ファイルに読み替える

つながらない場合は？

  • サーバー側でちゃんとpostgreSQLが起動しているか
  • pg_hba.conf の設定はちゃんとできているか（METHODを「md5」に設定したか）
  • pg_hba.conf 設定後、postgreSQL を再起動したか

scpでローカルにあるtsvファイルをec2上に転送する

scp -i [秘密鍵のパス] [転送したいファイルのパス] ec2-user@[ipアドレス]:[/ec2上のディレクトリ]

本題のtsvファイルの読み込み

sshトンネルを使いec2上のPostgresにローカルのpgAdminでアクセスする。

pgAdminからcopyコマンドを実行したが、権限がないと怒られる。
→何の権限？

しょうがないので、tmpディレクトリに一時的に.tsvファイルを移動し、再度copyコマンドを実行したら成功した。

/home/ec2-user/tmp

デプロイに関しては下記のURLが参考になりました。