CODEWITHSUNDEEP
postgresql
thor
thor

Reputation: 22500

How to batch create PostgreSQL databases?

How can one batch create many PostgreSQL databases and users, one database for each user?

I have a list of linux usernames, and I need to create a new postgres user for each linux username, and then an exclusive database for the new user in the home folder which no other users can see except the root.

I know how to use CREATE DATABASE but am not very familiar with loops etc. in PostgreSQL. Any pointers are appreciated.

Upvotes: 0

Views: 451

Answers (2)

kometen
kometen

Reputation: 7802

One way is to make a shell script like db.sh

#!/bin/sh

users="foo bar baz"

for user in $users
do
  PGUSER=pguser createdb $user
done

Then do a

chmod 0755 db.sh

And execute it with

./db.sh

You may have to change PGUSER to your environment.

Shell loops: http://www.tutorialspoint.com/unix/unix-loop-control.htm

Upvotes: 2

Daniel Lyons
Daniel Lyons

Reputation: 22803

Do it from the shell, it will be a lot less work:

$ for user in $(cat usernames.txt); do 
   createuser -e $user
   createdb -e -O $user $user
done;

Incidentally, for random Postgres commands, you can use psql -c to run SQL from the command line non-interactively.

Upvotes: 0

Related Questions