Re: Starting new cluster from base backup

This provides part of the answer to my previous post, from the 9.4 doc
(although I'm running 9.3 but I guess the second phrase in the paragraph
applies to my case):

Tablespaces will in plain format by default be backed up to the same path
they have on the server, unless the option --tablespace-mapping is used.
Without this option, running a plain format base backup on the same host as
the server will not work if tablespaces are in use, because the backup
would have to be written to the same directory locations as the original
tablespaces.

I know the -T option is not available in 9.3. Is there another way to
circumvent the problem and still be able to backup using -X stream and
plain format when tablespace have been created elsewhere?

Thanks!

2015-02-17 9:54 GMT-05:00 Guillaume Drolet <droletguillaume(at)gmail(dot)com>:

> 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?
>
> Thanks.
>
>
>
> 2015-02-16 15:21 GMT-05:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:
>
> 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(dot)klaver(at)aklaver(dot)com
>>
>
>