PG常用命令

太阳2周前技术文章22

1、连库相关

#连库
$ psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]
#连库并执行命令
$ psql -h <hostname or ip> -p <端口> -d [数据库名称]  -U <用户名> -c "运行一个命令;"

备注:
    可以将连接命令中的参数在环境变量中指定;
    比如环境变量中配置如下,那么执行psql等同于执行psql -h 192.168.56.11 -p 5432 testdb postgres。

export PGDATABASE=testdb
export PGHOST=192.168.56.11
export PGPORT=5432
export PGUSER=postgres


2、一些查看命令

#查看命令语法的帮助命令
\h
#查看有哪些库
\l
#进入指定数据库
\c $db_name
#查看当前库下的所有pattern(表、视图、索引、序列)信息
\d
#查看当前库下的pattern(表、视图、索引、序列)信息,并输出详细内容
\d +
#查看当前库下某张表的结构定义或某个表的索引信息
\d $table_name/$index_name
#只查看当前库下表的信息
\dt
#只查看当前库下的索引信息
\di
#只查看当前库下的序列信息
\ds
#只查看当前库下的视图信息
\dv
#只查看当前库下的函数信息
\df
#列出当前库下所有shcema
\dn
#列出所有的表空间
\db
#列出所有的用户/角色的高级权限
\du或\dg
#列出表/视图/序列及访问它们的相关权限
\dp或\z
#列出默认权限
\ddp


3、修改库名

1.先关闭该库下的连接会话:
# SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity WHERE datname='t1' AND pid<>pg_backend_pid();
 pg_terminate_backend 
----------------------
 t
(1 row)
说明:
pg_terminate_backend:用来终止与数据库的连接的进程id的函数。
pg_stat_activity:是一个系统表,用于存储服务进程的属性和状态。
pg_backend_pid():是一个系统函数,获取附加到当前会话的服务器进程的ID。

2.再用alter修改库名:
# alter database t1 rename to t2;
ALTER DATABASE


4、复制数据库到相同的实例

# 创建targetdb库并将sourcedb库中的数据复制到targetdb
CREATE DATABASE targetdb WITH TEMPLATE sourcedb;


5、schema相关

#查看库下的schema:
  SELECT * FROM information_schema.schemata;
  或者\dn
#创建schema:
  create schema $schema_name;
#创建schema并指定owner用户
  create schema $schame_name authorization $user_name;
#修改schema名称或属主
  alter schema $old_name rename to $new_name;
  alter schema $schema_name owner to $new_owner;
#查看当前所在的schema:
  show search_path;
#切换schema:
  set search_path to $schema_name;
#删除一个空的schema(其中所有对象已被删除):
  drop schema $schema_name;
#删除schema及其中包含的所有对象:
  drop schema $schema_name cascade;


6、查看活跃会话

#查看活跃会话
select * from pg_stat_activity where state<>'idle' ;

#查看包含在事物内的会话
select * from pg_stat_activity where state like '%idle%transaction%';

#查看耗时1s以上的活跃会话
select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '1 s' order by query_start ;


pg_stat_activity视图各字段含义:

字段

描述

datid

数据库OID。

datname

数据库名称。

procpid

后端进程的进程ID。

说明 只有4.3版本支持procpid字段。

pid

后端进程的进程ID。

说明 只有6.0版本支持pid字段。

sess_id

会话ID。

usesysid

用户OID。

usename

用户名。

current_query

当前正在执行的查询。默认情况下,查询文本最多显示1024个字符,超出部分会被截断,如需显示更多字符,可以通过参数track_activity_query_size配置。

说明 只有4.3版本支持current_query字段。

query

最近查询的文本。如果stateactive,显示当前正在执行的查询。在其他状态下,显示上一个执行的查询。 默认情况下,查询文本最多显示1024个字符,超出部分会被截断,如需显示更多字符,可以通过参数track_activity_query_size配置。

说明 只有6.0版本支持query字段。

waiting

如果当前SQL在锁等待,值为True,否则为False。

query_start

当前活动查询开始执行的时间。如果state

不是active,显示上一个查询的开始时间。

backend_start

当前后端进程的开始时间。

backend_xid

后端进程当前的事务ID。

backend_xmin

后端的xmin范围。

client_addr

客户端的IP地址。如果client_addr为空,表示客户端通过服务器上的Unix套接字连接,或者表示进程是内部进程(例如AUTOVACUUM)。

client_port

客户端和后端通信的TCP端口号。如果使用Unix套接字,值为-1。

client_hostname

客户端主机名,通过client_addr

的反向DNS查找报告。

application_name

客户端应用名。

xact_start

当前事务的启动时间。如果没有活动事务,值为空。如果当前查询是第一个事务,值与query_start

的值相同。

waiting_reason

当前执行等待的原因,可能是等锁或者等待节点间数据的复制。

state

后端的当前状态,取值范围:active,idle,idle in transaction,idle in transaction (aborted),fastpath function call,disabled。

说明 只有6.0版本支持state字段。

state_change

上次state

状态切换的时间。

说明 只有6.0版本支持state_change字段。

rsgid

资源组OID。

rsgname

资源组名称。

rsgqueueduration

对于排队查询,查询排队的总时间。


7、kill会话

【kill会话】
select pg_terminate_backend($pid);

【只取消当前某一个进程的查询操作,但不能释放数据库连接】
SELECT pg_cancel_backend($pid);


