Thread: error while creating database

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

Quote:

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

If you are actually connecting with this script into template1 before running the create database, you will then receive that message. Ensure that the script is not doing this, or create a new template2 database, to use as a template, instead of the default one.

My 2 cents.

g.-




--
"Adopting the position that you are smarter than an automatic
optimization algorithm is generally a good way to achieve less
performance, not more" - Tom Lane.

Greetings:
I may be wrong, but you could try using the createdb / dropdb utils
from the pgsql/bin directory, or calling psql with some of the
parameters used by such tools (-c -d , etc).

Hope it helps.
Best regards

Dario




On 12/6/05, Karthik.S <skarthik@midascomm.com> wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


--
Atte: Dario Brignardello

I would also strongly recommend upgrading from 7.1.3. That version is
over 3 years old, is no longer supported, and IIRC has known data
integrity issues.

On Tue, Dec 06, 2005 at 03:55:16PM +0000, Guido Barosio wrote:
> Quote:
>
> "I am having a script(uses psql) which drops and creates a database by
> logging
> into 'template1'. This will be invoked from crontab."
>
> If you are actually connecting with this script into template1 before
> running the create database, you will then receive that message. Ensure that
> the script is not doing this, or create a new template2 database, to use as
> a template, instead of the default one.
>
> My 2 cents.
>
> g.-
>
> On 12/6/05, Karthik.S <skarthik@midascomm.com> wrote:
> >
> > 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
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
>
>
>
> --
> "Adopting the position that you are smarter than an automatic
> optimization algorithm is generally a good way to achieve less
> performance, not more" - Tom Lane.

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461