管理資料庫

**Database 創建方法

1. 基礎創建：從 template1

 
CREATE DATABASE myapp_db 
    OWNER myapp_user
    ENCODING 'UTF8'
    TABLESPACE app_tablespace;
 
 
CREATE DATABASE simple_db OWNER app_user;
 
 
CREATE DATABASE new_db 
    TEMPLATE custom_template
    OWNER app_user;

2. 複製現有 Database

CREATE DATABASE test_db 
    TEMPLATE production_db
    OWNER test_user;
 
-- 注意：來源資料庫必須沒有其他連線（對應 Oracle 的關閉要求）

3. Database 遷移

 
pg_dump -h source_host -U postgres production_db -Fc -f db_backup.dump
 
createdb -O app_user target_db
pg_restore -h target_host -U postgres -d target_db db_backup.dump

Database 運作模式管理

連接控制

-- 禁止連接
ALTER DATABASE myapp_db CONNECTION LIMIT 0;
 
-- 允許連接
ALTER DATABASE myapp_db CONNECTION LIMIT 100;
 
-- 檢查連接狀態
SELECT datname, datallowconn, datconnlimit 
FROM pg_database WHERE datname = 'myapp_db';

Database 重新命名

-- 重新命名（需要獨占存取）
ALTER DATABASE old_name RENAME TO new_name;
 
-- 變更擁有者
ALTER DATABASE myapp_db OWNER TO new_owner;

Database 參數管理

Database 級別設定

-- 設定 Database 特定參數
ALTER DATABASE myapp_db SET work_mem = '16MB';
ALTER DATABASE myapp_db SET maintenance_work_mem = '256MB';
 
-- 查看 Database 特定設定
SELECT setdatabase, unnest(setconfig) as setting 
FROM pg_db_role_setting 
WHERE setdatabase = (SELECT oid FROM pg_database WHERE datname = 'myapp_db');

Database 檔案位置管理

Tablespace 分配

對應 Oracle：檔案位置控制

-- 創建 tablespace（對應 Oracle 的檔案位置概念）
CREATE TABLESPACE app_data LOCATION '/u01/postgresql/app_data';
 
-- 在特定 tablespace 創建 database
CREATE DATABASE myapp_db 
    OWNER app_user
    TABLESPACE app_data;
 
-- 移動 database 到不同 tablespace
ALTER DATABASE myapp_db SET TABLESPACE new_tablespace;

Database 狀態監控

狀態檢查

-- 查看所有 database 狀態
SELECT datname as db_name, 
       datallowconn as allow_conn,
       datconnlimit as conn_limit,
       pg_database_size(datname) as size_bytes
FROM pg_database 
WHERE datname NOT IN ('template0', 'template1');
 
-- 當前連線數
SELECT datname, count(*) as active_connections 
FROM pg_stat_activity 
GROUP BY datname;

Database 資訊檢視

-- Schema 資訊
SELECT nspname as schema_name, nspowner 
FROM pg_namespace 
WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema';
 
-- Database 大小資訊
SELECT datname, 
       pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database 
WHERE datname = 'myapp_db';

Database 刪除

刪除操作

-- 終止所有連線
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'myapp_db' AND pid <> pg_backend_pid();
 
-- 刪除 database
DROP DATABASE myapp_db;

複習與問題

1. PostgreSQL管理工具