Re: Starting new cluster from base backup

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
>