postgres 收集的一些问题

1. 删除数据库语句

双引号一点要添加

drop database "cn_axf_17Q2_test0904" ;

2.  去掉某个会话
一定要单引号

select pg_terminate_backend(pid) from pg_stat_activity where datname='cn_axf_17Q2_test0904' and pid<>pg_backend_pid();

3. 显示默认表空间的存储目录结构

show data_directory;

4. 显示所有表空间的存储目录

select spcname, pg_tablespace_location(oid) from pg_tablespace;

5.显示所有数据库

select pg_database.datname from pg_database

 

6.显示数据库中所有表格

select schemaname,tablename from pg_tables

 

7.所有schema

select * from information_schema.schemata;

8.其他命令

\c content_unidb

 

select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

drop schema denali_cn_17q1_20170817_eql cascade;

 

select pg_size_pretty(pg_database_size('content_unidb'));

select * from information_schema.schemata where schema like 'denali_vde%';

select pg_size_pretty(pg_database_size('denali_vde'));

9. 老师的邮件内容

1.    http://stackoverflow.com/questions/4970966/how-can-i-tell-what-is-in-a-postgresql-tablespace
2.    http://dba.stackexchange.com/questions/9603/postgresql-query-for-location-of-global-tablespace 
3.    https://www.postgresql.org/docs/9.5/static/manage-ag-tablespaces.html
4.    In PostgreSQL, a tablespace can be used by any PostgreSQL database. (As long as the requesting user has sufficient privileges, that is.) I think this query
5.    SELECT spcname, spclocation FROM pg_tablespace;
6.    will show you the directory that index_old is using in the filesystem in PostgreSQL version through 9.1. Prowl around in there to see if something real is in your way. I'd be really cautious about trying to delete anything in there apart from using PostgreSQL's interface, though.
7.    In 9.2+, try
8.    select spcname, pg_tablespace_location(oid) from pg_tablespace;
9.    
10.    
11.    Find the database tablespace, tablespace location, size.
12.    SELECT datname, spcname, pg_size_pretty(pg_database_size(datname)),  pg_tablespace_location(t.oid) FROM pg_database d, pg_tablespace t WHERE d.dattablespace = t.oid order by spcname;