Thread: PgBackRest question?

Greetings,

* chiru r (chirupg@gmail.com) wrote:
> The pgbackrest.conf  configuration file has the option *repo-path* to mention
> the mount point to store the backups and wal archive files.

That's correct, that's where the backups and the WAL are stored.

> We are looking for an option, Can we store online backups and wal archive
> files separately in different directories?.

That's an interesting requirement- why would you need them to be
different directories?

I'll point out that PostgreSQL backups absolutely require the WAL in
order to be able to be restored (at least the WAL generated during the
backup) in order to reach consistency.  If the two were to be seperated,
you'd probably still want the backups to 'stand alone' and that would
mean duplicating all of the WAL which is created during the backup and
storing it with the backup.

The first question really is why you're looking for this though..?  If
there's a good use-case for it, we could look at adding that it as an
option.

Thanks!

Stephen

On 12/15/17 4:36 PM, chiru r wrote:
> 
> Thanks,I am thinking about a specific recovery case.
> 
> Lets assume Heavy transactional system we configured.
> It is generating WAL 2000/hr and recycling automatically in pg_wal
> directory. 
> 
> QA :
> 
> Sunday -- 11 PM  -- Full backup done.
> Monday -- 11 PM  -- Differential Backup done
> Tuesday-- 10 AM incremental backup is done
> Note :  Every  2 hrs  incremental backup scheduled on system.
> 
> For example, if we want to restore to DEV server:
> 
> We want to recover database as of 11:30 AM Tuesday on DEV server using
> QA backups.
> 
> Is it possible to restore using pgbackrest tool for this scenario?.

Yes.  See the documentation here on how to create a standby:

http://pgbackrest.org/user-guide.html#replication/hot-standby

And to recover to a specific point in time:

http://pgbackrest.org/user-guide.html#pitr

Then simply promote the standby and you have a dev server.

> How pgbackrest keeps track of transactions since the last backup? Where
> it stores transaction information for recovery ?.

The transactions since the last backup are tracked in the WAL.  Here's
how to setup WAL archiving:

http://pgbackrest.org/user-guide.html#quickstart/configure-archiving

It looks like you have a lot of WAL volume so you'll want to use async
archiving:

http://pgbackrest.org/user-guide.html#backup-host/async-archiving

You mentioned in your original post:

> We are looking for an option, Can we store online backups and wal archive
> files separately in different directories?.

But it's not clear to me why you would need to do that based on your use
case.

Regards,
-- 
-David
david@pgmasters.net

Greetings,

* chiru r (chirupg@gmail.com) wrote:
> Please help me on below recovery scenario, if any one is using pgBackRest.

David answered your questions here:

https://www.postgresql.org/message-id/e252cb30-9707-3801-f688-75dd2cde4819%40pgmasters.net

I suggest you review/reply to that if you have further questions.

Thanks!

Stephen

On 12/17/17 7:10 PM, chiru r wrote:
> Thanks David for the replay.
> 
> we are not interested to use replication/Standby configuration at this
> moment with pgbackrest.

Database restores all work the same way -- the only difference is a few
configuration parameters.

> We are looking to restore the QA backups on Dev systems . Please help
> us if any blogs/documentations for remote databases recovery scenarios?.

I pointed you at the standby documentation because it describes a remote
database recovery scenario.  The only thing you need to do is exclude
standby_mode=on and you will have your dev system.

Regards,
-- 
-David
david@pgmasters.net

On 12/28/17 3:38 PM, chiru r wrote:
> Thank you for the details  David and Stephen..
> 
> I am unable to recover the database which associated with user table space .
> 
> Please see the below test case and suggest me,if any one has the  issues 
> while recovering single database.
> 
> *Test case: *
> 
> 1) created tablespace   tblsp1 .
> 2) created database  db1 with tablespace tblsp1;
> 3) created  another database db2 ( with out tablespace , it means it is 
> going to be created under pg_default table space)
> 4) Taken full backup.
> 5) create  table t1 on both  db1 and db2 databases .
> 6) Taken Incremental backup
> 7)   a) We have tried  to restore the db1 on different location by 
> mapping table-space and without table-space .

This all looks reasonable.

> However we got the below Error
>   pgbackrest --stanza=online_backups  --db-include=db1 
>   --tablespace-map-all=/u02/pgdata02/report2  restore
> ERROR [080]: : database to include 'db1' does not exist

It appears that db1 is not listed as a valid database in the backup 
manifest.

> b) We have tried  to restore the db1 on different location and we got 
> the below Error
> 
> pgbackrest --stanza=online_backups  --delta --db-include=db1  restore
> ERROR [080]: : database to include 'db1' does not exist
> 
> Please help me can we restore single database ,if it is assigned with 
> tables space? .
> And also let me know if anything missing in my test case ?
> 
> *Note *: if I restore the db2, whicich is associated with pg_default 
> tablespace it is working.
> pgbackrest --stanza=online_backups  --delta --db-include=db2  restore

Can you run this query on the cluster where the backups are running and 
attach the result:

select datname, oid from pg_database;

Thanks,
-- 
-David
david@pgmasters.net

On 12/28/17 5:15 PM, chiru r wrote:
> 
> Please find the below details.
> 
> postgres=# select datname, oid from pg_database;
>    datname  |  oid
> -----------+--------
>   template0 |  13289
>   postgres  |  13294
>   template1 |      1
>   db1       | 770161
>   db2       | 770162
>   db3       | 770169
> (6 rows)
That looks OK. The next thing to look at is the backup that you are 
trying to restore.  Based on your commands this should be the most 
recent backup.

Please attach the backup.manifest file from:

[repo-path]/backup/online_backups/latest/backup.manifest

It's probably best if you compress it before attaching.

Thanks,
-- 
-David
david@pgmasters.net

On 12/28/17 6:15 PM, chiru r wrote:
> 
> I am unable to copy the complete backup.manifest file due to security
> reasons . please find the below contents.
> 
> [backup:target]
> pg_data={"path":"/u02/pgdata01/9.5/data","type":"path"}
> pg_tblspc/721349={"path":"/u02/pgdata02/report1","tablespace-id":"721349","tablespace-name":"report1","type":"link"}
> 
> [db]
> db1={"db-id":770161,"db-last-system-id":13289}
> db2={"db-id":770162,"db-last-system-id":13289}
> db3={"db-id":770169,"db-last-system-id":13289}
> postgres={"db-id":13294,"db-last-system-id":13289}
> template0={"db-id":13289,"db-last-system-id":13289}
> template1={"db-id":1,"db-last-system-id":13289}

OK -- it looks like this is a bug.  pgBackRest is validating the
database mappings against the files in the manifest but does not
recognize databases that are assigned to a tablespace.  It's OK if
tables are assigned to a tablespace, but not the entire database.

We'll fix this in the next release.  I've opened an issue on github to
track it: https://github.com/pgbackrest/pgbackrest/issues

Thanks!
-- 
-David
david@pgmasters.net

Greetings,

* David Steele (david@pgmasters.net) wrote:
> On 12/28/17 6:15 PM, chiru r wrote:
> > I am unable to copy the complete backup.manifest file due to security
> > reasons . please find the below contents.
> >
> > [backup:target]
> > pg_data={"path":"/u02/pgdata01/9.5/data","type":"path"}
> >
pg_tblspc/721349={"path":"/u02/pgdata02/report1","tablespace-id":"721349","tablespace-name":"report1","type":"link"}
> >
> > [db]
> > db1={"db-id":770161,"db-last-system-id":13289}
> > db2={"db-id":770162,"db-last-system-id":13289}
> > db3={"db-id":770169,"db-last-system-id":13289}
> > postgres={"db-id":13294,"db-last-system-id":13289}
> > template0={"db-id":13289,"db-last-system-id":13289}
> > template1={"db-id":1,"db-last-system-id":13289}
>
> OK -- it looks like this is a bug.  pgBackRest is validating the
> database mappings against the files in the manifest but does not
> recognize databases that are assigned to a tablespace.  It's OK if
> tables are assigned to a tablespace, but not the entire database.
>
> We'll fix this in the next release.  I've opened an issue on github to
> track it: https://github.com/pgbackrest/pgbackrest/issues

Of course, a full restore should work just fine, this issue occurs
only if you're doing a single-database restore from a cluster.

Presuming you're doing more than just testing, doing a full restore
(and then dropping the databases that you don't want) would be a
workaround until 1.28 is out.

Thanks!

Stephen

On 12/29/17 1:53 PM, Stephen Frost wrote:
> Greetings,
>
> * David Steele (david@pgmasters.net) wrote:
>> On 12/28/17 6:15 PM, chiru r wrote:
>>> I am unable to copy the complete backup.manifest file due to security
>>> reasons . please find the below contents.
>>>
>>> [backup:target]
>>> pg_data={"path":"/u02/pgdata01/9.5/data","type":"path"}
>>>
pg_tblspc/721349={"path":"/u02/pgdata02/report1","tablespace-id":"721349","tablespace-name":"report1","type":"link"}
>>>
>>> [db]
>>> db1={"db-id":770161,"db-last-system-id":13289}
>>> db2={"db-id":770162,"db-last-system-id":13289}
>>> db3={"db-id":770169,"db-last-system-id":13289}
>>> postgres={"db-id":13294,"db-last-system-id":13289}
>>> template0={"db-id":13289,"db-last-system-id":13289}
>>> template1={"db-id":1,"db-last-system-id":13289}
>>
>> OK -- it looks like this is a bug.  pgBackRest is validating the
>> database mappings against the files in the manifest but does not
>> recognize databases that are assigned to a tablespace.  It's OK if
>> tables are assigned to a tablespace, but not the entire database.
>>
>> We'll fix this in the next release.  I've opened an issue on github to
>> track it: https://github.com/pgbackrest/pgbackrest/issues
>
> Of course, a full restore should work just fine, this issue occurs
> only if you're doing a single-database restore from a cluster.
>
> Presuming you're doing more than just testing, doing a full restore
> (and then dropping the databases that you don't want) would be a
> workaround until 1.28 is out.

Stephen is correct -- if you have the space to do full restores then
that will work until this is fixed.

--
-David
david@pgmasters.net