48

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
Community
  • 1
  • 1
Homunculus Reticulli
  • 65,167
  • 81
  • 216
  • 341
  • 1
    Your question is ambiguous. First you want to create a template database using multiple files. But then your code example tries to create multiple databases based off a template database. What exactly is it you want to do? – Erwin Brandstetter Dec 21 '11 at 19:18
  • @ErwinBrandstetter: Not quite. I'll try to clarify. A (sql command) file contains a set of file to create A SINGLE template database. There is a 1:1 mapping between the (sql command) files and the template databases. I want to create a bash script that automates the creation of databases. The created databases are "derived" from the template databases that were created earlier on in the script. The bash script creates different template databases (and their derivatives) depending on run time script variables. HTH. – Homunculus Reticulli Dec 21 '11 at 19:51
  • correction: A (sql command) file contains a set of SQL COMMANDS to create A SINGLE template database. – Homunculus Reticulli Dec 21 '11 at 23:17

5 Answers5

64

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
3

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
Elias Dorneles
  • 22,556
  • 11
  • 85
  • 107
  • I guess your answer relates to the second part of my question (BTW, will psql not prompt for username and pwd the way you have written it?). Its not clear from the above, how to execute individual commands in a file (unless you are suggesting reading lines in file, iterating over the lines and echoing to psql?) – Homunculus Reticulli Dec 21 '11 at 19:10
  • What goes in echo is the same thing it would be in the normal input in psql prompt, so you can do `\i file_with_commands.sql` to execute a whole file, or you can create filters using sed and then pass its output to psql, like `echo "CREATE DATABASE __dbname__" | sed "s/__db_name/$variable_with_db_name/g" | psql`. As for psql asking for password, it depends on your PostgresSQL configuration, but you can still type in your password manually, if you don't want to configure a user with no password. – Elias Dorneles Dec 21 '11 at 19:21
1

Store your sql scripts under a root dir Use dev,tst,prd parametrized dbs Use find to run all your pgsql scripts as shown here Exit on errors

Or just git clone the whole tool from here

Yordan Georgiev
  • 5,114
  • 1
  • 56
  • 53
1

For that use case where you have to do it....

Here is a script I've used for importing JSON into PostgreSQL (WSL Ubuntu), which basically requires that you mix psql meta commands and SQL in the same command line. Note use of the somewhat obscure script command, which allocates a pseudo-tty:

$ more update.sh
#!/bin/bash
wget <filename>.json
echo '\set content `cat $(ls -t <redacted>.json.* | head -1)` \\ delete from <rable>; insert into <table> values(:'"'content'); refresh materialized view <view>; " | PGPASSWORD=<passwd> psql -h <host> -U <user> -d <database>
$
Joseph H
  • 33
  • 3
1

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

Spencer Rathbun
  • 14,510
  • 6
  • 54
  • 73
  • Hmmm, you may be onto something there. As it is, I have just (today!) started using sqlalchemy ... but steep learning curve, thought it would be easier/quicker to use bash instead. – Homunculus Reticulli Dec 21 '11 at 19:11
  • 2
    @HomunculusReticulli Oh it is absolutely easier to use bash. But, anything useful will be reused, and all those edges you told yourself wouldn't come up for your one off script, will come back and bite you. – Spencer Rathbun Dec 21 '11 at 19:22
  • @HomunculusReticulli Coincidentally, I created something very similar, though the DB was a mssql instance, and I had some other requirements as well. It took me the better part of a month, and there are some bits I wish I had done differently. But mostly, I wish I'd used sqlalchemy. If your interested, I can upload the code onto github, for your perusal. – Spencer Rathbun Dec 21 '11 at 19:24
  • I agree that a bash approach although (ostensibly) quicker for now, may come back to bite me at a later stage. I would prefer the sqlAlchemy approach - especially if I have a starting base to work from. It will be very helpful to see what you have done previously. Please upload your code to github, so I can see if it can give me some ideas on using sqlalchemy instead. Thanks – Homunculus Reticulli Dec 22 '11 at 09:47
  • @HomunculusReticulli I've added a link to the github repo. Please note that I built it for *my* needs. Since it is sql and extra commands, it has some limitations, just like all mini languages. Namely, it fits my problem set and nobody else's. – Spencer Rathbun Dec 22 '11 at 14:11
  • Thanks. I've cloned the repository. I'll take a look at it as soon as I have a chance. Hopefully, it will prove to be a good starting point!. – Homunculus Reticulli Dec 22 '11 at 21:32
  • @HomunculusReticulli If you run into a problem or have a question, just leave a message on the github issues page. It'll send me an email, and I'll do my best to give ya a hand. – Spencer Rathbun Dec 22 '11 at 21:35