Re: ZFS snapshots - to use pg_start_backup() and pg_stop_backup() or no?

Thanks.

If anyone is interested, I added postgresql to the zfstools project and the
owner merged my pull request:
https://github.com/bdrewery/zfstools

I know there are probably 1000 ways to do this out there, but we were
already using this code to manage our MySQL zfs snapshotting process, so
extending it became the path of least resistance.

The tricky part for me was separating the pg_stop_backup command from the
first two commands so that you don't get into a situation where you start
backup, fail on zfs snapshot create, and then pg_stop_backup is not
executed. Maybe not the most elegant, but I'd rather have a failed
pg_stop_backup command than a pg_start_backup command that is left open
indefinitely.

Greg Haase

On Tue, Sep 17, 2013 at 12:33 PM, Vick Khera <vivek(at)khera(dot)org> wrote:

>
> On Wed, Sep 11, 2013 at 8:00 PM, Gregory Haase <haaseg(at)onefreevoice(dot)com>wrote:
>
>> Typically how fast is a crash recovery for a ~1TB database with heavy
>> OTLP load? Are we talking several seconds, several minutes, several hours?
>>
>
> This will depend on how fast your file system is, how frequent your
> checkpoints are (this is tunable), and how many WAL segments you allow
> (also tunable). The trade off is if you do less frequent checkpointing and
> have more WAL segments, you can boost your write speeds, but the cost is
> longer recovery. Ideally you want to tune the number of WAL segments to be
> just the right number to keep from forcing checkpoints before your
> configured timeout to run a checkpoint, and you configure your checkpoint
> time to whatever duration of time you need to keep your recovery time as
> short as you want.
>