PostgreSQL 16，PostGIS 3，ODBC ドライバ，JDBC ドライバのインストール，テーブル定義，レコード挿入（Ubuntu 24.04 上）

Ubuntu 24.04 の標準リポジトリに含まれる PostgreSQL 16 と，PostGIS 3，ODBC ドライバ，JDBC ドライバ等をインストールする。そして，psql を用いたテーブル定義とレコード挿入を行う．

【目次】

  1. 前準備
  2. PostgreSQL 16，PostGIS 3，ODBC ドライバ，JDBC ドライバのインストール（Ubuntu 上）
  3. peer 認証の確認とパスワード認証の設定
  4. PostgreSQL データベースサーバの起動と終了
  5. psql の基本操作
  6. テーブル定義とレコード挿入
  7. PostGIS の有効化

【サイト内の関連ページ】

【関連する外部ページ】

前準備

Ubuntu のシステム更新

Ubuntu で OS のシステム更新を行うときは， 端末で，次のコマンドを実行する。これは、パッケージ情報を最新の状態に保ち、インストール済みのパッケージをセキュリティアップデートやバグ修正を含めて更新するためである。

Ubuntu のインストールはこちらの別ページで説明する。 

# パッケージリストの情報を更新
sudo apt update
# インストール済みのパッケージを包括的に更新 (依存関係も考慮)
sudo apt full-upgrade
# カーネル更新等で実際に再起動が必要な場合のみ実行を推奨
# sudo shutdown -r now

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

PostgreSQL はリレーショナル・データベース管理システム． コマンドラインクライアントは psql で起動．

Ubuntu 24.04 の標準リポジトリに含まれる PostgreSQL は 16 である。他のバージョンを使うときは，PostgreSQL 公式の APT リポジトリ (PGDG) を追加する（別ページで説明）．

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

    端末で，次のコマンドを実行する。odbc-postgresql は ODBC ドライバ，libpostgresql-jdbc-java は JDBC ドライバ，python3-psycopg2 は Python 用のドライバである． 

    # パッケージリストの情報を更新
sudo apt update
sudo apt -y install postgresql-16 postgresql-client-16 postgresql-client-common postgresql-common postgresql-contrib odbc-postgresql postgresql-16-postgis-3 postgresql-server-dev-16 python3-psycopg2 default-jdk libpostgresql-jdbc-java postgresql-plpython3-16 libpq-dev
  2. PostgreSQL サーバを起動開始

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

    sudo pg_ctlcluster 16 main start
sudo pg_ctlcluster 16 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 認証の確認とパスワード認証の設定

peer 認証について確認する．そして，パスワード認証 (scram-sha-256) について設定できることを確認する．

peer 認証の確認

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

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

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

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

    grep postgres /etc/passwd
    * 2 番目のフィールドの「x」は，パスワードハッシュが /etc/shadow に格納されていることを示す．

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

  2. PostgreSQL の認証は peer 認証が設定されていることを確認 
    sudo cat /etc/postgresql/16/main/pg_hba.conf
  3. Ubuntu のサービスアカウント postgres と peer 認証により，PostgreSQL の psql を使ってみる．

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

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

    sudo -u postgres psql
\c
\q

パスワード認証 (scram-sha-256) の設定

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

下の手順で，PostgreSQL で，新しいロール testuser を作成し，パスワード認証で接続できるように設定する．PostgreSQL 16 では，パスワードの保存形式の既定値 (password_encryption) は scram-sha-256 である．md5 は旧来の方式であり，pg_hba.conf の認証方式にも scram-sha-256 を指定する．

  1. PostgreSQL で，新しいロール testuser を作成
    パスワードは，下のものをそのまま使うのでなく，必ず独自に設定してください． 
    sudo -u postgres psql
create role "testuser" with login password 'hoge7618mhty';
\du
\q
  2. /etc/postgresql/16/main/pg_hba.conf を書き換えて，パスワード認証を有効にする．

    「local all postgres peer」の行の下に，次の１行を追加する． 

    local   all             all                                scram-sha-256
    pg_hba.conf は上の行から順に照合され，最初に一致した行だけが使用される．「local all postgres peer」の行を残しておくことにより，サービスアカウント postgres からの peer 認証での接続は，そのまま利用できる．
  3. PostgreSQL サーバの再起動

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

    sudo pg_ctlcluster 16 main restart
sudo pg_ctlcluster 16 main status
  4. 新しいロール testuser で，パスワード認証により psql を使用できるか確認する．
    パスワード認証のときは psql -U ＜ロール名＞ -d ＜データベース名＞ 
    psql -U testuser -d postgres
\c
\q

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

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

sudo pg_ctlcluster 16 main restart
sudo pg_ctlcluster 16 main status

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

sudo pg_ctlcluster 16 main stop
sudo pg_ctlcluster 16 main status

psql の基本操作

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

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

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

PostGIS の有効化

PostGIS は，パッケージをインストールしただけでは使用できない。使用するデータベースごとに，拡張機能 (extension) の登録が必要である．

  1. 「PostgreSQL データベース管理者のユーザ名」で，psql を用いて接続． 
    sudo -u postgres psql
  2. 拡張機能 postgis の登録とバージョン確認 
    create extension postgis;
select postgis_full_version();
  3. 空間データ型の動作確認 
    select ST_AsText(ST_MakePoint(132.46, 34.40));
  4. psql の終了 
    \q