佛山网站推广优化公司,公司变更名称,saas系统开发教程,网络营销试题文章目录 1、导出数据库某张表2、导入某张表到数据库3、查看数据库占用磁盘页数情况4、查看数据库大小5、查看数据表大小6、查看索引大小7、对数据库中表索引按照大小排序8、对数据库中表按照大小排序9、回收空间#xff08;建议先回收指定表#xff09;10、设置主键自增序列… 文章目录 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.reltypeb.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 2)加载配置
select pg_reload_conf(); 确定是否修改 3查看log
tail -f /data10/pgdata/pg_log/ postgresql-*.log 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区别
1vacuum full会lock table vacuum不会。
2vacuum只是将deadtuple的行的空间转换为能够使用的状态额外空间一般不会返回给os除非文件末尾有大量的连续空闲空间。vacuum full会将这些空间碎片后面的数据上移将数据写入一个新的磁盘文件中。
18、只导出某张表的insert语句
su - postgres
pg_dump --tablet_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_processFROM pg_catalog.pg_locks blocked_locksJOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pidJOIN pg_catalog.pg_locks blocking_locksON blocking_locks.locktype blocked_locks.locktypeAND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.databaseAND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relationAND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.pageAND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tupleAND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxidAND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionidAND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classidAND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objidAND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubidAND blocking_locks.pid ! blocked_locks.pidJOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pidWHERE NOT blocked_locks.granted;
