Thread: Restarting DB after moving to another drive
I am working on windows and I had to move my database on another hard drive after the original one started overheating. In order to move the DB I did the following…
-Stop postgresql-x64-9.3 service – and wait until there were no more system access to on the original drive
-Copy the entire content of the original drive to the new one (the drive is dedicated to the DB)
-Turn off the original hard drive and reassign the old drive letter to the new one
-Restart the DB
I tried to connect to the database by using PgAdmin III and I got the following error message:
“Could not read symbolic link “pg_tblspc/100589”: Invalid argument”
I concluded something went wrong and I decided to get back to the old drive. I stopped the DB, turned off the new drive, turned on the old one and tried to restart the postgresql service but it does not start anymore. The only message I get is:
“Postgresql-x64-9.3 service on local computer started and then stopped. Some services stop automatically if they are not in use by other services or programs”
I am the only user of the database
Hoping someone can help
Daniel
On 11/05/2015 12:03, Daniel Begin wrote: > I am working on windows and I had to move my database on another hard > drive after the original one started overheating. In order to move the > DB I did the following… > > > > -Stop postgresql-x64-9.3 service – and wait until there were no more > system access to on the original drive > > -Copy the entire content of the original drive to the new one (the drive > is dedicated to the DB) > > -Turn off the original hard drive and reassign the old drive letter to > the new one > > -Restart the DB > > > > I tried to connect to the database by using PgAdmin III and I got the > following error message: > > “Could not read symbolic link “pg_tblspc/100589”: Invalid argument” > > > > I concluded something went wrong and I decided to get back to the old > drive. I stopped the DB, turned off the new drive, turned on the old one > and tried to restart the postgresql service but it does not start > anymore. The only message I get is: > > “Postgresql-x64-9.3 service on local computer started and then stopped. > Some services stop automatically if they are not in use by other > services or programs” Hi there, Sounds like you're on Windows - you can get more information from PostgreSQL's own logs, which by default on Windows are in a directory called pg_log under the data directory. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
I just get it back running with the old drive - was some Windows hidden behavior! However, does someone could tell me what went wrong with the procedure I used to move the DB? And/or what procedure I should have used in order to get it right? Daniel -----Original Message----- From: Raymond O'Donnell [mailto:rod@iol.ie] Sent: May-11-15 07:50 To: Daniel Begin; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive On 11/05/2015 12:03, Daniel Begin wrote: > I am working on windows and I had to move my database on another hard > drive after the original one started overheating. In order to move the > DB I did the following. > > > > -Stop postgresql-x64-9.3 service - and wait until there were no more > system access to on the original drive > > -Copy the entire content of the original drive to the new one (the > drive is dedicated to the DB) > > -Turn off the original hard drive and reassign the old drive letter to > the new one > > -Restart the DB > > > > I tried to connect to the database by using PgAdmin III and I got the > following error message: > > "Could not read symbolic link "pg_tblspc/100589": Invalid argument" > > > > I concluded something went wrong and I decided to get back to the old > drive. I stopped the DB, turned off the new drive, turned on the old > one and tried to restart the postgresql service but it does not start > anymore. The only message I get is: > > "Postgresql-x64-9.3 service on local computer started and then stopped. > Some services stop automatically if they are not in use by other > services or programs" Hi there, Sounds like you're on Windows - you can get more information from PostgreSQL's own logs, which by default on Windows are in a directory called pg_log under the data directory. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 11/05/2015 13:38, Daniel Begin wrote: > I just get it back running with the old drive - was some Windows hidden > behavior! > > However, does someone could tell me what went wrong with the procedure I > used to move the DB? > And/or what procedure I should have used in order to get it right? I've never done it myself, but I understand that one way to do it is with tablespaces: http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html As I understand it, you create a new tablespace on the new disk, then move your database objects to it. How that plays with Windows, I've no idea.... I'd guess that changing the drive letter might well cause Bad Things to happen. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Thank for the link! Just to make sure I understand properly... When I installed Postgresql, I set $PGDATA to point on my old drive and I must now move everything on the new one. In order to move everything on the new drive I must create a tablespace on the new drive and then explicitly define this tablespace as the new location of... - the postgres database - my personal database - pg_default - pg_global Anything I missed or put in the wrong sequence? Regards, Daniel -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Raymond O'Donnell Sent: May-11-15 09:19 To: Daniel Begin; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive On 11/05/2015 13:38, Daniel Begin wrote: > I just get it back running with the old drive - was some Windows > hidden behavior! > > However, does someone could tell me what went wrong with the procedure > I used to move the DB? > And/or what procedure I should have used in order to get it right? I've never done it myself, but I understand that one way to do it is with tablespaces: http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html As I understand it, you create a new tablespace on the new disk, then move your database objects to it. How that plays with Windows, I've no idea.... I'd guess that changing the drive letter might well cause Bad Things to happen. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
( OOps, forgot to cc the list again in previous, quoting all message for context ). On Mon, May 11, 2015 at 3:14 PM, Daniel Begin <jfd553@hotmail.com> wrote: > Makes sense considering the error message! Maybe an image backup would make the job... I do not know how they are, made in windows, but it should. Also, IIRC there were versions of the GNU unix common utilities ( cp, rm, mv, ln ) for windows which correctly worked with links, if you can get hold of a reaosnable windows tar a 'tar c | tar x' should do the trick too. Also, look at the copy command help, in linux I know cp -a ( for archive ) does normally the trick. Francisco Olarte. > -----Original Message----- > From: Francisco Olarte [mailto:folarte@peoplecall.com] > Sent: May-11-15 08:55 > To: Daniel Begin > Subject: Re: [GENERAL] Restarting DB after moving to another drive > > Hi Daniel. > > On Mon, May 11, 2015 at 2:38 PM, Daniel Begin <jfd553@hotmail.com> wrote: >> I just get it back running with the old drive - was some Windows >> hidden behavior! >> However, does someone could tell me what went wrong with the procedure >> I used to move the DB? > > It's been more than a decade since I've used windows, but from my past experience I'll bet you did not copy the DB right( you did not copy the symbolic links, IIRC windows normal tools did not cope with them too well, but that was on w2k,YMMV ). > > Francisco Olarte. >
Hi Daniel. On Mon, May 11, 2015 at 4:42 PM, Daniel Begin <jfd553@hotmail.com> wrote: > Just to make sure I understand properly... > When I installed Postgresql, I set $PGDATA to point on my old drive and I > must now move everything on the new one. > In order to move everything on the new drive I must create a tablespace on > the new drive and then explicitly define this tablespace as the new location > of... > - the postgres database > - my personal database > - pg_default > - pg_global > Anything I missed or put in the wrong sequence? I do not think it is that easy. You can move nearly everything, but it will be slow an you are not going to get rid of the old disk. One question, ¿ How big/critical is your database ? because all your problems can be solved with a dump/initdb/restore easily, and if you can leave it doing overnight it is th easier way. Also, I've moved directories across disks before, and it has worked, but I use linux which is much simpler, and just did an stop / cp / change pgdata / restart, I suppose windows must have comparable ways. Francisco Olarte.
Francisco wrote: "How big/critical is your database?" How big? According to PgAdmin my personal database is about 2TB... How critical? Well, about a year of work!-) 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” Considering both drives are identical, could an image backup have done the job properly instead of a plane copy? Daniel -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Francisco Olarte Sent: May-11-15 11:01 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 4:42 PM, Daniel Begin <jfd553@hotmail.com> wrote: > Just to make sure I understand properly... > When I installed Postgresql, I set $PGDATA to point on my old drive > and I must now move everything on the new one. > In order to move everything on the new drive I must create a > tablespace on the new drive and then explicitly define this tablespace > as the new location of... > - the postgres database > - my personal database > - pg_default > - pg_global > Anything I missed or put in the wrong sequence? I do not think it is that easy. You can move nearly everything, but it will be slow an you are not going to get rid of theold disk. One question, ¿ How big/critical is your database ? because all your problems can be solved with a dump/initdb/restore easily,and if you can leave it doing overnight it is th easier way. Also, I've moved directories across disks before, andit has worked, but I use linux which is much simpler, and just did an stop / cp / change pgdata / restart, I suppose windowsmust have comparable ways. 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
Hi, have you checked that the links in $PGDATA\pg_tblspc on the new drive are valid ? They possibly still point to the old drive. I guess you have to correct them per hand before starting the moved DB. regards, Marc Mamin ________________________________________ Von: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org]" im Auftrag von "Daniel Begin [jfd553@hotmail.com] Gesendet: Montag, 11. Mai 2015 17:30 An: 'Francisco Olarte' Cc: rod@iol.ie; pgsql-general@postgresql.org Betreff: Re: [GENERAL] Restarting DB after moving to another drive Francisco wrote: "How big/critical is your database?" How big? According to PgAdmin my personal database is about 2TB... How critical? Well, about a year of work!-) 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” Considering both drives are identical, could an image backup have done the job properly instead of a plane copy? Daniel -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Francisco Olarte Sent: May-11-15 11:01 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 4:42 PM, Daniel Begin <jfd553@hotmail.com> wrote: > Just to make sure I understand properly... > When I installed Postgresql, I set $PGDATA to point on my old drive > and I must now move everything on the new one. > In order to move everything on the new drive I must create a > tablespace on the new drive and then explicitly define this tablespace > as the new location of... > - the postgres database > - my personal database > - pg_default > - pg_global > Anything I missed or put in the wrong sequence? I do not think it is that easy. You can move nearly everything, but it will be slow an you are not going to get rid of theold disk. One question, ¿ How big/critical is your database ? because all your problems can be solved with a dump/initdb/restore easily,and if you can leave it doing overnight it is th easier way. Also, I've moved directories across disks before, andit has worked, but I use linux which is much simpler, and just did an stop / cp / change pgdata / restart, I suppose windowsmust have comparable ways. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Interesting, The symbolic links on the old drive -still used by the DB- look like windows' shortcuts to parent folder, while they are empty folders in the copy of the database I have on the new drive... When I do a plane copy of those links on another drive I also get the same empty folders. I am getting closer to the initial problem, and closer to the solution! Maybe an image backup of the drive would have done the job properly instead of using a plane copy? Daniel -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Marc Mamin Sent: May-11-15 13:10 To: Daniel Begin; 'Francisco Olarte' Cc: rod@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive Hi, have you checked that the links in $PGDATA\pg_tblspc on the new drive are valid ? They possibly still point to the old drive. I guess you have to correct them per hand before starting the moved DB. regards, Marc Mamin ________________________________________ Von: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org]" im Auftrag von "Daniel Begin [jfd553@hotmail.com] Gesendet: Montag, 11. Mai 2015 17:30 An: 'Francisco Olarte' Cc: rod@iol.ie; pgsql-general@postgresql.org Betreff: Re: [GENERAL] Restarting DB after moving to another drive Francisco wrote: "How big/critical is your database?" How big? According to PgAdmin my personal database is about 2TB... How critical? Well, about a year of work!-) 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 Considering both drives are identical, could an image backup have done the job properly instead of a plane copy? Daniel -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Francisco Olarte Sent: May-11-15 11:01 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 4:42 PM, Daniel Begin <jfd553@hotmail.com> wrote: > Just to make sure I understand properly... > When I installed Postgresql, I set $PGDATA to point on my old drive > and I must now move everything on the new one. > In order to move everything on the new drive I must create a > tablespace on the new drive and then explicitly define this tablespace > as the new location of... > - the postgres database > - my personal database > - pg_default > - pg_global > Anything I missed or put in the wrong sequence? I do not think it is that easy. You can move nearly everything, but it will be slow an you are not going to get rid of the old disk. One question, ¿ How big/critical is your database ? because all your problems can be solved with a dump/initdb/restore easily, and if you can leave it doing overnight it is th easier way. Also, I've moved directories across disks before, and it has worked, but I use linux which is much simpler, and just did an stop / cp / change pgdata / restart, I suppose windows must have comparable ways. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 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.
On 11 May 2015 at 19:01, Daniel Begin <jfd553@hotmail.com> wrote: > Interesting, > > The symbolic links on the old drive -still used by the DB- look like > windows' shortcuts to parent folder, while they are empty folders in the > copy of the database I have on the new drive... > > When I do a plane copy of those links on another drive I also get the same > empty folders. I am getting closer to the initial problem, and closer to > the solution! > > Maybe an image backup of the drive would have done the job properly instead > of using a plane copy? > Daniel > Robocopy can copy symlinks properly in Windows, if you use the /SL option. Something like robocopy /E /SL sourcedir targetdir See https://technet.microsoft.com/en-GB/library/cc733145.aspx.
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
On 5/13/2015 11:06 AM, Daniel Begin wrote: > 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? those are created when you initdb the target cluster prior to restoring the pg_dumpall output. -- john r pierce, recycling bits in santa cruz
Thank John, I was naive hoping I just had to run "$ psql -f pgdumpall.output postgres"... but it makes sense since the drive on which it is all stored won't be there anymore. So let's read initdb documentation :-) Daniel -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce Sent: May-13-15 14:16 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive On 5/13/2015 11:06 AM, Daniel Begin wrote: > 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? those are created when you initdb the target cluster prior to restoring the pg_dumpall output. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Hi Daniel: On Wed, May 13, 2015 at 8:06 PM, Daniel Begin <jfd553@hotmail.com> wrote: ... > - I still have a lot to learn on database management (it was simpler on user's side!-) Yep, we all do, even if we've been using it since it was called Postgres. > Fortunately, I have found that pg_dumpall could do the job (I did not have a problem with it, I just did not know aboutit!-). If you didn't know about it I'll urge you to take the manual and do a sequential reading ( maybe not in full, this would take a long time, but at least skim through all of it sequentially, it's full of very interesting info and it's very useful and when you hit a problem you'll probably know there is something for it and search for it ). For me the manual is one of the major points for using pg . pg_dumpall is a fundamental tool for backups, as it's the only one that dumps the global objects. Good luck. Francisco Olarte.
I use this script, run nightly via crontab, on my small pgsql servers.
it runs as the postgres user.
#!/bin/bash
/usr/pgsql-9.3/bin/pg_dumpall --globals-only | gzip >
/home2/backups/pgsql/pgdumpall.globals.`date +\%a`.sql.gz
for db in $(psql -tc "select datname from pg_database where not
datistemplate"); do \
pg_dump -Fc -f
/home2/backups/pgsql/pgdump.$i.$(date +\%a).dump $db
done
this creates a globals-only backup and a seperate backup of each
database, for each day of the week.
--
john r pierce, recycling bits in santa cruz
Bonjour Francisco. Skimming the documentation sequentially is a cleaver advice, especially since the doc is much of the time well done and exhaustive.Unfortunately, even if I actually did it about 1 year ago, it seems this specific item slipped out of my mind:-( About dump/restore operation, restoring the database cluster is running for 24hrs now (psql -f pgdumpallOutputfile postgres).Since it took 13hrs to dump the cluster, I begin to wonder how long it is going to take to restore it... My main concern is about how the indexes are managed in dump/restore operations. I understand that pg_dumpall actually usespg_dump where the doc says "Post-data items include definitions of indexes, triggers..." I would not worry if the docsaid that indexes are simply copied but it says "includes definition of indexes". Since some of the indexes took days to build... does someone could confirm indexes are rebuilt instead of copied? If indexes are actually rebuilt, why should it be done that way? - There must be good reason! Best regards, Daniel -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Francisco Olarte Sent: May-15-15 05:12 To: Daniel Begin Cc: rod@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive Hi Daniel: On Wed, May 13, 2015 at 8:06 PM, Daniel Begin <jfd553@hotmail.com> wrote: ... > - I still have a lot to learn on database management (it was simpler > on user's side!-) Yep, we all do, even if we've been using it since it was called Postgres. > Fortunately, I have found that pg_dumpall could do the job (I did not have a problem with it, I just did not know aboutit!-). If you didn't know about it I'll urge you to take the manual and do a sequential reading ( maybe not in full, this wouldtake a long time, but at least skim through all of it sequentially, it's full of very interesting info and it's veryuseful and when you hit a problem you'll probably know there is something for it and search for it ). For me the manual is one of the major points for using pg . pg_dumpall is a fundamental tool for backups, as it's the onlyone that dumps the global objects. Good luck. 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
HI Daniel: On Fri, May 15, 2015 at 5:35 PM, Daniel Begin <jfd553@hotmail.com> wrote: > Bonjour Francisco. Buenos dias. > Skimming the documentation sequentially is a cleaver advice, especially since the doc is much of the time well done andexhaustive. Unfortunately, even if I actually did it about 1 year ago, it seems this specific item slipped out of my mind:-( Bad luck, you managed to get one of the most important commands, but it's solved now. > About dump/restore operation, restoring the database cluster is running for 24hrs now (psql -f pgdumpallOutputfile postgres).Since it took 13hrs to dump the cluster, I begin to wonder how long it is going to take to restore it... Much longer than this, especially if as you say below you have a lot of indexes. It's one of the reasons many of us do not use pg_dumpall for anything but global objects, but use something like the crontab which John R. Pierce posted ( I use a similar thing, but with an intermediate script with dumps critical databases more frequently, skips recreatable ( may be someone can confirm if that word is right ? I mean test things which can be created from scratch ( as they come from a script ) ) databases and keeps several numbered copies ). Doing it this ways insures we can restore on criticality order if it needs to be done ( I even move unmodified partitions to a 'historic" schema, which gets dumped only after a change, which cuts my backups times to a tenth ) One thing. I do not know how you are restoring the database, but when doing this things we use a specially tuned postgresql.conf ( fsync off, minimal loging, lots of worrk mems and similar things, as we do not care about durability ( you can just rerun initdb and redo the restore, and there is only 1 session connected, the restoring one ). This cuts the restore times to easily a tenth, then after ending it we restart the server with the normal cong. It is a must when doing this short of things. > My main concern is about how the indexes are managed in dump/restore operations. I understand that pg_dumpall actuallyuses pg_dump where the doc says "Post-data items include definitions of indexes, triggers..." I would not worry ifthe doc said that indexes are simply copied but it says "includes definition of indexes". > Since some of the indexes took days to build... does someone could confirm indexes are rebuilt instead of copied? > If indexes are actually rebuilt, why should it be done that way? - There must be good reason! You are out of luck, and it has a reason. First, pg_dumps does not copy, it dumps. It's simpler behaviour ( text output ) just output a SQL script which recreates everything and inserts all the data ( normally using copy for speed, but it's the same as inserting ). It takes care of generating a fast script ( meaning it creates the tables, then inserts the data, then creates indexes and reactivates constraints, which is faster than defining everything and inserting with indexes and constraints actives ). The reason to do it in text mode is you can dump between different version and/or architectures, and also the dump is much smaller than the db, specially if you compress it ( I always do it, testing a bit you can always find a compresor with will lead to faster backups, as saved disk writing easily offsets compression times, specially in moder multicpu memory rich machines ). Bear in mind in many scenarios you backup a lot ( we dump some critical things hourly, even if we are using replication ) and restore nearly never, and prefer to use a couple days more for the restore than a couple hours of degraded performance every backup. This being said, if you have an 820G db ( I still do not know which size is this, I suppose it's $PGDATA footprint ) of important data ( it does not seem critical in availability, as you are taking days and still in bussiness ) and you are having these kind of problems to dump and restore and move directories in your OS, and do not know how much time it takes for backups, you have a problem. You should practice backup AND restore more, because your question indicates you MAY be backing up your data, but you have never restored a backup. Also, the text output format is really good for the global objects in pg_dumpall, but not so much for the normal databases. For this you should use the custom format, unless it is a really small db. The problem with it is it can only do a database per file, and needs pg_restore to be read ( I know those are minors ). The advantage is instead of generating a plain text dump it builds a kind of tar file with the definitions and data for every object clearly separated, so you can do partial restores or whatever thing you want ( in fact, without options and without connecting to the database pg_restore spits out the same text file that a text dump will generate ). If you had used this technique you could have restored your tables in order, or restored only the data and then reindexed them concurrently with some other ( performance degraded ) work. You can do the same thing by editing the text dump, but it gets impractical and really complex beyond a few megabytes. regards. Francisco Olarte.
Thank for that comprehensive response! And you are right about practicing restore, I never had to :-) However, I use pg_dump on a regular basis (custom format) but I did not know the difference between database/database cluster(and pg_dumpall) until I had to move everything because the PGDATA drive started overheating. Now that I better understand what is going on with backup/restore processes, and considering... - The time it is going to take to rebuild the whole cluster ; - That I am the only user of the database; - That everything was just fine with the database, except the temperature of the drive - And considering the initial concern of this tread was about bad copy of symbolic links with windows I will make another attempt to copy everything on another drive from windows, unless someone tells me it is not possible. - I will move my external tablespaces content back to pgdata and drop them for the time I copy the db to the new drive. - Doing so, I will get rid of the symbolic link (from tablespaces) from where originated the initial error message - Without symbolic links, I should be able to copy the db using standard windows commands. - Setting up the new drive's letter to the old one before restarting the db is easy -The whole process should take 12hours instead of a week. Hoping it makes sense and that I have not missed something important (again) 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-15-15 12:20 To: Daniel Begin Cc: rod@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive HI Daniel: On Fri, May 15, 2015 at 5:35 PM, Daniel Begin <jfd553@hotmail.com> wrote: > Bonjour Francisco. Buenos dias. > Skimming the documentation sequentially is a cleaver advice, > especially since the doc is much of the time well done and exhaustive. > Unfortunately, even if I actually did it about 1 year ago, it seems > this specific item slipped out of my mind :-( Bad luck, you managed to get one of the most important commands, but it's solved now. > About dump/restore operation, restoring the database cluster is running for 24hrs now (psql -f pgdumpallOutputfile postgres).Since it took 13hrs to dump the cluster, I begin to wonder how long it is going to take to restore it... Much longer than this, especially if as you say below you have a lot of indexes. It's one of the reasons many of us do notuse pg_dumpall for anything but global objects, but use something like the crontab which John R. Pierce posted ( I usea similar thing, but with an intermediate script with dumps critical databases more frequently, skips recreatable ( maybe someone can confirm if that word is right ? I mean test things which can be created from scratch ( as they come from a script ) ) databases and keeps several numberedcopies ). Doing it this ways insures we can restore on criticality order if it needs to be done ( I even move unmodifiedpartitions to a 'historic" schema, which gets dumped only after a change, which cuts my backups times to a tenth) One thing. I do not know how you are restoring the database, but when doing this things we use a specially tuned postgresql.conf( fsync off, minimal loging, lots of worrk mems and similar things, as we do not care about durability ( youcan just rerun initdb and redo the restore, and there is only 1 session connected, the restoring one ). This cuts the restore times to easily a tenth, then after ending it we restart the server with the normal cong. It is a mustwhen doing this short of things. > My main concern is about how the indexes are managed in dump/restore operations. I understand that pg_dumpall actuallyuses pg_dump where the doc says "Post-data items include definitions of indexes, triggers..." I would not worry ifthe doc said that indexes are simply copied but it says "includes definition of indexes". > Since some of the indexes took days to build... does someone could confirm indexes are rebuilt instead of copied? > If indexes are actually rebuilt, why should it be done that way? - There must be good reason! You are out of luck, and it has a reason. First, pg_dumps does not copy, it dumps. It's simpler behaviour ( text output )just output a SQL script which recreates everything and inserts all the data ( normally using copy for speed, but it's thesame as inserting ). It takes care of generating a fast script ( meaning it creates the tables, then inserts the data,then creates indexes and reactivates constraints, which is faster than defining everything and inserting with indexesand constraints actives ). The reason to do it in text mode is you can dump between different version and/or architectures, and also the dump is muchsmaller than the db, specially if you compress it ( I always do it, testing a bit you can always find a compresor withwill lead to faster backups, as saved disk writing easily offsets compression times, specially in moder multicpu memoryrich machines ). Bear in mind in many scenarios you backup a lot ( we dump some critical things hourly, even if weare using replication ) and restore nearly never, and prefer to use a couple days more for the restore than a couple hoursof degraded performance every backup. This being said, if you have an 820G db ( I still do not know which size is this, I suppose it's $PGDATA footprint ) of importantdata ( it does not seem critical in availability, as you are taking days and still in bussiness ) and you are havingthese kind of problems to dump and restore and move directories in your OS, and do not know how much time it takesfor backups, you have a problem. You should practice backup AND restore more, because your question indicates you MAYbe backing up your data, but you have never restored a backup. Also, the text output format is really good for the global objects in pg_dumpall, but not so much for the normal databases.For this you should use the custom format, unless it is a really small db. The problem with it is it can only doa database per file, and needs pg_restore to be read ( I know those are minors ). The advantage is instead of generatinga plain text dump it builds a kind of tar file with the definitions and data for every object clearly separated,so you can do partial restores or whatever thing you want ( in fact, without options and without connecting tothe database pg_restore spits out the same text file that a text dump will generate ). If you had used this technique youcould have restored your tables in order, or restored only the data and then reindexed them concurrently with some other( performance degraded ) work. You can do the same thing by editing the text dump, but it gets impractical and reallycomplex beyond a few megabytes. 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
A follow-up... As expected, I was able to copy the database cluster on the new drive during the night. I changed the drive letter to fitthe original database drive and I restarted the DB. Everything is now running on the new drive and I have been able torecreate the tablespaces. However, the time expected to run queries on some tables seems longer. - Could copying tables and indexes have had an effect on indexes? - How can I verify that some of the indexes were not corrupted? Daniel -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Daniel Begin Sent: May-15-15 15:17 To: 'Francisco Olarte' Cc: rod@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive Thank for that comprehensive response! And you are right about practicing restore, I never had to :-) However, I use pg_dump on a regular basis (custom format) but I did not know the difference between database/database cluster(and pg_dumpall) until I had to move everything because the PGDATA drive started overheating. Now that I better understand what is going on with backup/restore processes, and considering... - The time it is going to take to rebuild the whole cluster ; - That I am the only user of the database; - That everything was just fine with the database, except the temperature of the drive - And considering the initial concern of this tread was about bad copy of symbolic links with windows I will make another attempt to copy everything on another drive from windows, unless someone tells me it is not possible. - I will move my external tablespaces content back to pgdata and drop them for the time I copy the db to the new drive. - Doing so, I will get rid of the symbolic link (from tablespaces) from where originated the initial error message - Without symbolic links, I should be able to copy the db using standard windows commands. - Setting up the new drive's letter to the old one before restarting the db is easy -The whole process should take 12hoursinstead of a week. Hoping it makes sense and that I have not missed something important (again) 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-15-15 12:20 To: Daniel Begin Cc: rod@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive HI Daniel: On Fri, May 15, 2015 at 5:35 PM, Daniel Begin <jfd553@hotmail.com> wrote: > Bonjour Francisco. Buenos dias. > Skimming the documentation sequentially is a cleaver advice, > especially since the doc is much of the time well done and exhaustive. > Unfortunately, even if I actually did it about 1 year ago, it seems > this specific item slipped out of my mind :-( Bad luck, you managed to get one of the most important commands, but it's solved now. > About dump/restore operation, restoring the database cluster is running for 24hrs now (psql -f pgdumpallOutputfile postgres).Since it took 13hrs to dump the cluster, I begin to wonder how long it is going to take to restore it... Much longer than this, especially if as you say below you have a lot of indexes. It's one of the reasons many of us do notuse pg_dumpall for anything but global objects, but use something like the crontab which John R. Pierce posted ( I usea similar thing, but with an intermediate script with dumps critical databases more frequently, skips recreatable ( maybe someone can confirm if that word is right ? I mean test things which can be created from scratch ( as they come from a script ) ) databases and keeps several numberedcopies ). Doing it this ways insures we can restore on criticality order if it needs to be done ( I even move unmodifiedpartitions to a 'historic" schema, which gets dumped only after a change, which cuts my backups times to a tenth) One thing. I do not know how you are restoring the database, but when doing this things we use a specially tuned postgresql.conf( fsync off, minimal loging, lots of worrk mems and similar things, as we do not care about durability ( youcan just rerun initdb and redo the restore, and there is only 1 session connected, the restoring one ). This cuts the restore times to easily a tenth, then after ending it we restart the server with the normal cong. It is a mustwhen doing this short of things. > My main concern is about how the indexes are managed in dump/restore operations. I understand that pg_dumpall actuallyuses pg_dump where the doc says "Post-data items include definitions of indexes, triggers..." I would not worry ifthe doc said that indexes are simply copied but it says "includes definition of indexes". > Since some of the indexes took days to build... does someone could confirm indexes are rebuilt instead of copied? > If indexes are actually rebuilt, why should it be done that way? - There must be good reason! You are out of luck, and it has a reason. First, pg_dumps does not copy, it dumps. It's simpler behaviour ( text output )just output a SQL script which recreates everything and inserts all the data ( normally using copy for speed, but it's thesame as inserting ). It takes care of generating a fast script ( meaning it creates the tables, then inserts the data,then creates indexes and reactivates constraints, which is faster than defining everything and inserting with indexesand constraints actives ). The reason to do it in text mode is you can dump between different version and/or architectures, and also the dump is muchsmaller than the db, specially if you compress it ( I always do it, testing a bit you can always find a compresor withwill lead to faster backups, as saved disk writing easily offsets compression times, specially in moder multicpu memoryrich machines ). Bear in mind in many scenarios you backup a lot ( we dump some critical things hourly, even if weare using replication ) and restore nearly never, and prefer to use a couple days more for the restore than a couple hoursof degraded performance every backup. This being said, if you have an 820G db ( I still do not know which size is this, I suppose it's $PGDATA footprint ) of importantdata ( it does not seem critical in availability, as you are taking days and still in bussiness ) and you are havingthese kind of problems to dump and restore and move directories in your OS, and do not know how much time it takesfor backups, you have a problem. You should practice backup AND restore more, because your question indicates you MAYbe backing up your data, but you have never restored a backup. Also, the text output format is really good for the global objects in pg_dumpall, but not so much for the normal databases.For this you should use the custom format, unless it is a really small db. The problem with it is it can only doa database per file, and needs pg_restore to be read ( I know those are minors ). The advantage is instead of generatinga plain text dump it builds a kind of tar file with the definitions and data for every object clearly separated,so you can do partial restores or whatever thing you want ( in fact, without options and without connecting tothe database pg_restore spits out the same text file that a text dump will generate ). If you had used this technique youcould have restored your tables in order, or restored only the data and then reindexed them concurrently with some other( performance degraded ) work. You can do the same thing by editing the text dump, but it gets impractical and reallycomplex beyond a few megabytes. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general