PostgreSQL - read an SQL file into a PostgreSQL database from the commandline

Question

I use Ruby to generate a bunch of SQL commands, and store this into a file.

I then login to my PostgreSQL database. Then I do something like:

\i /tmp/bla.sql

And this populates my database.

This all works fine as it is, no problem here.

I dislike the manual part where I have to use \i, though (because I need this to work in a cron job eventually, and I think commands like \i are only available when you are directly in the interactive psql prompt).

So my question now is:

Is it possible to use a psql command from the command line that directly will start to read in an external file?

Answer 1

You can directly use the psql command as shown below. Works for me with Ubuntu and Mint. On Windows it should be quite the same...

psql -U user -d database -f filepath

Example:

psql -U postgres -d testdb -f /home/you/file.sql

For more information take a lock at the official documentation: http://www.postgresql.org/docs/current/static/app-psql.html

Answer 2

When you try to execute an sql file using cron, you will also need to set the environment - database name, password etc. This is a short shell script snippet that does it all

source /var/lib/pgsql/scripts/.pgenv
echo $PATH
psql << AAA
select current_date;
select sp_pg_myprocedure(current_date);
AAA

In .pgenv, you set the values such as

export PGPORT=<yourport>
export PGHOST=<yourhost>
export PGDATA=<yourdatadir> 

Also have a .pgpass file so that the password is supplied. http://www.postgresql.org/docs/current/static/libpq-pgpass.html Replace the part where SELECT is being done with whatever you want to do, or do it as @Kuchi has shown.