PG常用命令
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。 |
pid | 后端进程的进程ID。 |
sess_id | 会话ID。 |
usesysid | 用户OID。 |
usename | 用户名。 |
current_query | 当前正在执行的查询。默认情况下，查询文本最多显示1024个字符，超出部分会被截断，如需显示更多字符，可以通过参数track_activity_query_size配置。 |
query | 最近查询的文本。如果 |
waiting | 如果当前SQL在锁等待，值为True，否则为False。 |
query_start | 当前活动查询开始执行的时间。如果 不是 |
backend_start | 当前后端进程的开始时间。 |
backend_xid | 后端进程当前的事务ID。 |
backend_xmin | 后端的xmin范围。 |
client_addr | 客户端的IP地址。如果 |
client_port | 客户端和后端通信的TCP端口号。如果使用Unix套接字，值为-1。 |
client_hostname | 客户端主机名，通过 的反向DNS查找报告。 |
application_name | 客户端应用名。 |
xact_start | 当前事务的启动时间。如果没有活动事务，值为空。如果当前查询是第一个事务，值与 的值相同。 |
waiting_reason | 当前执行等待的原因，可能是等锁或者等待节点间数据的复制。 |
state | 后端的当前状态，取值范围：active，idle，idle in transaction，idle in transaction (aborted)，fastpath function call，disabled。 |
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]
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');
