1

My current script looks something like this:

!/bin/sh 
sudo -u postgres createdb mydb
sudo -u postgres psql mydb
CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));
CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));
exit 0;

As of right now, when I execute the script it creates the database and logs me into it. However, after it logs me into mydb the script does not continue to do the CREATE TABLE commands.

I'm new when it comes to this stuff so I have no idea if I'm going about this the right way. Basically I just want a script that creates a database and then a few tables inside it (using PostgreSQL).

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
  • Somewhat related: http://stackoverflow.com/questions/7942632/how-to-extrace-pg-backend-pid-from-postgresql-in-shell-script-and-pass-it-to-ano/8305578#8305578 – Craig Ringer Oct 16 '12 at 05:32
  • 1
    Once you log into postgres your script waits because you're in another application. Take a look at the question and answer here http://stackoverflow.com/questions/8594717/bash-shell-script-to-execute-pgsql-commands-in-files it is related to your question. – Joshua Oct 16 '12 at 05:41

3 Answers3

3

One method is to use a here-doc (this is a shell feature and depends on what shell you're running):

psql mydb <<EOF
    CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));
    CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));
EOF

Another would be to write the commands to a sufficiently protected (with permissions) temporary file and then pass that as a script to postgres with the -f FILENAME option.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • Note that you can use `$variable` substitutions in your here document, substituting shell variables into the SQL. If you don't want to do that you should usually quote the here-document to prevent substitution, eg `<<'__EOF__'` instead of `<<__EOF__`. Unexpected and exciting results arise when you use things like `$1` or dollar-quoting in SQL without quoting your here-documents. – Craig Ringer Oct 16 '12 at 05:35
1
#!/bin/bash

psql -d mydb -U postgres -c "CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));"
psql -d mydb -U postgres -c "CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));"

You can run all queries with -c option.

TraviJuu
  • 255
  • 5
  • 12
  • Hey thank you so much man this is the method that I ended up using. –  Oct 17 '12 at 00:50
1

After editing the hba_conf file to "local all postgres trust" (I used trust so I wouldn't have to supply a password for my scripts), I used TraviJuu's suggestion and it now my script works flawlessly. Here is what the end result looks like.

!/bin/sh

sudo -u postgres createdb mydb

psql -w -d mydb -U postgres -c "CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));"

psql -w -d mydb -U postgres -c "CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));"

exit 0;