We help IT Professionals succeed at work.
Get Started
Troubleshooting Question

Help required on Unix Shell Scripting and PostgreSQL

Last Modified: 2020-11-28
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 (
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:
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 :
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.

Watch Question
Software Engineer
Distinguished Expert 2019
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE