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

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(at)likeness(dot)com>wrote:

>
> On Sep 11, 2013, at 4:29 PM, Gregory Haase <haaseg(at)onefreevoice(dot)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(at)test; \\ select pg_stop_backup();" | psql postgres
>
> I do:
>
> psql -c "select pg_start_backup('whatever');" && zfs snapshot pool/fs(at)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
>
>