Archive

Posts Tagged ‘PostgreSQL’

PostgreSQL使用PL/SQL和游标实现按日期批量执行

March 26th, 2019 No comments

现有的DWH系统的是按天创建数据表的,使得定期维护变得麻烦,例如每个月底需要将按当月产生的临时表archive。

方式1.批量生成SQL,按固定的日期值生成一堆SQL,SQL生成方法多样。但是需要确认全部sql是否正确。

方式2.编写PL/SQL function,使用游标方式批量执行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
drop function fun_datecursor(from_date text,to_date text,cond text);
create or replace function fun_datecursor(from_date text,to_date text,cond text) returns text
as $ 
declare
    cur_date refcursor;
    buffer text := '';
    var_day date;
begin
open cur_date for execute 'SELECT generate_series('''|| from_date ||'''::date,'''|| to_date ||'''::date,'''|| cond ||''')'; 
    loop  --开始循环
        fetch cur_date into var_day;  --将游标指定的值赋值给变量
        if found then 
             ---------------------------------------
             --任意的逻辑,或调用其他function
             ---------------------------------------
            buffer:= buffer || to_char(var_day,'yyyyMMdd'); 
           else 
            exit; 
            end if; 
    end loop;  --结束循环
close cur_date;  --关闭游标
return buffer;
end
$ language plpgsql;

执行function

1
select fun_datecursor('2008-03-01','2008-03-31','1 day’);
Categories: 零敲碎打 Tags: , ,

PostgreSQL创建ReadOnly只读用户

March 26th, 2019 No comments

PostgreSQL可以通过schema和table级别对数据表进行只读控制
一般会使用PostgreSQL创建只读用户,然后给予相应的只读权限方式实现

通过使用

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建readonly_user用户,密码为readonly_password
create user readonly_user with encrypted password 'readonly_password';
-- 设置readonly_user用户为只读事务
alter user readonly_user set default_transaction_read_only=on;
-- 授予usage权限给到readonly_user用户
grant usage on schema "public" to readonly_user;
-- 将默认"public"schema下新建表的读取权限授予给readonly_user
alter default privileges in schema "public" grant select on tables to readonly_user;
-- 授予select权限给到readonly_user用户
grant select on all tables in schema "public" to readonly_user;
grant select on all sequences in schema "public" to readonly_user;
-- 允许readonly_user用户连接到指定数据库
grant connect on database splrp_dev to readonly_user;

注意:
已有的数据表进行readonly设置,可以通过

1
2
3
4
5
-- 授予usage权限给到readonly_user用户
grant usage on schema "public" to readonly_user;
-- 授予select权限给到readonly_user用户
grant select on all tables in schema "public" to readonly_user;
grant select on all sequences in schema "public" to readonly_user;

对于将来创建的新表,则需要通过

1
2
-- 将默认"public"schema下新建表的读取权限授予给readonly_user
alter default privileges in schema "public" grant select on tables to readonly_user;

可以讲以后创建的table也赋予readonly_user只读权限。

Categories: 零敲碎打 Tags: ,

PostgreSQL查询表和index占用空间大小

November 12th, 2018 No comments

PostgreSQL查询表和index占用空间大小

PostgreSQL表和index占用空间大小信息存储在
information_schema.tables中
通过SQL可以查询到相应的统计数据

1
2
--查出单个表的大小
select pg_size_pretty(pg_relation_size('TABLENAME'));

查出表大小按大小含Index

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 查出表大小按大小含Index
SELECT
"table_name",
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
SUBSTRING("table_name",1,10) as short_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
where all_tables.table_name like '%TABLENAME%'
ORDER BY total_size DESC
) AS pretty_sizes

Read more…

PostgreSql查询正在执行的SQL和查询执行耗时的SQL

November 9th, 2018 No comments

运行在AWS RDS上的PostgreSql今天TransactionID耗尽,原因是有一个SQL执行占用CPU超过12小时

–查询正在执行的SQL

1
select * from pg_stat_activity where datname='schema名称';

–结束正在进行的R查询

1
select pg_cancel_backend(pid);

–结束正在执行CUD操作

1
select  pg_terminate_backend(pid)

Read more…

Categories: 系统管理, 语言编程 Tags: , ,