Oracle Linux Virtualization Manager(OLVM) Engine PostgreSQL Database Queries Cheat Sheet
This post will explain some practical psql commands and describe how to run the PostgreSQL queries from the command line to get useful information from the engine database.
Connect To The ‘Postgres’ Database From The Command Line
To connect to the ‘Postgres’ database:
# su - postgres
Last login: Thu Apr 30 20:25:36 AEST 2020 on pts/1
As Postgres comes from software collections, you have to enable postgresql to connect engine database:
For 4.2.8:
-bash-4.2$ scl enable rh-postgresql95 "psql -d engine -U postgres"
psql (9.5.14)
Type "help" for help.
engine=#
For 4.3.6:
-bash-4.2$ scl enable rh-postgresql10 "psql -d engine -U postgres"
psql (10.6)
Type "help" for help.
Some Practical And Useful psql Commands
1. Get help on psql commands - To know all available psql commands, you use the \? command to get help.
engine=# \?
2. List all databases with ‘\l’.
OLVM has two related databases: engine and ovirt-engine-history. OLVM creates a PostgreSQL database called engine. While installing the ovirt-engine-dwh package creates a second database called ovirt-engine-history, which contains historical configuration information and statistical metrics collected every minute over time from the engine operational database. You can see the two databases information below:
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)
Using ‘\l+’ for detailed information:
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. View information about the current database connection:
engine=# \conninfo
You are connected to database "engine" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
4. List availalbe schemas:
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. List all tables with ‘\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. Show tables in the current engine database:
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 | disk_lun_map | table | engine
public | disk_profiles | table | engine
public | disk_vm_element | table | engine
public | dwh_osinfo | table | engine
...
public | vds_static | table | engine
public | vds_statistics | table | engine
public | vfs_config_labels | table | engine
public | vfs_config_networks | table | engine
public | vm_device | table | engine
7. Describe a table:
engine-# \d table_name
For example:
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
Run The SQL Query From Engine Tables To Get Information
Here are some example queries:
1. To retrieve all Users in PostgreSQL engine database:
engine=# select user_id, name, username from users;
2. To get all your network:
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. To get KVM hosts information:
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. To check the maximum number of client connections allowed:
engine=# select setting::bigint from pg_settings where name='max_connections';
setting
---------
150
(1 row)
5. To check the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files:
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. To check the shared buffers about how much memory is dedicated to PostgreSQL use for caching data:
engine=# select setting::bigint from pg_settings where name='shared_buffers';
setting
---------
16384
(1 row)
7. To check an estimated max RAM usage:
engine=# select pg_size_pretty(shared_buffers::bigint*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::bigint from pg_settings where name='block_size') as block_size, (select setting::bigint from pg_settings where name='shared_buffers') as shared_buffers, (select setting::bigint from pg_settings where name='max_connections') as max_connections,(select setting::bigint from pg_settings where name='work_mem') as work_mem, (select setting::bigint from pg_settings where name='autovacuum_max_workers') as autovacuum_max_workers,(select setting::bigint from pg_settings where name='autovacuum_work_mem') as autovacuum_work_mem,(select setting::bigint from pg_settings where name='maintenance_work_mem') as maintenance_work_mem)as _ ;
estimated_max_ram_usage
-------------------------
1712 MB
(1 row)
Miscellaneous
1. Reload the config files using select pg_reload_config():
engine=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
2. Display command history, you use the \s command.
engine=# \s
3. To quit psql, you use \q command and press enter to exit psql.
engine=# \q