PostgreSQL現在のスキーマを完全マスター！確認・切り替え・search_path設定まで実例で解説

データベース・SQL

「今どのスキーマで作業してるの？」 「テーブルが見つからない…スキーマが違う？」 「デフォルトのスキーマを変更したい」

こんな疑問や悩みを抱えていませんか？

PostgreSQLのスキーマは、データベース内でオブジェクトを整理する名前空間です。 適切にスキーマを管理できれば、複数のアプリケーションや環境を1つのデータベースで効率的に運用できます。

この記事では、現在のスキーマの確認方法から、スキーマの切り替え、search_pathの設定まで、実例たっぷりで解説します。 もう「テーブルが見つからない」なんてエラーとはサヨナラしましょう！

スポンサーリンク

スキーマとは？基本を3分で理解

スキーマって何？

スキーマは、データベース内の論理的な名前空間です。 フォルダのように、テーブルやビューなどのオブジェクトを整理できます。

イメージ：

データベース (mydb)
├── スキーマ (public)
│   ├── テーブル (users)
│   ├── テーブル (products)
│   └── ビュー (v_sales)
├── スキーマ (dev)
│   ├── テーブル (users)
│   └── テーブル (test_data)
└── スキーマ (archive)
    └── テーブル (old_logs)

なぜスキーマを使うの？

メリット：

  1. 名前の衝突を防ぐ：同じ名前のテーブルを別スキーマで作成可能
  2. 権限管理が簡単：スキーマ単位でアクセス制御
  3. 論理的な整理：用途別にオブジェクトを分類
  4. マルチテナント対応：顧客ごとにスキーマを分離

現在のスキーマを確認する5つの方法

方法1：current_schema()関数（最も簡単）

-- 現在のスキーマを確認
SELECT current_schema();
-- 結果: public

-- より詳しい情報と一緒に表示
SELECT 
    current_database() AS 現在のDB,
    current_schema() AS 現在のスキーマ,
    current_user AS 現在のユーザー;

方法2：search_pathを確認

-- 現在のsearch_pathを確認
SHOW search_path;
-- 結果: "$user", public

-- より詳細に確認
SELECT 
    name AS 設定名,
    setting AS 現在値,
    unit AS 単位,
    source AS 設定元
FROM pg_settings 
WHERE name = 'search_path';

方法3：psqlメタコマンド

-- psqlでの確認コマンド
\dn         -- スキーマ一覧
\dn+        -- スキーマ一覧（詳細）
\dn *public* -- publicを含むスキーマ

-- 現在の接続情報を確認
\conninfo
-- 結果: You are connected to database "mydb" as user "postgres"...

方法4：システムカタログから確認

-- すべてのスキーマと詳細情報
SELECT 
    nspname AS スキーマ名,
    nspowner::regrole AS 所有者,
    nspacl AS アクセス権限,
    obj_description(oid, 'pg_namespace') AS 説明
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
  AND nspname != 'information_schema'
ORDER BY nspname;

-- 現在使用可能なスキーマ
SELECT 
    n.nspname AS スキーマ名,
    CASE 
        WHEN n.nspname = current_schema() THEN '★現在' 
        ELSE '' 
    END AS 状態,
    has_schema_privilege(current_user, n.nspname, 'USAGE') AS 使用可能,
    has_schema_privilege(current_user, n.nspname, 'CREATE') AS 作成可能
FROM pg_namespace n
WHERE n.nspname NOT LIKE 'pg_%'
  AND n.nspname != 'information_schema'
ORDER BY n.nspname;

方法5：オブジェクトのスキーマを確認

-- テーブルがどのスキーマにあるか確認
SELECT 
    schemaname AS スキーマ,
    tablename AS テーブル,
    tableowner AS 所有者
FROM pg_tables
WHERE tablename = 'users'
ORDER BY schemaname;

-- 現在のスキーマのテーブル一覧
SELECT 
    table_schema AS スキーマ,
    table_name AS テーブル名,
    table_type AS 種類
