Ron:
Here is an explanation that may help a bit.
Your script is executing a PHYSICAL backup. A physical
backup is simply a full copy of the cluster (instance) data
directory ($PGDATA). A physical backup is your best option
when you need to backup the cluster data as well as all
configuration for the cluster. Essentially, if you had to
rebuild the entire computer hosting the cluster, you could
just reinstall the same version of postgres, copy in the
backup data directory, and the cluster would run exactly as it
did before with the same data. A physical backup is also
necessary when the databases get very large.
In the backup script you posted, the 'pg_start_backup' and
'pg_stop_backup' commands fulfill two purposes. The first is
to create a label for the point in time the backup was started
- this is done by pg_start_backup. The second is to ensure
that all WAL segments that have been written since the backup
began have been safely archived. That is done by
pg_stop_backup. This approach is necessary to accomplish an
online physical backup.
As others have mentioned pg_dump is a LOGICAL backup tool
similar to any SQL dump you've done with another DBMS. The
pg_dump command will do a SQL dump to recreate everything
within a single database. So, if you have multiple databases
in your cluster, its not the best option. pg_dumpall is the
logical backup tool that will do a logical dump of all globals
(schema + roles) along with all databases in the cluster.
Because the pg_dump/pg_dumpall commands are not executing a
physical backup, the pg_start_backup and pg_stop_backup
commands do not apply.
As for times when you would elect to do a logical backup,
as others have mentioned, this is the only valid option when
you are restoring to a different version of Postgres. It is
also a good option to do a backup of a single small database
or several small databases. And, if for any reason the backup
needs to be human-readable, this is the approach of choice as
well.
Darren
The first
