I'm trying to find out if I can automate the inserting of data from a flat file into PostgreSQL. I'd like to be able to tell my server to run a .sql file at a certain time of the day. This will help me to automate the population of data in PostgreSQL that I am taking off a mainframe. Any ideas would be greatly appreciated.
Thanks,
dcwebmaster
Is CRON what you are looking for?
I was planning to use CRON, but I'm unsure if it will work since CRON runs as root and my databases connect as a different user. Can I tell CRON to connect to a particular database as a certain user and then run the .sql script?
Thanks for your help.
I'm not shure but I think you can tell cron to run a shell script that does the thing (connect and issue commands to db).
Thanks anyway, does anyone else have any ideas?
Bear in mind that the users in Linux and the users defined for the db are different, you can log to the system as root and then log into the db as mrx.
Yep, I'm going to give it a try. I'll let you know hwo it works.
You want to setup a cron job that looks like this:
> su postgres -c 'psql mydatabase< my.sql.file'
Where my.sql.file is your text file with SQL commands.
Awesome... Thanks for your help. That's exactly what I was needing.
You may not have to su to the postgres user if you have your own crontab set up on the server
how would you use cron in relation to the pg_dump command which requires you to enter the username and password in the console before proceeding?
-S
Since I wrote the original question I have since learned a lot about setting up the crontab. You can set up a crontab to run as each individual user on your Linux system. For instance: I am now running a crontab as the postgres user in order to automate a pgdump command and then I am running a crontab as the root level user so I can perform automated system backups.
You will need to su over to the postgres user and then run the crontab -e command. This will start the process for you.
I hope that helps.
-- dcwebmaster
I tried to run a crob job to dump my postgres database but it wouldn't run. However, if I ran it on a command line, it worked fine. Any idea why? Here are my command line and cron job:
The resulted files I got were:
-rw-r--r-- 1 postgres postgres 509544131 May 13 09:49 mydatabase.gz
and
-rw-r--r-- 1 postgres postgres 0 May 13 09:50 mydatabase.dump.gz
I was having a similar problem when backing up my DB's using cron. The bash script I had worked fine, however, when I ran the script from a cron job, the pg_dump seemed to not work. To solve the problem, I redirected pg_dump stderr and stdout to a file by modifying a line in my script:
After looking at test.out, I saw that pg_dump wasn't working because the "pg_dump" operation wasn't found. I adjusted the Path in crontab (crontab -e). Things work well now. I hope this works for you. It might be as simple as a path problem for you, if not, try redirecting stderr and stdout, and investigating.
Good Luck!
I tried to run a crob job to dump my postgres database but it wouldn't run. However, if I ran it on a command line, it worked fine. Any idea why? Here are my command line and cron job:
The resulted files I got were:
-rw-r--r-- 1 postgres postgres 509544131 May 13 09:49 mydatabase.gz
and
-rw-r--r-- 1 postgres postgres 0 May 13 09:50 mydatabase.dump.gz