Types of Databases: Import PostgreSQL Logs in Partition tables.

1) First Create the Master Table on Database Sever using below query.

CREATE TABLE postgres_log

(

log_time timestamp(3) with time zone,

user_name text,

database_name text,

process_id integer,

connection_from text,

session_id text,

session_line_num bigint,

command_tag text,

session_start_time timestamp with time zone,

virtual_transaction_id text,

transaction_id bigint,

error_severity text,

sql_state_code text,

message text,

detail text,

hint text,

internal_query text,

internal_query_pos integer,

context text,

query text,

query_pos integer,

location text,

application_name text,

PRIMARY KEY (session_id, session_line_num)

);

2) Then create the child table like below example:

To insert current day logs

create table postgres_log_07062018 (check (log_time::date >= current_date)) inherits (postgres_log);

To insert yesterday log

create table postgres_log_07052018 (check (log_time::date >= (current_date - interval '1 day'::interval)::date)) inherits (postgres_log);

To insert tomorrow log

create table postgres_log_07072018 (check (log_time::date >= (current_date + interval '1 day'::interval)::date)) inherits (postgres_log);

3) Then create the below function called logs_insert which will insert the data insert child table whenever insert or copy

create or replace function logs_insert() returns trigger as \$\$

declare

tbl_var text := 'postgres_log_07062018';

tbl_var1 text := 'postgres_log_07052018';

tb1_var2 text := 'postgres_log_07072018';

qry1 text;

qry2 text;

qry3 text;

begin

if ( new.log_time::date=current_date) then

qry1 := ' insert into ' || quote_ident(tbl_var) || ' SELECT ($1).*';

execute qry1 USING NEW;

elsif ( new.log_time::date=(current_date - interval '1 day'::interval)::date ) then

qry2 := ' insert into ' || quote_ident(tbl_var1) || ' SELECT ($1).*';

execute qry2 USING NEW;

elsif ( new.log_time::date=(current_date + interval '1 day'::interval)::date ) then

qry3 := ' insert into ' || quote_ident(tbl_var2) || ' SELECT ($1).*';

execute qry3 USING NEW;

else

raise exception 'log_time date out of range';

end if;

return null;

end;

\$\$ language plpgsql;

EOF

4) Create the trigger log_insert on master table(postgres_log ).

create trigger logs_insert before insert on postgres_log for each row execute procedure logs_insert();

Now, whenever there is insert on master table postgres_log above trigger log_insert will triggered and insert into respective child tables.

COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;

Use the below code to automate the log import process master and child tables using shell script

#!/usr/bin/ksh

export DATABASE=Test

LOGTABLE=$(date +%m%d%Y)

YESLOGTABLE=$(date --date='1 days ago' '+%m%d%Y')

TOMLOGTABLE=$(date --date="next day" +%m%d%Y)

create_objects ()

{

psql -d ${DATABASE} -p 5432 -c "create table postgres_log_$YESLOGTABLE (check (log_time::date >= (current_date - interval '1 day'::interval)::date)) inherits (postgres_log);"

psql -d ${DATABASE} -p 5432 -c "create table postgres_log_${LOGTABLE} (check (log_time::date >= current_date)) inherits (postgres_log);"

psql -d ${DATABASE} -p 5432 << EOF

create or replace function logs_insert() returns trigger as \$\$

declare

tbl_var text := 'postgres_log_$LOGTABLE';

tbl_var1 text := 'postgres_log_$YESLOGTABLE';

tb1_var2 text := 'postgres_log_$TOMLOGTABLE';