[CentOS] [Marketing Mail] Re: LVM and Backups

[Lange, Markus]

On Wed, 2018-09-19 at 08:55 +0200, Alessandro Baggi wrote:
> Il 18/09/2018 17:14, Gordon Messmer ha scritto:
> > On 9/17/18 11:38 PM, Alessandro Baggi wrote:
> > > Il 17/09/2018 22:12, Gordon Messmer ha scritto:
> > > > That doesn't look right.  It should look more like 1) stop or
> > > > freeze 
> > > > all of the services (httpd and database), 2) make the snapshot,
> > > > 3) 
> > > > start or thaw all of the services, 4) mount the snapshot, 5)
> > > > back up 
> > > > the data, 6) remove the snapshot.
> > > 
> > > About database setup I perform backups via pg_dump so how the
> > > snapshot 
> > > affects pgsql database? What your suggestion I must perform
> > > database 
> > > backup copying only filesystem file and not pgsql.sql database
> > > dump?
> > 
> > 
> > If you want a plain-text dump of the DB, you can do that before the
> > LVM 
> > snapshot sequence: 1) pg_dump, 2) stop or freeze all of the
> > services 
> > (httpd and database), 3) make the snapshot, 4) start or thaw all of
> > the 
> > services, 5) mount the snapshot, 6) back up the data, 7) remove
> > the 
> > snapshot.
> > 
> > Typically, the reason you want to use snapshots for the backup is
> > that 
> > you don't need to do pg_dump to get a consistent DB backup,
> > though. 
> > pg_dump style backups are extremely slow to restore.  If you freeze
> > the 
> > DB, make a snapshot, and thaw, you can make a safe, consistent
> > backup of 
> > the DB files directly, and restore in minimal time.
> > 
> > 
> > > > Are you using bacula's built-in snapshot support, or are you
> > > > rolling 
> > > > your own?
> > > 
> > > No I'm using pre/post job script where I have lvm commands to
> > > create 
> > > and destroy snapshot volume.
> > > 
> > 
> > I really recommend using a ready-made process rather than rolling
> > your 
> > own.  Bacula has snapshot support.  Alternately, my project can
> > manage 
> > snapshots and handle freezing / thawing PostgreSQL services.  I
> > think 
> > it's a better option than Bacula's, but either is better than 
> > reinventing this wheel.
> > 
> > https://bitbucket.org/gordonmessmer/dragonsdawn-snapshot
> > 
> > _______________________________________________
> > CentOS mailing list
> > CentOS at centos.org
> > https://lists.centos.org/mailman/listinfo/centos
> 
> Thank you for the suggestion.
> 
> I don't know why I considered pg_dump better then filesystem backup.
> At 
> this moment I prefer pg_dump because in this mode I can restore data
> on 
> different version of postgresql. With filesystem dump I can restore
> only 
> for a specific version. Is right?
> 
> I will give a try.
> 
> Thank you again for suggestions.
> 
> _______________________________________________
> CentOS mailing list
> CentOS at centos.org
> https://lists.centos.org/mailman/listinfo/centos

Hi,

an restore may need additional attention while restoring database files
to a different version of postgres. However, while the versions does
not differ that much (an official upgrade path exists, or no layout
change was made between the versions) the files work just as if you
updated the database using yum (check out spec file from source pkg to
find out what happens on package upgrades, you may need to do upgrade
steps manually).

This should only be a problem when you try to restore to a new major
version of postgres. But in that case I would recommend an additional
pg_dump backup to have a save fallback.

best regards

ps: I do not use postgres, please understand my testimony as not tested
practically.