Questo post spiegherà alcuni pratici comandi psql e descriverà come eseguire le query PostgreSQL dalla riga di comando per ottenere informazioni utili dal database del motore.
Connettiti al database "Postgres" dalla riga di comando
Per connettersi al database "Postgres":
# su - postgres Last login: Thu Apr 30 20:25:36 AEST 2020 on pts/1
Poiché postgres proviene da raccolte di software, devi abilitare postgresql per connettere il database del motore:
Per 4.2.8:
-bash-4.2$ scl enable rh-postgresql95 "psql -d engine -U postgres" psql (9.5.14) Type "help" for help. engine=#
Per 4.3.6:
-bash-4.2$ scl enable rh-postgresql10 "psql -d engine -U postgres" psql (10.6) Type "help" for help.
Alcuni pratici e utili comandi psql
1. Ottieni aiuto sui comandi psql.
Per conoscere tutti i comandi psql disponibili, usi \? comando per ottenere aiuto.
engine=# \?
2. Elenca tutti i database con '\l'.
RHV ha due database correlati:engine e ovirt-engine-history. RHV crea un database PostgreSQL chiamato engine. Durante l'installazione del pacchetto ovirt-engine-dwh viene creato un secondo database chiamato ovirt-engine-history, che contiene informazioni di configurazione storiche e metriche statistiche raccolte ogni minuto nel tempo dal database operativo del motore. Puoi vedere le informazioni sui due database di seguito:
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)
Utilizzo di '\l+' per informazioni dettagliate:
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. Visualizza le informazioni sulla connessione al database corrente.
engine=# \conninfo You are connected to database "engine" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
4. Elenca gli schemi disponibili:
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. Elenca tutte le tabelle 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. Mostra le tabelle nel database del motore corrente:
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. Descrivi una tabella:
engine-# \d table_name
Ad esempio:
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 Esegui la query SQL dalle tabelle del motore per ottenere informazioni
Ecco alcuni esempi di query:
1. Per recuperare tutti gli utenti nel database del motore PostgreSQL:
engine=# select user_id, name, username from users;
2. Per ottenere tutta la tua rete:
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. Per ottenere informazioni sugli host 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. Per verificare il numero massimo di connessioni client consentite:
engine=# select setting::bigint from pg_settings where name='max_connections'; setting --------- 150 (1 row)
5. Per controllare la quantità di memoria da utilizzare per le operazioni di ordinamento interno e le tabelle hash prima di scrivere su file temporanei del disco:
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. Per controllare i buffer condivisi su quanta memoria è dedicata all'uso di PostgreSQL per la memorizzazione nella cache dei dati:
engine=# select setting::bigint from pg_settings where name='shared_buffers'; setting --------- 16384 (1 row)
7. Per verificare un utilizzo massimo stimato della 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)
Altri
1. Ricaricare i file di configurazione utilizzando select pg_reload_config():
engine=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
2. Visualizza la cronologia dei comandi, usi il comando \s.
engine=# \s
3. Per uscire da psql, usa il comando \q e premi invio per uscire da psql.
engine=# \q