乐趣区

关于数据库:PostgreSQL开发与实战2常用命令

jiezi

作者：太阳

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 最近查问的文本。如果 state 为 active，显示以后正在执行的查问。在其余状态下，显示上一个执行的查问。默认状况下，查问文本最多显示 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');

更多技术信息请查看云掣官网 https://yunche.pro/?t=yrgw

退出移动版