Re: backup postgresql with snapshot AWS

On 1/19/2018 08:00, David Steele wrote:
> On 1/19/18 8:32 AM, flumbador(at)virgilio(dot)it wrote:
>> I have a postgresql in Cloud AWS and I want to build the backup strategy
>> described below.
>>
>> The postgresql has PGDATA, Tablespace and XLOG on a Physical Volume,
>> mapping a single EBS disk.
>> The WAL archive filesystem is on a separate Physical Volume, mapping a
>> separate EBS disk.
>> OS is SLES 11sp4 and PG is 9.4
>>
>> The Backup procedure is:
>>
>> 1) Start Backup
>> 2) "AWS EBS Snapshot" of the Physical Volume containing PGDATA,
>> Tablespace and XLOG
>> 3) Stop Backup
>> 4) "AWS EBS Snapshot" of the Physical Volume containing WAL archived
>>
>> To allow PITR the WAL archive are also archived with a filesystem backup
>>
>> Is someone using a similar backup strategy?
> A strategy like this is posted to the lists from time to time.
>
>> Are there tips or warning that I should be aware of?
> Rolling your own backup solution is generally not a good idea. It's a
> lot harder to get right than it seems and an incorrect solution is often
> not discovered until it is too late.
>
> Since you are on AWS you should consider using a backup solution like
> pgBackRest or WAL-G that support backups to S3.
>
> Regards,
Seconded.

Clouded-based "snapshots" are not necessarily atomic.  On a *local* disk
(on your own hardware) a zfs snapshot where *all* of the postgres data
is on the same filesystem being snapshotted (NOT descendants of it) is
probably safe, because the operation is allegedly atomic.  So in such an
environment a "zfs snapshot" followed by "zfs send" should be ok.

But this is not necessarily true in the case of a cloud-based storage
system no matter who's it is, and if it's not true then you are likely
to find out the hard way that your "snapshot" was really not much
different than a live block copy or "rsync" equivalent -- which is
definitely *not* safe.

The problem with believing that these tools are atomic in a cloud
environment is that when you find out they're not it's usually after
some sort of event where you really *need* that backup to be good.

IMHO you need to use the postgres tools to either make/stream a backup
so the database can do its thing to make sure you have a consistent copy
or use pg_dump and friends (e.g. pg_dumpall.)

--
Karl Denninger
karl(at)denninger(dot)net <mailto:karl(at)denninger(dot)net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/