On Mon, Oct 9, 2017 at 1:19 PM, Ron Johnson <ron.l.john...@cox.net> wrote:
> 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)? > Yes -- however* you must configure WAL archiving* first. If not, no backup tool, cp, rsync, etc... will provide a good backup. Oh, and BTW -- The obligatory: You are on an ancient, EOL version of PG. Upgrade. Make sure that these are set: - archive_mode - archive_command Then, on when you restore the backup, you need to create a recovery.conf and configure - restore_command https://www.postgresql.org/docs/8.4/static/continuous-archiving.html > > > > 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.l.john...@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('Incrementalba >> ckup',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 > dar...@synsesolutions.com > 520-661-5885 <(520)%20661-5885> > > > > -- > World Peace Through Nuclear Pacification > > -- -- Scott Mead Sr. Architect *OpenSCG <http://openscg.com>* http://openscg.com
