• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Useful RHV Engine PostgreSQL Database Queries

by admin

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’.

RHV has two related databases: engine and ovirt-engine-history. RHV 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           |                       | 54321 |                          | 
(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)

Others

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

Filed Under: CentOS/RHEL, RHV

Some more articles you might also be interested in …

  1. How to configure AD group in sudoers file to provide sudo access to users on Linux system
  2. How to calculate recommended value of vm.min_free_kbytes Kernel Tuning Parameter
  3. How to Switch Module Streams in CentOS/RHEL 8
  4. ‘ulimit: max user processes: cannot modify limit:operation not permitted’ Shown When Login
  5. How to delete unused kernels and keep only the running kernel in CentOS/RHEL
  6. “No space left on device” – kdump generation issue (CentOS/RHEL)
  7. Understanding Power Management in RedHat Virtualization (RHV)
  8. How to block a specific IP Connecting to a server with firewall-cmd
  9. Shell Script to Find Network Interface Link Status and Speed (CentOS/RHEL)
  10. How To Configure NIS (Network Information System) Master and Slave Servers in CentOS/RHEL

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright