连接方式
- pgadmin 自带客户端(windows),最新版本已经可以在浏览器登录了
- dbvis JDBC(咱也不懂,咱也不想查),反正oracle,sqlserver,pgsql都能连.用着也顺手,就是不免费
- psql 自带客户端(linux) /home/postgres/pgsql/bin/psql -U username -H hostaddr -d database
配置文件
配置文件postgresql.conf,里面的含义网上都有.搜索下很方便查到.
这里只记录几个个人常用的配置项:
log_statement (string) 开启后数据库日志pg_log会更新,方便定位问题
ddl包括所有数据定义语句，如CREATE、ALTER和DROP语句
mod包括所有ddl语句和更新数据的语句，例如INSERT、UPDATE、DELETE、TRUNCATE、 COPY FROM、PREPARE和 EXECUTE
All包括所有的语句。只有超级用户才能修改这个参数
shared_buffers (integer) 设置pgsql共享缓存区大小,吃内存.但可以提高命中率（好像需要和其他参数配合使用,后续补充）
这个参数只有在启动数据库时，才能被设置。它表示数据缓冲区中的数据块的个数，每个数据块的大小是8KB。数据缓冲区位于数据库的共享内存中，它越大越好，不能小于128KB。默认值是1024
常用语句
查数据库大小
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, ‘CONNECT’)
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE ‘No Access’
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, ‘CONNECT’)
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC – nulls first
LIMIT 20查占用空间最大的20张表
SELECT nspname || ‘.’ || relname AS “relation”,
pg_size_pretty(pg_total_relation_size(C.oid)) AS “total_size”
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN (‘pg_catalog’, ‘information_schema’)
AND C.relkind <> ‘i’
AND nspname !~ ‘^pg_toast’
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
查所有的表
select tablename from pg_tables where tablename like ‘%fav%’;
查所有表的字段
select count(attname) from pg_attribute;
查某张表中所有字段
select * from information_schema.columns where table_schema=’public’ and table_name = ‘tbl_user’;
查看某个字段存在与哪张表中
SELECT * from information_schema.columns WHERE COLUMN_NAME = ‘name’ ;
SELECT * from information_schema.columns WHERE COLUMN_NAME like ‘%parent%’;查看数据库连接
select * from pg_stat_activity;
查看各客户端的连接数
SELECT client_addr,count() from pg_stat_activity group by client_addr order by count() desc;
数据库的oid
select * from pg_database where oid=16384;
表的oid
select relname from pg_class where relfilenode = 25207;
按占用时间统计,pgadmin中运行
SELECT query, calls, total_time/calls AS onecall_time, total_time, shared_blks_hit,shared_blks_dirtied,shared_blks_read,shared_blks_written,rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements WHERE query like ‘select%tbl_res%’ ORDER BY total_time DESC LIMIT 1000;
按调用次数统计,pgadmin中运行
SELECT query, calls, total_time/calls AS onecall_time, total_time, rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY calls DESC LIMIT 50;
查看正在执行的sql 每个进程一行,会覆盖
select * from pg_stat_activity where query_start is not null and query like ‘%tbl_bm_poll_info%’ order by query_start DESC;
查询闲置连接数
select count(*) from pg_stat_activity where state=’idle’;
select * from pg_stat_activity where state != ‘idle’;清空统计结果
select pg_stat_statements_reset() ;
修改密码
ALTER USER postgres PASSWORD ‘admin’;
创建pg_stat_statements
create extension pg_stat_statements;