Re: [GENERAL] Using cp to back up a database? - Mailing list pgsql-general
From | Darren Douglas |
---|---|
Subject | Re: [GENERAL] Using cp to back up a database? |
Date | |
Msg-id | CALC17OpqHJCn3rJfU+_92Ti8Rg4EKzewXQG2y5Bmj_xP0VuyQQ@mail.gmail.com Whole thread Raw |
In response to | Re: [GENERAL] Using cp to back up a database? (Darren Douglas <darren@synsesolutions.com>) |
List | pgsql-general |
Sorry, forgot about your main question about cp ...
So, because the physical backup is a copy of the entire cluster data directory, ANY valid and safe method for copying that directory is ok.
In most production installations, that means that an enterprise backup tool may be used to accomplish the copy. It may mean a shell script that does backup rotation, etc. However, at its simplest, it may just be a cp or rsync command.
So yes, very simply, the cp -r command issued in the script, along with ensuring that WALs are safely archived, is a valid backup approach.
Personally, I would use cp -ar instead. The 'a' option invokes cp in "archive" mode, which will preserve file permissions.
On Mon, Oct 9, 2017 at 9:49 AM, Darren Douglas <darren@synsesolutions.com> 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.DarrenThe firstOn Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson <ron.l.johnson@cox.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--
pgsql-general by date: