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

Hi All,

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?.

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

Thanks,
Chiru

On Wed, Dec 13, 2017 at 5:45 PM, Stephen Frost <sfrost@snowman.net> wrote:

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

Please help me on below recovery scenario, if any one is using pgBackRest.

On Fri, Dec 15, 2017 at 4:36 PM, chiru r <chirupg@gmail.com> wrote:

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:

/messages/by-id/e252cb30-9707-3801-f688-75dd2cde4819@pgmasters.net

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

Thanks!

Stephen

Thanks David for the replay.

we are not interested to use replication/Standby configuration at this
moment with pgbackrest.

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

Thanks,
Chiru

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

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 .
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

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

Thanks,
Chiru

On Mon, Dec 18, 2017 at 9:20 AM, David Steele <david@pgmasters.net> wrote:

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

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)

On Thu, Dec 28, 2017 at 4:26 PM, David Steele <david@pgmasters.net> wrote:

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

I am unable to copy the complete backup.manifest file due to security
reasons . please find the below contents.

[backup:db]
db-catalog-version=201510051
db-control-version=942
db-id=1
db-system-id=6444557285095914282
db-version="9.5"

[backup:option]
option-archive-check=true
option-archive-copy=false
option-backup-standby=false
option-checksum-page=false
option-compress=true
option-hardlink=false
option-online=true

[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}

Below are the databases and table-spaces associated with it.

postgres=# \l+

List of databases
Name | Owner | Encoding | Collate | Ctype |
Access privileges | Size | Tablespace |
Description

-----------+----------+----------+-------------+-------------+---------------------------------------+---------+------------+-------------------------------------------
-
db1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
| 7280 kB | report1 |
db2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
| 7280 kB | report1 |
db3 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
| 7280 kB | pg_default |

Thanks,
Chiranjeevi

On Thu, Dec 28, 2017 at 5:41 PM, David Steele <david@pgmasters.net> wrote:

Can you please help on below pgbackrest restoration issue.

On Thu, Dec 28, 2017 at 6:15 PM, chiru r <chirupg@gmail.com> 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

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

Thank you very much David and Stephen for your quick reply.

On Fri, Dec 29, 2017 at 1:55 PM, David Steele <david@pgmasters.net> wrote: