Thread: Starting new cluster from base backup

On 02/16/2015 11:31 AM, Guillaume Drolet wrote:
> Dear listers,
>
> I want to move a cluster from one machine to another. I used
> pg_basebackup to create an archive and copied/extracted it over the old
> PGDATA location on the new machine (the server was stopped). If I start
> pgsql I get these messages in my log file:
>
> 2015-02-16 14:29:12 EST LOG:  database system was interrupted; last
> known up at 2015-02-07 06:31:41 EST
> 2015-02-16 14:29:12 EST LOG:  invalid checkpoint record
> 2015-02-16 14:29:12 EST FATAL:  could not locate required checkpoint record
> 2015-02-16 14:29:12 EST HINT:  If you are not restoring from a backup,
> try removing the file "E:/data/backup_label".
> 2015-02-16 14:29:12 EST LOG:  startup process (PID 3148) exited with
> exit code 1
> 2015-02-16 14:29:12 EST LOG:  aborting startup due to startup process
> failure
>
> I assume this is due to the fact the pg_xlog folder is empty (this is
> how pg_basebackup makes it in the archive) and that I haven't supplied a
> recovery.conf file with the restore restore_command = 'copy
> "E:\\archivedir\\%f" "%p"', and the archived WAL files.
>
> Now my question is: it this a correct way of moving a cluster between
> machines?
>
> If yes, what WAL files will I put in pg_xlog? Would I have needed to
> copy those that were in the old machine right after the base backup?
>
> If this is not the right way to do it, what is the best way?

http://www.postgresql.org/docs/9.3/interactive/app-pgbasebackup.html

"
-X method
--xlog-method=method

     Includes the required transaction log files (WAL files) in the
backup. This will include all transaction logs generated during the
backup. If this option is specified, it is possible to start a
postmaster directly in the extracted directory without the need to
consult the log archive, thus making this a completely standalone backup
....
"

There is more under -X, so I would read the whole section.
>
> Thanks a lot for your help,
>
> Guillaume
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 02/17/2015 06:54 AM, Guillaume Drolet wrote:
> Adrian: thanks for this information.
>
> I tried running pg_basebackup in plain format with option -X stream
> (pg_basebackup -D "F:\208376PT\db" -X stream -l "208376PT17022015" -U
> postgres -P) but I got the message:
>
> pg_basebackup: directory "E:\Data\Database" exists but is not empty"
>
> I creatde a tablespace using CREATE TABLESPACE at the location mentioned
> in the message. According to what I read online about this, this message
> is issued when a tablespace was created under PGDATA. In my case, only
> the directory junction pointing to my tablespace (on a different drive
> than PGDATA) exists under PGDATA, not the tablespace itself.

I believe all pg_basebackup cares about is whether the directory or not.
It does not do any sort of further investigation to determine what is in
the directory.

>
> The only way I can run pg_basebackup with WAL files is with option -Ft
> and -X fetch. I'd much prefer using plain mode since my 670 GB
> tablespace takes a lot of time to extract when tarred. Is there another
> way to approach this?
>
> Thanks.
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 02/17/2015 06:54 AM, Guillaume Drolet wrote:
> Adrian: thanks for this information.
>
> I tried running pg_basebackup in plain format with option -X stream
> (pg_basebackup -D "F:\208376PT\db" -X stream -l "208376PT17022015" -U
> postgres -P) but I got the message:
>
> pg_basebackup: directory "E:\Data\Database" exists but is not empty"
>
> I creatde a tablespace using CREATE TABLESPACE at the location mentioned
> in the message. According to what I read online about this, this message
> is issued when a tablespace was created under PGDATA. In my case, only
> the directory junction pointing to my tablespace (on a different drive
> than PGDATA) exists under PGDATA, not the tablespace itself.
>
> The only way I can run pg_basebackup with WAL files is with option -Ft
> and -X fetch. I'd much prefer using plain mode since my 670 GB
> tablespace takes a lot of time to extract when tarred. Is there another
> way to approach this?

All I can come up with at the moment:

So what is the path on the original machine and can it be replicated on
the new machine, at least temporarily?

I'm thinking if the path can be replicated, let pg_basebackup write to
it and then create the tablespace you want and do ALTER TABLE SET
TABLESPACE to move the tables. You would also need to do this for indexes.

