Re: Using cp to back up a database?

Maybe my original question wasn't clear, so I'll try again: is it safe to do
a physical using cp (as opposed to rsync)?

On 10/09/2017 11:49 AM, Darren Douglas wrote:
> 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
>
> On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net
> <mailto:ron(dot)l(dot)johnson(at)cox(dot)net>> wrote:
>
> Hi,
>
> v8.4.20
>
> This is what the current backup script uses:
>
> /usr/bin/psql -U postgres -c "SELECT
> pg_start_backup('Incrementalbackup',true);"
> cp -r /var/lib/pgsql/data/* $dumpdir/data/
> /usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"
>
>
> Should it use rsync or pg_dump instead?
>
> Thanks
>
> --
> World Peace Through Nuclear Pacification
>
>
>
>
> --
> Darren Douglas
> Synse Solutions
> darren(at)synsesolutions(dot)com <mailto:darren(at)synsesolutions(dot)com>
> 520-661-5885

--
World Peace Through Nuclear Pacification