PostgreSQL常用脚本

1、Linux登录数据库
2、查看版本号
3、切换数据库和用户
4、创建数据库
5、杀掉进程
6、查询被锁定表、对象等
7、查询表、列、表空间
8、删除模式
9、初始化数据库
10、查看数据库连接数
11、创建dblink函数
12、删除表但数据文件没有减小
13、重建索引
14、通过表名查询文件ID
15、统计库表大小

99、字典表

//////////////////////////////////////////////////////////
1、Linux登录数据库，输入第一条命令后，显示 -bash-4.2$，继续输入psql就进入数据库了。
su - postgres
psql
--或者通过命令参数登录远程数据库
psql -h 192.168.1.2 -U username -p 5432 -d dbname
--退出，输入第一条命令后，显示 -bash-4.2$，继续输入第二条提示输入密码，就回到root了。
\q
su - root
--常用命令
列举数据库：\l
切换数据库：\c dbname
切换用户：\c - username
切换模式：set search_path to schemaname
查看当前库中的所有表：\dt
查看当前库中的所有表：\d dbname
查看表结构：\d tablename
显示客户端字符集：\encoding
显示客户端字符集：\encoding client_encoding
显示服务端字符集：\encoding server_encoding
设置用户密码：password username
执行sql文件：\i aa.sql
显示所有用户：\du
显示当前数据库和连接信息：\conninfo

2、查看版本号
show server_version;
show server_version_num;
select version();

3、切换数据库和用户
--需要在psql命令界面下
\c daname;
\c - username;
--显示当前
select current_user, session_user;
select current_database();
--命令行显示当前
\c

4、创建数据库
--创建用户
create user common with password 'common';
--创建表空间。文件夹右键选择属性|安全，修改Users用户的权限为完全控制。
create tablespace common owner common location 'D:\Software\PostgreSQL\data\common';
drop tablespace common;
--创建数据库，lc_collate与lc_ctype对应postgresql.conf中的lc_messages对应的字符集
create database common with
owner = common
encoding = 'UTF8'
lc_collate = 'Chinese (Simplified)_China.936'
lc_ctype = 'Chinese (Simplified)_China.936'
tablespace = common
connection limit = -1;
--创建模式，先切换数据库到dbname下，psql命令下用：\c dbname;
create schema common authorization common;
alter schema name rename to new_name;
alter schema name owner to new_owner;
--search_path模式列表(可忽略)：查询时先查用户对应的模式，找不到数据则按路径中的模式列表依次查询
show search_path;
select d.datname,s.setconfig from pg_db_role_setting s join pg_database d on d.oid=s.setdatabase;
alter database dbname set search_path to "$user", public;
alter database dbname reset search_path;
--创建表，创建时尽量添加模式名，表将会属于该模式
create table common.t_application
(application_id varchar(32) PRIMARY KEY,
 task_id varchar(32),
 project_name varchar(100)
);
COMMENT ON TABLE t_application IS '申请表';
--将表的所有者从public转到common
ALTER TABLE public.t_application OWNER TO common;

5、杀掉进程
--杀掉空闲进程
select pg_terminate_backend(pid) from pg_stat_activity where state='idle';
--关闭当前用户下的后台进程，向后台发送SIGINT信号，用于关闭事务，此时session还在，并且事务回滚
pg_cancel_backend()
--关闭所有的后台进程，需要superuser权限，向后台发送SIGTERM信号，用于关闭事务，此时session也会被关闭，并且事务回滚
pg_terminate_backend()

6、查询被锁定表、对象等
--ExclusiveLock查询的是排它锁
select * from pg_locks a
join pg_class b on a.relation = b.oid
join pg_stat_activity c on a.pid = c.pid
where a.mode like '%ExclusiveLock%';

7、查询表、列、表空间
--查询表及其注释语句
select a.tablename,obj_description(relfilenode, 'pg_class') as 注释
from pg_tables a join pg_class b on a.tablename=b.relname where a.schemaname='schemaname';
--查询表详细信息，表对应的reltablespace为0时表示使用的是数据库默认表空间
select c.relname 表名,obj_description(c.relfilenode, 'pg_class') as 注释,c.relpages 页数,
pg_size_pretty(pg_relation_size(c.oid)) 大小,t.table_catalog 数据库,t.table_schema 模式,
case c.reltablespace when 0 then ss.spcname else s.spcname end as 表空间
from pg_class c
join information_schema.tables t on c.relname=t.table_name
join pg_database d on t.table_catalog=d.datname
join pg_tablespace ss on d.dattablespace=ss.oid
left join pg_tablespace s on c.reltablespace=s.oid
where c.relkind='r' and c.relname not like 'pg_%' and c.relname not like 'sql_%'
--查询列信息
--去除回车换行符：replace(replace(col_description(a.attrelid, a.attnum), chr(10), ''), chr(13), '') 别名
select t.tableowner 用户,t.schemaname 模式,c.relname 表名,a.attnum 序号,a.attname 列名,
col_description(a.attrelid, a.attnum) 别名,format_type(a.atttypid, a.atttypmod) 类型
from pg_attribute a join pg_class c on a.attrelid=c.oid
join pg_tables t on c.relname=t.tablename
where a.attnum>0 and c.relkind='r' and c.relname not like 'pg_%'
and c.relname not like 'sql_%' and a.atttypid>0
order by c.relname,a.attnum
--查询数据库默认表空间
--如果表空间为空，则默认属于所在数据库的默认表空间
select d.datname,d.dattablespace,s.spcname from pg_database d join pg_tablespace s on d.dattablespace=s.oid;

8、删除模式
drop schema ncxtxk cascade

9、初始化数据库
--以管理员身份运行cmd
--验证环境，命令正常运行不报错，说明环境满足，否则安装vc运行库
pg_ctl --help
--创建data目录，命令初始化，通过 -W 参数会提示输入超级用户密码
--chs: Chinese_China.936，zh_CN.UTF8: zh_CN.UTF-8，
--Windows Server 2016不指定locale，是Chinese (Simplified)_China.936
initdb.exe -D PATH\pgsql\data -E UTF8 --locale=zh_CN.UTF8 -U postgres -W
initdb.exe -D PATH\pgsql\data -E UTF8 -U postgres -W
--配置pg_hba.conf，添加以下语句
host all all 0.0.0.0/0 md5
--配置postgresql.conf，配置项需删除前面的注释符 #，其他配置根据需求
listen_addresses = '*'
port = 5432
max_connections = 1000
--安装服务
pg_ctl.exe register -D PATH\pgsql\data -N postgresql-9.6
--卸载服务，成功后删除data文件夹
pg_ctl.exe unregister -N postgresql-9.6

10、查看数据库连接数
--当前连接数据
select count(1) from pg_stat_activity;
--最大连接数据，或查看配置文件postgresql.conf
show max_connections;

11、创建dblink函数
--安装DbLink扩展即可，该扩展已包含在PG数据库模块中，直接执行语句即可。
create extension dblink;
drop extension dblink;

12、删除表但数据文件没有减小
--数据库执行drop table后，查看du -sh /pgdb已下降，但df -h /pgdb没有减少，是有还有进程使用对应的文件句柄
--查询表对应的文件ID
select pg_relation_filepath('table_name');
--命令lsof|grep “文件ID” 确认占用文件的进程PID，确认进程是否可以删除
select * from pg_stat_activity where pid='23024';
--删除对应进程
select pg_terminate_backend('PID');
--删除表
drop table table_name;

13、重建索引
--删除表数据后，数据库文件夹大小还是很大，因为没有重建索引，索引文件也很大
reindex { index | table | database | system } name;
--说明
index 重构指定的索引
table 重构指定表的所有索引，包括下级TOAST表
databse 重构指定数据库的所有索引，不能再一个事务块中执行
system 重构这个系统的索引包含当前的数据库。不能在一个事务块中执行

14、通过表名查询文件ID
select pg_relation_filepath('table_name');
select oid from pg_class where relname='table_name';
select * from pg_class where oid='file_id';
select * from pg_class where relfilenode='file_id';

15、统计库表大小
--查询库大小(byte)，加上pg_size_pretty表示以KB、MB、GB等显示
select pg_size_pretty(pg_database_size('dbname'));
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;
select pg_size_pretty(sum(pg_database_size(pg_database.datname))) AS total_size from pg_database;
--查看表空间大小
select pg_size_pretty(pg_tablespace_size('tablespace_name'));
--查询表大小
select pg_size_pretty(pg_relation_size('table_name'));
--查看索引大小
select pg_size_pretty(pg_relation_size('index_name'));
--查看表总大小，包括索引大小
select pg_size_pretty(pg_total_relation_size('XXX'));

 

99、字典表
pg_database           --数据库信息
pg_tablespace        --表空间信息
pg_tables               --表信息
pg_class                 --对象信息
information_schema.tables    --数据库、模式、表对应关系
pg_settings            --数据库配置信息