ADB for postgres

--全量备份(用户、权限、不包含数据)

./pg_dumpall --schema-only -h127.0.0.1 -p3432 -U postgres -f all.sql

--备份uat库表结构

./pg_dump --schema-only -h127.0.0.1 -p3432 -U postgres -d uat -f uat.sql

--全量数据(表结构)恢复：

./psql -h127.0.0.1 -p3432 -U postgres < all.sql

--uat库表结构恢复：

./psql -h127.0.0.1 -p3432 -U postgres -d uat < uat.sql

--pg登录：

./psql -h127.0.0.1 -p3432 -U postgres -d uat

--pg语法：

1、列举数据库：\l

2、切换数据库：\c 数据库名

3、列举schema：\dn

4、查看该某个库中的所有表：\dt 

5、查看数据库中所有role及其权限：\du

6、查看某个库中的某个表结构：\d 表名

7、查看某个库中某个表的记录：select * from apps limit 1;

8、显示字符集：\encoding

9、退出psgl：\q

10、视图列表：\dv

10、查看索引：\di    

11、查看存储过程：\df 

12、schema切换：set search_path to test;   

--pg权限

----只读账号创建、授权

--创建用户
CREATE USER readonly WITH ENCRYPTED PASSWORD ' ';

--修改用户密码
ALTER USER 用户名 WITH PASSWORD ' ';

--设置用户默认开启只读事务
ALTER USER readonly SET default_transaction_read_only = ON;

--设置用户登录权限

ALTER USER readonly WITH login;

--将schema中usage权限赋予给readonly用户,访问所有已存在的表
GRANT usage ON SCHEMA xyh TO readonly;

--将schema中表的查询权限赋予给readonly用户,访问所有已存在的表
GRANT SELECT ON ALL tables IN SCHEMA xyh TO readonly;

--未来访问xyh模式下所有新建的表：
ALTER DEFAULT privileges IN SCHEMA xyh GRANT SELECT ON tables TO readonly;

--用户权限查看
select * from information_schema.table_privileges where grantee='ro_query';

--用户设置数据库权限

grant all privileges on database ktxmch  to ktchsys;

--必须要给用户设置对scheam的使用权限
grant USAGE on SCHEMA public to test ;

--用户设置表权限
grant all on all tables in schema data_out to user_name;

----数据库、普通表、分区表大小统计

--表大小统计：
select schemaname,round(sum(pg_total_relation_size(schemaname || '.' || tablename))/1024/1024,2) "MB" from pg_tables where schemaname=' ' and tablename=' ' group by 1;

select schemaname,tablename,round(sum(pg_total_relation_size(schemaname || '.' || tablename))/1024/1024,2) "MB" from pg_tables where schemaname='bcd' group by 1,2;

--分区表大小统计：
select schemaname,tablename,round(sum(pg_total_relation_size(schemaname || '.' || partitiontablename))/1024/1024,2) "MB" from pg_partitions where schemaname='' and tablename='' group by 1,2;

--查看数据库的大小
select datname,pg_size_pretty(pg_database_size(datname)) from pg_database where datname = ' ';

SELECT pg_size_pretty(pg_database_size('test2'));

--新增分区

如果存在default partition，则不能新增分区，只能split default partition

ALTER TABLE test_partition_range ADD partition p2 start ('2017-02-01') end ('2017-02-28');

--删除分区

ALTER TABLE test_partition_range DROP partition p2;

--重命名分区

ALTER TABLE test_partition_range RENAME PARTITION p2 TO Feb17;

========造数据命令========
create table tbl_test (id int, info text, c_time timestamp);

insert into tbl_test select generate_series(1,1000000),md5(random()::text),clock_timestamp();

select count(id) from tbl_test;

create table test1 (id int, test text);
方法二：
select id,'test:'||id as text into test6 from  generate_series(1,200000000) as id;
插入数据：
insert into test2(id,test)select i,'test:'||i from generate_series(30000001,40000000)as i;
insert into test2(id,text)select i,'test:'||i from generate_series(40000001,140000000)as i;  

法三：
insert into part_test select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,1000000) t(id);
select create_hash_partitions('part_test'::regclass,'crt_time',128);

法四：
CREATE TABLE almart_all  
(  
    date_key date,  
    hour_key smallint,  
    client_key integer,  
    item_key integer,  
    account integer,  
    expense numeric  
);  
插入数据：
INSERT INTO almart_all  
select  
    (select  
        array_agg(i::date)  
     from generate_series('2015-12-01'::date, '2015-12-30'::date, '1 day'::interval) as t(i)  
    )[floor(random()*4)+1] as date_key,  
    floor(random()*24) as hour_key,  
    floor(random()*1000000)+1 as client_key,  
    floor(random()*100000)+1 as item_key,  
    floor(random()*20)+1 as account,  
    floor(random()*10000)+1 as expense  
from  
    generate_series(1,3000000,1);