PostgreSQL 13, pgAdmin 3, PostGIS 3 のインストール（Ubuntu 20.04 上）

Ubuntu に，PostgreSQL 13, pgAdmin 3, PostGIS 3 のインストールを行う．そして，pgAdmin 3 の主要機能を動かしてみる．

* インストールは，https://wiki.postgresql.org/wiki/Apt に記載の手順に従う．

【目次】

前準備
PostgreSQL 13, pgAdmin 3, PostGIS 3 のインストール（Ubuntu 20.04 上）
peer 認証の確認と md5 認証の設定
PostgreSQL データベースサーバの起動と終了
psql の基本操作
テーブル定義とレコード挿入

【関連する外部ページ】

PostgreSQL Web ページ: http://www.postgresql.org/
カーネル設定: http://www.postgresql.jp/document/12/html/kernel-resources.html
インストール: http://www.postgresql.jp/document/12/html/installation.html

前準備

Ubuntu のシステム更新

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

Ubuntu のインストールは別ページ »で説明

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

PostgreSQL 13, pgAdmin 3, PostGIS 3 のインストール（Ubuntu 20.04 上）

https://wiki.postgresql.org/wiki/Apt に記載の手順に従う．

レポジトリキーのインストール

sudo apt -y update
sudo apt -y install curl ca-certificates gnupg
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

パッケージリストの取得

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

パッケージ情報の更新
sudo apt -y update

インストール

apt-cache search postgresql
sudo apt -y install pgadmin3 postgresql-13-postgis-3

インストール直後なので，確認のため，サーバを手動で起動して停止してみて，エラーメッセージが出ないことを確認する
sudo pg_ctlcluster 13 main restart sudo pg_ctlcluster 13 main status
PostgreSQL の動作確認
Ubuntu のサービスアカウント postgres と peer 認証により，PostgreSQL の psql を使ってみる．
「\c」により，使用されている PostgreSQL のロール名と，オープンされているデータベース名を確認．
確認したら，「\q」により終了．
sudo -u postgres psql \c \q
データベースの確認
postgres, template0, template1 の 3 つのデータベースが表示されることを確認．
sudo -u postgres psql \l \q
psql のバージョン確認
psql --version

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

peer 認証について確認する．そして，md5 認証について設定できることを確認する．

peer 認証の確認

PostgreSQL サービスアカウント (service account)の確認
PostgreSQL サービスアカウントは，Postgres サーバの起動等に使うもの．Ubuntu では，既定（デフォルト）で，peer 認証が行えるようにも設定されている．
Ubuntu でパッケージを使って PostgreSQL をインストールすると，PostgreSQL サービスアカウント（ユーザ名は「postgres」)が自動的に作成される．
次のコマンドで確認できる．
sudo cat /etc/passwd | grep postgres
* 「x」は no password という意味（パスワードがないという意味ではない)

PostgreSQL サービスアカウントは Linux が管理するアカウントのこと．PostgreSQL が管理するロールとは別のものである．
PostgreSQL の認証は peer 認証が設定されていることを確認
sudo cat /etc/postgresql/13/main/pg_hba.conf
Ubuntu のサービスアカウント postgres と peer 認証により，PostgreSQL の psql を使ってみる．
「\c」により，使用されている PostgreSQL のロール名と，オープンされているデータベース名を確認．
確認したら，「\q」により終了．
sudo -u postgres psql \c \q

md5 認証の設定

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

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

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

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

パスワードは，下のものをそのまま使うのでなく，必ず独自に設定してください．
sudo -u postgres psql create role "testuser" with login encrypted password 'hoge7618mhty'; \du \q
/etc/postgresql/13/main/pg_hba.conf を書き換えて，全ユーザである all の md5 認証を有効にする．
そのために，次の１行を追加する
local all all md5
PostgreSQL サーバの再起動
エラーメッセージが出ていなければ OK．
sudo pg_ctlcluster 13 main restart sudo pg_ctlcluster 13 main status
新しいロール testuser で，md5 認証により psql を使用できるか確認する．

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

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

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

sudo pg_ctlcluster 13 main restart
sudo pg_ctlcluster 13 main status

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

sudo pg_ctlcluster 13 main stop
sudo pg_ctlcluster 13 main status

psql の基本操作

psql の種々の操作について: 別ページ »で説明

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

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

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

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 ) );

SQL を用いたレコード挿入

begin transaction;
insert into order_records (id, year, month, day, customer_name, product_name, unit_price, qty) values( 1, 2023, 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, 2023, 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, 2023, 7, 27,  'kaneko',   'orange B', 1.2, 8 );
insert into order_records (id, year, month, day, customer_name, product_name, unit_price) values( 4, 2023, 7, 28,  'miyamoto',   'Apple L', 3 );
commit;

確認表示
select * from order_records;

更新し確認表示

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

テーブル一覧の表示
\d
psql の終了
\q