Posting ini akan menjelaskan beberapa perintah psql praktis dan menjelaskan cara menjalankan kueri PostgreSQL dari baris perintah untuk mendapatkan informasi yang berguna dari database mesin.
Hubungkan Ke Database 'Postgres' Dari Baris Perintah
Untuk terhubung ke database 'Postgres':
# su - postgres Last login: Thu Apr 30 20:25:36 AEST 2020 on pts/1
Karena postgres berasal dari kumpulan perangkat lunak, Anda harus mengaktifkan postgresql untuk menghubungkan database mesin:
Untuk 4.2.8:
-bash-4.2$ scl enable rh-postgresql95 "psql -d engine -U postgres" psql (9.5.14) Type "help" for help. engine=#
Untuk 4.3.6:
-bash-4.2$ scl enable rh-postgresql10 "psql -d engine -U postgres" psql (10.6) Type "help" for help.
Beberapa Perintah psql Praktis Dan Berguna
1. Dapatkan bantuan tentang perintah psql.
Untuk mengetahui semua perintah psql yang tersedia, Anda menggunakan \? perintah untuk mendapatkan bantuan.
engine=# \?
2. Daftar semua database dengan ‘\l’.
RHV memiliki dua database terkait:engine dan ovirt-engine-history. RHV membuat database PostgreSQL yang disebut engine. Saat menginstal paket ovirt-engine-dwh membuat database kedua yang disebut ovirt-engine-history, yang berisi informasi konfigurasi historis dan metrik statistik yang dikumpulkan setiap menit dari waktu ke waktu dari database operasional mesin. Anda dapat melihat dua informasi database di bawah ini:
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)
Menggunakan '\l+' untuk informasi detail:
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. Lihat informasi tentang koneksi database saat ini.
engine=# \conninfo You are connected to database "engine" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
4. Cantumkan skema yang tersedia:
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. Daftar semua tabel dengan '\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. Tampilkan tabel dalam database mesin saat ini:
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. Jelaskan tabel:
engine-# \d table_name
Misalnya:
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 Jalankan Kueri SQL Dari Tabel Mesin Untuk Mendapatkan Informasi
Berikut adalah beberapa contoh kueri:
1. Untuk mengambil semua Pengguna di database mesin PostgreSQL:
engine=# select user_id, name, username from users;
2. Untuk mendapatkan semua jaringan Anda:
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. Untuk mendapatkan informasi 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. Untuk memeriksa jumlah maksimum koneksi klien yang diizinkan:
engine=# select setting::bigint from pg_settings where name='max_connections'; setting --------- 150 (1 row)
5. Untuk memeriksa jumlah memori yang akan digunakan oleh operasi sortir internal dan tabel hash sebelum menulis ke file disk sementara:
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. Untuk memeriksa buffer bersama tentang berapa banyak memori yang didedikasikan untuk penggunaan PostgreSQL untuk menyimpan data:
engine=# select setting::bigint from pg_settings where name='shared_buffers'; setting --------- 16384 (1 row)
7. Untuk memeriksa perkiraan penggunaan RAM maksimal:
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)
Lainnya
1. Muat ulang file konfigurasi menggunakan pilih pg_reload_config():
engine=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
2. Menampilkan riwayat perintah, Anda menggunakan perintah \s.
engine=# \s
3. Untuk keluar dari psql, gunakan perintah \q dan tekan enter untuk keluar dari psql.
engine=# \q