Re: PgBackrest questions - Mailing list pgsql-general

Greetings,

* chiru r (chirupg@gmail.com) wrote:
> On Wed, Mar 14, 2018 at 6:17 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > * chiru r (chirupg@gmail.com) wrote:
> > > I am testing Pgbackrest and I have few questions.
> >
> > Great!
> >
> > > 1. I used postures user to perform backups and restores with Pgbackrest
> > > tool.
> > > The Trust authentication in pg_hba.conf file is working without issues.
> >
> > Please don't use 'trust'.
> >
> > > If I use md5 authentication in pg_hba_conf file and postgres user
> > password
> > > .pgpass file, the pgbackrest backup is failing.
> >
> > There really shouldn't ever be a need to use md5 authentication with
> > pgbackrest.  Instead, I'd strongly suggest you use 'peer'.  The 'peer'
> > method is perfectly safe as it depends on the authentication which
> > Unix provides, but it doesn't require a password or any of the
> > associated complications.  Note that 'trust' should *not* be used and
> > I'm glad to see that you're looking for alternatives to using 'trust'.
>
> Is there any alternative method other than Peer and Trust  to use with
> pgbackrest tool?.

pgbackrest uses Perl's DBD::Pg to connect to PostgreSQL, which should
pick up on having a password provided through the environment variable
PGPASSWORD or that of DBI_PASS.  I would suggest setting one of those to
the password instead of trying to use .pgpass (though, given that
DBD::Pg uses libpq underneath, it's not clear to me off-hand why .pgpass
wouldn't work, but it's possible it doesn't because the username is
explicitly provided by pgbackrest to DBD::Pg's connect() routine).

> > > 2.  All the restores through the backrest is going to refer the  Wal
> > > archive files under archive/<stanza name>/* directory, which are taken
> > from
> > > pgbackrest?
> >
> > When pgbackrest performs a restore, it will write out the recovery.conf
> > file for you which includes the restore command to pull the WAL from the
> > repo and stanza configured.  You shouldn't need to worry about where
> > those files are, specifically, coming from (and it's even possible that
> > it might change in the future...).  Is there a specific reason you're
> > asking?
>
> I am concerned about WAL archives because, I am planning to transfer my
> backups to Tape as soon as pgbackrest completes backup on disk.
> The restore of backups is going to be on different server.So i am concerned
> about recovery.
>
> Ex: Always I restore my production backups on DEV environment.
>
> And also I am planning to *remove *the Full,Diff and incremental backups on
> disk as soon as it transferred to tape. Is there any issue? since I am
> removing backups instead of Pg-backrest tool.

I would strongly recommend against trying to expire out the backups
yourself as you have to take a great deal of caution when it comes to
expiring the WAL- if you expire the wrong WAL then your backups will no
longer be valid.  pgbackrest has expiration options and an expiration
process which runs automatically and which will take care of cleaning
out any WAL which is no longer useful.

We do have plans for improving the options around expiration.

pgbackrest does have an option called '--archive-copy' which will copy
the WAL files required to restore a given backup (and only that WAL)
into the backup directory.  If you use that option and disable
compression then the backup directory for a full backup can be copied
to a new location directly and started as a PG server, where it will
replay all of the WAL.  If you're using differentials or incrementals,
then you would also need to use the --repo-hardlink option (and be on a
filesystem which supports hard-links) to allow you to do the same with
those backups.

In general, this is all a great deal simpler by making the repository
available for doing restores instead of having to ship things between
repos in the way you're describing.  It's also possible to perform a
regular filesystem-level backup and restore of the repository itself and
use that directly instead of trying to extract things from the repo.

> 4. I observed that the *backup.info <http://backup.info>* and
> *backup.info.copy* files under stanza directory. I compared both files I
> did not see any difference.
>     What is the reason to keep two files with same contents in same
> directory ?

We don't want a crash or other issue to cause the backup.info file to be
only half-written, or similar, hence the reason for two copies.

> 5. The *backup.manifest *and *backup.manifest.copy* files exist under each
> backup directory(full/diff/incremental). What is the reason to keep to
> files in same directory?.

This is more-or-less the same reason as above.

>     What is the difference of backup.manifest file under *backup.history*
> directory and  under each backup directory?

The backup.history file simply contains historical information about
past backups, in the form of the manifest files.  Not much is done with
them currently but we envision having a way to get statistical
information about how the backups (and therefore, the database) have
changed in size over time and in how long they take to run and perhaps
other interesting metrics.

Thanks!

Stephen