postgres应用实战（一）：psql功能与应用

postgres应用实战（一）：psql功能与应用

-使用psql连接数据库和基本选项介绍
- psql元命令介绍
- psql执行sql脚本
- *使用psql定制日常维护脚本

---

**

一.使用psql连接数据库和基本选项介绍

**
1.连接postgres 数据库 命令：
psql -h 192.168.6.219 -p 5432 postgres postgres

-h 后面接ip地址 端口是5432 第三个是数据库名字 第四个是用户名名字（当然了 我这里已经默认创建好了 postgres这个数据库啦）

进去之后可以使用 \q 退出

2.psql的一些基础语法使用
A. -A 设置非对齐输出模式
例如: psql -h 192.168.6.219 -p 5432 postgres postgres -A -c “select * from test_1 limit 3” （-c 执行sql语句）

B. -t 只显示记录数据
我们可以将-t参数和-A 参数结合一起使用，就可以返回数据的本身，例如：
psql -h 192.168.6.219 -p 5432 postgres postgres -At -c “select name from test_1 limit 3”

这样的结果仅仅返回数据的本身，在编写shell脚本时候非常管用，特别是支取一个字段的时候

C. -q 不显示任何输出信息
例如我现在有一个脚本 test_q.sql
drop table if exists test_q;
create table if not exists test_q(id int4);
truncate table test_q;
insert into test_q values(1);
insert into test_q values(2);

然后我执行命令：psql -h 192.168.6.219 -p 5432 postgres postgres -f test_q.sql 就会在终端（terminal）返回大量信息，如果我加上-q 这些信息将不会返回 这种使用，在执行维护操作过程时候，特别管用

**

二.psql元命令介绍

**
1. \l （查看数据库列表）
2. \db （查看表空间列表， 这个貌似要postgres10才可以支持）
3. \d （查看数据表列表）
4. \dt + 后接表名 查看表大小 例如：

5.\di+ 索引名 （查看索引大小） 例如：

6.psql 提供的元命令实质是向数据库发送相应的sql查询， 当时用psql连接数据库时，-E 选项可以获取元命令的sql代码 例如：

---

**

三.psql执行sql脚本

**
1.psql的-c选项支持在终端通过psql向数据库发起sql命令，-c 后面可以使用单引号也可以使用双引号，同事支持格式化输出，如果想仅仅显示命令行返回的结果 ，psql可以加上-At选项，例如：

2.-f选项导入sql脚本，例如：psql -h 192.168.6.219 -p 5432 postgres postgres -f test_2.sql
如果上述命令没有报错，则表示脚本运行成功
注意：psql的 -single-transaction 或 -1 选项支持一个事务中执行脚本，要么所有的sql执行成功，如果其中有一句sql执行错误，则所有的sql都要回滚

3.\set 元命令方式传递变量
例如： \set v_id 2
select * from test_1 where id=:v_id;

如果想取消之前的变量设置的值， \set 后面接参数名字即可
\set v_id
这种方式的一个典型应用案例就是使用pgbench进行压力测试时候使用\set 为变量赋值

4.psql 的 -v参数传递变量
首先编写select_1.sql 脚本，内容如下：
select * from test_1 where id=:v_id;

然后在终端执行这条命令：
psql -h 192.168.6.219 -p 5432 postgres postgres -v v_id=3 -f select_1.sql

---

**

四.使用psql定制日常维护脚本

**
pg_stat_activity视图显示postgresql进程信息，每一个进程在视图中存在一条记录，pid指的是进程号，username指的是数据库用户名， datname指的是数据库名称， query显示进城最近执行的sql，如果state=active，则query显示当前正在执行的sql，client_addr是进城客户端的ip
1.定制维护脚本：查询活动会话
在~/.psqlrc文件 写上一行
\set active_session_jqs ‘select pid, usename, datname, query, client_addr from pg_stat_activity where pid <> pg_backend_pid() and state=\’active\’ order by query;’

2.定制维护脚本：查询等待事件
在~/.psqlrc文件 写上一行
\set wait_event ‘select pid, usename, datname, query, client_addr from pg_stat_activity where pid <> pg_backend_pid() and wait_event is not null order by wait_event_type;’

3.定制维护脚本：查看数据库连接数
在~/.psqlrc文件 写上一行
\set connections_jqs ‘select usename, datname, client_addr, count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1,2,3 order by 1,2,4 desc;’