从引擎表运行 SQL 查询以获取信息

以下是一些示例查询：

1. 检索 PostgreSQL 引擎数据库中的所有用户：

engine=# select user_id, name, username from users;

2. 获取所有网络：

engine=# select id,name,description,storage_pool_id,vdsm_name from network;
                   id                 | name      | description        |           storage_pool_id            | vdsm_name
--------------------------------------+-----------+--------------------+--------------------------------------+----------
              <network id>            | ovirtmgmt | Management Network |          <storage_pool_id>           | ovirtmgmt
              <network id>            | vm_pub    | vm network         |          <storage_pool_id>           | vm_pub

3. 获取KVM主机信息：

engine=# select vds_name, vds_unique_id, port,cluster_id ,_create_date from vds_static;
vds_name                  |          vds_unique_id               | port  |            cluster_id                | _create_date
--------------------------+--------------------------------------+-------+--------------------------------------+------------------------------
xxx.xxx.xxx.xxx           |         <KVM Host UUID>              | 54321 |           <Cluster ID>               | <KVM Creation Date>
(1 row)

4. 检查允许的最大客户端连接数：

engine=# select setting::boirnt from pg_settings where name='max_connections';
setting
--------
150
(1 row)

5. 在写入临时磁盘文件之前检查内部排序操作和哈希表要使用的内存量：

engine=# select name, setting, unit, source from pg_settings where name = 'work_mem';
name | setting | unit | source
----------+---------+------+-------------------
work_mem | 8192 | kB | configuration file
(1 row)

6. 要检查共享缓冲区中有多少内存专用于 PostgreSQL 用于缓存数据：

engine=# select setting::boirnt from pg_settings where name='shared_buffers';
setting
--------
16384
(1 row)

7. 要检查估计的最大 内存 使用量：

engine=# select pg_size_pretty(shared_buffers::boirnt*block_size + max_connections*work_mem*1024 + autovacuum_max_workers*(case when autovacuum_work_mem=-1 then maintenance_work_mem else autovacuum_work_mem end)*1024) as estimated_max_ram_usage from ( select (select setting::boirnt from pg_settings where name='block_size') as block_size, (select setting::boirnt from pg_settings where name='shared_buffers') as shared_buffers, (select setting::boirnt from pg_settings where name='max_connections') as max_connections,(select setting::boirnt from pg_settings where name='work_mem') as work_mem, (select setting::boirnt from pg_settings where name='autovacuum_max_workers') as autovacuum_max_workers,(select setting::boirnt from pg_settings where name='autovacuum_work_mem') as autovacuum_work_mem,(select setting::boirnt from pg_settings where name='maintenance_work_mem') as maintenance_work_mem)as _ ;
estimated_max_ram_usage
------------------------
1712 MB
(1 row)

这篇文章将解释一些实用的 psql 命令，并描述如何从命令行运行 PostgreSQL 查询以从引擎数据库中获取有用的信息。

一些实用且有用的 psql 命令

1. 获取有关 psql 命令的帮助。

要了解所有可用的 psql 命令，请使用 ?命令获得帮助。

engine=# \?

2. 列出所有带有'\l' 的数据库。

RHV 有两个相关的数据库：engine 和 ovirt-engine-history。
RHV 创建了一个名为 engine 的 PostgreSQL 数据库。
在安装 ovirt-engine-dwh 包时，会创建一个名为 ovirt-engine-history 的第二个数据库，其中包含历史配置信息和每分钟从引擎操作数据库中收集的统计指标。
可以看到下面的两个数据库信息：

engine=# \l
List of databases
Name                    | Owner                | Encoding | Collate | Ctype | Access privileges
----------------------+------------------------+----------+-------------+-------------+----------------------
engine                  | engine               | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
ovirt_engine_history    | ovirt_engine_history | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres                | postgres             | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
templates               | postgres             | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +    | | | | | postgres=CTc/postgres
template1               | postgres             | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +       | | | | | postgres=CTc/postgres
(5 rows)

使用 '\l+' 获取详细信息：

engine=# \l+
List of databases
Name                  | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace |  Description
----------------------+----------------------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------------------------
engine                | engine | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 28 MB | pg_default |
ovirt_engine_history  | ovirt_engine_history | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 48 MB | pg_default |
postgres              | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7288 kB | pg_default | default administrative connection database
templates             | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7153 kB | pg_default | unmodifiable empty database  | | | | | postgres=CTc/postgres | | |
template1             | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7288 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | | |
(5 rows)

3. 查看当前数据库连接信息。

engine=# \conninfo
You are connected to database "engine" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

4. 列出可用模式：

engine=# \dn+
                   List of schemas
Name      | Owner    | Access privileges    | Description
----------+----------+----------------------+-----------------------
aaa_jdbc  | engine   |                      |
public    | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres         |
(2 rows)

5. 列出所有带有 '\z' 的表：

engine=# \z
Access privileges
Schema  | Name                                             | Type | Access privileges | Column privileges | Policies
--------+--------------------------------------------------+----------+-------------------+-------------------+---------
public  | active_migration_network_interfaces              | view | | |
public  | ad_groups                                        | table| | |
...
public  | all_disks                                        | view | | |
public  | all_disks_for_vms                                | view | | |
public  | all_disks_including_snapshots                    | view | | |
public  | all_disks_including_snapshots_and_memory         | view | | |
public  | audit_log                                        | table | | |
public  | cluster                                          | table | | |

6. 显示当前引擎数据库中的表：

engine-# \dt
List of relations
Schema  | Name                                | Type  | Owner
--------+-------------------------------------+-------+-------
public  | ad_groups                           | table | engine
public  | cluster                             | table | engine
public  | cluster_features                    | table | engine
public  | cluster_policies                    | table | engine
public  | cluster_policy_units                | table | engine
...
public  | vds_static                          | table | engine
public  | vfs_config_networks                 | table | engine
public  | vm_device                           | table | engine

7. 描述一张表：

engine-# \d table_name

例如：

engine-# \d vds_static
                                Table "public.vds_static"
Column                         | Type                     | Modifiers
-------------------------------+--------------------------+---------------------------------------------------
vds_id                         | uuid                     | not null
vds_name                       | character varying(255)   | not null
vds_unique_id                  | character varying(128)   |
host_name                      | character varying(255)   | not null
...
kernel_cmdline                 | text                     |
last_stored_kernel_cmdline     | text                     |
reinstall_required             | boolean                  | not null default false

其他

1. 使用 select pg_reload_config() 重新加载配置文件：

engine=# select pg_reload_conf();
pg_reload_conf
---------------
t
(1 row)

2. 显示命令历史，你使用\s 命令。

engine=# \s

3. 要退出 psql，请使用 \q 命令并按 Enter 退出 psql。

engine=# \q

从命令行连接到“Postgres”数据库

要连接到“Postgres”数据库：

# su - postgres
Last login: Thu Apr 30 20:25:36 AEST 2020 on pts/1

由于 postgres 来自软件集合，因此我们必须启用 postgresql 才能连接引擎数据库：

对于 4.2.8：

-bash-4.2$ scl enable rh-postgresql95 "psql -d engine -U postgres"
psql (9.5.14)
Type "help" for help.
engine=#

对于 4.3.6：

-bash-4.2$ scl enable rh-postgresql10 "psql -d engine -U postgres"
psql (10.6)
Type "help" for help.