Thread: Create Database using JDBC
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
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
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
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: > > > 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
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
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
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
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
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
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
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
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 [mailto:books@ejurka.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
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
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: > >> >> 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 davec@postgresintl.com www.postgresintl.com ICQ #14675561 jabber davecramer@jabber.org ph (519 939 0336 )
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
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
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
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
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
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