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:
> 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
> > 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: