3 ms·

> incremental/differential backups What's wrong with WAL backup? Postgres can run your script and pass it path to newly created WAL chunk, that you can ship o

> incremental/differential backups What's wrong with WAL backup? Postgres can run your script and pass it path to newly created WAL chunk, that you can ship off anywhere. Or run `pg_start_backup`, rsync data directory, `pg_stop_backup`.

clg2 10y ago
You would back up the WAL as well. However there might be a lot of WAL to apply, as data gets added then deleted or changed. A differential backup (see Note) solves this somewhat for large databases by just backing up the blocks changed since the last full backup. So you might do a full backup once a week, and differentials nightly in between and also save the WAL. This means you are not backing up many TBs every night. To recover, you would restore the full backup, plus the last differential backup plus any WAL since the last differential. This reduces the volume of WAL to be applied in a recovery. Note : Differential backups would include all the changes since the last FULL backup. Incremental backups would include changes since the last backup, whether a full or the last incremental.
dgregd 10y ago
> What's wrong with WAL backup? I use PG for 10 years and I never needed incremental backups. But if someone asked me about it then I would respond that the PG has no incremental backups. So WAL backup is not well known or documented in my opinion. An average db admin needs a tool which will generate incremental backup as a single file named like dbname_20160926.sql
- MustardTiger 10y ago
  >So WAL backup is not well known or documented in my opinion What? It is the standard backup method clearly explained in the docs.
- SEJeff 10y ago
  Barman[1] gives you something better than incremental backups actually. It sets up the backup server as a database slave via PG's built-in replication. It gives you point in time recovery with the nicest CLI of any backup tool for any DB I've ever used. If you think backup tools are a minus for PG, I guess you've never used barman. Easy as pie and has saved my bacon several times when CASCADE DELETE wrecked a few dbs for me due to coworkers futzing around. [1] http://www.pgbarman.org/ http://www.pgbarman.org/
  - dgregd 10y ago
    > If you think backup tools are a minus for PG, I guess you've never used barman You are right, I've never used barman. But I have streaming replication configured manually. Generally I am quite happy with PG backup tools. However this discussion made me to think about pg_dump deficiencies. WAL backup is for whole db cluster. But how do I incrementally update one db? E.g. one prod db to my dev environment.
    - SEJeff 10y ago
      Indeed, that is something that WAL files don't work for. There aren't a lot of great answers for that. I generally will restore a point in time snapshot somewhere and then dump out that entire db to restore via sql or whatnot.

clg2 10y ago

You would back up the WAL as well. However there might be a lot of WAL to apply, as data gets added then deleted or changed. A differential backup (see Note) solves this somewhat for large databases by just backing up the blocks changed since the last full backup. So you might do a full backup once a week, and differentials nightly in between and also save the WAL. This means you are not backing up many TBs every night. To recover, you would restore the full backup, plus the last differential backup plus any WAL since the last differential. This reduces the volume of WAL to be applied in a recovery. Note : Differential backups would include all the changes since the last FULL backup. Incremental backups would include changes since the last backup, whether a full or the last incremental.

dgregd 10y ago

> What's wrong with WAL backup? I use PG for 10 years and I never needed incremental backups. But if someone asked me about it then I would respond that the PG has no incremental backups. So WAL backup is not well known or documented in my opinion. An average db admin needs a tool which will generate incremental backup as a single file named like dbname_20160926.sql

MustardTiger 10y ago
>So WAL backup is not well known or documented in my opinion What? It is the standard backup method clearly explained in the docs.
SEJeff 10y ago
Barman[1] gives you something better than incremental backups actually. It sets up the backup server as a database slave via PG's built-in replication. It gives you point in time recovery with the nicest CLI of any backup tool for any DB I've ever used. If you think backup tools are a minus for PG, I guess you've never used barman. Easy as pie and has saved my bacon several times when CASCADE DELETE wrecked a few dbs for me due to coworkers futzing around. [1] http://www.pgbarman.org/ http://www.pgbarman.org/
- dgregd 10y ago
  > If you think backup tools are a minus for PG, I guess you've never used barman You are right, I've never used barman. But I have streaming replication configured manually. Generally I am quite happy with PG backup tools. However this discussion made me to think about pg_dump deficiencies. WAL backup is for whole db cluster. But how do I incrementally update one db? E.g. one prod db to my dev environment.
  - SEJeff 10y ago
    Indeed, that is something that WAL files don't work for. There aren't a lot of great answers for that. I generally will restore a point in time snapshot somewhere and then dump out that entire db to restore via sql or whatnot.