FROM information_schema.tables
WHERE table_schema = current_schema()
ORDER BY table_name;

search_pathの仕組みと設定

search_pathとは？

search_pathは、スキーマを検索する順序を定義します。 スキーマ名を省略した時に、どの順番でスキーマを探すかを決めるんです。

動作イメージ：

-- search_path = "$user", public の場合

SELECT * FROM users;
-- 1. まず現在のユーザー名と同じスキーマを探す
-- 2. 次にpublicスキーマを探す
-- 3. 見つかったら、そのテーブルを使用

search_pathの設定方法

セッションレベルの設定（一時的）：

-- 現在のセッションのみ有効
SET search_path TO myschema, public;

-- 確認
SHOW search_path;

-- 複数スキーマを設定
SET search_path TO dev, test, public;

-- デフォルトに戻す
RESET search_path;

ユーザーレベルの設定（永続的）：

-- 特定ユーザーのデフォルトsearch_pathを設定
ALTER USER tanaka SET search_path TO app_schema, public;

-- データベース接続時のデフォルトを設定
ALTER DATABASE mydb SET search_path TO production, public;

-- ロール（グループ）に設定
ALTER ROLE developers SET search_path TO dev, public;

関数レベルの設定：

-- 関数実行時のsearch_pathを固定
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
    SELECT COUNT(*) FROM users;
$$ LANGUAGE sql
SET search_path = app_schema;  -- この関数は常にapp_schemaを使用

search_pathの優先順位

-- 優先順位の確認
SELECT 
    'セッション' AS レベル,
    current_setting('search_path') AS 設定値
UNION ALL
SELECT 
    'ユーザー',
    (SELECT rolconfig[1] FROM pg_roles WHERE rolname = current_user)
UNION ALL
SELECT 
    'データベース',
    (SELECT datconfig[1] FROM pg_database WHERE datname = current_database());

-- 実効的なsearch_pathを確認
SELECT unnest(current_schemas(false)) AS 有効なスキーマ;

スキーマの作成と切り替え

スキーマの作成

-- 基本的なスキーマ作成
CREATE SCHEMA myschema;

-- 所有者を指定して作成
CREATE SCHEMA dev_schema AUTHORIZATION developer;

-- IF NOT EXISTSで安全に作成
CREATE SCHEMA IF NOT EXISTS test_schema;

-- コメント付きで作成
CREATE SCHEMA reporting;
COMMENT ON SCHEMA reporting IS '月次レポート用スキーマ';

-- スキーマ作成と同時にオブジェクトも作成
CREATE SCHEMA app
    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100)
    )
    CREATE VIEW active_users AS
        SELECT * FROM users WHERE is_active = true;

スキーマの切り替え

一時的な切り替え：

-- 方法1: search_pathを変更
SET search_path TO dev_schema;

-- 方法2: スキーマを明示的に指定
SELECT * FROM dev_schema.users;

-- 方法3: 一時的にスキーマを最優先に
SET search_path TO test_schema, "$user", public;

スキーマ切り替えの実例：

-- 開発環境での作業
BEGIN;
    SET LOCAL search_path TO dev;  -- トランザクション内のみ有効
    
    -- dev スキーマで作業
    CREATE TABLE test_table (id INT);
    INSERT INTO test_table VALUES (1), (2), (3);
    
    -- 確認
    SELECT current_schema();  -- dev
    
COMMIT;
-- COMMITするとsearch_pathは元に戻る

SELECT current_schema();  -- public

実践的な使用パターン

マルチテナント環境の構築

-- テナントごとにスキーマを作成
CREATE SCHEMA tenant_001;
CREATE SCHEMA tenant_002;
CREATE SCHEMA tenant_003;

-- 共通テーブルをテンプレートスキーマに作成
CREATE SCHEMA template;

CREATE TABLE template.users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 各テナントにテーブルをコピー
DO $$
DECLARE
    tenant_schema TEXT;
