Database clone stuck in the middle

Hi,

I'm using PostgreSQL 8.2 on RedHat ES 5.

I connect to our database which is hosted remotely using PuTTY thro SSH
protocol.
As part of my maintenance activity, I was cloning the database using the
command:

CREATE DATABASE newdb WITH TEMPLATE=olddb;

While cloning was in-progress, my Internet got disconnected in the middle,
so database cloning was also stuck in the middle. Then I again logged-in to
my server and listed my databases using psql '\l' command. My newdb was not
listed here, but a new database OID folder is created in the PostgreSQL data
folder "/usr/local/pgsql/data/base/".

My questions are:
1. Can I go ahead and delete this newdb-OID manually from this location
using:
rm -fr /usr/local/pgsql/data/base/
2. Is there any implication to other databases because of manually deleting
in this way?
3. Does PostgreSQL postmaster check for anything of this newdb-OID reference
during start/stop/restart?
4. Considering this Internet disconnection problem, what is the safest way
to clone a database using CREATE DATABASE WITH TEMPLATE command, so that
this will
not happen in the future?



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database clone stuck in the middle

"Gnanakumar" wrote:

> I connect to our database which is hosted remotely using PuTTY
> thro SSH protocol.

> my Internet got disconnected in the middle,
> so database cloning was also stuck in the middle.

> 4. Considering this Internet disconnection problem, what is the
> safest way to clone a database using CREATE DATABASE WITH TEMPLATE
> command, so that this will not happen in the future?

You putty to the server and run psql from a shell on the database
server?

If so, try putting the word "nohup" in front of "psql" -- that tells
the program to keep running even if the connection is lost. (It is
short for "no hangup", I believe.)

On the other questions, I suspect that you'd be OK to delete the
partial database, as long as it doesn't show up in pg_database. A
command which doesn't run to completion is always *supposed* to
leave the database cluster in a good state. It never hurts to make
sure you've got a good backup before trying anything unusual,
however.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database clone stuck in the middle

Le 02/04/2010 15:38, Kevin Grittner a =E9crit :
> "Gnanakumar" wrote:
> =20
>> I connect to our database which is hosted remotely using PuTTY
>> thro SSH protocol. =20
> =20
>> my Internet got disconnected in the middle,
>> so database cloning was also stuck in the middle.
> =20
>> 4. Considering this Internet disconnection problem, what is the
>> safest way to clone a database using CREATE DATABASE WITH TEMPLATE
>> command, so that this will not happen in the future?
> =20
> You putty to the server and run psql from a shell on the database
> server?
> =20
> If so, try putting the word "nohup" in front of "psql" -- that tells
> the program to keep running even if the connection is lost. (It is
> short for "no hangup", I believe.)
> =20

Or use screen.


--=20
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database clone stuck in the middle

"Kevin Grittner" writes:
> "Gnanakumar" wrote:
>> my Internet got disconnected in the middle,
>> so database cloning was also stuck in the middle.

> You putty to the server and run psql from a shell on the database
> server?

> If so, try putting the word "nohup" in front of "psql" -- that tells
> the program to keep running even if the connection is lost. (It is
> short for "no hangup", I believe.)

The whole thing is quite odd: even if the client connection failed,
I'd have expected the CREATE DATABASE command to run to completion
before noticing that. (Occasionally we get dinged for the fact that
loss of connection doesn't stop the executing command, but in contexts
like this it's a good thing.) Using nohup or not on the psql process
shouldn't have made any difference. I wonder whether the OP is doing
something weird like launching the server as a subprocess of his login
shell.

Another possibility (if the source is a really big database) is that
the copy operation is actually still going on. I'd make real sure the
old session is gone before trying to manually remove the allegedly-dead
subdirectory.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database clone stuck in the middle

I cannot use screen, since my database server is hosted remotely. I have
only command-line access to my server by PuTTY.

-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]=20
Sent: Friday, April 02, 2010 7:32 PM
To: Kevin Grittner
Cc: pgsql-admin@postgresql.org; gnanam@zoniac.com
Subject: Re: [ADMIN] Database clone stuck in the middle

Le 02/04/2010 15:38, Kevin Grittner a =E9crit :
> "Gnanakumar" wrote:
>
>> I connect to our database which is hosted remotely using PuTTY
>> thro SSH protocol.
>
>> my Internet got disconnected in the middle,
>> so database cloning was also stuck in the middle.
>
>> 4. Considering this Internet disconnection problem, what is the
>> safest way to clone a database using CREATE DATABASE WITH TEMPLATE
>> command, so that this will not happen in the future?
>
> You putty to the server and run psql from a shell on the database
> server?
>
> If so, try putting the word "nohup" in front of "psql" -- that tells
> the program to keep running even if the connection is lost. (It is
> short for "no hangup", I believe.)
>

Or use screen.


--=20
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database clone stuck in the middle

Hi Tom,

> I wonder whether the OP is doing
> something weird like launching the server as a subprocess of his login
> shell.

I don't understand what you mean here. What is OP?

> Another possibility (if the source is a really big database) is that
> the copy operation is actually still going on. I'd make real sure the
> old session is gone before trying to manually remove the allegedly-dead
> subdirectory.

My database is really not very big. Usually, cloning of my database gets
completed within 5 minutes. So, I'm very sure the database clone was stuck
in the middle. Even I've waited for almost a day to confirm that copy
operation would run successfully to completion but in vain.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database clone stuck in the middle

On Fri, Apr 2, 2010 at 10:53 PM, Gnanakumar wrote:
> I cannot use screen, since my database server is hosted remotely. =A0I ha=
ve
> only command-line access to my server by PuTTY.

screen is a cli application. Indispensable.

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database clone stuck in the middle

Any pointers to relevant links of CLI application are appreciated.

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]=20
Sent: Saturday, April 03, 2010 11:15 AM
To: gnanam@zoniac.com
Cc: Guillaume Lelarge; Kevin Grittner; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Database clone stuck in the middle

On Fri, Apr 2, 2010 at 10:53 PM, Gnanakumar wrote:
> I cannot use screen, since my database server is hosted remotely. =A0I ha=
ve
> only command-line access to my server by PuTTY.

screen is a cli application. Indispensable.



--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database clone stuck in the middle

man screen

should get you started.

On Sat, Apr 3, 2010 at 12:01 AM, Gnanakumar wrote:
> Any pointers to relevant links of CLI application are appreciated.
>
> -----Original Message-----
> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Sent: Saturday, April 03, 2010 11:15 AM
> To: gnanam@zoniac.com
> Cc: Guillaume Lelarge; Kevin Grittner; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Database clone stuck in the middle
>
> On Fri, Apr 2, 2010 at 10:53 PM, Gnanakumar wrote:
>> I cannot use screen, since my database server is hosted remotely. =A0I h=
ave
>> only command-line access to my server by PuTTY.
>
> screen is a cli application. =A0Indispensable.
>
>
>



--=20
When fascism comes to America, it will be intolerance sold as diversity.

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database clone stuck in the middle

Thank you very much for sharing this "nohup" command. That's really
helpful.

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Friday, April 02, 2010 7:09 PM
To: pgsql-admin@postgresql.org; gnanam@zoniac.com
Subject: Re: [ADMIN] Database clone stuck in the middle

"Gnanakumar" wrote:

> I connect to our database which is hosted remotely using PuTTY
> thro SSH protocol.

> my Internet got disconnected in the middle,
> so database cloning was also stuck in the middle.

> 4. Considering this Internet disconnection problem, what is the
> safest way to clone a database using CREATE DATABASE WITH TEMPLATE
> command, so that this will not happen in the future?

You putty to the server and run psql from a shell on the database
server?

If so, try putting the word "nohup" in front of "psql" -- that tells
the program to keep running even if the connection is lost. (It is
short for "no hangup", I believe.)

On the other questions, I suspect that you'd be OK to delete the
partial database, as long as it doesn't show up in pg_database. A
command which doesn't run to completion is always *supposed* to
leave the database cluster in a good state. It never hurts to make
sure you've got a good backup before trying anything unusual,
however.

-Kevin


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Database clone stuck in the middle

Thank you very much for sharing this "screen" command. That's really
helpful.

-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]=20
Sent: Friday, April 02, 2010 7:32 PM
To: Kevin Grittner
Cc: pgsql-admin@postgresql.org; gnanam@zoniac.com
Subject: Re: [ADMIN] Database clone stuck in the middle

Le 02/04/2010 15:38, Kevin Grittner a =E9crit :
> "Gnanakumar" wrote:
>
>> I connect to our database which is hosted remotely using PuTTY
>> thro SSH protocol.
>
>> my Internet got disconnected in the middle,
>> so database cloning was also stuck in the middle.
>
>> 4. Considering this Internet disconnection problem, what is the
>> safest way to clone a database using CREATE DATABASE WITH TEMPLATE
>> command, so that this will not happen in the future?
>
> You putty to the server and run psql from a shell on the database
> server?
>
> If so, try putting the word "nohup" in front of "psql" -- that tells
> the program to keep running even if the connection is lost. (It is
> short for "no hangup", I believe.)
>

Or use screen.


--=20
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin