PostgreSQL 命令行工具介绍

前言

psql 是 PostgreSQL 自带的命令行交互客户端工具，类似于 MySQL 的 mysql -u -p 不过相当于 MySQL 的命令行工具 psql 功能更丰富些，例如单击 tab 自动补全。本篇文章介绍 psql 的使用及常用命令

1. 连接数据库

1.1 psql 本地连接

已经安装好 PostgreSQL 直接输入 psql 即可连接到数据库：

[postgres@db4 data]$ psql
psql (12.2)
Type "help" for help.

postgres=# 

为什么不用输入用户名和密码呢？安装 PostgreSQL 时，会创建一个数据库初始化时与操作系统用户同名的 super 账号，在本地 OS 用户下登陆数据库时，因为执行的是操作系统认证，所以不需要用户和密码，这块也可以在 pg_hba.cnf 中配置。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust

1.2 psql 远程连接

使用 psql 连接远程的 PostgreSQL：

[postgres@db4 data]$ psql -h 172.16.104.56 -p 5432 -U abc -W 
Password: 
psql (12.2)
Type "help" for help.

abc=> 

涉及的连接参数：

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "postgres")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

1.3 指定客户端字符集

当客户端字符集与服务器字符集不一致时，就会出现乱码，可使用下方命令切换客户端字符集：

\encoding gbk

2. pg_ctl 管理命令

除了 psql 还有一个 pg_ctl 管理命令也是 DBA 经常要使用的命令，在此也介绍一下用于 初始化、重启、加载配置文件等。

2.1 初始化数据库

只在安装阶段使用的命令，初始化数据库。

pg_ctl init[db] [-D datadir] [-s] [-o initdb-options]

2.2 启动数据库

启动（start）、关闭（stop）、重启（restart） 数据库，如果没有指定参数，将从环境变量 /etc/profile 中读取：

pg_ctl [动作] [-D datadir] [-l filename]

2.3 加载配置文件

修改配置文件后，重新加载配置文件：

pg_ctl reload [-D datadir] [-s]

2.4 查看数据库状态

pg_ctl status [-D datadir]

3. 数据库信息查看

3.1 查看数据库版本

select version();

                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

3.2 查看数据库的启动时间

select pg_postmaster_start_time();

   pg_postmaster_start_time    
-------------------------------
 2023-09-14 14:41:24.890359+08

通过数据库的启动时间，可以判断数据库是否发生过重启中断。

3.3 查看配置文件 load 时间

select pg_conf_load_time();

       pg_conf_load_time       
-------------------------------
 2023-09-14 14:41:24.849112+08

使用 pg_ctl reload 或重启数据库会触发配置文件重新加载，改时间会更新。

3.4 查看当前连接用户

select user;

3.5 查看修改参数

查看参数：

-- 下面两个命令都可以
show [参数名];

select current_setting('参数名');

修改参数：

set [参数名] to '改后值';

3.6 查看数据库大小

 select pg_size_pretty(pg_database_size('mydb'));

3.7 查看表大小

-- 仅查看表大小，不包含索引大小
select pg_size_pretty(pg_relation_size('pgbench_accounts'));

-- 包含索引大小
select pg_size_pretty(pg_total_relation_size('pgbench_accounts'));

4. 常见用法

4.1 执行外部文件

与 MySQL 中的 source 命令作用一样：

\i 文件名.sql

还有一种方法 psql -x -f 文件名.sql

psql -d postgres -x -f 文件名.sql

4.2 编辑模式

进入编辑模式，与 MySQL edit 命令一样，SQL 特别长的场景下可以使用编辑模式：

\e

4.3 列显示模式

与 MySQL \G 一样，不过 psql 执行前需要先打开：

postgres=# \x
Expanded display is on.

postgres=# \d

List of relations
-[ RECORD 1 ]------------
Schema | public
Name   | pgbench_accounts
Type   | table
Owner  | postgres
-[ RECORD 2 ]------------
Schema | public
Name   | pgbench_branches
Type   | table
Owner  | postgres
-[ RECORD 3 ]------------
Schema | public
Name   | pgbench_tellers
Type   | table
Owner  | postgres
-[ RECORD 4 ]------------
Schema | public
Name   | v_locks_monitor
Type   | view
Owner  | postgres