BEGIN
    FOR tenant_schema IN 
        SELECT nspname FROM pg_namespace 
        WHERE nspname LIKE 'tenant_%'
    LOOP
        EXECUTE format('
            CREATE TABLE %I.users (LIKE template.users INCLUDING ALL)',
            tenant_schema
        );
    END LOOP;
END $$;

-- アプリケーションでテナントを切り替え
SET search_path TO tenant_001, public;  -- テナント1のデータにアクセス

開発/本番環境の分離

-- 環境別スキーマ
CREATE SCHEMA production;
CREATE SCHEMA staging;
CREATE SCHEMA development;

-- 環境変数や接続ユーザーで自動切り替え
CREATE OR REPLACE FUNCTION set_environment_schema()
RETURNS void AS $$
BEGIN
    IF current_user = 'prod_user' THEN
        SET search_path TO production, public;
    ELSIF current_user = 'dev_user' THEN
        SET search_path TO development, public;
    ELSE
        SET search_path TO staging, public;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- ログイン時に自動実行（イベントトリガー使用）
CREATE EVENT TRIGGER set_schema_on_login
    ON login
    EXECUTE FUNCTION set_environment_schema();

バージョン管理されたスキーマ

-- バージョンごとのスキーマ
CREATE SCHEMA v1;
CREATE SCHEMA v2;
CREATE SCHEMA v3_beta;

-- 現在のバージョンを示すビュー
CREATE VIEW public.current_version AS
SELECT 'v2' AS version, '2025-09-14' AS release_date;

-- バージョン切り替え関数
CREATE OR REPLACE FUNCTION switch_version(target_version TEXT)
RETURNS void AS $$
BEGIN
    EXECUTE format('SET search_path TO %I, public', target_version);
    RAISE NOTICE 'Switched to version: %', target_version;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT switch_version('v3_beta');

トラブルシューティング

問題1：テーブルが見つからない

エラー：relation “users” does not exist

-- 診断クエリ
-- どのスキーマにテーブルがあるか確認
SELECT 
    schemaname AS スキーマ,
    tablename AS テーブル
FROM pg_tables
WHERE tablename = 'users';

-- 現在のsearch_pathを確認
SHOW search_path;

-- 解決策1：スキーマを明示
SELECT * FROM myschema.users;

-- 解決策2：search_pathに追加
SET search_path TO myschema, public;

問題2：権限エラー

エラー：permission denied for schema

-- スキーマ権限を確認
SELECT 
    nspname AS スキーマ,
    has_schema_privilege(current_user, nspname, 'USAGE') AS USAGE権限,
    has_schema_privilege(current_user, nspname, 'CREATE') AS CREATE権限
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%';

-- 権限を付与（管理者として実行）
GRANT USAGE ON SCHEMA myschema TO myuser;
GRANT CREATE ON SCHEMA myschema TO myuser;

問題3：スキーマが切り替わらない

-- 現在の設定を詳細に確認
SELECT 
    name,
    setting,
    source,
    sourcefile,
    sourceline
FROM pg_settings
WHERE name = 'search_path';

-- すべての設定源を確認
SELECT 
    'Database' AS source,
    datname AS name,
    datconfig AS config
FROM pg_database
WHERE datname = current_database()
UNION ALL
SELECT 
    'User',
    rolname,
    rolconfig
FROM pg_roles
WHERE rolname = current_user;

-- 強制的にリセット
RESET ALL;
SET search_path TO myschema, public;

ベストプラクティス

スキーマ命名規則

-- 推奨される命名パターン
CREATE SCHEMA app_v1;           -- バージョン管理
CREATE SCHEMA tenant_acme;      -- テナント識別
CREATE SCHEMA dev_feature_x;    -- 開発ブランチ
CREATE SCHEMA archive_2025;     -- 時系列アーカイブ
CREATE SCHEMA temp_migration;   -- 一時的な用途

-- 避けるべき名前
-- CREATE SCHEMA user;         -- 予約語
-- CREATE SCHEMA "My Schema";  -- スペース含む
-- CREATE SCHEMA 123schema;    -- 数字始まり

