PostgreSQL 日常SQL记录
平时用的比较多的SQL语句，有时候会忘掉一点点，在这里记录一下：
1.创建表的同时，插入数据：
create table test as select generate_series(1, 1000000) as id, cast(random() as text) as info , now() as crt_time;
mydb=# create table test as select generate_series(1, 1000000) as id, cast(random() as text) as info , now() as crt_time;
SELECT 1000000
mydb=# \d test
Table "public.test"
Column | Type | Modifiers
----------+--------------------------+-----------
id | integer |
info | text |
crt_time | timestamp with time zone |
mydb=> select pg_relation_filepath('test1');
pg_relation_filepath
----------------------------------------------
pg_tblspc/16474/PG_9.5_201510051/16475/16477
(1 row)
mydb=> select pg_size_pretty(pg_relation_size('test1'));
pg_size_pretty
----------------
5888 kB
(1 row)
2.创建及查看数据库的表空间
select oid, * from pg_tablespace where oid in (select dattablespace from pg_database where datname='mydb');
postgres=# create tablespace mydb location '/var/lib/pgsql/mydb_tbspace'; CREATE TABLESPACE postgres=# \db
List of tablespaces Name | Owner | Location ------------+----------+----------------------------- mydb | postgres | /var/lib/pgsql/mydb_tbspace pg_default | postgres | pg_global | postgres | (3 rows) postgres=# create database mydb encoding='utf-8' tablespace = mydb; CREATE DATABASE postgres=# select oid, * from pg_tablespace where oid in (select dattablespace from pg_database where datname='mydb'); oid | spcname | spcowner | spcacl | spcoptions -------+---------+----------+--------+------------ 16474 | mydb | 10 | | (1 row)
添加一些别的，有部分为转载：
查看服务进程：
2020-09-01添加：
引自：
https://www.cnblogs.com/slqdba/p/10365691.html
分区表主表与子表一致性检查
分区表检查sql
SELECT parent.relname AS parent,max(child.relname) AS child
FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid
--WHERE parent.relname like 't_%'
group by parent.relname
order by parent.relname;
分区表下有多少分区子表
SELECT parent.relname AS parent,child.relname AS child
FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid
order by 1,2;
select relname , consrc from pg_inherits i join pg_class c on c.oid = inhrelid join pg_constraint on c.oid = conrelid where contype = 'c' and inhparent in (SELECT distinct parent.oid AS parent FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid) and (relname like '%201812' or relname like '%201901') order by relname asc;
分区表索引检查
CREATE OR REPLACE FUNCTION check_partition_table_index(
in_benchmark_table varchar,
in_parent_table varchar
)
RETURNS void AS $$
DECLARE
v_sql_str text;
cur_all_child_table refcursor;
rec_all_child_table record;
v_table_name varchar;
cur_check_result refcursor;
rec_check_result record;
v_create_index_str text;
BEGIN
v_sql_str := ' SELECT child.relname AS table_name
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = $1
AND child.relname <> $2
order by parent.relname';
open cur_all_child_table for execute v_sql_str using in_parent_table, in_benchmark_table;
loop
fetch cur_all_child_table INTO rec_all_child_table;
if not found then
exit;
end if;
v_table_name := rec_all_child_table.table_name;
v_sql_str := 'select pg_get_indexdef(i.indexrelid) as create_index_str
from pg_index i
where indrelid = (select oid from pg_class where relname = $1)
and not exists
(select 1 from pg_index pi
where pi.indrelid = (select oid from pg_class where relname = $2)
and pi.indnatts = i.indnatts
and pi.indisunique = i.indisunique
and pi.indkey = i.indkey
and pi.indcollation = i.indcollation
and pi.indclass = i.indclass
and pi.indoption = i.indoption
)';
--基准表的索引是否都在其他表中
open cur_check_result for execute v_sql_str using in_benchmark_table, v_table_name;
loop
fetch cur_check_result INTO rec_check_result;
if not found then
exit;
end if;
v_create_index_str := rec_check_result.create_index_str;
raise warning '% lacks %', v_table_name, v_create_index_str;
end loop;
close cur_check_result;
--其他表的索引是否都在基准表中
open cur_check_result for execute v_sql_str using v_table_name, in_benchmark_table;
loop
fetch cur_check_result INTO rec_check_result;
if not found then
exit;
end if;
v_create_index_str := rec_check_result.create_index_str;
raise warning '% adds %', v_table_name, v_create_index_str;
end loop;
close cur_check_result;
end loop;
close cur_all_child_table;
END;
$$ LANGUAGE plpgsql;
SELECT 'select check_partition_table_index('''||child.relname||''','''||parent.relname||''');'
FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE child.relname like '%201812'
order by parent.relname;
pg数据库sql awr
select pg_stat_statements_reset();
--cpu排序
select *, total_time/calls as per_time, round(total_time*100/sum_time) as ratio from (
select query, calls, total_time, (select sum(total_time) from public.pg_stat_statements) as sum_time from public.pg_stat_statements order by total_time desc limit 10) as a;
select substr(query,0,100), calls, total_time,sum_time, total_time/calls as per_time, round(total_time*100/sum_time) as ratio from (
select query, calls, total_time, (select sum(total_time) from public.pg_stat_statements) as sum_time from public.pg_stat_statements order by total_time desc limit 10) as a;
--读排序
select *, round(total_time*100/sum_time) as ratio from (
select query, calls, total_time, (select sum(total_time) from public.pg_stat_statements) as sum_time from public.pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5) as a;
--calls
select *,calls, total_time/calls as per_time, round(calls*100/sum_calls) as ratio from (
select query, calls, total_time, (select sum(calls) from public.pg_stat_statements) as sum_calls from public.pg_stat_statements order by calls desc limit 10) as a;
--per_time
select *,calls, total_time/calls as per_time, round(calls*100/sum_calls) as ratio from (
select query, calls, total_time, (select sum(calls) from public.pg_stat_statements) as sum_calls from public.pg_stat_statements where query not like '%__rds_pg_stats__%') as a order by per_time desc limit 10;
2020-09-02添加：
--检测表是否被使用
SELECT MODE,pl.pid,datname,(SELECT relname FROM pg_class c WHERE c.oid=pl.relation)
,client_addr,query,locktype
FROM pg_locks pl LEFT JOIN pg_stat_activity psa
ON pl.pid = psa.pid;
--终止相应的进程或连接，如果此连接正在写数据，终止可能导致操作丢失
SELECT pg_terminate_backend(pid);
