Solved

Batch File

Posted on 2007-11-15
7
440 Views
Last Modified: 2013-12-06
Is there a way to write a batch file that can be scheduled in PostGre running on Red Hat Linux environment ?, I am tyring to run the copy command to import a CSV file into PostGre on a daily basis.
0
Comment
Question by:mayaldi
7 Comments
 
LVL 14

Accepted Solution

by:
arrkerr1024 earned 250 total points
ID: 20296397
Do you mean PostgreSQL?  If so then SURE.  Just make a bash script to run postgres with the necessary commands to import the csv, and then schedule it using crontab.  Cron is the (typical) linux scheduler.  See the man pages for exact usage, but you pretty much just type "crontab -e" and then tell it what days/times to run a command.
0
 

Author Comment

by:mayaldi
ID: 20296411
yes, PostgreSQL ... Can you give me an example on how to write a script  ?
0
 
LVL 61

Assisted Solution

by:gheist
gheist earned 250 total points
ID: 20300730
Just a normal shell script invoking psql with postgresql functions called

#!/bin/sh
/usr/bin/psql <<EOT
vacuum full analyze verbose;
EOT
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
ID: 20334576
   Hi!

You could also use the COPY command as shown here
http://www.java2s.com/Code/PostgreSQL/Table/CopyinganASCIIfiletodatatable.htm

Regards,
   Tomas Helgi
0
 
LVL 10

Expert Comment

by:adrpo
ID: 20749000

Hi,

You should just give the points to arrkerr1024 and gheist.
Here is my version of the same thing with more explanations.
More about crontab here:
http://www.adminschoice.com/docs/crontab.htm

01. log as the postgres user:
      as root: $ su postgress
02. create the script:
      make a directory for script
      $ cd /home/postgress
      $ mkdir bin
      $ cd bin
      $ cat > copy-script.sh
         #!/bin/sh
         /usr/bin/psql "copy tablename from '/absolute/path/to/file.sql' with delimiter ';' CSV;"
        press CTRL+D here to end the cat command
      Now you have a file copy-script.sh. Make it executable:
      $ chmod +x copy-script.sh
03. edit the crontab:
      make sure the editor is emacs, as you may have problems with vi if you don't know how
      to use it.
      $ export EDITOR=emacs
      $ crontab -e  
         Now the editor is opened, write in there:
         0 22 * * * /home/postgres/bin/copy-script.sh
         Save the file Ctrl+X then Ctrl+S
         Exit from Emacs: Ctrl+X then Ctrl+C

The copy-script.sh will run each day at 22:00. You can change that by running
crontab -e again and change the values in there. The values mean, see below.

Cheers,
za-k/


*     *   *   *    *  /path/to/the/scheduled/script.sh

-     -   -   -    -

|     |   |   |     |

|     |   |   |     +----- day of week (0 - 6) (Sunday=0)

|     |   |   +------- month (1 - 12)

|     |   +--------- day of month (1 - 31)

|     +----------- hour (0 - 23)

+------------- min (0 - 59)

Open in new window

0
 
LVL 1

Expert Comment

by:Computer101
ID: 21198044
Forced accept.

Computer101
EE Admin
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Users are often faced with high disk consumption without really knowing where the largest amount of data resides. Disk Usage Analyzer (aka Baobab) is is a graphical, menu-driven application to analyse disk usage in any Gnome environment and can e…
The purpose of this article is to show how we can create Linux Mint virtual machine using Oracle Virtual Box. To install Linux Mint we have to download the ISO file from its website i.e. http://www.linuxmint.com. Once you open the link you will see …
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now