Esta publicación explicará algunos comandos psql prácticos y describirá cómo ejecutar las consultas de PostgreSQL desde la línea de comandos para obtener información útil de la base de datos del motor.
Conéctese a la base de datos 'Postgres' desde la línea de comandos
Para conectarse a la base de datos 'Postgres':
# su - postgres Last login: Thu Apr 30 20:25:36 AEST 2020 on pts/1
Como postgres proviene de colecciones de software, debe habilitar postgresql para conectar la base de datos del motor:
Para 4.2.8:
-bash-4.2$ scl enable rh-postgresql95 "psql -d engine -U postgres" psql (9.5.14) Type "help" for help. engine=#
Para 4.3.6:
-bash-4.2$ scl enable rh-postgresql10 "psql -d engine -U postgres" psql (10.6) Type "help" for help.
Algunos comandos psql prácticos y útiles
1. Obtenga ayuda sobre los comandos psql.
Para conocer todos los comandos psql disponibles, use el \? comando para obtener ayuda.
engine=# \?
2. Enumere todas las bases de datos con '\l'.
RHV tiene dos bases de datos relacionadas:motor y ovirt-engine-history. RHV crea una base de datos PostgreSQL llamada motor. Al instalar el paquete ovirt-engine-dwh, se crea una segunda base de datos llamada ovirt-engine-history, que contiene información de configuración histórica y métricas estadísticas recopiladas cada minuto a lo largo del tiempo desde la base de datos operativa del motor. Puede ver la información de las dos bases de datos a continuación:
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)
Usando '\l+' para obtener información detallada:
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. Ver información sobre la conexión actual a la base de datos.
engine=# \conninfo You are connected to database "engine" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
4. Lista de esquemas disponibles:
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. Enumere todas las tablas con '\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. Mostrar tablas en la base de datos del motor actual:
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 una tabla:
engine-# \d table_name
Por ejemplo:
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 Ejecute la consulta SQL desde las tablas del motor para obtener información
Estas son algunas consultas de ejemplo:
1. Para recuperar todos los usuarios en la base de datos del motor PostgreSQL:
engine=# select user_id, name, username from users;
2. Para obtener toda su red:
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. Para obtener información de hosts 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 || 54321 | | (1 row)
4. Para comprobar el número máximo de conexiones de clientes permitidas:
engine=# select setting::bigint from pg_settings where name='max_connections'; setting --------- 150 (1 row)
5. Para verificar la cantidad de memoria que usarán las operaciones de clasificación interna y las tablas hash antes de escribir en archivos de disco temporales:
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. Para verificar los búferes compartidos sobre cuánta memoria se dedica al uso de PostgreSQL para almacenar datos en caché:
engine=# select setting::bigint from pg_settings where name='shared_buffers'; setting --------- 16384 (1 row)
7. Para verificar un uso máximo estimado de RAM:
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)
Otros
1. Vuelva a cargar los archivos de configuración usando select pg_reload_config():
engine=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
2. Muestra el historial de comandos, utiliza el comando \s.
engine=# \s
3. Para salir de psql, use el comando \q y presione Intro para salir de psql.
engine=# \q