PostgreSQL常用操作
- 创建数据库：
CREATE DATABASE db_name template tpl_name; - 删除数据库：
drop database xxx; - 创建用户：
CREATE USER xxx WITH PASSWORD 'admin'; - 删除用户：
drop user username; - 授权：
GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;
- 撤销权限：
REVOKE ALL PRIVILEGES ON DATABASE my_database FROM my_user; - 统计数据库总数：
SELECT COUNT(*) FROM pg_database WHERE datallowconn;、SELECT COUNT(*) FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'highgo'); - 检测数据库中表总数：
SELECT COUNT(*) FROM pg_catalog.pg_tables WHERE schemaname = 'public'; - 检测序列总数：
SELECT COUNT(*) FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'S' AND n.nspname = 'public'; - 检测主键数：
SELECT COUNT(*) AS number_of_primary_key_columns FROM information_schema.table_constraints JOIN information_schema.key_column_usage USING (constraint_name, table_schema, table_name) WHERE constraint_type = 'PRIMARY KEY' AND table_schema = 'public'; 批量删除所有表：
DO $$ DECLARE r RECORD; BEGIN FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE'; END LOOP; END$$;批量删除所有序列：
DO $$ DECLARE r RECORD; BEGIN FOR r IN (SELECT 'DROP SEQUENCE ' || quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) || ';' AS drop_cmd FROM information_schema.sequences) LOOP EXECUTE r.drop_cmd; END LOOP; END $$;获取数据库列表
#!/bin/bash file="/web/database_list.txt" # 使用psql获取数据库列表，并去除空白行 formatted_databases=$(psql -U 'sysdba' -lqt | cut -d \| -f 1 | sed '/^\s*$/d') formatted_databases=$(echo "$formatted_databases" | tr -d ' ') # 将换行符替换为空格 formatted_databases=$(echo "$formatted_databases" | tr '\n' ' ') # 输出结果 echo "$formatted_databases" > "$file" # 删除指定值 sed -i "s/ highgo//g" "$file" sed -i "s/ install_v0//g" "$file" sed -i "s/ install_v1//g" "$file" sed -i "s/ install_v2//g" "$file" sed -i "s/ install_v3//g" "$file" sed -i "s/ template0//g" "$file" sed -i "s/ template1//g" "$file"断开数据库连接：
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='db_name' AND pid<>pg_backend_pid();pg_terminate_backend：用来终止与数据库的连接的进程id的函数。
pg_stat_activity：是一个系统表，用于存储服务进程的属性和状态。
pg_backend_pid()：是一个系统函数，获取附加到当前会话的服务器进程的ID。