PostgreSQLコマンド操作(PSQL)

RDS for PostgreSQLを構築して、ユーザのDBを取り込んだテストを実施することになった。実データ同様のデータベースを構築する必要が出てきた。
この準備に作業なステップは以下の通り
①RDS上でのデータベースの作成
➁スキーマの作成
③テーブルやインデックスの作成
RDS for PostgreSQLの構築は、AWSの管理コンソールから実施できるため、➁と③が実施できるようになることが必要要件である。

ローカル操作端末にPSQLコマンドが使えるようになった(PostgreSQLのクライアントをインストールされた)前提で、必要な手順を記載する。

管理者による接続

まずは、RDSの登録後にAWS管理コンソール記載の接続先URLに、登録時で指定のIDとパスワードで接続する。

psql -h test-db.hogehoge.ap-northeast-1.rds.amazonaws.com -p 5432 -U postgres -d postgres

ユーザの作成

今回必要な操作を実施するためのユーザを登録する。

create user sctest with password 'Passpass';

CREATE ROLE
と表示され、ユーザ sctest が作成される。

スキーマの作成

create schema sctest;

スーキマ sctest が作成され、要件のうち➁が完了

ユーザへのDB操作権限の許可

作成済みのユーザには権限がなく、何もできないため、権限の強いロールを割り当てる。

ALTER schema sctest OWNER TO sctest;

強い権限という意味では、DBのオーナー権限を付与することでもできる。(以下は DB"postgres"のオーナーをユーザ sctestに付与する例)
ALTER DATABASE postgres OWNER TO sctest;
他に必要な要件があれば、更に実行しておく。
RDSのスーパーユーザ権限の付与: GRANT rds_superuser TO sctest;
DB作成権限の付与: alter user sctest CREATEDB;
ユーザ作成権限: alter user sctest CREATEROLE;
PSQLコマンドを抜ける

exit

作成したユーザでの操作

PSQLコマンドでのDB接続

PSQLコマンドを使い、作成したユーザで接続する。

psql -h test-db.hogehoge.ap-northeast-1.rds.amazonaws.com -p 5432 -U sctest -d postgres

この時点でテーブルを作成してみたところ、テーブルはpublicスキーマにできてしまうことが判り、スキーマは明示的に移動する必要であることが判った。

スキーマの移動

スキーマの移動は set search_pathを利用する

set search_path to sctest;

「SET」がエラーなく戻った後、current_schemaを確認する。

select current_schema;

想定したスキーマが表示されていればOK。

postgres=> select current_schema;
 current_schema
----------------
 sctest
(1 row)

DB操作

テーブル/索引の作成

create table taurin2000 (
  id character varying(8) not null
  , cntrcd character(6) not null
  , prod_name character varying(128)
  , prod_comment character varying(255)
  , update_count integer
);

CREATE TABLE
と表示され、コマンド成功が確認できれば、正しく権限が付与されている。(今回の③の要件もクリアしている)
操作は割愛するが、インデックスも同様に管理できる。

確認(コマンド操作)

SQL(SELECT)を実行してみる。

select * from taurin2000;

カラムが表示される。データは入ってないので、0行の結果となる。
id | cntrcd | prod_name | prod_comment | update_count
----+--------+-----------+--------------+--------------
(0 行)

データを入れてみる

INSERT INTO taurin2000(id, cntrcd, prod_name, prod_comment, update_count) 
      VALUES ('11111111', '111111', 'prod_name1', 'comment hogehoge', 0);

INSERT 0 1

データ確認

select * from taurin2000;
    id    | cntrcd | prod_name  |   prod_comment   | update_count
----------+--------+------------+------------------+--------------
 11111111 | 111111 | prod_name1 | comment hogehoge |            0
(1 行)

おまけ - コマンド以外の操作

コマンド以外のGUIツールで操作できると、SQLを組み立てなくても操作ができるので便利である。ここではPostgreSQL標準のpgAdminの例を添付しておく。

pgAdminによる確認

pgAdminを起動すると、GUIツールが起動される。
メニューからPSQLを起動することもできる。

せっかくのGUIツールなので、SQL操作なしでのデータ確認をしてみる。

いちいち大量のテーブル名カラム名等を打ち込む必要がないだけでなく、筆者のように、SQLの知識があやふやな人間にとっては、利便性を感じる。
筆者は他のツールも使っているので、機会があれば、照会したい。