Re: Automating databse creation - Mailing list pgsql-general

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ashish Karalkar
Sent: 09 February 2007 07:54
To: pgsql-general@postgresql.org
Cc: Shoaib Mir
Subject: [GENERAL] Automating databse creation

Hello list,

I want to automate database creation, user creation,table creation via script. this script will be run by an external programme to postgresql server.

is there any way?

what i want to do is as follows:

#!/bin/sh

su - postgres

TIMEDUMP=`date +%Y%m%d"_"%H%M`

PG_PATH=/usr/local/pgsql/bin

${PG_PATH}/pg_dump -d qsweb -U postgres -p 5432 -Ft -f /usr/local/pgsql/data/backup/BACKUP_QSWEB_${TIMEDUMP}.tar

echo ' Backup of Database for Quick School kept at path /usr/local/pgsql/data/backup/BACKUP_QSWEB_${TIMEDUMP}.tar'

${PG_PATH}/dropdb  -U qsweb qsweb

echo ' Old Database for Quick School Dropped'

${PG_PATH}/dropuser qsweb

echo ' Old user qsweb for Quick School Dropped'

${PG_PATH}/createuser -s qsweb

echo ' New user qsweb for Quick School created'

${PG_PATH}/createdb -O qsweb qsweb

echo ' New Database qsweb for Quick School created'

${PG_PATH}/createlang -U qsweb -d qsweb plpgsql

echo ' Database language plpgqslfor Quick School created'

echo ' Database for Quick School created. Please run the Create_Tables.sql script'

${PG_PATH}/psql -U qsweb qsweb

and then Run  Create_Tables.sql

_EOF_

is there any way??

right now the scripts stop after switching over to postgres user.

With regards

Ashish...