サイトマップ サイト内検索 まとめ

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
  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 認証について確認する．そして，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 の種々の操作について: 別ページ »で説明

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

  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