Thread: Incremental Backup
Hi guys. How can we perform incremental backups after a full backup?
Majid Azimi wrote: > Hi guys. > > How can we perform incremental backups after a full backup? > > Incremental backup doesn't make sense in this case, because incremental backup will backup all the files that have changed since the last incremental backup of different order. In particular, that means that all tables that are being constantly used will be in both backups. PostgreSQL has no block change tracking so you can not only backup database blocks that have changed since the last backup. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
Hi,
Wouldn’t incremental backup be achieved by archiving the WAL files?
http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html
Regards
Craig
Wouldn’t incremental backup be achieved by archiving the WAL files?
http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html
Regards
Craig
On 4 October 2010 23:25, Mladen Gogala <mgogala@vmsinfo.com> wrote:
Majid Azimi wrote:Incremental backup doesn't make sense in this case, because incremental backup will backup all the files that have changed since the last incremental backup of different order. In particular, that means that all tables that are being constantly used will be in both backups. PostgreSQL has no block change tracking so you can not only backup database blocks that have changed since the last backup.Hi guys.
How can we perform incremental backups after a full backup?
--
Mladen Gogala Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Craig Barnes wrote: > Hi, > > Wouldn’t incremental backup be achieved by archiving the WAL files? > > http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html > > Regards > > Craig That depends on how do you define an incremental backup. Being a former system administrator, my definition of an incremental backup is a backup that only backs up files changed since the last backup. You will find that to be the most frequent definition of the phrase "incremental backup". In addition to that, it would be highly impractical to make a single hot backup and than have a gazillion archives to recover. What you're talking about is called hot backup and point in time recovery, but not an incremental backup. For what is worth, I find incremental backups impractical. With an incremental backup, one also has to restore the full backup and all incremental backups after it, which sort of misses the point. I do a full on-line (hot) backup weekly and a daily archive backup. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
Majid Azimi wrote: > What do you mean by "on-line (hot) backup weekly and a daily archive > backup." > > hot backup means using pg_dump? > what is archive backup? you mean WAL? The phrase "hot backup" means that the DB is open while backup is taken. When you shut the cluster down, the backup is "cold". I do it by using pg_start_backup to put the database in the "backup mode" (cause it to start logging the entire blocks in the WAL archives, not just the changes) and then back i t up with Symantec NetBackup. Strictly speaking, pg_dump is not backup since it cannot be used for recovery. The function "pg_start_backup is described here: http://developer.postgresql.org/pgdocs/postgres/functions-admin.html And yes, by the "archive backup" I mean WAL archives. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com
Hi,
Is there a way to trigger a Stored Procedure at a specific time of the day ? Lets say, every night at 2:00 AM ??
I am using PG 8.4.4
Thanks!
Arvind
Is there a way to trigger a Stored Procedure at a specific time of the day ? Lets say, every night at 2:00 AM ??
I am using PG 8.4.4
Thanks!
Arvind
On Thu, Oct 7, 2010 at 1:47 PM, Arvind Sharma <arvind321@yahoo.com> wrote: > Hi, > > Is there a way to trigger a Stored Procedure at a specific time of the day ? > Lets say, every night at 2:00 AM ?? > > I am using PG 8.4.4 > > Thanks! > Arvind > I see this in recent archives: http://archives.postgresql.org/pgsql-general/2010-09/msg00739.php make sure to read the followup. -- Mike Ellsworth
Mladen Gogala wrote: > Craig Barnes wrote: > > Hi, > > > > Wouldn?t incremental backup be achieved by archiving the WAL files? > > > > http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html > > > > Regards > > > > Craig > > That depends on how do you define an incremental backup. Being a former > system administrator, my definition of an incremental backup is a > backup that only backs up files changed since the last backup. You will > find that to be the most frequent definition of the phrase "incremental > backup". In addition to that, it would be highly impractical to make a > single hot backup and than have a gazillion archives to recover. What > you're talking about is called hot backup and point in time recovery, > but not an incremental backup. Agreed. Incremental backups are implemented by pg-rman: http://code.google.com/p/pg-rman/ > For what is worth, I find incremental backups impractical. With an > incremental backup, one also has to restore the full backup and all > incremental backups after it, which sort of misses the point. I do a > full on-line (hot) backup weekly and a daily archive backup. Also agreed. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian wrote: > > > Agreed. Incremental backups are implemented by pg-rman: > > http://code.google.com/p/pg-rman/ > > This looks extremely interesting. Any chance of having a RPM package or even make it into the distribution itself, like pg_upgrade? -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
Mladen Gogala wrote: > Bruce Momjian wrote: > > > > > > Agreed. Incremental backups are implemented by pg-rman: > > > > http://code.google.com/p/pg-rman/ > > > > > > This looks extremely interesting. Any chance of having a RPM package or > even make it into the distribution itself, like pg_upgrade? No idea. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian wrote: > Mladen Gogala wrote: > >> Bruce Momjian wrote: >> >>> Agreed. Incremental backups are implemented by pg-rman: >>> >>> http://code.google.com/p/pg-rman/ >>> >>> >>> >> This looks extremely interesting. Any chance of having a RPM package or >> even make it into the distribution itself, like pg_upgrade? >> > > No idea. > > Is there any way to build it without having the source? -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
Mladen Gogala wrote: > Bruce Momjian wrote: > > Mladen Gogala wrote: > > > >> Bruce Momjian wrote: > >> > >>> Agreed. Incremental backups are implemented by pg-rman: > >>> > >>> http://code.google.com/p/pg-rman/ > >>> > >>> > >>> > >> This looks extremely interesting. Any chance of having a RPM package or > >> even make it into the distribution itself, like pg_upgrade? > >> > > > > No idea. > > > > > Is there any way to build it without having the source? No idea. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Oct 22, 2010, at 2:39 AM, Mladen Gogala wrote: > Bruce Momjian wrote: >> Mladen Gogala wrote: >> >>> Bruce Momjian wrote: >>> >>>> Agreed. Incremental backups are implemented by pg-rman: >>>> >>>> http://code.google.com/p/pg-rman/ >>>> >>>> >>> This looks extremely interesting. Any chance of having a RPM package or even make it into the distribution itself, likepg_upgrade? >>> >> >> No idea. >> >> > Is there any way to build it without having the source? > If you have installed postgresql, then you have to do set the path of pg_config in PATH Variable, then you can use make USE_PGXS=1 make USE_PGXS=1 install. Installation Steps also mention in README. -Vibhor.