8、查看库表大小

1.查看各库大小:
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database; 

2.查看当前库下各schema表数量
select schemaname,count(*) from pg_stat_user_tables group by schemaname;

3.查看当前库下top 20表或去掉limit 20查看所有表大小
select relname,schemaname, pg_size_pretty(pg_total_relation_size(relid)) from pg_stat_user_tables order by pg_total_relation_size(relid) desc limit 20;

4.查看某张表总大小(表数据+索引数据):
select pg_size_pretty(pg_total_relation_size('xxx')); 

5.查看表数据大小,不包含索引:
select pg_size_pretty(pg_table_size('xxx')); 

6.查看表的索引大小:
select pg_size_pretty(pg_indexes_size('xxx'));


9、表字段变更

1.增加字段:alter table tbl_name add column col_name [col definer] ;
2.删除字段:alter table tbl_name drop column col_name ;
3.增加约束:alter table tbl_name add [constraint];
  eg:alter table tbl_name alter column col_name set not null; (非空约束)
4.删除约束:alter table tbl_name drop constraint_name; (约束名\d+ tbl_name查看)        
5.修改字段数据类型:alter table tbl_name alter column col_name [col definer];
  eg:alter table tai alter column name type varchar(500);
6.重命名字段名称:alter table tbl_name rename column col_name to col_name_new;


10、pg_ctl

1.初始化数据库实例
pg_ctl init[db] [-s] [-D datadir] [-o options]

2.启动、关闭数据库实例等
pg_ctl start [-w] [-s] [-D datadir] [-l filename] [-o options] [-p path]
pg_ctl stop [-W] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ]
pg_ctl restart [-w] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] [-o options]
pg_ctl status [-D datadir]

3.重新加载配置文件(pg_hba.conf、postgresql.conf等)
pg_ctl reload [-s] [-D datadir]
pg_ctl后缀参数含义,详见官方文档:http://postgres.cn/docs/13/app-pg-ctl.html


11、查看一些信息

1.查看当前数据库版本信息
select version();
2.查看数据库的启动时间
select pg_postmaster_start_time();
3.查看最后load配置文件的时间
select pg_conf_load_time();
备注:使用$pg_ctl reload会改变配置的装载时间
4.显示当前数据库时区
show timezone;
5.查看当前用户名
select user;或elect current_user;
6.查看session用户
select session_user;
备注:session_user查看的是连接数据库的原始用户,如果中途用set role改变用户角色,用session_user查看的还是原始用户,用user查看的是改变后的用户
7.查看当前连接的数据库名称
select current_catalog;或select current_database();
8.查看当前session所在客户端的IP及端口
select inet_client_addr(),inet_client_port();
9.查看当前数据库服务器的IP地址及端口
select inet_server_addr(),inet_server_port();
10.查看当前session的后台服务进程的PID
select pg_backend_pid();
11.查看当前参数数值
show xxx;或select current_setting('xxx');
12.修改当前session的参数配置
set xxx to 'xxx';或select set_config('xxx','xxx',false);
13.查看当前正在写的WAL文件
select pg_xlogfile_nale(pg_current_xlog_location());
14.查看当前WAL文件的buffer还有多少字节没有写入磁盘
select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());
15.查看数据库实例是否正在做基础备份
select pg_is_in_backup(),pg_backup_start_time();
16.查看当前数据库实例处于Hot Standby状态还是正常数据库状态
select pg_is_in_recovery();
备注:如果结果为真,则为Hot Standby状态
17.查看表对应的数据文件
select pg_relation_filepath('xxx');


相关文章

企业级大数据安全架构

企业级大数据安全架构

前言1.企业级大数据平台安全隐患目前企业级大数据平台面临的一些安全隐患,只要将这些安全隐患全部解决之后才可以部署到生产环境去使用,因此安全性是大数据平台必备的能力之一。1.1缺乏统一的访问控制机制大数...

kubernetes dashboard

kubernetes dashboard

1、背景Dashboard 是基于网页的 Kubernetes 用户界面。 你可以使用 Dashboard 将容器应用部署到 Kubernetes 集群中,也可以对容器应用排错,还能管理集群资源。你可...

Hive优化之配置参数的优化(一)

Hive优化之配置参数的优化(一)

 Hive是大数据领域常用的组件之一,主要是大数据离线数仓的运算,关于Hive的性能调优在日常工作和面试中是经常涉及的一个点,因此掌握一些Hive调优是必不可少的一项技能。影响Hive效率的...

大数据集群部署规划(一)关于节点的定义

大数据集群部署规划(一)关于节点的定义

节点定义:CDH集群节点定义CDH 官方文档有一份更详细的节点和角色配置,官网将主机分成以下四种类型: 1) Master hosts run Hadoop master processes such...

大数据集群部署规划(二)硬件配置要求

CPU(虚拟机建议配置*2)X86服务器:最低配置:双路4核Intel处理器。推荐配置:双路8核Intel处理器。ARM服务器:双路32核ARM处理器Bit-mode64位内存(虚拟机不建议超分)物理...

CDP实操--集群扩容

CDP实操--集群扩容

一、前提准备工作1.确保OS的yum源可以正常使用,通过yum repolist命令可以查看到匹配的OS的所有包2.确保Cloudera Manager的yum源运行正常3.hosts文件配置,需要将...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。