Home > Slackware > Linux bash script for creating new tables and rules for huge partitioned PostgreSQL table

Linux bash script for creating new tables and rules for huge partitioned PostgreSQL table

in general this script is editable to be started on 1st of December each year

#!/bin/sh
# 2012-05-19 05:54:39.946835+03
# HLR lookups control tasks hor sharting
# inject new tables and rules for partitioned HLR lookups table 
# for s_year in '2012' # skip 2013
# set a cron at 1st of December , every year

echo "
CREATE TABLE IF NOT EXISTS bsms_in_hlr_tasks
(
  task_id character varying(90) NOT NULL,
  msisdn bigint NOT NULL,
  sid integer NOT NULL,
  ext_id character varying(90) NOT NULL,
  ops_to_be_processed integer[] NOT NULL DEFAULT '{1,2,3}'::integer[],
  status integer NOT NULL DEFAULT 0,
  intime timestamp without time zone NOT NULL DEFAULT now(),
  ptime timestamp without time zone,
  control_date date NOT NULL, -- partitioning per date
  CONSTRAINT bsms_in_hlr_tasks_pkey PRIMARY KEY (task_id )
)
WITH (
  OIDS=FALSE
);
"

# for s_year in $(date +%Y --date="next year")
for s_year in 2012 $(date +%Y --date="next year")
do

for s_manth in `seq 1 12`
do


if [ "${s_manth}" == "12" ]
then
	(( n_year =  s_year + 1)) 
	n_month=01
else
	echo "-- calculating next month of ${s_manth} before december"
	n_year=${s_year}
	s_month=${s_manth#0}
	(( n_month= s_month +1 )) 
fi


if [ ${#s_manth} -eq 1 ]
then
	s_manth="0${s_manth}"
fi

if [ ${#n_month} -eq 1 ]
then
	n_month="0${n_month}"
fi

show=1
if [ "${show}" == "1" ]
then
echo " 
CREATE TABLE if not exists bsms_in_hlr_tasks_y${s_year}m${s_manth} (
    CHECK ( control_date >= DATE '${s_year}-${s_manth}-01' AND control_date < DATE '${n_year}-${n_month}-01' )
,CONSTRAINT bsms_in_hlr_tasks_y${s_year}m${s_manth}_pkey PRIMARY KEY (task_id )
) INHERITS (bsms_in_hlr_tasks);

CREATE INDEX bsms_in_hlr_tasks_y${s_year}m${s_manth}_ext_id_idx
  ON bsms_in_hlr_tasks_y${s_year}m${s_manth}
  USING btree
  (ext_id );

CREATE RULE OR REPLACE route_bsms_in_hlr_tasks_y${s_year}m${s_manth} AS
ON INSERT TO bsms_in_hlr_tasks WHERE
    ( control_date >= DATE '${s_year}-${s_manth}-01' AND control_date < DATE '${n_year}-${n_month}-01'  )
DO INSTEAD
    INSERT INTO bsms_in_hlr_tasks_y${s_year}m${s_manth} VALUES (NEW.*);
"

fi; 
done

done

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: