Help required on Unix Shell Scripting and PostgreSQL

Hi All,

Daily I am performing data archival manually from Production to Archival database for 72 tables following below steps. I am beginner in Unix and PostgreSQL technology.

I would like to automate below steps via cron job on unix platform. Please help how to add below all steps in single shell (.sh) script to automate archival for all my tables from Prod to Archival DB.

Production Steps:
Step 1: Kill data purging jobs on Prod when job is "Sleeping for 5 secs job can be klilled.."
Jobs name1: run_archive_job_messagelog
Jobs name2: run_archive_job_Auditlog

Step 2:Connect to PostgreSQL and rename the archival tables as a _arc_<today's date> as mentioned below

-bash-4.2$ psql -p 5444 -d mydb -U user1
Password for user user:
psql.bin (9.5.22.28)
Type "help" for help.


mydb=#alter table Table1_arc rename to Table1_arc_27112020;
mydb=#alter table Table2_arc rename to Table2_arc_27112020;

Step 3:Create original *_arc table without data as mentioned below.

mydb=#create table Table1_arc as select * from Table1_arc_27112020 where 1=2;
mydb=#create table Table2_arc as select * from Table2_arc_27112020 where 1=2;

Step 4:Restart purging jobs

nohup /appdb/edb/scripts/run_archive_job_messagelog.sh 1>/appdb/edb/scripts/logs/nohup_archive_job_messagelog.log 2>&1 &
nohup /appdb/edb/scripts/run_archive_job_Auditlog.sh 1>/appdb/edb/scripts/logs/nohup_archive_job_Auditlog.log 2>&1 &

Archival Steps:
1. Start purging for Table1_arc from Prod to Archival database:
(a).

vi Arch_Table1_arc_27112020.sh

(b). Insert below command in Arch_Table1_arc_27112020.sh

export PGPASSWORD=user1
/appdb/edb/install/9.5AS/bin/psql -h x.x.x.85 -p 5444 -U user1 -d mydb -c "COPY (SELECT * FROM myschema.Table1_arc_27112020) TO STDOUT;"  | /appdb/edb/install/9.5AS/bin/psql -h x.x.x.86 -p 5444 -U user1 -d mydb -c "COPY myschema.Table1_arc FROM STDIN;"

(c). Grant read and write access to user1

chmod 755 Arch_Table1_arc_27112020.sh

(d). run .sh file

nohup ./Arch_Table1_arc_27112020.sh > Arch_Table1_arc_27112020.log 2>&1 &

2. Start purging for Table2_arc from Prod to Archival database :
(a).

vi Table2_arc_27112020.sh

(b). Insert below command in Arch_Table2_arc_27112020.sh

export PGPASSWORD=user1
/appdb/edb/install/9.5AS/bin/psql -h x.x.x.85 -p 5444 -U user1 -d mydb -c "COPY (SELECT * FROM myschema.Table2_arc_27112020) TO STDOUT;"  | /appdb/edb/install/9.5AS/bin/psql -h x.x.x.86 -p 5444 -U user1 -d mydb -c "COPY myschema.Table2_arc FROM STDIN;"

(c). Grant read and write access to user1

chmod 755 Arch_Table2_arc_27112020.sh

(d). run .sh file

nohup ./Arch_Table2_arc_27112020.sh > Arch_Table2_arc_27112020.log 2>&1 &

Finally I will verify the row count in .log file and will compare the this row count with Production tables.