1. 首页
  2. IT资讯

postgresql数据库常用命令

–获取数据库软件版本
select version();
–获取数据库启动时间
select pg_postmaster_start_time();
–获取配置文件最近load时间
select pg_conf_load_time();
–获取当前数据库时区
show timezone;
–获取当前实例中所有数据库
psql -l
–获取当前数据库用户
select user;
select current_user;
–获取当前会话用户
select session_user;
–获取会话客户端地址及端口
select inet_client_addr(),inet_client_port();
–获取当前数据库服务器地址及端口
select inet_server_addr(),inet_server_port();
–获取当前会话服务进程
select pg_backend_pid();
–获取当前参数配置
show shared_buffer;
select current_setting(‘shared_buffers’);
–修改会话参数配置
set maintenance_work_mem to ’32m’;
select set_confit(‘maintenance_work_mem’,’32m’,false);
–获取当前WAL文件
select pg_xlogfile_name(pg_current_xlog_location());
–获取没写到磁盘的WAL buffer
select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());
–获取实例中当前进行的备份
select pg_is_in_backup(),pg_backup_start_time();
–获取当前实例的角色状态(primary or standby)
select pg_is_in_recovery();
–获取数据库大小
select pg_database_size(‘mydb’),pg_size_pretty(pg_database_size(‘mydb’));
–获取表大小
–表
select pg_size_pretty(pg_relation_size(‘mytab’));
–表及索引
select pg_size_pretty(pg_total_relation_size(‘mytab’));
–获取索引大小
select pg_size_pretty(pg_indexes_size(‘mytab’));
–获取表空间大小

select pg_size_pretty(pg_tablespace_size(‘my_tbs’));

–获取表中每个数据块的自由空间

select pg_freespace(‘mytab’);

–获取表中每个数据块中自由空间比例

SELECT (100 * (upper – lower) / pagesize::float8)::integer AS free_pct
FROM page_header(get_raw_page(‘mytab’,11));

–获取表对应的数据文件
select pg_relation_filepath(‘mytab’);
–重新加载配置文件
pg_ctl reload
select pg_reload_conf();
–切换log文件
select pg_rotate_logfile();
–切换xlog文件
select pg_rotate_xlog();
–手工产生检查点
checkpoint;
–取消正在执行的SQL
select pg_cancel_backend(pid);
–终止后台服务进程
select pg_terminate_backend(pid);
–获取正在执行的SQL

select pid,username,query_start,query from pg_stat_activity;

–获取Combo Command Ids

SELECT t_xmin AS xmin,
t_xmax::text::int8 AS xmax,
t_field3::text::int8 AS cmin_cmax,
(t_infomask::integer & X’0020′::integer)::bool AS is_combocid
FROM heap_page_items(get_raw_page(‘mytab’,0))
ORDER BY 2 DESC, 3;

–创建获取某数据块元组原型视图

CREATE VIEW t1_page0 AS
SELECT ‘(0,’|| lp || ‘)’ AS ctid,
CASE lp_flags
WHEN 0 THEN ‘Unused’
WHEN 1 THEN ‘Normal’
WHEN 2 THEN ‘Redirect to ‘|| lp_off
WHEN 3 THEN ‘Dead’
END,
t_xmin::text::int8 AS xmin,
t_xmax::text::int8 AS xmax,
t_ctid
FROM heap_page_items(get_raw_page(‘t1’,0))
ORDER BY lp;

select * from t1_page0;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8484829/viewspace-2120392/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/184818.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code