I am trying to automate a set of procedures that create TEMPLATE databases.

I have a set of files (file1, file2, ... fileN), each of which contains a set of pgsql commands required for creating a TEMPLATE database.

The contents of the file (createdbtemplate1.sql) looks roughly like this:

CREATE DATABASE mytemplate1 WITH ENCODING 'UTF8';

\c mytemplate1

CREATE TABLE first_table ( 
 --- fields here ..
);

-- Add C language extension + functions
\i db_funcs.sql

I want to be able to write a shell script that will execute the commands in the file, so that I can write a script like this:

# run commands to create TEMPLATE db mytemplate1
# ./groksqlcommands.sh createdbtemplate1.sql

for dbname in foo foofoo foobar barbar
do
    # Need to simply create a database based on an existing template in this script
    psql CREATE DATABASE $dbname TEMPLATE mytemplate1
done

Any suggestions on how to do this? (As you may have guessed, I'm a shell scripting newbie.)

Edit

To clarify the question further, I want to know:

  1. How to write groksqlcommands.sh (a bash script that will run a set of pgsql cmds from file)
  2. How to create a database based on an existing template at the command line
bash postgresql psql
Edit
Report