>
> Thanks.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 02/18/2015 04:26 AM, Guillaume Drolet wrote:
>
>
> 2015-02-17 17:14 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>
>     On 02/17/2015 06:54 AM, Guillaume Drolet wrote:
>
>         Adrian: thanks for this information.
>
>         I tried running pg_basebackup in plain format with option -X stream
>         (pg_basebackup -D "F:\208376PT\db" -X stream -l
>         "208376PT17022015" -U
>         postgres -P) but I got the message:
>
>         pg_basebackup: directory "E:\Data\Database" exists but is not empty"
>
>         I creatde a tablespace using CREATE TABLESPACE at the location
>         mentioned
>         in the message. According to what I read online about this, this
>         message
>         is issued when a tablespace was created under PGDATA. In my
>         case, only
>         the directory junction pointing to my tablespace (on a different
>         drive
>         than PGDATA) exists under PGDATA, not the tablespace itself.
>
>         The only way I can run pg_basebackup with WAL files is with
>         option -Ft
>         and -X fetch. I'd much prefer using plain mode since my 670 GB
>         tablespace takes a lot of time to extract when tarred. Is there
>         another
>         way to approach this?
>
>
>     All I can come up with at the moment
>
>     So what is the path on the original machine and can it be replicated
>     on the new machine, at least temporarily?
>
>
> The path on the original (i.e. source) machine is:
> "E:\Data\Database\PG_9.3_201306121\.."
>
>
>     I'm thinking if the path can be replicated, let pg_basebackup write
>     to it and then create the tablespace you want and do ALTER TABLE SET
>     TABLESPACE to move the tables. You would also need to do this for
>     indexes.
>
>
> Not sure I understand when you say "let pg_basebackup write to it". This
> tablespace already exists on the source machine so cannot be written
> over. It needs to be written in the backup so that I can than recreate
> it on the destination machine.

So is E:\ a network drive shared by both machines?

>
> Anyway, in the end I want to move the database that's in that tablespace
> back to pg_default. I see two possibilities:
>
> 1) Moving it now, before taking the base backup, using ALTER DATABASE
> mydb SET TABLESPACE pg_default; Then I assume I should be able to use -X
> stream and plain format with pg_basebackup.
>
> Or
>
> 2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X
> stream and plain format, copy the tablespace from the source to the
> destination machine. Create a new symbolic link in data/pg_tblspc on the
> new machine and point it to the copied tablespace.
>
> Are these two approaches feasible?

I would say 1 would be more feasible then 2. If you use 2, delete the
symlink and do the backup, what happens with any dependencies between
objects in the default tablespace and the one you cut out? Also the
pg_basebackup will be taking a backup of one part of the cluster at one
point in time and the copy of the remote tablespace will possibly be at
another point in time. I do no see that ending well.

>
>
>
>
>
>         Thanks.
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 02/18/2015 10:24 AM, Guillaume Drolet wrote:
>
>
> 2015-02-18 11:06 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>

>
>     So is E:\ a network drive shared by both machines?
>
>
> No, E:\ is a local drive on which I created a tablespace, in order to
> have enough space for my database. In my current setup on the source
> machine, PGDATA is in the default PGSQL installation on the OS disk so
> space is limited. On the destination machine, PGDATA will be on a
> different, larger disk than the OS disk.

So is there an E:\ drive available on the destination machine?

>
>
>
>         Anyway, in the end I want to move the database that's in that
>         tablespace
>         back to pg_default. I see two possibilities:
>
>         1) Moving it now, before taking the base backup, using ALTER
>         DATABASE
>         mydb SET TABLESPACE pg_default; Then I assume I should be able
>         to use -X
>         stream and plain format with pg_basebackup.
>
>         Or
>
>         2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup
>         with -X
>         stream and plain format, copy the tablespace from the source to the
>         destination machine. Create a new symbolic link in
>         data/pg_tblspc on the
>         new machine and point it to the copied tablespace.
>
>         Are these two approaches feasible?
>
>
>     I would say 1 would be more feasible then 2. If you use 2, delete
>     the symlink and do the backup, what happens with any dependencies
>     between objects in the default tablespace and the one you cut out?
>     Also the pg_basebackup will be taking a backup of one part of the
>     cluster at one point in time and the copy of the remote tablespace
>     will possibly be at another point in time. I do no see that ending well.
>
>
> You're probably right about that. My understanding was that, since this
> is a single-user database (at least for now) on my machine, if I wasn't
> performing any query or task during the backup, then the problem you
> mentioned would in fact not be a problem.

Except Postgres performs tasks behind the scenes, so changes are
happening. There is also still the dependency issue.

>
>
>
>
>
>
>
>                  Thanks.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 02/18/2015 11:51 AM, Guillaume Drolet wrote:
>
>
> 2015-02-18 13:40 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>
>     On 02/18/2015 10:24 AM, Guillaume Drolet wrote:
>
>
>
>         2015-02-18 11:06 GMT-05:00 Adrian Klaver
>         <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.__com
>         <mailto:adrian.klaver@aklaver.com>>>:
>
>
>
>              So is E:\ a network drive shared by both machines?
>
>
>         No, E:\ is a local drive on which I created a tablespace, in
>         order to
>         have enough space for my database. In my current setup on the source
>         machine, PGDATA is in the default PGSQL installation on the OS
>         disk so
>         space is limited. On the destination machine, PGDATA will be on a
>         different, larger disk than the OS disk.
>
>
>     So is there an E:\ drive available on the destination machine?
>
>
> Yes there is an E:\ drive available on the destination machine. But for
> now, these two machines don't communicate. I take the backup on a hot
> swap disk (F:\) and then swap it into the destination machine.
> Ultimately when my database will be running on the destination machine,
> I'll connect to it from other machines in my local network.

So if I understand correctly you have:

1) On source machine a directory E:\Data\Database.
2) On the source machine in Postgres you have a created a tablespace
that points at E:\Data\Database.
3) On destination machine you have an E:\ drive also.

Then have you tried:

1) Create \Data\Database directory under E:\ on the destination machine.
2) Do the pg_basebackup.


>
>
> Can't the dependency issue be fixed by creating a new junction in
> data/pg_tblspc that would point to the relocated tablespace?

The docs say you can:

http://www.postgresql.org/docs/9.3/static/manage-ag-tablespaces.html

"The directory $PGDATA/pg_tblspc contains symbolic links that point to
each of the non-built-in tablespaces defined in the cluster. Although
not recommended, it is possible to adjust the tablespace layout by hand
by redefining these links. Under no circumstances perform this operation
while the server is running. Note that in PostgreSQL 9.1 and earlier you
will also need to update the pg_tablespace catalog with the new
locations. (If you do not, pg_dump will continue to output the old
tablespace locations.)"


I have not done it and I see the "Although not recommended.." part
above, so I would say that is a last resort solution.

>
>
>
>
>
>
>
>
>
>
>                           Thanks.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 02/18/2015 01:48 PM, Guillaume Drolet wrote:
>
>

>
>
>     So if I understand correctly you have:
>
>     1) On source machine a directory E:\Data\Database.
>     2) On the source machine in Postgres you have a created a tablespace
>     that points at E:\Data\Database.
>     3) On destination machine you have an E:\ drive also.
>
> You're correct
>
>     Then have you tried:
>
>     1) Create \Data\Database directory under E:\ on the destination machine.
>
>     2) Do the pg_basebackup.
>
>
> I'm not sure I understand why, at this moment in the sequence of
> operation, I would create \Data\Database under E:\ on the destination
> machine.
> pg_basebackup, when run on the source DB on the source machine, has no
> idea about the destination machine. Maybe you're confused with the F:\
> drive, which is the drive on which I tried to save my base backup with
> the command:

I am confused, but not about F:\ drive:). My confusion was on where the
error "directory "E:\Data\Database" exists but is not empty" occurred. I
just ran a test. So the issue is in plain mode pg_basebackup does the
binary copy to F:\208376PT\db which is fine. The problem is that it can
still see E:\Data\Database on the source machine, so when it tries to
set up the copy of the tablespace it sees that the directory is not
empty and stops. So the only way this going to work in 9.3 with plain is
to copy not to F:\ but to the destination machine directly. I am
guessing that is not possible?  It works in the tar case because the
tablespace directory gets renamed.

>
> pg_basebackup -D "F:\208376PT\db" -X stream -l "208376PT17022015" -U
> postgres -P
>
> This drive (F:\) is not the destination machine, it's a swappable disk I
> use to move my base backup from one machine (the source) to another (the
> destination).

>
>
>     I have not done it and I see the "Although not recommended.." part
>     above, so I would say that is a last resort solution.
>
>
> I confirm this method works. I've done it in the past using the steps in
> this blog and its comments:
>
> http://www.databasesoup.com/2013/11/moving-tablespaces.html

Interesting post, I missed it the first time around. Seems worth a try.
>
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com