Database Reference
In-Depth Information
How to do it...
To perform a differential physical backup, you can use
rsync
to compare the existing files
against the previous full backup, and then overwrite just changed data blocks. It's a bad plan
to overwrite your last backup, so keep two or more copies. An example backup schedule would
be as follows:
Day of Week
Backup Set 1
Backup Set 2
Sunday
New full backup to Set 1
New full backup to Set 2
Monday
Differential to Set 1
Differential to Set 2
Tuesday
Differential to Set 1
Differential to Set 2
Wednesday
Differential to Set 1
Differential to Set 2
Thursday
Differential to Set 1
Differential to Set 2
Friday
Differential to Set 1
Differential to Set 2
Saturday
Differential to Set 1
Differential to Set 2
You should keep at least two full backup sets.
Many large databases have tables that are insert-only. In that case, it's easy to store away
parts of those tables. If the tables are partitioned by insertion/creation date or a similar field,
it makes doing that much simpler. Either way, you're still going to need a good way of recording
what data is where in your backup.
In the general case, there's no easy way to run a differential backup using
pg_dump
.
How it works...
PostgreSQL doesn't explicitly keep track of last changed date or similar information for a file
or table. PostgreSQL tables are held as files, so you should be able to rely on the
modification
time
(
mtime
) of the files on the filesystem. If, for some reason, you don't trust that or that has
been disabled, then incremental backup is not for you.
pg_dump
doesn't allow
WHERE
clauses to be specified, so even if you add your own columns
to track
last_changed_date
you'll still need to perform that manually somehow.
