Thread: createdb fails
Hi, =20 Running postgresql v7.3 on RH7.3 linux; db creation fails and is reproducible after a fresh image install. Any subsequent rebooting seems to work fine. We start up Postgres, create users and db using a Java (IBM1.4.1) class. The following commands are executed in the following order: =20 /usr/local/pgsql/bin/pg_ctl -w start -D /var/lib/pgsql -l /var/log/pgsqld.log -o -i =20 /usr/local/pgsql/bin/createdb management <-- fails here /usr/local/pgsql/bin/createdb test <-- fails here =20 =20 Any help on what's causing this behavior and how to fix is appreciated. =20 -Allen =20 =20 =20 +=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D+ pgsqld.log: =20 2004-02-02 17:33:00 [1555] LOG: database system is ready 2004-02-02 17:33:01 [1557] LOG: connection received: host=3D[local] 2004-02-02 17:33:01 [1557] LOG: connection authorized: user=3Dpgsql database=3Dtemplate1 2004-02-02 17:33:01 [1557] LOG: query: begin; select getdatabaseencoding(); commit 2004-02-02 17:33:01 [1557] LOG: duration: 0.035097 sec 2004-02-02 17:33:01 [1557] LOG: query: SELECT d.datname as "Name", u.usename as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding" FROM pg_catalog.pg_database d LEFT JOIN pg_catalog.pg_user u ON d.datdba =3D u.usesysid ORDER BY 1; 2004-02-02 17:33:01 [1557] LOG: duration: 0.024846 sec 2004-02-02 17:33:01 [1596] LOG: connection received: host=3D[local] 2004-02-02 17:33:01 [1596] LOG: connection authorized: user=3Dpgsql database=3Dtemplate1 2004-02-02 17:33:01 [1596] LOG: query: begin; select getdatabaseencoding(); commit 2004-02-02 17:33:01 [1596] LOG: duration: 0.000521 sec 2004-02-02 17:33:01 [1596] LOG: query: SET autocommit TO 'on';CREATE DATABASE "management" 2004-02-02 17:33:01 [1621] LOG: connection received: host=3D[local] 2004-02-02 17:33:01 [1621] LOG: connection authorized: user=3Dpgsql database=3Dtemplate1 2004-02-02 17:33:01 [1621] LOG: query: begin; select getdatabaseencoding(); commit 2004-02-02 17:33:01 [1621] LOG: duration: 0.000509 sec 2004-02-02 17:33:01 [1621] LOG: query: SET autocommit TO 'on';CREATE USER "management" CREATEDB CREATEUSER 2004-02-02 17:33:01 [1626] LOG: connection received: host=3D[local] 2004-02-02 17:33:01 [1626] LOG: connection authorized: user=3Dpgsql database=3Dtemplate1 2004-02-02 17:33:01 [1626] LOG: query: begin; select getdatabaseencoding(); commit 2004-02-02 17:33:01 [1626] LOG: duration: 0.000509 sec 2004-02-02 17:33:01 [1626] LOG: query: SET autocommit TO 'on';CREATE DATABASE "test" 2004-02-02 17:33:01 [1626] ERROR: CREATE DATABASE: source database "template1" is being accessed by other users 2004-02-02 17:33:01 [1631] LOG: connection received: host=3D[local] 2004-02-02 17:33:01 [1631] LOG: connection authorized: user=3Dpgsql database=3Dmanagement ***************************************************************************= *********** This email message and any files transmitted with it contain confidential i= nformation intended only for the person(s) to whom this email message is ad= dressed. If you have received this email message in error, please notify th= e sender immediately by telephone or email and destroy the original message= without making a copy. Thank you. ***************************************************************************= ***********
> /var/log/pgsqld.log -o -i > > > > /usr/local/pgsql/bin/createdb management <-- fails here > > /usr/local/pgsql/bin/createdb test <-- fails here > > > > > > Any help on what's causing this behavior and how to fix is appreciated. > > > > -Allen > > > > > > > > +======================================================================= > ===================+ > > pgsqld.log: > > > > 2004-02-02 17:33:00 [1555] LOG: database system is ready > > 2004-02-02 17:33:01 [1557] LOG: connection received: host=[local] > > 2004-02-02 17:33:01 [1557] LOG: connection authorized: user=pgsql > database=template1 > > 2004-02-02 17:33:01 [1557] LOG: query: begin; select > getdatabaseencoding(); commit > > 2004-02-02 17:33:01 [1557] LOG: duration: 0.035097 sec > > 2004-02-02 17:33:01 [1557] LOG: query: SELECT d.datname as "Name", > > u.usename as "Owner", > > pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding" > > FROM pg_catalog.pg_database d > > LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid > > ORDER BY 1; > > 2004-02-02 17:33:01 [1557] LOG: duration: 0.024846 sec > > 2004-02-02 17:33:01 [1596] LOG: connection received: host=[local] > > 2004-02-02 17:33:01 [1596] LOG: connection authorized: user=pgsql > database=template1 > > 2004-02-02 17:33:01 [1596] LOG: query: begin; select > getdatabaseencoding(); commit > > 2004-02-02 17:33:01 [1596] LOG: duration: 0.000521 sec > > 2004-02-02 17:33:01 [1596] LOG: query: SET autocommit TO 'on';CREATE > DATABASE "management" > > 2004-02-02 17:33:01 [1621] LOG: connection received: host=[local] > > 2004-02-02 17:33:01 [1621] LOG: connection authorized: user=pgsql > database=template1 > > 2004-02-02 17:33:01 [1621] LOG: query: begin; select > getdatabaseencoding(); commit > > 2004-02-02 17:33:01 [1621] LOG: duration: 0.000509 sec > > 2004-02-02 17:33:01 [1621] LOG: query: SET autocommit TO 'on';CREATE > USER "management" CREATEDB CREATEUSER > > 2004-02-02 17:33:01 [1626] LOG: connection received: host=[local] > > 2004-02-02 17:33:01 [1626] LOG: connection authorized: user=pgsql > database=template1 > > 2004-02-02 17:33:01 [1626] LOG: query: begin; select > getdatabaseencoding(); commit > > 2004-02-02 17:33:01 [1626] LOG: duration: 0.000509 sec > > 2004-02-02 17:33:01 [1626] LOG: query: SET autocommit TO 'on';CREATE > DATABASE "test" > > 2004-02-02 17:33:01 [1626] ERROR: CREATE DATABASE: source database > "template1" is being accessed by other users > > 2004-02-02 17:33:01 [1631] LOG: connection received: host=[local] > > 2004-02-02 17:33:01 [1631] LOG: connection authorized: user=pgsql > database=management > > ************************************************************************************** > This email message and any files transmitted with it contain confidential information intended only for the person(s) towhom this email message is addressed. If you have received this email message in error, please notify the sender immediatelyby telephone or email and destroy the original message without making a copy. Thank you. > ************************************************************************************** > > >
On Mon, 2 Feb 2004, Hamedany, Allen wrote: > Running postgresql v7.3 on RH7.3 linux; db creation fails and is > reproducible after a fresh image install. Any subsequent rebooting seems > to work fine. > > We start up Postgres, create users and db using a Java (IBM1.4.1) class. > The following commands are executed in the following order: > > /usr/local/pgsql/bin/pg_ctl -w start -D /var/lib/pgsql -l > /var/log/pgsqld.log -o -i > > /usr/local/pgsql/bin/createdb management <-- fails here > /usr/local/pgsql/bin/createdb test <-- fails here > > 2004-02-02 17:33:01 [1626] LOG: query: SET autocommit TO 'on';CREATE > DATABASE "test" > > 2004-02-02 17:33:01 [1626] ERROR: CREATE DATABASE: source database > "template1" is being accessed by other users This is showing that the second createdb call is failing with the error that there is an open connection to template1. In your case I wonder if even though the "createdb management" client has exited allowing the code to proceed to "createdb test" call, the backend associated with this connection may not have completely shut down yet. What happens if you add a sleep 5 call in between the createdb calls? Kris Jurka
Kris Jurka <books@ejurka.com> writes: > What happens if you add a sleep 5 call in between the createdb calls? Even a fractional-second sleep would likely do it. I have traced through scenarios like this in the past, and AFAICT the exiting backend is not doing anything wrong or even using an unreasonable amount of CPU time before it quits. It simply isn't getting scheduled to run because for one reason or another the kernel gives time slices to the foreground clients and the new backend first. regards, tom lane