Re: [GENERAL] pg_basebackup ----xlog-method=stream

[Ian Harding]

> On Apr 21, 2017, at 3:29 PM, Michael Paquier  
> wrote:
> 
>> On Sat, Apr 22, 2017 at 3:02 AM, Ian Harding  wrote:
>> Am I misunderstanding how this works?  I have WAL archiving set up, so the
>> files are available, but I wanted them included in the backup.
> 
> Please note that if you have a WAL archive available, you may not even
> need to have --xlog-method=stream, which is useful to have fully
> safe-contailed backups. For the error regarding the missing WAL
> segment, it could happen if Postgres completes two checkpoints when
> taking the backup. One thing that you could use is a replication slot
> that gives the guarantee that segments are retained. Those have been
> integrated in pg_basebackup with 9.6.

Yeah. I really want to use replication slots but put the server into production 
without setting the appropriate config settings. I need to schedule some 
downtime for a restart. 

> -- 
> Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_basebackup ----xlog-method=stream

[Michael Paquier]

On Sat, Apr 22, 2017 at 3:02 AM, Ian Harding  wrote:
> Am I misunderstanding how this works?  I have WAL archiving set up, so the
> files are available, but I wanted them included in the backup.

Please note that if you have a WAL archive available, you may not even
need to have --xlog-method=stream, which is useful to have fully
safe-contailed backups. For the error regarding the missing WAL
segment, it could happen if Postgres completes two checkpoints when
taking the backup. One thing that you could use is a replication slot
that gives the guarantee that segments are retained. Those have been
integrated in pg_basebackup with 9.6.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_basebackup ----xlog-method=stream

[Adrian Klaver]

On 04/21/2017 11:02 AM, Ian Harding wrote:

I used this command to set up a streaming replica and it worked perfectly.


Was that the full command?

If not can you show the full command you used?

Postgres version?



I tried to run it to create an online backup of the master on that
replica for backup purposes and it seems not to have worked as well.

I thought that streaming the WAL would eliminate the need to keep tons
of WAL around, that since they were streamed concurrently with the file
they would be in place.  However, I got

pg_basebackup: could not get transaction log end position from server:
ERROR:  requested WAL segment 0001496F00BF has already been
removed


Where was pg_basebackup running, on the master or somewhere else?

The pg_basebackup was from the same Postgres version as the master?



The ...backup file in the archive logs folder of the master looks like this"

# cat 0001496F00BF.00578DF8.backup
START WAL LOCATION: 496F/BF578DF8 (file 0001496F00BF)
STOP WAL LOCATION: 4971/11FC6528 (file 000149710011)
CHECKPOINT LOCATION: 496F/C1ACE0D8
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2017-04-21 01:08:38 PDT
LABEL: full-20170421
STOP TIME: 2017-04-21 06:31:28 PDT

so the "...already been removed" message is referring to the very first
file.

The files were definitely being streamed, I restarted and on the master
I see this:

[root@db20 ~]# ps aux | grep stream
postgres 113855  0.4  0.0 274955352 2932 ?  Ss   10:53   0:00
postgres: wal sender process postgres 192.168.4.21(51292) streaming
4972/7FBC22B8
root 115958  0.0  0.0 112652   956 pts/2S+   10:56   0:00 grep
--color=auto stream
postgres 142072  0.3  0.0 274955348 3160 ?  Ss   Apr20   3:47
postgres: wal sender process postgres 192.168.4.21(51240) streaming
4972/7FBC22B8


although the --verbose output from pg_basebackup mentions starting the
streaming, it doesn't mention the file names like it does for the
datadir files.

Am I misunderstanding how this works?  I have WAL archiving set up, so
the files are available, but I wanted them included in the backup.

Thanks!

- Ian



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_basebackup ----xlog-method=stream

[Ian Harding]

I used this command to set up a streaming replica and it worked perfectly.

I tried to run it to create an online backup of the master on that replica
for backup purposes and it seems not to have worked as well.

I thought that streaming the WAL would eliminate the need to keep tons of
WAL around, that since they were streamed concurrently with the file they
would be in place.  However, I got

pg_basebackup: could not get transaction log end position from server:
ERROR:  requested WAL segment 0001496F00BF has already been
removed

The ...backup file in the archive logs folder of the master looks like this"

# cat 0001496F00BF.00578DF8.backup
START WAL LOCATION: 496F/BF578DF8 (file 0001496F00BF)
STOP WAL LOCATION: 4971/11FC6528 (file 000149710011)
CHECKPOINT LOCATION: 496F/C1ACE0D8
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2017-04-21 01:08:38 PDT
LABEL: full-20170421
STOP TIME: 2017-04-21 06:31:28 PDT

so the "...already been removed" message is referring to the very first
file.

The files were definitely being streamed, I restarted and on the master I
see this:

[root@db20 ~]# ps aux | grep stream
postgres 113855  0.4  0.0 274955352 2932 ?  Ss   10:53   0:00 postgres:
wal sender process postgres 192.168.4.21(51292) streaming 4972/7FBC22B8
root 115958  0.0  0.0 112652   956 pts/2S+   10:56   0:00 grep
--color=auto stream
postgres 142072  0.3  0.0 274955348 3160 ?  Ss   Apr20   3:47 postgres:
wal sender process postgres 192.168.4.21(51240) streaming 4972/7FBC22B8


although the --verbose output from pg_basebackup mentions starting the
streaming, it doesn't mention the file names like it does for the datadir
files.

Am I misunderstanding how this works?  I have WAL archiving set up, so the
files are available, but I wanted them included in the backup.

Thanks!

- Ian