Setting timezone to get correct table constraints.ĮXECUTE FORMAT( 'SELECT set_config(%L, %L, TRUE) ', 'timezone ', 'UTC ') ĮXECUTE 'SELECT count(1) FROM information_schema.tables WHERE table_schema=$1 AND table_name=$2 ' INTO v_table_exists USING v_schema_name, v_table_name The db log files are in UTC timezone so that date extracted from filename will also be UTC. Set the search path to make sure the tables are created in dblogs schemaĮXECUTE FORMAT( 'SELECT set_config(%L, %L, TRUE) ', 'search_path ', v_schema_name) When CSV format is used, a check-constraint is added to the child table created for each log fileĬREATE OR REPLACE FUNCTION public.load_postgres_log_files(v_schema_name TEXT DEFAULT 'logs ', v_table_name TEXT DEFAULT 'postgres_logs ', v_prefer_csv BOOLEAN DEFAULT TRUE)ĮXECUTE FORMAT( 'SELECT count(1) FROM pg_catalog.pg_extension WHERE extname=%L ', 'log_fdw ') INTO v_ext_exists ĮXECUTE 'SELECT count(1) FROM pg_catalog.pg_foreign_server WHERE srvname=$1 ' INTO v_server_exists USING v_server_name ĮXECUTE FORMAT( 'CREATE SERVER %s FOREIGN DATA WRAPPER log_fdw ', v_server_name) ĮXECUTE FORMAT( 'CREATE SCHEMA IF NOT EXISTS %I ', v_schema_name) Supports the scenario where log files list consist of both the file name formats Daily, hourly and minute-based log file name formats are supported for CSV and non-CSV output files By default, CSV file format is preferred, it can be changed via argument v_prefer_csv Each log file is loaded as a foreign table and then made child of table logs.postgres_logs If the schema 'logs' does not exist, it will be created. If the table already exists, it will be DROPPED The schema name and table name can be changed via arguments. By default, a table named "postgres_logs" is created in schema "logs". SELECT public.load_postgres_log_files() 2) Run the following to load all the log files This function uses log_fdw to load all the available RDS / Aurora PostgreSQL DB log files as a table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |