【Postgres】Postgres常用命令

蔚落 2024-02-20 10:42 57阅读 0赞

#### 文章目录 ####

*  1、导出数据库某张表
 *  2、导入某张表到数据库
 *  3、查看数据库占用磁盘页数情况
 *  4、查看数据库大小
 *  5、查看数据表大小
 *  6、查看索引大小
 *  7、对数据库中表索引按照大小排序
 *  8、对数据库中表按照大小排序
 *  9、回收空间（建议先回收指定表）
 *  10、设置主键自增序列最大值（解决insert时主键冲突）
 *  11、创建索引
 *  12、postgresql开启慢查询日志
 *  13、查看当前正在执行的sql任务
 *  14、查询pg当前连接数
 *  15、查询pg最大连接数
 *  16、导出导入数据库
 *  17、vacuum和vacuum full区别
 *  18、只导出某张表的insert语句
 *  19查询阻塞进程SQL语句

## 1、导出数据库某张表 ##

pg\_dump -t \[表名\] -f \[导出文件名.sql\] \[数据库\]  
只导出insert语句：pg\_dump --table=\[表名\] --data-only --column-inserts \[数据库\] > \[导出文件.sql\]

## 2、导入某张表到数据库 ##

psql -d \[数据库\] -f \[导入文件名.sql\]

## 3、查看数据库占用磁盘页数情况 ##

select a.relpages, a.reltuples, a.relfilenode,a.reltype,b.typname from pg\_class a, pg\_type b where a.relname like ‘\[数据表\]’ and a.reltype=b.oid;  
–查看数据库表的磁盘文件路径

select pg\_relation\_filepath(‘t\_company\_risk’);

## 4、查看数据库大小 ##

select pg\_size\_pretty(pg\_database\_size(‘\[数据库\]’));

## 5、查看数据表大小 ##

select pg\_size\_pretty(pg\_table\_size(‘t\_company\_risk’));

## 6、查看索引大小 ##

select pg\_size\_pretty(pg\_relation\_size(‘t\_company\_risk\_pkey’));  
select \* from pg\_indexes where tablename=‘t\_company\_risk’; – 查看表的索引

## 7、对数据库中表索引按照大小排序 ##

select indexrelname, pg\_size\_pretty(pg\_relation\_size(indexrelid)) from pg\_stat\_user\_indexes where schemaname=‘public’ order by pg\_relation\_size(relid) desc;

## 8、对数据库中表按照大小排序 ##

select relname, pg\_size\_pretty(pg\_relation\_size(relid)) from pg\_stat\_user\_tables where schemaname=‘public’ order by pg\_relation\_size(relid) desc;

## 9、回收空间（建议先回收指定表） ##

vacuum full t\_company\_risk; – 回收指定表

## 10、设置主键自增序列最大值（解决insert时主键冲突） ##

select setval(‘t\_report\_component\_id\_seq’,(select max(id) from t\_report\_component));

## 11、创建索引 ##

CREATE INDEX ix\_t\_company\_risk\_company\_id on t\_company\_risk(company\_id);  
CREATE UNIQUE INDEX t\_company\_risk\_pkey ON t\_company\_risk USING btree (insert\_key);–主键索引

## 12、postgresql开启慢查询日志 ##

1）修改配置文件postgres.conf  
vim /data10/pgdata/postgresql.conf  
![在这里插入图片描述][96e60652501140dd8f2e519c2548693d.png]  
2)加载配置

select pg\_reload\_conf();  
![在这里插入图片描述][bd77bffaffbd4b83b19b5a813d18acf2.png]  
确定是否修改  
![加粗样式][49b7a125a1824e95a455a4c737660ec2.png]  
3）查看log

tail -f /data10/pgdata/pg\_log/ postgresql-\*.log

![在这里插入图片描述][3785ba02e49548dbbb79fbf04c5d632b.png]

## 13、查看当前正在执行的sql任务 ##

select \* from pg\_stat\_activity;

select pid,application\_name,client\_addr,query\_start,waiting,state,query from pg\_stat\_activity;

## 14、查询pg当前连接数 ##

select count(1) from pg\_stat\_activity;

## 15、查询pg最大连接数 ##

show max\_connections;

## 16、导出导入数据库 ##

su - postgres

\#导出数据库

pg\_dump \[数据库名\]> \[导出文件名.sql\]

\#创建数据库  
psql

create database \[新数据库名\];

\#导入数据库

\\q  
psql -d \[新数据库名\] -f \[导出文件名.sql\];

## 17、vacuum和vacuum full区别 ##

1）vacuum full会lock table ，vacuum不会。

2）vacuum只是将deadtuple的行的空间转换为能够使用的状态，额外空间一般不会返回给os（除非文件末尾有大量的连续空闲空间）。vacuum full会将这些空间碎片后面的数据上移，将数据写入一个新的磁盘文件中。

## 18、只导出某张表的insert语句 ##

su - postgres

pg\_dump --table=t\_cascade\_lower\_info --data-only --column-inserts situation > t\_cascade\_lower\_info20200818.sql

## 19查询阻塞进程SQL语句 ##

SELECT blocked_locks.pid     AS blocked_pid,
             blocked_activity.usename  AS blocked_user,
             blocking_locks.pid     AS blocking_pid,
             blocking_activity.usename AS blocking_user,
             blocked_activity.query    AS blocked_statement,
             blocking_activity.query   AS current_statement_in_blocking_process
       FROM  pg_catalog.pg_locks         blocked_locks
        JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
        JOIN pg_catalog.pg_locks         blocking_locks
            ON blocking_locks.locktype = blocked_locks.locktype
            AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
            AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
            AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
            AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
            AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
            AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
            AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
            AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
            AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
            AND blocking_locks.pid != blocked_locks.pid
     
        JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
       WHERE NOT blocked_locks.granted;

[96e60652501140dd8f2e519c2548693d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/e98932519f334659ac9dff861364a25d.png
[bd77bffaffbd4b83b19b5a813d18acf2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/c86e861dabae49c68a64c87f8ec70a1e.png
[49b7a125a1824e95a455a4c737660ec2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/c8f4498a6462443490b62d05601fe312.png
[3785ba02e49548dbbb79fbf04c5d632b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/2a8f28951c0148bd98a6818f3767267b.png