<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Il 20/03/2017 17:45, Ben Chobot ha
scritto:<br>
</div>
<blockquote
cite="mid:F78A1CFE-BCE4-4BBB-9B44-9D491010B280(at)silentmedia(dot)com"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<br class="">
<div>
<blockquote type="cite" class="">
<div class="">On Mar 20, 2017, at 6:31 AM, Moreno Andreo <<a
moz-do-not-send="true"
href="mailto:moreno(dot)andreo(at)evolu-s(dot)it" class="">moreno(dot)andreo(at)evolu-s(dot)it</a>>
wrote:</div>
<br class="Apple-interchange-newline">
<div class="">
<div class="">Hi everyone,<br class="">
<br class="">
I have my PostgreSQL 9.5 server running on a VM
instance on Google Compute Engine (Google Cloud Platform)
on Debian Jessie (8.3), and I have another dedicated VM
instance that, every night at 3.00, takes a snapshot of
the whole disk, without stopping the PG instance itself.<br
class="">
Snapshots are stored and kept by Google in an incremental
way, and we keep the last 2 weeks of history.<br class="">
The question is: Keeping all two weeks worth of pg_xlog
files, I don't think I still need a periodic pg_basebackup
to perform PITR, do I?<br class="">
</div>
</div>
</blockquote>
</div>
<br class="">
<div class="">You need a base backup to apply your wals to. So
long as you have one from after the start of your wal stream,
you should be good for PITR.</div>
</blockquote>
Hmmm... I went back in the docs and noticed I missed something. To
achieve PITR, the cluster needs to checkpoint, and this can be
obtained with the pg_start_backup() function... so if I try to get a
snapshot and start a recover (creating recovery.conf etc.) it will
not even start recovery, right?<br>
<br>
Now I'm gonna try 2 approaches:<br>
1. (straightforward) barman with basebackup and WAL archiving<br>
2. (GoogleCloud-oriented) disk snapshot between pg_start_backup and
pg_stop_backup (so the snapshot is taken just after the checkpoint),
WAL archiving<br>
<br>
I will report the results.<br>
<br>
<blockquote
cite="mid:F78A1CFE-BCE4-4BBB-9B44-9D491010B280(at)silentmedia(dot)com"
type="cite">
<div class=""> That said, replaying 2 weeks of wal files can take
a long time. For that reason alone, it might well make sense to
have more than a single basebackup snapshot.</div>
</blockquote>
That's right, my (wrong) thought was to have a snapshot per day and
all 14 days worth of WALs, but after the meeting with the Google
Specialist, I'm oriented to make a base backup per day and then
store it on Nearline, making it expire after 14 days. Same for WAL
files.<br>
<br>
<blockquote
cite="mid:F78A1CFE-BCE4-4BBB-9B44-9D491010B280(at)silentmedia(dot)com"
type="cite">
<div class="">Also, I cannot stress enough how important it is to
actually test your recovery strategy. </div>
</blockquote>
I totally agree... that's why I'm here. I don't want to prepare a
backup strategy when I already need to recover....<br>
<br>
Thanks<br>
Moreno.<br>
<br>
</body>
</html>