トップページインストール,使い方Ubuntu, WSL2PostgreSQL 12,pgAdmin 3,PostGIS 3 のインストール,テーブル定義,レコード挿入(Ubuntu 20.04 上)

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

目次

  1. 前準備
  2. PostgreSQL 12,pgAdmin 3,PostGIS 3,ODBC ドライバ,JDBC ドライバのインストール(Ubuntu 上)
  3. peer 認証の確認と md5 認証の設定
  4. PostgreSQL データベースサーバの起動と終了
  5. psql の基本操作
  6. テーブル定義とレコード挿入

サイト内の関連ページ

外部へのリンク

前準備

Ubuntu のシステム更新

Ubuntu で OS のシステム更新を行うときは, 端末で,次のコマンドを実行する.

Ubuntu のインストールは別ページで説明している.

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

PostgreSQL 12,pgAdmin 3,PostGIS 3,ODBC ドライバ,JDBC ドライバのインストール(Ubuntu 上)

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

  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
    

    [image]
  3. PostgreSQL の動作確認

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

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

    確認したら,「\q」により終了.

    sudo -u postgres psql
    \c
    \q
    

    [image]
  4. 「\l」 により,データベースの確認.「\q」により,psql の終了

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

    sudo -u postgres psql
    \l
    \q
    

    [image]

    [image]
  5. psql のバージョン確認
    psql --version
    

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

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
    

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

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

    確認したら,「\q」により終了.

    sudo -u postgres psql
    \c
    \q
    

    [image]

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
    

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

    そのために,次の1行を追加する

    local   all             all                                md5
    

    [image]
  3. PostgreSQL サーバの再起動

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

    sudo pg_ctlcluster 12 main restart
    sudo pg_ctlcluster 12 main status
    

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

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

    psql -U testuser -d postgres
    \c
    \q
    

    [image]

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

[image]

psql の基本操作

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

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

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

    sudo -u postgres psql
    

    [image]
  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 ) );
    

    [image]
  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;
    

    [image]
  4. 確認表示

    select * from order_records;
    

    [image]
  5. 更新し確認表示

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

    [image]
  6. テーブル一覧の表示
    \d
    

    [image]
  7. psql の終了
    \q