Re: Restarting DB after moving to another drive

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 directories to exist before the restore". External tablespaces directories are easy to create but what's about pg_default and pg_global tablespace since I never created specific tablespaces for them?

Thank for your patience
Daniel

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Francisco Olarte
Sent: May-13-15 06:57
To: Daniel Begin
Cc: rod(at)iol(dot)ie; pgsql-general(at)postgresql(dot)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(at)hotmail(dot)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 see wheter 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 it offline 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 see if they look right ( ls -l does it in linux, I suppose the windows ls or whatever tool you use to list a directory in windows 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 as I 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've done a sector-by-sector copy ( only on nearly full disks, otherwise cp is faster ) to a bigger disks and the OS didn't notice after the swap ( and neither Postgres ). On identical disks, a sector copy should work, but I wouldn't be surprised if 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, it is 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 is due 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 you more, also bear in mind I abandoned windows in 2001 an use pgadmin only for seeing queries in a grid, I do all my admin tasks 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(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general