postgreSQL常用维护命令

查看配置文件位置

select name,setting from pg_settings where category='File Locations';

查看关键点的设置

select name,context,unit,setting,boot_val,reset_val from pg_settings where name in('listen_addresses','max_connections','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem') order by context,name;

要是context被设置成了postmaster，也就是修改改值需要重启服务。如果设置成了user,改变需要一次重载，

unit说明设置的单元大小。

需要重启的重要参数

listen_addresses,port ，max_connections,shared_buffers

需要重载的重要参数

effective_cache_size建议该值设置成物理内存一半,work_mem，maintenance_work_mem该值设置不应超过1GB.

重载控制文件的方法

1pg_ctl reload -D your_data_directory_here

2service postgresql-9.1 reload postgresql9.1是服务名

3select pg_reload_conf();

创建一个用户，能登录数据库并且创建数据库对象

create role leo login password 'lion'

created valid until 'infinity';

修改密码，使用psql,连接上去，使用\password

移动一个数据库中的所有对象到一个表空间

alter database mydb set tablespace secondary;

移动一个表到一个表空间

alter table mytable set tablespace secondary;

给表空间赋予特定的用户

ALTER TABLESPACE new_tablespace OWNER TO eliza;

ALTER USER eliza SET default_tablespace = 'new_tablespace';

创建不记日志的表，这种表在异常断电后，内容就没了。

create unlogged table web_sessions(session_id text primary key,add_ts timestamptz,session_state xml);

多行插入

insert into logs_2011(user_name,description,log_ts) values('robe','logged in','2011-01-10') ,('lhs','logged out','2011-01-11');

创建唯一约束

altertable logs_2011 add constraint uq_us_log unique(user_name,log_ts;

创建函数索引

create index idx_featnames_ufullname_varops on featnames_short using btree(upper(fullname) varchar_pattern_ops);

创建部分索引

create unique index idx_1 on test using btree (lower(username)) where deactivate is null;

创建多列索引

create index idx_1 on sometable using btree(type_id,upper(fullname));

查看server在哪个端口上监听

select inet_server_port();

查看当前的数据库

select current_user;

查看当前userid

select inet_srver_addr();

查看版本

select version(); psql --version

口令文件放置在环境变量PGPASSFILE下，如果没有指定这个变量，那么默认的文件名和位置按下面的搜索：

在*nix系统上检查~/.pgpass

在windows上，检查%appdata%\postgresql\pgpass.conf

查看服务运行了多长时间

select date_trunc('second',current_timestamp-pg_postmaster_start_time()) as uptime;

查看服务器控制文件

pg_controldata

查看有哪些数据库

select datname from pg_database;

查看数据库中有多少表

select count(*) from information_schema.tabels where table_schema not in('information_schema','pg_catalog');

查看数据库大小

seect pg_database_size(current_database());

select sum(pg_database_size(datname)) from pg_database;

查看表大小

select pg_relation_size('accounts');

查看表及相关索引的大小

select pg_total_relation_size('accounts');

查看最大的表

select table_name,pg_relation_szie(tbale_name) as size from information_schema.tables where table_schema not in('information_schema','pg_catalog') order by size desc limit 10;

快速获得表的行

select (case when reltuples>0 then pg_relation_szie('mytable')/(8192*relpages/reltuples) else 0 end)::bigint as estimated_row_count from pg_class where oid= 'mytable'::regclass;

查看语言

select * from pg_language;

查看会话进程

select * from pg_stat_activity;

查看表

select　* from pg_tables;

查看表字段

select * from information_schema.columns where table_schema = 'public' and table_name = 't';

查看视图
select * from pg_views where schemaname = 'public';
select * from information_schema.views where table_schema = 'public';

查看触发器

查看触发器
select * from information_schema.triggers;

查看序列

select * from information_schema.sequences where sequence_schema = 'public';

查看索引

select * from pg_index;

查看序列与表的对应关系
  WITH fq_objects AS (SELECT c.oid,c.relname AS fqname ,
                           c.relkind, c.relname AS relation
                    FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),


     sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'), 
     tables    AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' ) 
         SELECT
       s.fqname AS sequence,
       '->' as depends,
       t.fqname AS table
      FROM
       pg_depend d JOIN sequences s ON s.oid = d.objid 
                 JOIN tables t ON t.oid = d.refobjid 
          WHERE
       d.deptype = 'a' and t.fqname = 'cc';

查看表空间大小
select pg_tablespace_size('pg_default');

查看锁

select * from pg_locks;

 --查看约束
select * from pg_constraint where contype = 'p'  
--u unique,p primary,f foreign,c check,t trigger,x exclusion

select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 'cc';

--查看表上存在哪些索引以及大小
select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in (
select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc');

SELECT c.relname,c2.relname, c2.relpages*8 as size_kb
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'cc' AND
c.oid = i.indrelid AND
c2.oid = i.indexrelid
ORDER BY c2.relname; 


--查看索引定义
select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc';
select pg_get_indexdef(b.indexrelid);


--查看过程函数定义
select oid,* from pg_proc where proname = 'insert_platform_action_exist'; --oid = 24610
select * from pg_get_functiondef(24610);


--查看表大小(不含索引等信息)
select pg_relation_size('cc');                         --368640 byte
select pg_size_pretty(pg_relation_size('cc'))   --360 kB


--查看DB大小
select pg_size_pretty(pg_database_size('smiletao'));   --12M


--查看服务器DB运行状态
[postgres@eyar ~]$ pg_ctl status -D $PGDATA
pg_ctl: server is running (PID: 2373)
/home/postgres/bin/postgres "-D" "/database/pgdata" 


--查看每个DB的使用情况(读,写,缓存,更新,事务等)
select * from pg_stat_database


--查看索引的使用情况
select * from pg_stat_user_indexes;


--查看表所对应的数据文件路径与大小
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'empsalary';


--查看索引与相关字段及大小
 SELECT n.nspname AS schema_name,
        r.rolname as table_owner,
       bc.relname AS table_name,
       ic.relname AS index_name,
       a.attname  AS column_name,
       bc.relpages*8 as index_size_kb     
  FROM pg_namespace n,
       pg_class bc,             -- base class
       pg_class ic,             -- index class
       pg_index i,
       pg_attribute a,           -- att in base
       pg_roles r
  WHERE bc.relnamespace = n.oid
     and i.indrelid = bc.oid
     and i.indexrelid = ic.oid
     and bc.relowner = r.oid
     and i.indkey[0] = a.attnum
     and i.indnatts = 1
     and a.attrelid = bc.oid
     and n.nspname = 'public'
     and bc.relname = 'cc'
  ORDER BY schema_name, table_name, index_name, attname;

去重

postgres=# SELECT * FROM new_cust;
customerid
------------
1
2
3
4
4

BEGIN;
LOCK TABLE new_cust IN ROW EXCLUSIVE MODE;
DELETE FROM new_cust
WHERE ctid NOT IN
(SELECT min(ctid)
FROM new_cust
WHERE customer_id IN (4) --specify exact duplicate ids
GROUP BY customerid);
COMMIT;
and then follow that with
VACUUM new_cust;

参照：http://my.oschina.net/Kenyon/blog/85395