プロジェクト

全般

プロフィール

Wiki » データベース »

PostgreSQL Usage

PostgreSQLの使い方メモです。

PostgreSQLの構成

データベース・スキーマ・テーブル

PostgreSQLは、データベース◇-スキーマ◇-テーブル の構造を持ちます。MySQLなどから移行するとスキーマという構造に馴染みがないです。スキーマはデータベースの中でテーブル群を束ねてテーブル群へのアクセス制御を行う単位となります。スキーマを意識しないで操作すると、デフォルトのPublicスキーマを使います。アクセス制御は個々のテーブルに設定することもできますが、テーブルを束ねるスキーマに設定するのが効率的のようです。

クライアント接続

PostgreSQLサーバー（バックエンド）とクライアント（フロントエンド）との接続は、TCP/IPまたはUNIXドメインソケットを用います。

稼働するプロセス

PostgreSQLは、役割毎に多数のプロセスが連携して動作します。

  • マスタサーバープロセス
    PostgreSQL起動時に立ち上がり、必要なサブプロセスの起動、クライアントからの接続を受けバックエンドプロセスを起動します。
  • バックエンドプロセス
    クライアントからの接続ごとにフォークされるプロセス、クエリの実行と結果の送信を行います。
  • WALライタプロセス
    WAL（Write Ahead Logging）バッファに書き込まれたWALをWALファイルに書き出します。
  • ライタプロセス
    共有バッファ内の更新ページを対応するデータファイルに書き出します。
  • チェックポインタプロセス
    全てのダーティページをデータファイルに反映します。
  • 自動バキュームランチャ／ワーカプロセス
    自動バキュームを制御／実行します。
  • 統計情報コレクタプロセス
    一定間隔で稼働統計情報を収集します。

バッファ

  • WAL（Write Ahead Logging）バッファ
    ディスクに書き込まれていないトランザクションログ（WAL）をキャッシュするバッファ
  • 共有バッファ
    テーブルやインデックスのデータをキャッシュするバッファ

psql（コマンドライン）

主要名オプション

コマンドラインオプション

よく使うオプション

  • -d データベース名
  • -h ホスト名
  • -p ポート番号
  • -U ユーザー名

接続

コマンドラインツールでPostgreSQLサーバーに接続します。デフォルトでは次の制約があります。

  • サーバーと同じホスト上（localhost）からの接続のみ受け付け
  • ローカルホストからのアクセスでは、コマンドを実行するOS上のユーザーとPostgreSQLのユーザーが同一であること（peer認証）

最初の設定は、/etc/postgresql/16/main/postgresql.confの次の記述によるものです。
#listen_addresses = 'localhost' # what IP address(es) to listen on;

  • コメントアウトを解除し、'localhost' を '*' に修正すると、外部からの接続が可能になります。

2番目の設定は、/etc/postgresql/16/main/pg_hda.confの次の記述によるものです。
local all all peer

  • peerをmd5に変更するとpeer認証を無効にし、パスワード認証が使用されます。
  • peerをtrustに変更すると、ローカルホストからの接続はすべて信頼できると判断され、ユーザー・パスワード情報なしに接続できます。
~ % psql -p 5432 -d postgres
  • ローカルホストのPostgreSQLにポート番号5432を指定し、使用するデータベース名 postgres を指定して接続

ユーザー管理

PostgreSQLでは、ユーザーは「ロール」という概念に含まれます。ログイン可能なロール（LOGIN権限を持つロール）がユーザーです。

ユーザー一覧

  • \du

ユーザーの作成

  • ユーザーとなるロールを作成（パスワードは未設定）
    CREATE USER <ユーザー名>;
    これは、次と等価
    CREATE ROLE <ユーザー名> LOGIN;
  • パスワードを指定してユーザーとなるロールを作成
    CREATE USER <ユーザー名> WITH PASSWORD 'パスワード';
  • superuse権限を持つユーザーとなるロールをパスワードを指定して作成
    CREATE USER <ユーザー名> WITH SUPERUSER PASSWORD 'パスワード';

ユーザーの削除

  • DROP USER <ユーザー名>;

パスワード変更

  • ALTER USER <ユーザー名> WITH PASSWORD '<パスワード>';

ロールの属性

  • LOGIN
    データベースに接続できるロール属性
  • SUPERUSER
    ログイン以外のすべての権限検査が行われない特権（危険）
  • CREATEDB
    データベースの作成権限
  • CREATEROLE
    ロール作成権限。ロールの変更や削除も可能
  • REPLICATION
    ストリーミングレプリケーションの新規接続権限
  • PASSWORD
    データベースに接続する際のパスワード
  • INHERIT
  • NOINHERIT
  • BYPASSRLS
  • CONNECTION LIMIT

データベース管理

データベース一覧

  • \l

データベース作成

  • CREATE DATABASE <データベース名>;

データベースの各種設定は、明示的に指定しない場合、template1 データベースをコピーして作られます。
明示的に指定する場合

CREATE DATABASE hello
  WITH OWNER=tom
  ENCODING='UTF8'
  TABLESPACE=pg_default
  LC_COLLATE='C'
  LC_TYPE='C'
  CONNECTION LIMIT=-1

のように、データベースの所有者、エンコーディングなどを指定します。

データベース削除

  • DROP DATABASE <データベース名>;

データベースの所有者変更

  • ALTER DATABASE <データベース名> OWNER TO <ユーザー名>;

スキーマ

PostgreSQLは、データベースの中に複数のスキーマが設定できます。テーブルはいずれかのスキーマに属します。通常はデフォルトで生成されるスキーマの一つpublicが使われます。

スキーマ一覧

  • \dn

テーブル

テーブル一覧

  • \dt

テーブル作成

  • CREATE TABLE <テーブル名> (<カラム名> <型>, ...);
プライマリキー

カラムの定義に指定する方法

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name varchar(255) NOT NULL
);
  • プライマリキーを指定するとインデックス対象となる
  • NOT NULL制約が自動で付与

テーブルの制約に指定する方法

CREATE TABLE products (
    id SERIAL,
    name varchar(255) NOT NULL,
    PRIMARY KEY (id)
);
  • 複合キーの場合、カラムの定義には指定できないのでこの方法を使用する

テーブル削除

  • DROP TABLE <テーブル名>;

テーブルを空にする

  • TRUNCATE <テーブル名>;

カラム一覧

  • \d <テーブル名>

外部キー

テーブルの間に関連（外部参照）を持たせるときに、制約を持たせる場合に設定します。いくつか方法があります。

  • CREATE TABLE でカラムの定義に REFERENCES で外部参照先のテーブル名・カラム名を設定
CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  product_no integer REFERENCES products (product_no),
  quantity integer
);

ordersテーブルのproduct_noカラムは、productsテーブルのproduct_noカラムを外部キーとし外部キー制約を定義。
ordersテーブルにレコードを作成するとき、productsテーブルに存在しないproduct_noを使用するとエラーとなります。
外部キーの参照先カラムはユニーク制約（そのカラムの値でレコードを一意に指定）できる必要があります。
主キーはユニーク制約です。

  • テーブルの主キーが複合キーのときは、カラムの定義ではなくテーブルの定義で外部キーの参照を指定します。
CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  product_no integer NOT NULL,
  product_factory_id integer NOT NULL,
  quantity integer,
  FOREIGN KEY (product_no, product_factory_id) REFERENCES products (product_no, product_factory_id)
);

約1ヶ月前に更新