PostgreSQL Usage¶

• 目次
• PostgreSQL Usage
  • PostgreSQLの構成
    • データベース・スキーマ・テーブル
    • クライアント接続
    • 稼働するプロセス
      • バッファ
  • psql（コマンドライン）
    • 主要名オプション
      • コマンドラインオプション
    • 接続
    • ユーザー管理
      • ユーザー一覧
      • ユーザーの作成
      • ユーザーの削除
      • パスワード変更
      • ロールの属性
    • データベース管理
      • データベース一覧
      • データベース作成
      • データベース削除
      • データベースの所有者変更
    • スキーマ
      • スキーマ一覧
    • テーブル
      • テーブル一覧
      • テーブル作成
      • テーブル削除
      • テーブルを空にする
      • カラム一覧

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 <データベース名>;

データベース削除¶

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

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

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

スキーマ¶

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

スキーマ一覧¶

• \dn

テーブル¶

テーブル一覧¶

• \dt

テーブル作成¶

• CREATE TABLE <テーブル名> (<カラム名> <型>, ...);

テーブル削除¶

• DROP TABLE <テーブル名>;

テーブルを空にする¶

• TRUNCATE <テーブル名>;

カラム一覧¶

• \d <テーブル名>