介绍PostgreSQL psql的几个魔法小技巧: 设置，预设，Echo和保存查询

1.前言

由于我一直在使用Postgres psql 命令行客户端，我从我的Crunchy Data同事那里学到了一些好习惯，这些习惯使我的终端数据库环境更容易使用。我想分享一些我发现的最喜欢的东西，这些东西可以让我更好地使用PostgreSQL。如果你刚刚开始使用psql，或者还没有脱离默认设置，那么这篇文章就是为你准备的。我将引导您完成一些最友好的psql设置，以及如何创建自己的预设设置文件。

2.一些最常用的psql命令行

2.1格式化psql输出

Postgres有一个扩展的显示模式，它将把查询结果作为一批列和数据来读取，而不是一个向右扩展显示的巨大的列列表。

扩展后的显示示例如下:

-[ RECORD 1 ]------------------------------
id         | 1
name       | Alice Johnson
position   | Manager
department | Sales
salary     | 75000.00
-[ RECORD 2 ]------------------------------
id         | 2
name       | Bob Smith
position   | Developer
department | Engineering
salary     | 65000.00

--Automatically format expanded display for wide columns
\x auto

如果您刚刚开始并想尝试这些命令，我有一个关于使用基本psql的教程^[1] 参考： https://www.crunchydata.com/developers/playground/psql-basics

2.2psql输出的列边界

如果不使用扩展显示，可以让psql使用\pset linestyle做一些花哨的列轮廓。

--Outline table borders and separators using Unicode characters
\pset linestyle unicode

这将使您的查询输出看起来像这样:

┌────┬───────┬─────┐
│ id │ name  │ age │
├────┼───────┼─────┤
│  1 │ Alice │  30 │
│  2 │ Bob   │  25 │
└────┴───────┴─────┘

看着有点像mysql中输出的样子。

2.3让psql输出带有时间开销值

这将为您提供以毫秒为单位的查询在底部运行时间的结果:

-- Always show query time
\timing

2.4在psql中为空值创建一个预设置

这将适用于表情符号或任何兼容utf-8的东西:

-- Set Null char output to differentiate it from empty string
\pset null '☘️'

2.5您的psql命令历史

你可以像这样为你的psql命令会话创建一个历史文件:

-- Creates a history file for each database in your config directory CHECK IF THIS IS RIGHT
\set HISTFILE ~/.config/psql/psql_history-:DBNAME

-- Number of commands to save in history
\set HISTSIZE 2000

2.6将PSQL元命令还原为它使用的SQL语句

任何psql斜杠命令(如\d)都运行在Postgres的系统表上。您可以使用psql echo命令显示用于给定命令的查询，这可以让您了解Postgres的内部表、目录和其他命名约定。

-- output any SQL run by psql slash commands
\set ECHO_HIDDEN on
-- short name of ECHO_HIDDEN on
-E

现在让ECHO显示一些东西。使用以下命令查找表:

\dt+

现在，您将看到它向您返回用于获取该数据的查询，并在底部加上\dt+的正常结果。

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  CASE c.relpersistence WHEN 'p' THEN 'permanent' WHEN 't' THEN 'temporary' WHEN 'u' THEN 'unlogged' END as "Persistence",
  am.amname as "Access method",
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

**************************

                                    List of relations
 Schema |  Name   | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+---------+-------+----------+-------------+---------------+--------+-------------
 public | weather | table | postgres | permanent   | heap          | 856 kB |
(1 row)

2.7Echo所有的psql查询

你也可以让psql回显它运行的所有查询:

-- Have psql echo back queries
\set ECHO queries
-- Short name of echo queries
-e

如果您正在从文件中运行查询，或者它们是psqlrc中的预设，并且希望第二次输出查询以保存记录，则这可能很有用。

如果你想深入了解其中任何一个，我有一个基于网络的ECHO隐藏和ECHO查询教程。

2.8使用.psqlrc设置缺省的psql环境

我列出的上述所有事情都可以设置为在每次使用本地psql时自动发生。当psql启动时，它查找.psqlrc文件，如果存在，它将执行其中的命令。这允许您自定义提示和其他psql设置。

你可以看看你是否有一个。psqlrc文件:

ls -l ~/.psqlrc

如果您想尝试添加一个:

