Thread: error in creating database

error in creating database

From
"Karthik.S"
Date:
Dear all,

        I am using postgres version: 7.1.3 in Red hat linux : 7.2.

I am having a script (uses psql) which drops and creates a database by
logging
into 'template1'. This will be invoked from crontab.

Sometimes (nearly 50% of the times) the database creation fails by saying
"ERROR:  CREATE DATABASE: source database "template1" is being
accessed by other users"

But I am sure that none of my scripts access 'template1' at that time.
I have also checked the 'ps -afx' output (logged at the time of failing),
but no suspects found.

Kindly give me a solution to overcome this problem.
Also kindly tell me a way to find, which process is accessing the
template1.


Thanks in advance.

regards
KArthik.S


Re: error in creating database

From
"A. Kretschmer"
Date:
am  06.12.2005, um 17:56:20 +0530 mailte Karthik.S folgendes:
> Dear all,
>
>         I am using postgres version: 7.1.3 in Red hat linux : 7.2.

Uuhhh, 7.1 is out of lifetime. You should update!



>
> I am having a script (uses psql) which drops and creates a database by
> logging
> into 'template1'. This will be invoked from crontab.

Why drop and create the database?


>
> Sometimes (nearly 50% of the times) the database creation fails by saying
> "ERROR:  CREATE DATABASE: source database "template1" is being
> accessed by other users"
>
> But I am sure that none of my scripts access 'template1' at that time.

Can you split the script into 2 parts (drop and create) an run
separately this scripts?

But, i think:
- drop/create the same database via cron is silly
- you should update to 8.1, in versions prior 7.4 there was any bugs.



HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Re: error in creating database

From
"Markus Wollny"
Date:
Hi!

> -----Ursprüngliche Nachricht-----
> Von: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Karthik.S
> Gesendet: Dienstag, 6. Dezember 2005 13:26
> An: pgsql-general@postgresql.org
> Betreff: [GENERAL] error in creating database
>
> Dear all,
>
>         I am using postgres version: 7.1.3 in Red hat linux : 7.2.

You should really consider upgrading. There has been a lot of development, both feature-wise and concerning
bug-squashing,since the 7.1-days. 

> Sometimes (nearly 50% of the times) the database creation
> fails by saying
> "ERROR:  CREATE DATABASE: source database "template1" is
> being accessed by other users"

8.1 has introduced the concept of a "maintenance-database", but you can easily do something similar by either creating
yourdb's with "CREATE DATABASE foo WITH TEMPLATE = template0;" (but anything you have changed in template1 after
PostgreSQL-installationwon't be in your new db then) or simply create another template-database, say template2 for this
purpose.

Kind regards

   Markus