0

We perform (Point in time recovery) PITR backup on postgresql.

We use pg_basebackup for daily snapshots and recovery_command for incremental backup.

So, we have a big table with logs in the database (about 70 Gb). We don't want to back it up every day.

How can we use all benefits of PITR and exclude one table from daily snapshot?

Tom O'Connor
  • 27,480
  • 10
  • 73
  • 148
user184843
  • 1

1 Answers1

1

No, this is not possible even on the database level.

pg_basebackup and PITR back up only the entire database cluster. There is no support for excluding parts of it. This is often requested and might be added in future, but it's complicated to get right with the way PITR is implemented.

If you want selective backups you will need to use dumps.

(Valid up to Pg 9.3, check the docs to see if this changed if you have a newer version)

Craig Ringer
  • 11,083
  • 9
  • 40
  • 61
  • Thank you! So, can we do something like incremental backups with dumps? – user184843 Aug 08 '13 at 08:23
  • @user184843 Nope. At this point you pretty much get incremental *or* selective, not both. – Craig Ringer Aug 08 '13 at 08:24
  • 1
    One possibility would be to split the massive table out onto a separate cluster, but this would probably be a PITA for PITR ;) – Tom O'Connor Aug 08 '13 at 08:40
  • Yeah, this is one of those things it'd be lovely to improve, but nobody with the expertise to do it has the time to do it, and nobody is currently willing to fund it in order to give those people the time. – Craig Ringer Aug 08 '13 at 08:50