postgresql中一些比较实用的命令

查看索引大小

hmc_main=# \di+ ck_era_doc_pkey
                            List of relations
 Schema |      Name       | Type  | Owner | Table  | Size  | Description
--------+-----------------+-------+-------+--------+-------+-------------
 public | ck_era_doc_pkey | index | inofa | ck_era | 16 kB |
(1 row)

查看表大小

hmc_main=# \dt+ goods_info
                         List of relations
 Schema |    Name    | Type  |  Owner   |    Size    | Description
--------+------------+-------+----------+------------+-------------
 public | goods_info | table | postgres | 8192 bytes |
(1 row)

查看函数代码

hmc_main=# \sf getarchinfo
CREATE OR REPLACE FUNCTION public.getarchinfo()
 RETURNS SETOF type_getarchinfo
 LANGUAGE plpgsql
AS $function$

begin
  RETURN QUERY
  select  pub_arch.arch_id ,pub_arch.arch_code, pub_arch.arch_name,octet_length(ltrim(pub_arch.arch_code)) as length
  from    pub_arch
  where   del_flag=0
  order by arch_code;
  end;

$function$

psql命令行执行sql (有无-A -t参数对结果的影响)

[postgres@hmc ~]$ psql -c "select * from pub_arch limit 2" greenerp_base inofa
 arch_id | arch_code | arch_name  | del_flag
---------+-----------+------------+----------
       1 | 101       | 质量部档案 |        0
(1 row)
-- 有空行
[postgres@hmc ~]$ psql -A -c "select * from pub_arch limit 2" greenerp_base inofa
arch_id|arch_code|arch_name|del_flag
1|101|质量部档案|0
(1 row)  -- 没有空行
[postgres@hmc ~]$ psql -t -c "select * from pub_arch limit 2" greenerp_base inofa
       1 | 101       | 质量部档案 |        0
-- 有空行
[postgres@hmc ~]$ psql -At -c "select * from pub_arch limit 2" greenerp_base inofa
1|101|质量部档案|0      -- 没有空行，只有纯数据，编写shell很有效果

创建外部服务

create server server_remote_jh foreign data wrapper postgres_fdw options(host '192.168.162.20',port '5432',dbname 'greenerp_base');

create user mapping for hmcf server server_remote_jh options(user 'hmcf',password 'hmcf');

然后创建外部表就可以测试了

死锁的查询与清除

// 根据数据库整体查询
pg_cancel_backend(pid)     // 取消后台操作，回滚未提交事物
pg_terminate_backend(pid)  // 中断session，回滚未提交事物

SELECT * FROM pg_stat_activity WHERE datname='greenerp_base' 
select pg_cancel_backend(2324);       
select pg_terminate_backend(2324);   

// 或者根据具体的表查询
select oid from pg_class where relname='pub_unit_cert' --oid是每个表隐藏的id
select pid from pg_locks where relation=19761
--如果查询到了结果 则释放锁定
select pg_cancel_backend(2324)
select pg_terminate_backend(2324); 

备份所有的函数、表、索引

#rm /tmp/dump_test
#rm /tmp/function_list

pg_dump -U inofa -Fc -s -f /tmp/dump_test greenerp_base
pg_restore -l /tmp/dump_test | grep FUNCTION > /tmp/function_list
pg_restore -L /tmp/function_list  /tmp/dump_test > /pg_func/`date "+%Y-%m-%d"`_function.sql

只需对关键字进行处理即可，如：
FUNCTION 、TABLE、INDEX

联合唯一约束

alter table sendredpack add constraint uk_dj_redpack unique (openid, activid); 
alter table sendredpack drop constraint "uk_dj_redpack ";