[TOC]

常用命令

# 查看pg版本
pg_ctl -V

# 进入命令行
export PGPASSWORD=spider;psql -h 172.16.122.19 -U spider -d gfecp_dev

# 单表授权
grant all on area to gfecp_dev;

# 视图所有表授权
grant all on all tables in schema public to gfecp_dev;

# 修改表的拥有者
alter table area owner to gfecp_dev;

# 增加表默认值
alter table area alter column colname set default 456;

# 增加表默认值
alter table area alter column colname set default 456;

# 增加自增列
CREATE SEQUENCE 表名_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

alter table 表名 alter column id set default nextval('表名_id_seq');

# 建表设置自增列
create table 表名(
id serial)

# 查看表大小
select pg_size_pretty(pg_relation_size('table_name'));

# 查看所有表大小
SELECT  table_schema || '.' || table_name AS table_full_name
      , pg_size_pretty(pg_total_relation_size('"' ||table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY pg_total_relation_size('"' ||table_schema || '"."' || table_name || '"') DESC limit 20

# 服务启动
service postgresql start       # 启动
service postgresql stop       # 停止
service postgresql status    # 查看状态

常用sql

-- 查询表的所有字段
select column_name
from information_schema.columns
where table_schema='public'
and table_name='tablename'

-- 查看表锁
select a.locktype, a.database, a.pid, a.mode, a.relation, b.relname
from pg_locks a
join pg_class b on a.relation = b.oid
where b.relname = 'tablename';

-- 解决表锁
select pg_cancel_backend(pid) -- 取消事务
-- , pg_terminate_backend(pid) -- 终止事务
from pg_locks
where relation in (select oid from pg_class where relname='tablename');

-- 查看所有表名
select tablename
from pg_tables
where schemaname='gfecp'
and tablename <> 't_gfecp_bigdata_prod_accident'
and tablename like 't_gfecp_bigdata_prod_accident%'
order by 1

--完全复制表,包括约束、索引、字段注释
CREATE TABLE "gfecp_dev"."tmp_t_gfecp_bigdata_pdp_apply" ( LIKE "gfecp_dev"."t_gfecp_bigdata_pdp_apply" INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS);

运维命令

# 备份
pg_dump -h 127.0.0.1 -p 5432  -U spider --format custom --blobs --verbose --file back.sql dbname

# 还原
pg_restore -h 127.0.0.1 -p 5432  -U spider --dbname dbname --verbose --clean back.sql

数据字典

查询字典
SELECT
    A .attname 字段,   
    concat_ws (
        '',
        T .typname,
        SUBSTRING (
            format_type (A .atttypid, A .atttypmod)
            FROM
                '\(.*\)'
        )
    ) AS 类型,
        case when s.pk is not null then '是'
                               else '否'
    end as 主键,
    case A.attnotnull when 'f' then '是'
                      when 't' then '否'
    end as 空,
    d.description 注释
FROM pg_attribute A
INNER JOIN pg_class C on A .attrelid = C .oid
INNER JOIN pg_type T on A .atttypid = T .oid
LEFT JOIN (SELECT conrelid, unnest(conkey) as pk
                        FROM pg_constraint
                        WHERE contype = 'p') S ON S.conrelid = C .oid
                                   AND A.attnum = S.pk
LEFT JOIN pg_description d on d.objoid = A .attrelid
                                                    AND d.objsubid = A .attnum
LEFT JOIN pg_namespace n on n.oid = c.relnamespace    
WHERE A.attnum > 0
AND n.nspname = 'public'   
AND C.relname = 't_gfecp_bigdata_blacklist'
ORDER BY
    C .relname,
    A .attnum
批量修改表所有者
-- 批量修改 所有者
DO $$
DECLARE
    r record;
    i int;
    v_schema text[] := '{public}';
    v_new_owner varchar := 'gfecp_dev';
BEGIN
    FOR r IN
        SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.tables WHERE table_schema = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.sequences WHERE sequence_schema = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.views WHERE table_schema = ANY (v_schema)
--        UNION ALL
--        SELECT 'ALTER FUNCTION "' || nsp.nspname || '"."' || p.proname || '"(' || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ' || v_new_owner || ';' AS a FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid WHERE nsp.nspname = ANY (v_schema)
--        UNION ALL
--        SELECT 'ALTER DATABASE "' || current_database() || '" OWNER TO ' || v_new_owner
    LOOP
        EXECUTE r.a;
    END LOOP;
    FOR i IN array_lower(v_schema, 1)..array_upper(v_schema, 1)
    LOOP
        EXECUTE 'ALTER SCHEMA "' || v_schema[i] || '" OWNER TO ' || v_new_owner;
    END LOOP;
END
$$;

results matching ""

    No results matching ""