PostgreSQL 12，pgAdmin 3，PostGIS 3 のインストール，テーブル定義，レコード挿入（Ubuntu 20.04 上）

前準備

Ubuntu のシステム更新

Ubuntu で OS のシステム更新を行うときは， 次のコマンドを実行．

sudo apt -y update
sudo apt -yV upgrade
sudo /sbin/shutdown -r now

PostgreSQL 12，pgAdmin 3，PostGIS 3，ODBC ドライバ，JDBC ドライバのインストール（Ubuntu 上）

1. PostgreSQL および関連ソフトウェア類のインストール

   インストールするには， 次のコマンドを実行．

   sudo apt -y update
   sudo apt -y install postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common postgresql-contrib odbc-postgresql pgadmin3 postgresql-12-postgis-3 postgresql-server-dev-12 python3-psycopg2 default-jdk libpostgresql-jdbc-java postgresql-plpython3-12 libpq-dev
   

2. PostgreSQL サーバを起動開始

   エラーメッセージが出ていなければ OK．

   sudo pg_ctlcluster 12 main start
   sudo pg_ctlcluster 12 main status
   

   
3. PostgreSQL の動作確認

   Ubuntu のサービスアカウント postgres と peer 認証により，PostgreSQL の psql を使ってみる．

   「\c」により，使用されている PostgreSQL のロール名と，オープンされているデータベース名を確認．

   確認したら，「\q」により終了．

   sudo -u postgres psql
   \c
   \q
   

   
4. 「\l」 により，データベースの確認．「\q」により，psql の終了

   postgres, template0, template1 の 3 つのデータベースが表示されることを確認．

   sudo -u postgres psql
   \l
   \q
   

   
   
5. psql のバージョン確認

   psql --version
   

peer 認証の確認と md5 認証の設定

peer 認証の確認

1. PostgreSQL サービスアカウント (service account)の確認

   PostgreSQL サービスアカウントは，Postgres サーバの起動等に使うもの．Ubuntu では，既定（デフォルト）で，peer 認証が行えるようにも設定されている．

   Ubuntu でパッケージを使って PostgreSQL をインストールすると，PostgreSQL サービスアカウント（ユーザ名は「postgres」)が自動的に作成される．

   次のコマンドで確認できる．

   sudo cat /etc/passwd | grep postgres
   

   ※ 「x」は no password という意味（パスワードがないという意味ではない)

   PostgreSQL サービスアカウントは Linux が管理するアカウントのこと．PostgreSQL が管理するロールとは別のものである．

2. PostgreSQL の認証は peer 認証が設定されていることを確認

   sudo cat /etc/postgresql/12/main/pg_hba.conf
   

   
3. Ubuntu のサービスアカウント postgres と peer 認証により，PostgreSQL の psql を使ってみる．

   「\c」により，使用されている PostgreSQL のロール名と，オープンされているデータベース名を確認．

   確認したら，「\q」により終了．

   sudo -u postgres psql
   \c
   \q
   

   

md5 認証の設定

PostgreSQL での新しいロールの追加と md5 認証の設定を行ってみる．

これは，「Redmine のインストール」に必要な設定ではない． PostgreSQL での新しいロールの追加と md5 認証の設定を試したいときのためのもの．

下の手順で，PostgreSQL で，新しいロール testuser を作成し md5 認証できるように設定してみる

1. PostgreSQL で，新しいロール testuser を作成

   パスワードは，下のものをそのまま使うのでなく，必ず独自に設定してください．

   sudo -u postgres psql
   create role "testuser" with login encrypted password 'hoge7618mhty';
   \du
   \q
   

   
2. /etc/postgresql/12/main/pg_hba.conf を書き換えて，全ユーザである all の md5 認証を有効にする．

   そのために，次の１行を追加する

   local   all             all                                md5
   

   
3. PostgreSQL サーバの再起動

   エラーメッセージが出ていなければ OK．

   sudo pg_ctlcluster 12 main restart
   sudo pg_ctlcluster 12 main status
   

   
4. 新しいロール testuser で，md5 認証により psql を使用できるか確認する．

   md5 認証のときは psql -U ＜ロール名＞ -d ＜データベース名＞

   psql -U testuser -d postgres
   \c
   \q
   

   

PostgreSQL データベースサーバの起動と終了

PostgreSQL データベースサーバの再起動

sudo pg_ctlcluster 12 main restart
sudo pg_ctlcluster 12 main status

PostgreSQL データベースサーバの終了

sudo pg_ctlcluster 12 main stop
sudo pg_ctlcluster 12 main status

psql の基本操作

• psql --version: psql バージョン確認:
• psql: psql 起動
• \copy: テーブルのコピー
• \d, \d+: テーブル等の情報の表示
• \db, \db+: テーブル空間の表示
• \c: データベースへの接続、現在接続しているデータベースとユーザの確認
• \l: データベースの情報の表示
• \q: 終了

テーブル定義とレコード挿入

1. 「PostgreSQL データベース管理者のユーザ名」で，psql を用いて接続．

   sudo -u postgres psql
   

   
2. SQL を用いたテーブル定義

   create table order_records (
       id            integer primary key not null,
       year          integer not null CHECK ( year > 2008 ),
       month         integer not null CHECK ( month >= 1 AND month <= 12 ),
       day           integer not null CHECK ( day >= 1 AND day <= 31 ),
       customer_name text not null,
       product_name  text not null,
       unit_price    real not null check ( unit_price > 0 ),
       qty           integer not null default 1 check ( qty > 0 ),
       created_at    timestamp with time zone not null default current_timestamp,
       updated_at    timestamp with time zone not null default current_timestamp,
       check ( ( unit_price * qty ) < 200000 ) );
   

   
3. SQL を用いたレコード挿入

   begin transaction;
   insert into order_records (id, year, month, day, customer_name, product_name, unit_price, qty) values( 1, 2020, 7, 26,  'kaneko', 'orange A', 1.2, 10 );
   insert into order_records (id, year, month, day, customer_name, product_name, unit_price, qty) values( 2, 2020, 7, 26,  'miyamoto', 'Apple M',  2.5, 2 );
   insert into order_records (id, year, month, day, customer_name, product_name, unit_price, qty) values( 3, 2020, 7, 27,  'kaneko',   'orange B', 1.2, 8 );
   insert into order_records (id, year, month, day, customer_name, product_name, unit_price) values( 4, 2020, 7, 28,  'miyamoto',   'Apple L', 3 );
   commit;
   

   
4. 確認表示

   select * from order_records;
   

   
5. 更新し確認表示

   begin transaction;
   update order_records set unit_price = 11.2 where id = 1;
   commit;
   select * from order_records;
   

   
6. テーブル一覧の表示

   \d
   

   
7. psql の終了

   \q