Automating databse creation

Yes, you can pass values to the scripts. Here is an example....

Suppose the script file 'test.sql' is like this:

insert into test values (:chk1 , :chk2);

Now you can pass the variables using psql as:

psql -d test -U postgres -v chk1=5 -v chk2='abc' -f test.sql

Hope that helps...

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

Show quoted text

On 2/9/07, Ashish Karalkar <ashish.karalkar@info-spectrum.com> wrote:

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...

Hi,

On Friday 09 February 2007 08:53, Ashish Karalkar wrote:
| 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

[rest of not working script]

| is there any way??
|
| right now the scripts stop after switching over to postgres user.

assuming the script is run as user root, you can invoke it as
su - postgres -c /path/to/script

Just remove the "su - postgres" from your script. You might want to look
at your init scripts (/etc/init.d/postgresql I assume) for how they do it.

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----

Hi Ashish Karalkar

That's because the shell is waiting for input.

Try do something like this

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

That should run the command as the postgres user.

Thanks

Bruce

_____

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...

__________ NOD32 2047 (20070208) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com