|
Home > Archive > PostgreSQL JDBC > December 2005 > Create Database using JDBC
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Create Database using JDBC
|
|
| Nidhi Srivastava 2005-07-01, 7:24 am |
| Hi all
I wish to create a database if one doesn't exist using JDBC. I tried
connecting to template1 database and then issuing the "CREATE DATABASE
test" command but I get the following error :
ERROR: CREATE DATABASE: source database "template1"
is being accessed by other users
How do I resolve this? I'm using PostGreSQL8.0
Thanks & Regards
Nidhi
| |
| Thomas Dudziak 2005-07-01, 7:24 am |
| On 7/1/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
> I wish to create a database if one doesn't exist using JDBC. I tried
> connecting to template1 database and then issuing the "CREATE DATABASE test"
> command but I get the following error :
>
> ERROR: CREATE DATABASE: source database "template1"
> is being accessed by other users
>
> How do I resolve this? I'm using PostGreSQL8.0
This should work (we're doing it exactly the same way). But there can
only be one user connected at a time to the template database, it
seems, at least when using the CREATE DATABASE command. Do you have
the one of the PostgreSQL tools open, eg. pgAdmin ?
Tom
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
| |
| Oliver Jowett 2005-07-01, 7:24 am |
| Nidhi Srivastava wrote:
> I wish to create a database if one doesn’t exist using JDBC. I tried
> connecting to template1 database and then issuing the “CREATE DATABASE
> test” command but I get the following error :
>
> ERROR: CREATE DATABASE: source database "template1"
> is being accessed by other users
This is no different for JDBC versus createdb. You can't use a database
as a source for CREATE DATABASE if it has more than one active
connection. I suspect you will find that 'createdb' fails with the same
error..
-O
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
| |
| Csaba Nagy 2005-07-01, 7:24 am |
| Hi all,
I'm not too knowledgeable about this matter, but wouldn't it work to
connect to another data base, not template1 ?
And make a policy not to connect to template1.
I guess it doesn't matter which data base are you connected to (except
that it must exist) when you create a new one. So just create a
"standard" data base which always exist, and connect to that one.
Cheers,
Csaba.
On Fri, 2005-07-01 at 12:26, Thomas Dudziak wrote:
> On 7/1/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
>
>
> This should work (we're doing it exactly the same way). But there can
> only be one user connected at a time to the template database, it
> seems, at least when using the CREATE DATABASE command. Do you have
> the one of the PostgreSQL tools open, eg. pgAdmin ?
>
> Tom
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Dave Cramer 2005-07-01, 7:24 am |
| Is it possible you have an existing open transaction on template1 ?
Have a look at the postgresql logs.
Dave
On 1-Jul-05, at 4:30 AM, Nidhi Srivastava wrote:
> Hi all
>
>
>
> I wish to create a database if one doesn’t exist using JDBC. I
> tried connecting to template1 database and then issuing the “CREATE
> DATABASE test” command but I get the following error :
>
>
>
> ERROR: CREATE DATABASE: source database "template1"
> is being accessed by other users
>
>
>
>
> How do I resolve this? I’m using PostGreSQL8.0
>
>
>
> Thanks & Regards
>
> Nidhi
>
>
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
ICQ #14675561
jabber davecramer@jabber.org
ph (519 939 0336 )
| |
| Thomas Dudziak 2005-07-01, 7:24 am |
| On 7/1/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
> Yes I have the pgAdmin open. Is there any work around to check if more
> than one users are connected to the server and resolve this?
You could close pgAdmin, or at least disconnect it from the database ?!
Tom
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
| |
| Nidhi Srivastava 2005-07-01, 11:23 am |
| Yes I have the pgAdmin open. Is there any work around to check if more
than one users are connected to the server and resolve this?
Or is there any way I can create a DB at the time of Postgres
installation itself. I'm using postgresql8.0 on windows.
If you can't see the bright side of life, polish the dull side.
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Thomas Dudziak
Sent: Friday, July 01, 2005 3:56 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Create Database using JDBC
On 7/1/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
> I wish to create a database if one doesn't exist using JDBC. I tried
> connecting to template1 database and then issuing the "CREATE DATABASE
test"
> command but I get the following error :
>
> ERROR: CREATE DATABASE: source database "template1"
> is being accessed by other users
>
> How do I resolve this? I'm using PostGreSQL8.0
This should work (we're doing it exactly the same way). But there can
only be one user connected at a time to the template database, it
seems, at least when using the CREATE DATABASE command. Do you have
the one of the PostgreSQL tools open, eg. pgAdmin ?
Tom
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Nidhi Srivastava 2005-07-04, 7:23 am |
| Even when I'm not accessing templte1 but any other scratch database
through pgAdmin and try to create a new DB using JDBC I get the same
ERROR: CREATE DATABASE: source database "template1" is being accessed
by other users. Is there a way to check if a DB exists and create it if
it doesn't using JDBC. How can I ensure that pgAdmin is not open on any
of the client machines and connected to the Postgre server on my
machine....becuase if even a single pgAdmin client is open on any of the
client machines it doesn't allow me to create a new DB throwing the
above error.
Thanks & Regards
Nidhi
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Nidhi Srivastava
Sent: Friday, July 01, 2005 4:04 PM
To: Thomas Dudziak; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Create Database using JDBC
Yes I have the pgAdmin open. Is there any work around to check if more
than one users are connected to the server and resolve this?
Or is there any way I can create a DB at the time of Postgres
installation itself. I'm using postgresql8.0 on windows.
If you can't see the bright side of life, polish the dull side.
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Thomas Dudziak
Sent: Friday, July 01, 2005 3:56 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Create Database using JDBC
On 7/1/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
> I wish to create a database if one doesn't exist using JDBC. I tried
> connecting to template1 database and then issuing the "CREATE DATABASE
test"
> command but I get the following error :
>
> ERROR: CREATE DATABASE: source database "template1"
> is being accessed by other users
>
> How do I resolve this? I'm using PostGreSQL8.0
This should work (we're doing it exactly the same way). But there can
only be one user connected at a time to the template database, it
seems, at least when using the CREATE DATABASE command. Do you have
the one of the PostgreSQL tools open, eg. pgAdmin ?
Tom
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Kris Jurka 2005-07-04, 7:23 am |
|
On Mon, 4 Jul 2005, Nidhi Srivastava wrote:
> Even when I'm not accessing templte1 but any other scratch database
> through pgAdmin and try to create a new DB using JDBC I get the same
> ERROR: CREATE DATABASE: source database "template1" is being accessed
> by other users. Is there a way to check if a DB exists and create it if
> it doesn't using JDBC. How can I ensure that pgAdmin is not open on any
> of the client machines and connected to the Postgre server on my
> machine....becuase if even a single pgAdmin client is open on any of the
> client machines it doesn't allow me to create a new DB throwing the
> above error.
>
I would suggest using an alernate template database to create new
databases with. You could even mark it to refuse connections to ensure no
one else will connect to it and block your operation.
Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Thomas Dudziak 2005-07-04, 7:23 am |
| On 7/4/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
>
> But even that alternate template database would need to be created the
> first time after server installation by connceting to the template1 DB??
> Right??
>
> All I want is to automate database creation the first time after server
> installation eliminate user inputs
But why then not requiring that no one is connected to the template1
database ? That shouldn't be too much of a requirement. Myself, I
develop with postgres on a daily basis (using JDBC), and even I have
pgAmin only open when necessary eg. for checking that some data was
inserted in the db or testing a query.
Tom
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
| |
| Thomas Dudziak 2005-07-04, 11:23 am |
| On 7/4/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
> Problem is if any client application is connected to another database on
> my server anywhere, I shall not able to create my database using
> template1. How can I determine which other clients are connected to my
> server?
Have you tried that this doesn't work ? If none of the clients is
connected to template1 but only to their own databases, then AFAIK it
should work (pgAdmin might be different in this matter, perhaps it is
always connected to the template databases ?).
You could write a small JDBC app to verify that it does/doesn't work, eg.
* open a connection to template1
* create database test
* close connection to template1
* open connection to test and keep it open
* open connection to template1
* create database test2
* close connection to template1
* close connection to test
if this doesn't work, then you probably should file a bug and attach
this sample app.
Tom
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org
| |
| Nidhi Srivastava 2005-07-04, 1:23 pm |
|
But even that alternate template database would need to be created the
first time after server installation by connceting to the template1 DB??
Right??
All I want is to automate database creation the first time after server
installation eliminate user inputs
-----Original Message-----
From: Kris Jurka & #91;mailto:books@eju
rka.com]
Sent: Monday, July 04, 2005 3:01 PM
To: Nidhi Srivastava
Cc: Thomas Dudziak; pgsql-jdbc@postgresql.org; John R Pierce
Subject: Re: [JDBC] Create Database using JDBC
On Mon, 4 Jul 2005, Nidhi Srivastava wrote:
> Even when I'm not accessing templte1 but any other scratch database
> through pgAdmin and try to create a new DB using JDBC I get the same
> ERROR: CREATE DATABASE: source database "template1" is being accessed
> by other users. Is there a way to check if a DB exists and create it
if
> it doesn't using JDBC. How can I ensure that pgAdmin is not open on
any
> of the client machines and connected to the Postgre server on my
> machine....becuase if even a single pgAdmin client is open on any of
the
> client machines it doesn't allow me to create a new DB throwing the
> above error.
>
I would suggest using an alernate template database to create new
databases with. You could even mark it to refuse connections to ensure
no
one else will connect to it and block your operation.
Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
| |
| Nidhi Srivastava 2005-07-04, 1:23 pm |
| Problem is if any client application is connected to another database on
my server anywhere, I shall not able to create my database using
template1. How can I determine which other clients are connected to my
server?
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Thomas Dudziak
Sent: Monday, July 04, 2005 4:39 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Create Database using JDBC
On 7/4/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
>
> But even that alternate template database would need to be created the
> first time after server installation by connceting to the template1
DB??
> Right??
>
> All I want is to automate database creation the first time after
server
> installation eliminate user inputs
But why then not requiring that no one is connected to the template1
database ? That shouldn't be too much of a requirement. Myself, I
develop with postgres on a daily basis (using JDBC), and even I have
pgAmin only open when necessary eg. for checking that some data was
inserted in the db or testing a query.
Tom
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)
| |
| Dave Cramer 2005-07-04, 8:24 pm |
| select * from pg_stat_activity
Dave
On 4-Jul-05, at 9:08 AM, Nidhi Srivastava wrote:
> Problem is if any client application is connected to another
> database on
> my server anywhere, I shall not able to create my database using
> template1. How can I determine which other clients are connected to my
> server?
>
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Thomas Dudziak
> Sent: Monday, July 04, 2005 4:39 PM
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Create Database using JDBC
>
> On 7/4/05, Nidhi Srivastava <nsrivastava@quark.com> wrote:
>
> DB??
>
> server
>
>
> But why then not requiring that no one is connected to the template1
> database ? That shouldn't be too much of a requirement. Myself, I
> develop with postgres on a daily basis (using JDBC), and even I have
> pgAmin only open when necessary eg. for checking that some data was
> inserted in the db or testing a query.
>
> Tom
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere
" to
> majordomo@postgresql
.org)
>
>
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
ICQ #14675561
jabber davecramer@jabber.org
ph (519 939 0336 )
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Oliver Jowett 2005-07-04, 8:24 pm |
| Nidhi Srivastava wrote:
> Problem is if any client application is connected to another database on
> my server anywhere, I shall not able to create my database using
> template1. How can I determine which other clients are connected to my
> server?
It's not "another database", it is specifically the database that you
are using as the template for CREATE DATABASE that must be idle. See the
CREATE DATABASE docs for details.
Usually template1 is not used for anything else so there's no problem. I
suspect that pgAdmin is connecting to template1 *as well* as to the
database you request -- so just the act of running it means that noone
else can CREATE DATABASE from template1.
Whatever database you end up using as a template, you will need to
ensure that there's noone else connected to it. If having random users
connect to the template DB is a problem, then consider using pg_hba.conf
to enforce this -- i.e. don't let people connect to template1 in the
first place unless they are a particular user who is allowed to CREATE
DATABASE..
This is not JDBC-specific though. I suggest you take the discussion to
pgsql-general where there's a wider audience and you may get more help.
-O
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Markus Schaber 2005-12-17, 9:23 am |
| Hi, Nidhi,
Nidhi Srivastava schrieb:
> I wish to create a database if one doesn’t exist using JDBC. I tried
> connecting to template1 database and then issuing the “CREATE DATABASE
> test” command but I get the following error :
>
> ERROR: CREATE DATABASE: source database "template1"
>
> is being accessed by other users
>
> How do I resolve this? I’m using PostGreSQL8.0
This is a PostgreSQL limitation, in that you cannot use a database as
template if anyone else except the one who issued the create database
has a connection to it.
You can either use "template0" as template (if you did not apply any
modifications to template1), or create your own templateblah database
which you use as template.
Btw, the Debian PostgreSQL packagers seem to add an empty database
called "postgresql" to each cluster, and then all tools (e. G. pgadmin)
are told to use this database to connect against, so the template
database is kept reserved for real templating issues. Maybe this could
be adopted upstream.
HTH,
Markus
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Markus Schaber 2005-12-17, 9:23 am |
| Hi, Thomas,
Thomas Dudziak schrieb:
> But why then not requiring that no one is connected to the template1
> database ? That shouldn't be too much of a requirement. Myself, I
> develop with postgres on a daily basis (using JDBC), and even I have
> pgAmin only open when necessary eg. for checking that some data was
> inserted in the db or testing a query.
This will not be possible on installations with a large userbase, you
have a bunch of users, admins and developers which are using all kinds
of programs that (partially implicitly) connect to template1.
Maybe the debian way (add an additional postgresql database for this
purposes) should be adopted?
Markus
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Markus Schaber 2005-12-17, 9:23 am |
| Hi, Nidhi,
Nidhi Srivastava schrieb:
> But even that alternate template database would need to be created the
> first time after server installation by connceting to the template1 DB??
> Right??
No, you can specify any database that has no active connections (exept
possibly your own single one) as template on database creation,
including template0 (which is usually set up to refuse connections).
So connect to template1, and create your new template database with
using template0 as template.
HTH,
Markus
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Jim C. Nasby 2005-12-20, 1:24 pm |
| On Sat, Dec 17, 2005 at 02:19:00PM +0100, Markus Schaber wrote:
> Btw, the Debian PostgreSQL packagers seem to add an empty database
> called "postgresql" to each cluster, and then all tools (e. G. pgadmin)
> are told to use this database to connect against, so the template
> database is kept reserved for real templating issues. Maybe this could
> be adopted upstream.
PostgreSQL adds that database automatically on initdb starting with
version 8.1.
--
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Markus Schaber 2005-12-20, 1:24 pm |
| Hi, Jim,
Jim C. Nasby schrieb:
> PostgreSQL adds that database automatically on initdb starting with
> version 8.1.
Oh, great, then this is not debian specific. :-)
Markus
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
|
|
|
|