セキュリティ考慮事項

-- publicスキーマの権限を制限
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 専用スキーマに限定的な権限
CREATE SCHEMA app_data;
GRANT USAGE ON SCHEMA app_data TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app_data TO app_user;

-- search_pathからpublicを除外（セキュリティ向上）
ALTER USER secure_user SET search_path TO app_data;

パフォーマンス最適化

-- スキーマごとの統計情報
SELECT 
    schemaname AS スキーマ,
    COUNT(*) AS テーブル数,
    SUM(n_live_tup) AS 総行数,
    pg_size_pretty(SUM(pg_total_relation_size(schemaname||'.'||tablename))) AS 総サイズ
FROM pg_stat_user_tables
GROUP BY schemaname
ORDER BY SUM(pg_total_relation_size(schemaname||'.'||tablename)) DESC;

-- 不要なスキーマの検出
SELECT 
    n.nspname AS スキーマ,
    COUNT(c.oid) AS オブジェクト数,
    pg_size_pretty(COALESCE(SUM(pg_total_relation_size(c.oid)), 0)) AS サイズ
FROM pg_namespace n
LEFT JOIN pg_class c ON n.oid = c.relnamespace
WHERE n.nspname NOT LIKE 'pg_%'
  AND n.nspname != 'information_schema'
GROUP BY n.nspname
HAVING COUNT(c.oid) = 0
ORDER BY n.nspname;

便利な関数とツール

スキーマ管理用カスタム関数

-- 現在の環境情報を表示する関数
CREATE OR REPLACE FUNCTION show_current_context()
RETURNS TABLE (
    項目 TEXT,
    値 TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT '現在のデータベース', current_database()::TEXT
    UNION ALL
    SELECT '現在のスキーマ', current_schema()::TEXT
    UNION ALL
    SELECT '現在のユーザー', current_user::TEXT
    UNION ALL
    SELECT 'search_path', current_setting('search_path')
    UNION ALL
    SELECT 'サーバーバージョン', version()::TEXT;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT * FROM show_current_context();

-- スキーマをクローンする関数
CREATE OR REPLACE FUNCTION clone_schema(
    source_schema TEXT,
    target_schema TEXT
) RETURNS void AS $$
DECLARE
    object_record RECORD;
BEGIN
    -- スキーマ作成
    EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', target_schema);
    
    -- テーブルをコピー
    FOR object_record IN
        SELECT tablename FROM pg_tables WHERE schemaname = source_schema
    LOOP
        EXECUTE format('CREATE TABLE %I.%I (LIKE %I.%I INCLUDING ALL)',
            target_schema, object_record.tablename,
            source_schema, object_record.tablename);
    END LOOP;
    
    RAISE NOTICE 'Schema % cloned to %', source_schema, target_schema;
END;
$$ LANGUAGE plpgsql;

まとめ：スキーママスターへの道

PostgreSQLのスキーマ管理について、重要ポイントをまとめます：

現在のスキーマ確認：

  1. 🔍 current_schema() – 最も簡単な確認方法
  2. 📋 SHOW search_path – 検索パスを確認
  3. 🛠️ \dn – psqlでスキーマ一覧表示

スキーマの切り替え：

  • SET search_path – セッション単位で変更
  • ALTER USER/DATABASE – 永続的な設定
  • スキーマ名.テーブル名 – 明示的な指定

活用パターン：

  • マルチテナント環境の構築
  • 開発/本番環境の分離
  • バージョン管理されたDB構造

ベストプラクティス：

  • 明確な命名規則を採用
  • 適切な権限設定
  • publicスキーマへの依存を避ける

スキーマを適切に管理することで、複雑なデータベース環境も整理整頓できます。 この記事で学んだテクニックを活用して、効率的なデータベース運用を実現しましょう！

Youtube

神話・歴史・伝承の解説チャンネル

スポンサーリンク

コメント

タイトルとURLをコピーしました