Topik ini menjelaskan cara menggunakan ekstensi log_fdw untuk mengquery log basis data dari instance ApsaraDB RDS for PostgreSQL.
Prasyarat
Instance RDS Anda harus menjalankan PostgreSQL 11. Jika Anda tidak dapat membuat ekstensi meskipun instance RDS Anda sudah menggunakan PostgreSQL 11, perbarui versi mesin minor instance RDS Anda. Untuk informasi lebih lanjut, lihat Perbarui versi mesin minor dari instance ApsaraDB RDS for PostgreSQL.Informasi latar belakang
Ekstensi log_fdw menyediakan fungsi-fungsi berikut:list_postgres_log_files(): mencantumkan semua file log .csv.create_foreign_table_for_log_file(IN table_name text, IN log_server text, IN log_file text): membuat tabel asing yang terkait dengan file log .csv tertentu.
Prosedur
- Buat ekstensi log_fdw.
postgres=> create extension log_fdw; CREATE EXTENSION - Buat definisi untuk server log.
postgres=> create server <Nama server log> foreign data wrapper log_fdw;Contoh:
postgres=> create server log_server foreign data wrapper log_fdw; CREATE SERVER - Panggil fungsi
list_postgres_log_files()untuk mencantumkan semua file log .csv.postgres=> select * from list_postgres_log_files() order by 1; file_name | file_size_bytes ----------------------------------+----------------- postgresql-2020-01-10_095546.csv | 3794 postgresql-2020-01-10_100336.csv | 318318 postgresql-2020-01-11_000000.csv | 198437 postgresql-2020-01-11_083546.csv | 4775 postgresql-2020-01-13_030618.csv | 3347 - Panggil fungsi
create_foreign_table_for_log_file(IN table_name text, IN log_server text, IN log_file text)untuk membuat tabel asing yang terkait dengan file log .csv tertentu.postgres=> select create_foreign_table_for_log_file('<Nama tabel asing>', '<Nama server log>', '<Nama file log .csv yang terkait dengan tabel asing>');Contoh:
postgres=> select create_foreign_table_for_log_file('ft1', 'log_server', 'postgresql-2020-01-13_030618.csv'); create_foreign_table_for_log_file ----------------------------------- t (1 row) - Query tabel asing untuk mendapatkan data dari file log .csv.
postgres=> select log_time, message from <Nama tabel asing> order by log_time desc limit 2;Contoh:
postgres=> select log_time, message from ft1 order by log_time desc limit 2; log_time | message ----------------------------+------------------------------------------------------------------- 2020-01-13 03:35:00.003+00 | cron job 1 completed: INSERT 0 1 1 2020-01-13 03:35:00+00 | cron job 1 starting: INSERT INTO cron_test VALUES ('Hello World') (2 rows)
Skema tabel asing
postgres=> \d+ ft1
Foreign table "public.ft1"
Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
------------------------+-----------------------------+-----------+----------+---------+-------------+----------+--------------+-------------
log_time | timestamp(3) with time zone | | | | | plain | |
user_name | text | | | | | extended | |
database_name | text | | | | | extended | |
process_id | integer | | | | | plain | |
connection_from | text | | | | | extended | |
session_id | text | | | | | extended | |
session_line_num | bigint | | | | | plain | |
command_tag | text | | | | | extended | |
session_start_time | timestamp with time zone | | | | | plain | |
virtual_transaction_id | text | | | | | extended | |
transaction_id | bigint | | | | | plain | |
error_severity | text | | | | | extended | |
sql_state_code | text | | | | | extended | |
message | text | | | | | extended | |
detail | text | | | | | extended | |
hint | text | | | | | extended | |
internal_query | text | | | | | extended | |
internal_query_pos | integer | | | | | plain | |
context | text | | | | | extended | |
query | text | | | | | extended | |
query_pos | integer | | | | | plain | |
location | text | | | | | extended | |
application_name | text | | | | | extended | |
Server: log_server
FDW options: (filename 'postgresql-2020-01-13_030618.csv')