Re: [PERFORM] Backup taking long time !!! - Mailing list pgsql-performance
| From | julyanto SUTANDANG |
|---|---|
| Subject | Re: [PERFORM] Backup taking long time !!! |
| Date | |
| Msg-id | CAGu3fESxPSSqbKrV-UHdVnWnLK0vYk2fTmxjNOoEtevW39j1uw@mail.gmail.com Whole thread Raw |
| In response to | [PERFORM] Backup taking long time !!! (Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>) |
| Responses |
Re: [PERFORM] Backup taking long time !!!
|
| List | pgsql-performance |
Hi Stephen,
Julyanto SUTANDANG
Equnix Business Solutions, PT
(An Open Source and Open Mind Company)
www.equnix.co.id
Pusat Niaga ITC Roxy Mas Blok C2/42. Jl. KH Hasyim Ashari 125, Jakarta Pusat
T: +6221 22866662 F: +62216315281 M: +628164858028
Caution: The information enclosed in this email (and any attachments) may be legally privileged and/or confidential and is intended only for the use of the addressee(s). No addressee should forward, print, copy, or otherwise reproduce this message in any manner that would allow it to be viewed by any individual not originally listed as a recipient. If the reader of this message is not the intended recipient, you are hereby notified that any unauthorized disclosure, dissemination, distribution, copying or the taking of any action in reliance on the information herein is strictly prohibited. If you have received this communication in error, please immediately notify the sender and delete this message.Unless it is made by the authorized person, any views expressed in this message are those of the individual sender and may not necessarily reflect the views of PT Equnix Business Solutions.
Please elaborate more of what you are saying. What i am saying is based on the Official Docs, Forum and our own test. This is what we had to do to save time, both backing up and restoring.
When PostgreSQL in the mode of Start Backup, PostgreSQL only writes to the XLOG, then you can safely rsync / copy the base data (snapshot) then later you can have full copy of snapshot backup data.
if you wanted to backup in later day, you can use rsync then it will copy faster because rsync only copy the difference, rather than copy all the data.
my latter explanation is: use pg_basebackup, it will do it automatically for you.
CMIIW,
Julyanto SUTANDANG
Equnix Business Solutions, PT
(An Open Source and Open Mind Company)
www.equnix.co.id
Pusat Niaga ITC Roxy Mas Blok C2/42. Jl. KH Hasyim Ashari 125, Jakarta Pusat
T: +6221 22866662 F: +62216315281 M: +628164858028
Caution: The information enclosed in this email (and any attachments) may be legally privileged and/or confidential and is intended only for the use of the addressee(s). No addressee should forward, print, copy, or otherwise reproduce this message in any manner that would allow it to be viewed by any individual not originally listed as a recipient. If the reader of this message is not the intended recipient, you are hereby notified that any unauthorized disclosure, dissemination, distribution, copying or the taking of any action in reliance on the information herein is strictly prohibited. If you have received this communication in error, please immediately notify the sender and delete this message.Unless it is made by the authorized person, any views expressed in this message are those of the individual sender and may not necessarily reflect the views of PT Equnix Business Solutions.
On Sun, Jan 22, 2017 at 9:55 PM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* julyanto SUTANDANG (julyanto@equnix.co.id) wrote:
> Best practice in doing full backup is using RSYNC, but before you can copy
> the DATADIR, you might you pg_start_backup to tell the server not to write
> into the DATADIR, because you are copying that data. After finished copy
> all the data in DATADIR, you can ask server to continue flushing the data
> from logs, by commanding pg_stop_backup. Remember, not to copy the XLOG
> dir.
Whoah. That is not, at all, correct, if I'm understanding what you're
suggesting.
PG most certainly *does* continue to write into the data directory even
after pg_start_backup() has been run. You *must* use archive_command or
pg_receivexlog to capture all of the WAL during the backup to have a
consistent backup.
> There are another way more simpler, which is applying command
> pg_basebackup, which actually did that way in simpler version.
pg_basebackup has options to stream the WAL during the backup to capture
it, which is how it handles that.
> if you did pg_dump, you wont get the exact copy of your data, and you will
> take longer downtime to recover the backup data. By that way, recovering is
> only starting up the postgres with that copy.
pg_dump will generally take longer to do a restore, yes. Recovering
from a backup does require that a recovery.conf exists with a
restore_command that PG can use to get the WAL files it needs, or that
all of the WAL from the backup is in pg_xlog/pg_wal.
Please do not claim that PG stops writing to the DATADIR or BASEDIR
after a pg_start_backup(), that is not correct and could lead to invalid
backups.
Thanks!
Stephen
pgsql-performance by date: