Thread: 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/<newdb-OID>".
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/<newdb-OID>
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?
"Gnanakumar" <gnanam@zoniac.com> 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
Le 02/04/2010 15:38, Kevin Grittner a écrit : > "Gnanakumar" <gnanam@zoniac.com> 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. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> "Gnanakumar" <gnanam@zoniac.com> 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
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] 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 écrit : > "Gnanakumar" <gnanam@zoniac.com> 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. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
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.
On Fri, Apr 2, 2010 at 10:53 PM, Gnanakumar <gnanam@zoniac.com> wrote: > I cannot use screen, since my database server is hosted remotely. I have > only command-line access to my server by PuTTY. screen is a cli application. Indispensable.
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 <gnanam@zoniac.com> wrote: > I cannot use screen, since my database server is hosted remotely. I have > only command-line access to my server by PuTTY. screen is a cli application. Indispensable.
man screen should get you started. On Sat, Apr 3, 2010 at 12:01 AM, Gnanakumar <gnanam@zoniac.com> 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 <gnanam@zoniac.com> wrote: >> I cannot use screen, since my database server is hosted remotely. I have >> only command-line access to my server by PuTTY. > > screen is a cli application. Indispensable. > > > -- When fascism comes to America, it will be intolerance sold as diversity.
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" <gnanam@zoniac.com> 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
Thank you very much for sharing this "screen" command. That's really helpful. -----Original Message----- From: Guillaume Lelarge [mailto:guillaume@lelarge.info] 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 écrit : > "Gnanakumar" <gnanam@zoniac.com> 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. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com