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)

 

 

添加一些别的,有部分为转载:

查看服务进程: 

select pid,usename,client_addr,client_port from pg_stat_activity;
 
查看当前数据库实例的版本:
 select version(); 
 
查看数据库的启动时间:
select pg_postmaster_start_time();
 
查看最后load配置文件的时间:
select pg_conf_load_time();
 
显示当前数据库时区:
show timezone;
 
postgres=# show timezone;
 TimeZone 
----------
 PRC
(1 row)
 
postgres=# select now();
              now              
-------------------------------
 2015-07-10 16:27:59.692621+08
(1 row)
 
显示实例中有哪些数据库:
postgres=# \l   或者 [postgre@pg-1 ~]$ psql -l
 
查看当前用户名:
select user;
或者
select current_user;
 
查看session用户:
 select session_user;
 
查询当前连接的数据库名称:
postgres=# select current_catalog,current_database();
 current_database | current_database 
------------------+------------------
 postgres         | postgres
(1 row)
 
查询当前session所在客户端的IP地址及端口:
 select inet_client_addr(),inet_client_port();
 
查询到当前数据库服务器的IP地址及端口:
 select inet_server_addr(),inet_server_port();
 
查询当前session的后台服务进程的pid:
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          12504
(1 row)
通过操作系统命令看下此后台进程:
[root@pg-1 postgresql-9.4.4]# ps -ef |grep  12504 |grep -v grep
postgre  12504  1590  0 16:34 ?        00:00:00 postgres: postgre postgres [local] idle
 
查看当前正在写的WAL文件:
postgres=# select pg_xlogfile_name(pg_current_xlog_location());
     pg_xlogfile_name     
--------------------------
 000000010000000000000001
(1 row)
 
查看当前WAL的buffer中还有多少字节的数据没有写到磁盘中:
postgres=# select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());
 pg_xlog_location_diff 
-----------------------
                     0
(1 row)
 
查看数据库实例是否在做基础备份:
postgres=# select pg_is_in_backup(),pg_backup_start_time();
 pg_is_in_backup | pg_backup_start_time 
-----------------+----------------------
 f               | 
(1 row)
 
查看当前数据库实例是Hot Standby状态还是正常数据库状态:
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)
 
查看数据库大小:
postgres=# select pg_database_size('pgtest'),pg_size_pretty(pg_database_size('pgtest'));
 pg_database_size | pg_size_pretty 
------------------+----------------
          6998688 | 6835 kB
(1 row)
注:如果数据库中有很多表,使用上述命令将比较慢,也可能对当前系统产生不利的影响。pg_size_pretty()函数会把数字以MB、GB等格式显示出来,这样会更直观。
 
查看表的大小:
pgtest=# select pg_size_pretty(pg_relation_size('test01'));
 pg_size_pretty 
----------------
 8192 bytes
(1 row)
注:仅计算表的大小,不包括索引的大小;
 
pgtest=# select pg_size_pretty(pg_total_relation_size('test01'));
 pg_size_pretty 
----------------
 24 kB
(1 row)
 
注:包括索引大小
 
查看表上所有索引的大小:
pgtest=# select pg_size_pretty(pg_indexes_size('test01'));
 pg_size_pretty 
----------------
 16 kB
(1 row)
 
查看表空间的大小:
pgtest=# select pg_size_pretty(pg_tablespace_size('pg_global'));
 pg_size_pretty 
----------------
 437 kB
(1 row)
 
查看表对应的数据文件:
pgtest=# select pg_relation_filepath('test01');
 pg_relation_filepath 
----------------------
 base/16403/16410
(1 row)
 
修改postgresql.conf后,让修改生效的方法有两种:
1)在操作系统下使用:pg_ctl   reload
2) 在psql中使用:select pg_reload_conf();
pgtest=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
 
pgtest=# select pg_conf_load_time();
       pg_conf_load_time       
-------------------------------
 2015-07-10 17:44:45.620045+08
(1 row)
 
注:如果需要重启数据库服务的配置项,修改后使用上面的方法并不能使之生效。
 
切换log日志到下一个的命令:
postgres=# select pg_rotate_logfile();
 pg_rotate_logfile 
-------------------
 t
(1 row)
 
切换WAL日志文件:
postgres=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/17AF678 
(1 row)
 
收工生成一次checkpoint:
postgres=# checkpoint;
CHECKPOINT
 
 
取消一个正在长时间执行的SQL方法:
有两个函数可以完成这个功能:
1)pg_cancel_backend(pid):取消一个正在执行的SQL;
2)pg_terminate_backend(pid):终止一个后台服务进程,同时释放此后台服务进程的资源。
这两个函数的区别是:pg_cancel_backend()函数实际上是给正在执行的SQL任务配置一个取消标志,正在执行的任务是在合适的时候检测到此标志后主动退出;但如果这个任务没有主动检测到这个标志,则该任务就无法正常退出,这时需要使用pg_terminate_backend()命令在终止SQL的执行。
通常是先查询pg_stat_activity,试图找出长时间运行的SQL:
postgres=# select pid,usename,query_start,query from pg_stat_activity ;
 pid  | usename |          query_start          |                            query                             
------+---------+-------------------------------+--------------------------------------------------------------
 1651 | postgre | 2015-07-11 15:35:39.745907+08 | select pid,usename,query_start,query from pg_stat_activity ;
 1722 | postgre | 2015-07-11 15:35:17.114117+08 | select pg_sleep(100);
(2 rows)
 
然后在使用pg_cancel_backend()取消这个SQL,如果pg_cancel_backend()取消不了,再使用pg_terminate_backend():
 
  postgres=# select pid,usename,query_start,query from pg_stat_activity ;
 pid  | usename |          query_start          |                            query                             
------+---------+-------------------------------+--------------------------------------------------------------
 1651 | postgre | 2015-07-11 15:40:11.085173+08 | select pid,usename,query_start,query from pg_stat_activity ;
 1747 | postgre | 2015-07-11 15:40:04.277972+08 | select pg_sleep(100);
(2 rows)
 
postgres=# select pg_cancel_backend(1747);
 pg_cancel_backend 
-------------------
 t
(1 row)
 
postgres=# select pid,usename,query_start,query from pg_stat_activity ;
 pid  | usename |          query_start          |                            query                             
------+---------+-------------------------------+--------------------------------------------------------------
 1651 | postgre | 2015-07-11 15:40:26.885772+08 | select pid,usename,query_start,query from pg_stat_activity ;
 1747 | postgre | 2015-07-11 15:40:04.277972+08 | select pg_sleep(100);
(2 rows)
 
 
 
 postgres=# select pg_terminate_backend(1747);
 pg_terminate_backend 
----------------------
 t
(1 row)
 
postgres=# select pid,usename,query_start,query from pg_stat_activity ;
 pid  | usename |          query_start          |                            query                             
------+---------+-------------------------------+--------------------------------------------------------------
 1651 | postgre | 2015-07-11 15:41:09.413959+08 | select pid,usename,query_start,query from pg_stat_activity ;
(1 row)
 
 
 
 
[postgre@pg-1 pg_log]$ pg_basebackup   -D backup     -Ft -z -P  
pg_basebackup: could not connect to server: FATAL:  number of requested standby connections exceeds max_wal_senders (currently 0)
由于没有设置max_wal_senders参数,修改postgresql.conf
max_wal_senders = 2
wal_level = hot_standby 

 

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);

posted @ 2017-05-03 16:32  狂神314  阅读(4221)  评论(0编辑  收藏  举报