はじめに
データベースを触ったことがないので、windowsにポスグレをインストールし基本的な操作を触ってみました。その後、バックアップしたデータをAWSのRDSで復元し同様の環境を構築してみます。
Windows編 PostgreSQLのインストール
こちらhttps://www.enterprisedb.com/downloads/postgres-postgresql-downloads
から使用のOSにあったものをインストールします。私はWindows x86-64の13.2をインストールしました。
\PostgreSQL\ﾊﾞｰｼﾞｮﾝ\data\postgresql.conf
こちらのテキストファイルのlisten_address ="*"と記載されている部分を'localhost'に書き換え、serviceでPostgreSQLを再起動し他の端末からアクセスされないようにします。
インストールが終わったら、コマンドプロンプトを起動しpsql.exeを実行しポスグレが起動するか確認してみます。
psql.exe -U postgres
ユーザ postgres のパスワード:設定したパスワードを入力
psql (13.2)
"help"でヘルプを表示します。
postgres=#
データベースの作成
shopという名前のデータベースを作成します。
postgres=# CREATE DATABASE shop;
CREATE DATABASE
データベースへのアクセス
psql.exe -U postgres -d shop
ユーザ postgres のパスワード:
psql (13.2)
"help"でヘルプを表示します。
shop=#
shopへアクセス出来ました。
テーブルの作成
小売店の商品データベースを想定し、以下のようなテーブルを作成しました
| 項目 | Shohinテーブルで定義した列名 | データ型 |
|---|---|---|
| 商品ID | shohin_id | 固定長文字列 |
| 商品名 | shohin_mei | 可変長文字列 |
| 商品分類 | shohin_bunrui | 可変長文字列 |
| 販売単価 | hanbai_tanka | データ型 |
| 仕入れ単価 | shiire_tanka | データ型 |
| 登録日 | tourokubi | 日付型 |
shop=# CREATE TABLE shohin
shop-# (shohin_id CHAR(4) NOT NULL,
shop(# shohin_mei VARCHAR(100) NOT NULL,
shop(# shohin_bunrui VARCHAR(32) NOT NULL,
shop(# hanbai_tanka INTEGER,
shop(# shiire_tanka INTEGER,
shop(# torokubi DATE,
shop(# PRIMARY KEY (shohin_id));
CREATE TABLE
データ型について
・INTEGER型
整数を入れる列に指定するデータ型
・CHAR型
文字の長さを固定する固定長文字列でCHAR(文字列の長さ)で指定する。
CHAR(8)で'abc'を定義すると'abc 'とスペースを後ろに挿入しで8文字に固定する
・VARCHAR型
CHAR型と違い、可変長文字列を指定する。VARCHAR(8)で'abc'を定義すると'abc'のまま格納される。
・DATE型 日付を入れる列にしているするデータ型
制約の設定
shohin_idの後にNOT NULLと記載してあるのは、必ずデータが入っていないとエラーを返すようにする制約です。商品IDのない商品を登録することが出来ないようにしています。
主キー制約
データベースのデータ（行、レコード）を一意に識別するための項目です。
商品IDをユニークな値に設定していることで、どの商品であるか特定が出来ます。
データ登録
shop=# begin transaction;
BEGIN
shop=*# insert into Shohin values('0001','Tシャツ','衣服',1000,500,'2009-09-20');
INSERT 0 1
shop=*# insert into Shohin values('0002','穴あけパンチ','事務用品',500,320,'2009-09-11');
INSERT 0 1
shop=*# commit;
COMMIT
出力結果
shop=# select * from Shohin;
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+--------------+---------------+--------------+--------------+------------
0001 | Tシャツ | 衣服 | 1000 | 500 | 2009-09-20
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
(2 行)
データベースにデータを格納し、それをselectで確認することが出来ました。
データのバックアップ
データのバックアップには、pg_dumpコマンドを利用しました。
$ pg_dump database名
表示されるバックアップデータに文字化けがないことを確認したら、データに出力させます
pg_dump database名 > バックアップファイル名
これで作成したデータベースのバックアップが取れました。
awsの環境構築
EC2をパブリックサブネットに、RDSをプライベートに配置しSSHでPCから接続します。
*EC2のインスタンス作成は割愛します
・postgreのパッケージを検索します
sudo yum list | grep postgre
freeradius-postgresql.x86_64 3.0.13-15.amzn2 amzn2-core
pcp-pmda-postgresql.x86_64 4.3.2-12.amzn2.0.1 amzn2-core
postgresql.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-contrib.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-devel.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-docs.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-jdbc.noarch 9.2.1002-8.amzn2 amzn2-core
postgresql-jdbc-javadoc.noarch 9.2.1002-8.amzn2 amzn2-core
postgresql-libs.i686 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-libs.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-odbc.x86_64 09.03.0100-2.amzn2.0.2 amzn2-core
postgresql-plperl.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-plpython.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-pltcl.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-server.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-static.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-test.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
postgresql-upgrade.x86_64 9.2.24-1.amzn2.0.1 amzn2-core
qt-postgresql.i686 1:4.8.5-15.amzn2.0.5 amzn2-core
qt-postgresql.x86_64 1:4.8.5-15.amzn2.0.5 amzn2-core
qt5-qtbase-postgresql.i686 5.9.2-3.amzn2.0.4 amzn2-core
qt5-qtbase-postgresql.x86_64 5.9.2-3.amzn2.0.4 amzn2-core
・パッケージをインストールします
yum install postgresql.x86_64
・パッケージがインストールされたことを確認します。
psql -V
psql (PostgreSQL) 9.2.24
RDSの作成
RDSの作成の前に、サブネットグループを作成します。
画像のように、AZを跨いだ構成にしないとエラーとなります。エラーについては参考文献を参照してください。
参考文献:https://dev.classmethod.jp/articles/rds-has-two-configs-concerning-availability-zone/
RDSを作成していきます。
PosgreSQLを許可するセキュリティグループを作成して、割り当てます。
作成が完了したら、エンドポイントを控えてください。EC2からの接続で使います。
RDSへの接続
EC2からRDSへ接続をします
psql -h エンドポイント -U ユーザー名
psql (9.2.24, server 13.1)
WARNING: psql version 9.2, server version 13.0.
Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.
postgres=>
EC2からRDSへのアクセスを確認することが出来ました。
バックアップデータのリストア
オンプレで作成したデータをS3にアップロードし、EC2で受け取ってRDSにリストアさせます。
①S3にアップロードしたデータのプロパティから、S3 URLをコピーします。
②S3にアクセス出来るロールを付与させ、EC2からS3にアクセスしバックアップデータを保存します。
$ aws S3 ls S3 URL
保存したデータが表示されたらＯＫ
EC2上に保存させます
$ mkdir postgres
$ aws s3 cp s3 URL postgres
$ ls postgres/
dbbackup
③バックアップデータをRDSにリストアさせます
$ psql -h RDSのエンドポイント -U ユーザー名 データベース名 < バックアップデータ
私の場合はこんな感じです。
$ psql -h database-1.000000000.us-east-2.rds.amazonaws.com -U postgres shop < postgresql/dbbackup
動作確認
最後に、オンプレで登録したテーブルの情報がRDS上で同じように見えているか確認します。
$ psql -h database-1.000000000.us-east-2.rds.amazonaws.com -U postgres -d shop
Password for user postgres:
psql (9.2.24, server 13.1)
WARNING: psql version 9.2, server version 13.0.
Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.
shop=> select * from shohin;
shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi
-----------+--------------+---------------+--------------+--------------+------------
0001 | Tシャツ | 衣服 | 1000 | 500 | 2009-09-20
0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11
(2 rows)
最後に
・バックアップとリストアするところで形式を間違えてはまりました。以下参照してください。https://qiita.com/rice_american/items/ceae28dad13c3977e3a8
・SQL全体の学習には以下の本を参考にさせていただきました。
SQL 第2版 ゼロからはじめるデータベース操作
https://www.amazon.co.jp/gp/product/B01HD5VWWO/ref=ppx_yo_dt_b_d_asin_title_o03?ie=UTF8&psc=1