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

On Sep 11, 2013, at 4:29 PM, Gregory Haase <haaseg@onefreevoice.com> wrote:

I was trying to figure out how to get the following syntax to work:

echo "select pg_start_backup('zfs_snapshot'); \\! zfs snapshot zroot/zpgsql@test; \\ select pg_stop_backup();" | psql postgres

I do:

psql -c "select pg_start_backup('whatever');" && zfs snapshot pool/fs@sn && psql -c "select pg_stop_backup();"

That way no need to shell out from psql :)

The above command successfully starts the backup and creates the snapshot but then fails to stop the backup. I've tried various combinations of \ and \\ here with different whitespace and I just can't seem to find a combination that works. I don't understand the proper use of \\ (described as the separator metacommand).

Keep in mind that echo "\\" will actually only echo '\' because \ is a shell escape as well...

However, in my research, I noted that a bunch of people seem to just not even bother with pg_start_backup/pg_stop_backup and I guess aren't that worried about the crash recovery process if they need to perform a restore. I also find the omission of the start/stop backup functions from the File System Level Backup page: http://www.postgresql.org/docs/9.2/static/backup-file.html

Is the pg_start_backup() and pg_stop_backup() even necessary?

If all of your Postgres files are part of *the same* consistent snapshot (i.e. are on one FS that gets snapshotted), then the start/stop backup should not be necessary. It will just look like a server crash instead.

pg_start_backup is used when you do not have filesystem snapshotting available, and is described in detail on the next manual page:

http://www.postgresql.org/docs/9.2/static/continuous-archiving.html

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

Good point on not needing to shell out. I think my process was a mental
holdover from the fact that MySQL releases 'flush tables with read lock' on
client disconnect.

Typically how fast is a crash recovery for a ~1TB database with heavy OTLP
load? Are we talking several seconds, several minutes, several hours?

Thanks,

-G

On Wed, Sep 11, 2013 at 4:46 PM, Steven Schlansker <steven@likeness.com>wrote:

On Wed, Sep 11, 2013 at 8:00 PM, Gregory Haase <haaseg@onefreevoice.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.

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@khera.org> wrote: