Re: Restarting DB after moving to another drive - Mailing list pgsql-general

From Daniel Begin
Subject Re: Restarting DB after moving to another drive
Date
Msg-id COL129-DS1E4AB2F4CE0D1D4985D9094D90@phx.gbl
Whole thread Raw
In response to Re: Restarting DB after moving to another drive  (Francisco Olarte <folarte@peoplecall.com>)
Responses Re: Restarting DB after moving to another drive  (John R Pierce <pierce@hogranch.com>)
Re: Restarting DB after moving to another drive  (Francisco Olarte <folarte@peoplecall.com>)
List pgsql-general
Thank Francisco,
After having poked around for a while to move it with Windows, I think I've finally understood a couple of things...

- Since my old drive contains the PGDATA folder, I actually need to move the whole PostgreSQL cluster (almost).
- Windows backup/copy/xcopy failed to do the job, even using options that should have copied symbolic links properly.
- I am not comfortable about creating the symbolic links manually because the links created by PostgreSQL...
   a) Actually seem to be junctions instead of symbolic links as stated
   b) Use target folders that look like [\??\M:\pgsqlData] instead of [M:\pgsqlData] as I was expected
   c) The impacts of not creating them properly are not clear to me
- I still have a lot to learn on database management (it was simpler on user's side!-)

Fortunately, I have found that pg_dumpall could do the job (I did not have a problem with it, I just did not know about
it!-). 

I am then currently running pg_dumpall on the database. I will restore the cluster on the new drive once completed.
However,there is still something obscure in the process. The doc says "pg_dumpall requires all needed tablespace
directoriesto exist before the restore". External tablespaces directories are easy to create but what's about
pg_defaultand pg_global  tablespace since I never created specific tablespaces for them?  

Thank for your patience
Daniel

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Francisco Olarte
Sent: May-13-15 06:57
To: Daniel Begin
Cc: rod@iol.ie; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restarting DB after moving to another drive

Hi Daniel:

On Mon, May 11, 2015 at 5:30 PM, Daniel Begin <jfd553@hotmail.com> wrote:
> How big? According to PgAdmin my personal database is about 2TB...

I do not know what pgAdmin reports, I was thinking in how bick a dump ( using -Fc, which compresses on th fly ) is, to
seewheter you can restore. 

> How critical? Well, about a year of work!-)

Well, my fault. I know its valuable, or you wouldn't be bothering with all of this. By critical I meant if you can take
itoffline for the time needed to do a dump/restore, but I suppose after all this time you have a problem with this
approach.

> Francisco wrote: "just did a stop/cp/change pgdata /restart, I suppose windows must have comparable ways"
> This is what I have just tried when I got “Could not read symbolic link “pg_tblspc/100589”: Invalid argument”

I do not know windows, but I suppose you must check this error, I mean, check the link in the original and the copy and
seeif they look right ( ls -l does it in linux, I suppose the windows ls or whatever tool you use to list a directory
inwindows can do it too ). 

> Considering both drives are identical, could an image backup have done the job properly instead of a plane copy?

I know the builtin windows copy ( in cmd ) was not good copying complex setups. cp for windows did it in my times, but
asI say it was a long time ago and windows is too complex for me. I also do not know what an image backup is. In Linux
I'vedone a sector-by-sector copy ( only on nearly full disks, otherwise cp is faster ) to a bigger disks and the OS
didn'tnotice after the swap ( and neither Postgres ). On identical disks, a sector copy should work, but I wouldn't be
surprisedif windows kept some kind of info and need some other adjustement. 

Anyway, what I mean is a normal copy should suffice, but windows is complex and a normal copy is very difficult to make
(at least for me ). But the error says it is getting a problem with a readlink. The first thing should be checking it,
itis simple in Linux, you just do ls l on both of them and you are done, you even have a readlink program to use in
scripts,I assume windows has a similar command, just check it. Given the error i would bet for an EINVAL which normally
isdue to the named file ( pg_tblspc/100589 ) not being a symlink, the windows equivalent to ls -l should tell you that
(my thought is somehow it is a directory, or a plain file, but you should be able to find it easily ). I cannot tell
youmore, also bear in mind I abandoned windows in 2001 an use pgadmin only for seeing queries in a grid, I do all my
admintasks with psql/pg_dump/pg_restore, so I cannot tell you zilch about windows or pgadmin specific stuff. 

Regards.
    Francisco Olarte.


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



pgsql-general by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: Getting a leading zero on negative intervals with to_char?
Next
From: John R Pierce
Date:
Subject: Re: Restarting DB after moving to another drive