Shell script to execute pgsql commands in files
First off, do not mix psql
meta-commands and SQL
commands. These are separate sets of commands. There are tricks to combine those (using the psql meta-commands \o
and \\
and piping strings to psql in the shell), but that gets confusing quickly.
- Make your files contain only SQL commands.
- Do not include the
CREATE DATABASE
statement in the SQL files. Create the db separately, you have multiple files you want to execute in the same template db.
Assuming you are operating as OS user postgres
and use the DB role postgres
as (default) Postgres superuser, all databases are in the same DB cluster on the default port 5432 and the role postgres
has password-less access due to an IDENT
setting in pg_hba.conf
- a default setup.
psql postgres -c "CREATE DATABASE mytemplate1 WITH ENCODING 'UTF8'
TEMPLATE template0"
I based the new template database on the default system template database template0
. Basics in the manual here.
Your questions
How to (...) run a set of pgsql cmds from file
Try:
psql mytemplate1 -f file
Example script file for batch of files in a directory:
#! /bin/sh
for file in /path/to/files/*; do
psql mytemplate1 -f "$file"
done
The command option -f
makes psql
execute SQL commands in a file.
How to create a database based on an existing template at the command line
psql -c 'CREATE DATABASE my_db TEMPLATE mytemplate1'
The command option -c
makes psql
execute a single SQL command string. Can be multiple commands, terminated by ;
- will be executed in one transaction and only the result of the last command returned.
Read about psql command options in the manual.
If you don't provide a database to connect to, psql
will connect to the default maintenance database named "postgres". In the second answer it is irrelevant which database we connect to.
you can echo
your commands to the psql input:
for dbname in foo foofoo foobar barbar
do
echo """
CREATE DATABASE $dbname TEMPLATE mytemplate1
""" | psql
done
If you're willing to go the extra mile, you'll probably have more success with sqlalchemy. It'll allow you to build scripts with python instead of bash, which is easier and has better control.
As requested in the comments: https://github.com/srathbun/sqlCmd