Обсуждение: ZFS snapshots - to use pg_start_backup() and pg_stop_backup() or no?
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
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).
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?
It would be nice to understand the proper syntax for the psql pipe, regardless whether or not it is even necessary in this case.
Thanks,
Greg Haase
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
triedvarious 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_backupand I guess aren't that worried about the crash recovery process if they need to perform
arestore. 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),
thenthe 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
manualpage:
http://www.postgresql.org/docs/9.2/static/continuous-archiving.html
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 do:
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
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 :)Keep in mind that echo "\\" will actually only echo '\' because \ is a shell escape as well...
>
> 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).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.
>
> 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?
>
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
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:
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:
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.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?