touch ~/.psqlrc

或者打开一个进行编辑：

open -e ~/.psqlrc

如果你想在启动psql时跳过命令的日志记录，你可以将这些添加到文件的开头和结尾:

-- Don't log these commands at the beginning of the file
\set QUIET 1

-- Reset command logging at the end of the file
\set QUIET 0

2.9定制您的行提示

psql的默认提示符显示您的数据库名称，除此之外没有其他内容。在psqlrc文件中，您可以更改psql提示行，以使用关于数据库主机和会话的不同信息组合。我个人喜欢在这里使用日期和时间，因为我保存会话以供以后参考。

-- Create a prompt with host, database name, date, and time
\set PROMPT1 '%m@%/ %`date "+%Y-%m-%d %H:%M:%S"` '

这样设置以后得到的提示结果如下所示：

[local]@crunchy-dev-db 2024-07-19 15:06:37

2.10将查询保存到psqlrc文件中

这个.psqlrc文件看起来很酷，对吧?但是等等，还有更多!您可以向该文件添加查询，这样您就可以使用一个超级简单的psql输入来运行它们。

将这些示例查询添加到psqlrc中，以获得长时间运行的查询、缓存命中率、未使用的索引和表大小。

\set long_running 'SELECT pid, now() - pg_stat_activity.xact_start AS duration, query, state FROM 
pg_stat_activity WHERE (now() - pg_stat_activity.xact_start) > interval ''5 minutes'' ORDER by 2 DESC;'

\set cache_hit 'SELECT ''index hit rate'' AS name, (sum(idx_blks_hit))  nullif(sum(idx_blks_hit + 
idx_blks_read),0) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT ''table hit rate'' AS name, 
sum(heap_blks_hit)  nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio FROM pg_statio_user_tables;'

\set unused_indexes 'SELECT schemaname || ''.'' || relname AS table, indexrelname AS index, 
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM 
pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan <
50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC 
NULLS FIRST, pg_relation_size(i.indexrelid) DESC;'

\set table_sizes 'SELECT c.relname AS name, pg_size_pretty(pg_table_size(c.oid)) AS size FROM pg_class c LEFT
JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (''pg_catalog'', 
''information_schema'') AND n.nspname !~ ''^pg_toast'' AND c.relkind=''r'' ORDER BY pg_table_size(c.oid)
DESC;'`,

然后在psql内部执行，使用冒号和查询的名称来运行它，例如:long_running。如果你正在使用我们的托管Postgres, Crunchy Bridge，我们用我们的CLI洞察力为你构建了一堆这样的东西。

2.11在psql环境中进行实验

我希望这些东西能给您一些关于实验psql环境的想法。这很简单也很有趣!我的成功秘诀:

优先处理那些每天需要花时间的事情。
您是否每周在数据库上运行一次查询?把它放到你的psqlrc文件中，这样下次就能看到了。

如果您远程连接到数据库，请不要发疯。如果不使用到数据库的本地连接和直接远程连接，则不要创建许多特殊工具，因为使用不同的环境可能会很痛苦。

查看我们关于基本psql和ECHO HIDDEN以及ECHO查询的教程，可以在web浏览器中进行实验。
（see: https://www.crunchydata.com/developers/playground/psql-basics)

在我们的Postgres技巧页面中，我们有很多其他方便的psql技巧^[2]。

3.参考原文:

[1]

使用基本psql的教程: https://www.crunchydata.com/developers/playground/psql-basics

往期导读: 
1. PostgreSQL中配置单双向SSL连接详解
2. 提升PSQL使用技巧：PostgreSQL中PSQL使用技巧汇集(1)
3. 提升PSQL使用技巧：PostgreSQL中PSQL使用技巧汇集(2)
4. PostgreSQL SQL的基础使用及技巧
5. PostgreSQL开发技术基础:过程与函数
6. PostgreSQL中vacuum 物理文件truncate发生的条件
7. PostgreSQL中表的年龄与Vacuum的实验探索：Vacuum有大用
8. PostgreSQL利用分区表来弥补AutoVacuum的不足
9. 也聊聊PostgreSQL中的空间膨胀与AutoVacuum
10. 正确理解SAP BTP中hyperscaler PG中的IOPS (AWS篇)