pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL 6+ messages / 3 participants [nested] [flat]
* several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL @ 2013-08-22 15:53 Patrick Dung <patrick_dkt@yahoo.com.hk> 2013-08-23 08:14 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Albe Laurenz <laurenz.albe@wien.gv.at> 0 siblings, 1 reply; 6+ messages in thread From: Patrick Dung @ 2013-08-22 15:53 UTC (permalink / raw) To: pgsql-admin Hi all, I have some questions about backup on PostgreSQL: 1) pg_dumpall I am sure that this is asked in somewhere: why the pg_dumpall does not support custom or tar format like pg_dump. I have heard that the custom format could be faster and may generate a smaller dump file. Is the feature enhancement being developed? 2) pg_start_backup in postgresql 8.4 After issuing pg_start_backup, we can take the file based backup (by tar/snapshot) on the postgresql data directory. If the use of file system snapshot is not possible. It is possible that there is file changes (added or file size changed) between the pg_start_backup and pg_stop_backup. Is the backup consistent? 3) extension of 2): If the postgresql version is 9.2 Is pg_basebackup better than pg_start_backup + file based backup + pg_stop_backup? 4) For the WAL backup in postgresql 8.4 After the archive mode is on and WAL is backup, how do I remove the old WAL files? How about newer version (for example 9.2?) Thanks and regards, Patrick ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL 2013-08-22 15:53 several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> @ 2013-08-23 08:14 ` Albe Laurenz <laurenz.albe@wien.gv.at> 2013-08-26 05:23 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> 0 siblings, 1 reply; 6+ messages in thread From: Albe Laurenz @ 2013-08-23 08:14 UTC (permalink / raw) To: Patrick Dung <patrick_dkt@yahoo.com.hk>; pgsql-admin Patrick Dung wrote: > I have some questions about backup on PostgreSQL: > > 1) pg_dumpall > I am sure that this is asked in somewhere: why the pg_dumpall does not support custom or tar format > like pg_dump. > I have heard that the custom format could be faster and may generate a smaller dump file. > Is the feature enhancement being developed? I suspect that the reason is that pg_dumpall just calls pg_dump and concatenates the output, and that probably only works in text format. > 2) pg_start_backup in postgresql 8.4 > After issuing pg_start_backup, we can take the file based backup (by tar/snapshot) on the postgresql > data directory. > If the use of file system snapshot is not possible. I don't understand this sentence. > It is possible that there is file changes (added or file size changed) between the pg_start_backup and > pg_stop_backup. Yes. > Is the backup consistent? The tar or snapshot itself will not be consistent, it will have to be recovered at least until the end on the online backup. > 3) extension of 2): If the postgresql version is 9.2 > Is pg_basebackup better than pg_start_backup + file based backup + pg_stop_backup? Technically they are equivalent. pg_basebackup may be easier to use, the other option may provide more flexibility. One difference is that there can be many pg_basebackups running at the same time. > 4) For the WAL backup in postgresql 8.4 > After the archive mode is on and WAL is backup, how do I remove the old WAL files? > How about newer version (for example 9.2?) You never touch the files in pg_xlog on a running server. You remove old WAL archives when you don't need them any more. Usually that is when there is no older base backup. Yours, Laurenz Albe -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL 2013-08-22 15:53 several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> 2013-08-23 08:14 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Albe Laurenz <laurenz.albe@wien.gv.at> @ 2013-08-26 05:23 ` Patrick Dung <patrick_dkt@yahoo.com.hk> 2013-08-26 08:30 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Albe Laurenz <laurenz.albe@wien.gv.at> 2013-08-27 19:31 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Kevin Grittner <kgrittn@ymail.com> 0 siblings, 2 replies; 6+ messages in thread From: Patrick Dung @ 2013-08-26 05:23 UTC (permalink / raw) To: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin Hi Able, Please see the reply in-line: Thanks, Patrick Dung ________________________________ From: Albe Laurenz <laurenz.albe@wien.gv.at> To: Patrick Dung <patrick_dkt@yahoo.com.hk>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org> Sent: Friday, August 23, 2013 4:14 PM Subject: Re: [ADMIN] several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL >Patrick Dung wrote: >> I have some questions about backup on PostgreSQL: >> >> 1) pg_dumpall >> I am sure that this is asked in somewhere: why the pg_dumpall does not support custom or tar format >> like pg_dump. >> I have heard that the custom format could be faster and may generate a smaller dump file. >> Is the feature enhancement being developed? > >I suspect that the reason is that pg_dumpall just calls pg_dump >and concatenates the output, and that probably only works >in text format. I would like to see the pg_dumpall support the custom/tar format. >> 2) pg_start_backup in postgresql 8.4 >> After issuing pg_start_backup, we can take the file based backup (by tar/snapshot) on the postgresql >> data directory. >> If the use of file system snapshot is not possible. > >I don't understand this sentence. After calling pg_start_backup, we can make backup by tar on PGDATA root directory. Or we can take file system snapshot on PGDATA partition and make the backup on the snapshot. I suppose the latter one is more consistent than the first one. >> It is possible that there is file changes (added or file size changed) between the pg_start_backup and >> pg_stop_backup. > >Yes. > >> Is the backup consistent? > >The tar or snapshot itself will not be consistent, it will have to be recovered >at least until the end on the online backup. I should ask: is the backup crash consistent? Which means it is consistent at the time that the pg_start_backup is run. >> 4) For the WAL backup in postgresql 8.4 >> After the archive mode is on and WAL is backup, how do I remove the old WAL files? >> How about newer version (for example 9.2?) > >You never touch the files in pg_xlog on a running server. > >You remove old WAL archives when you don't need them any more. >Usually that is when there is no older base backup. > Let me ask the question in this way: 4) For the WAL backup in postgresql 8.4 After setting the archive mode to on. And the WAL files is copied to another directory eg. PGDATA\wal. How do we remove the old WAL files in PGDATA\wal? We are not taking about PGDATA\pg_xlog. How about newer version (for example 9.2?) >Yours, >Laurenz Albe > >-- >Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-admin > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL 2013-08-22 15:53 several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> 2013-08-23 08:14 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Albe Laurenz <laurenz.albe@wien.gv.at> 2013-08-26 05:23 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> @ 2013-08-26 08:30 ` Albe Laurenz <laurenz.albe@wien.gv.at> 1 sibling, 0 replies; 6+ messages in thread From: Albe Laurenz @ 2013-08-26 08:30 UTC (permalink / raw) To: Patrick Dung <patrick_dkt@yahoo.com.hk>; pgsql-admin > Patrick Dung wrote: >>> I have some questions about backup on PostgreSQL: >>> >>> 1) pg_dumpall >>> I am sure that this is asked in somewhere: why the pg_dumpall does not support custom or tar format >>> like pg_dump. >>> I have heard that the custom format could be faster and may generate a smaller dump file. >>> Is the feature enhancement being developed? >> >> I suspect that the reason is that pg_dumpall just calls pg_dump >> and concatenates the output, and that probably only works >> in text format. > > I would like to see the pg_dumpall support the custom/tar format. I think that will not happen for the reason stated above. I concur that it would be nice. You can do it yourself by using pg_dumpall to just dump the global data (switch -g) and then call "pgdump -Fc" for all the databases in the cluster. >>> Is the backup consistent? >> >> The tar or snapshot itself will not be consistent, it will have to be recovered >> at least until the end on the online backup. > > I should ask: is the backup crash consistent? > Which means it is consistent at the time that the pg_start_backup is run. I am not sure that I understand you right, but let me explain: You cannot perform crash recovery with a backup unless it was taken with a truly atomic snapshot of everything at once. In all other cases, the individual files in the backup are not consistent in ways that crash recovery cannot repair, and even if you manage to get the backup running using crash recovery (by removing the "backup_label" file), you will end up with a corrupt database that will eat and destroy your data. On the other hand, the cluster from which the backup is being taken is fully operational and consistent. Does that answer your question? >>> 4) For the WAL backup in postgresql 8.4 >>> After the archive mode is on and WAL is backup, how do I remove the old WAL files? >>> How about newer version (for example 9.2?) >> >> You never touch the files in pg_xlog on a running server. >> >> You remove old WAL archives when you don't need them any more. >> Usually that is when there is no older base backup. >> > > Let me ask the question in this way: > > 4) For the WAL backup in postgresql 8.4 > After setting the archive mode to on. > And the WAL files is copied to another directory eg. PGDATA\wal. > How do we remove the old WAL files in PGDATA\wal? We are not taking about PGDATA\pg_xlog. > > How about newer version (for example 9.2?) That is up to you in any version of PostgreSQL. PostgreSQL does not know how long you need to keep your archived WAL files, so it will never delete them. You can use cron jobs or similar things to remove WAL archives older than a certain time, or you can use tools like Barman (http://www.pgbarman.org/) that manage your backups for you. Yours, Laurenz Albe -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL 2013-08-22 15:53 several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> 2013-08-23 08:14 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Albe Laurenz <laurenz.albe@wien.gv.at> 2013-08-26 05:23 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> @ 2013-08-27 19:31 ` Kevin Grittner <kgrittn@ymail.com> 2013-09-01 09:33 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> 1 sibling, 1 reply; 6+ messages in thread From: Kevin Grittner @ 2013-08-27 19:31 UTC (permalink / raw) To: Patrick Dung <patrick_dkt@yahoo.com.hk>; Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin Patrick Dung <patrick_dkt@yahoo.com.hk> wrote: > Albe Laurenz <laurenz.albe@wien.gv.at> >> Patrick Dung wrote: >>> It is possible that there is file changes (added or file size >>> changed) between the pg_start_backup and pg_stop_backup. >> >> Yes. >> >>> Is the backup consistent? >> >> The tar or snapshot itself will not be consistent, it will have >> to be recovered at least until the end on the online backup. > > I should ask: is the backup crash consistent? PITR restore procedures will use the crash recovery mechanism to make the database consistent, if that's what you mean. > Which means it is consistent at the time that the pg_start_backup > is run. No. It will be consistent with the time that pg_stop_backup was run, or any later point in time that you choose, as long as you have WAL to that point in time. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL 2013-08-22 15:53 several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> 2013-08-23 08:14 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Albe Laurenz <laurenz.albe@wien.gv.at> 2013-08-26 05:23 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> 2013-08-27 19:31 ` Re: several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Kevin Grittner <kgrittn@ymail.com> @ 2013-09-01 09:33 ` Patrick Dung <patrick_dkt@yahoo.com.hk> 0 siblings, 0 replies; 6+ messages in thread From: Patrick Dung @ 2013-09-01 09:33 UTC (permalink / raw) To: Kevin Grittner <kgrittn@ymail.com>; Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin 'Crash consistent' is a term I heard in some oracle white papers.. If I am correct, it means the database would be consistent after the crash recovery is done. >> Which means it is consistent at the time that the pg_start_backup >> is run. >No. It will be consistent with the time that pg_stop_backup was >run, or any later point in time that you choose, as long as you >have WAL to that point in time. In the manual, we where told to do three steps: 1) pg_start_backup 2) backup by tar 3) pg_stop_backup If the backup is consistent when pg_stop_backup is called, I think it worth to mention it in the manual. Thanks and regards, Patrick Dung ________________________________ From: Kevin Grittner <kgrittn@ymail.com> To: Patrick Dung <patrick_dkt@yahoo.com.hk>; Albe Laurenz <laurenz.albe@wien.gv.at>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org> Sent: Wednesday, August 28, 2013 3:31 AM Subject: Re: [ADMIN] several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> wrote: > Albe Laurenz <laurenz.albe@wien.gv.at> >> Patrick Dung wrote: >>> It is possible that there is file changes (added or file size >>> changed) between the pg_start_backup and pg_stop_backup. >> >> Yes. >> >>> Is the backup consistent? >> >> The tar or snapshot itself will not be consistent, it will have >> to be recovered at least until the end on the online backup. > > I should ask: is the backup crash consistent? PITR restore procedures will use the crash recovery mechanism to make the database consistent, if that's what you mean. > Which means it is consistent at the time that the pg_start_backup > is run. No. It will be consistent with the time that pg_stop_backup was run, or any later point in time that you choose, as long as you have WAL to that point in time. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-09-01 09:33 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2013-08-22 15:53 several questions about pg_dumpall, pg_start_backup, pg_basebackup and WAL Patrick Dung <patrick_dkt@yahoo.com.hk> 2013-08-23 08:14 ` Albe Laurenz <laurenz.albe@wien.gv.at> 2013-08-26 05:23 ` Patrick Dung <patrick_dkt@yahoo.com.hk> 2013-08-26 08:30 ` Albe Laurenz <laurenz.albe@wien.gv.at> 2013-08-27 19:31 ` Kevin Grittner <kgrittn@ymail.com> 2013-09-01 09:33 ` Patrick Dung <patrick_dkt@yahoo.com.hk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox