Обсуждение: PgBackRest question?
Hi All,
I am trying to understand the PgBackRest functionality for WAL files.
The pgbackrest.conf configuration file has the option repo-path to mention the mount point to store the backups and wal archive files.
We are looking for an option, Can we store online backups and wal archive files separately in different directories?.
Thanks,
Chiranjeevi
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:
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
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:
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 doneTuesday-- 10 AM incremental backup is doneNote : 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,ChiruOn Wed, Dec 13, 2017 at 5:45 PM, Stephen Frost <sfrost@snowman.net> wrote: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
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
Вложения
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/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
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 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
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:
On 12/28/17 5:15 PM, chiru r wrote: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 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)
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
Can you please help on below pgbackrest restoration issue.
On Thu, Dec 28, 2017 at 6:15 PM, chiru r <chirupg@gmail.com> wrote:
I am unable to copy the complete backup.manifest file due to security reasons . please find the below contents.[backup:db]db-catalog-version=201510051db-control-version=942db-id=1db-system-id=6444557285095914282 db-version="9.5"[backup:option]option-archive-check=trueoption-archive-copy=falseoption-backup-standby=falseoption-checksum-page=falseoption-compress=trueoption-hardlink=falseoption-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 databasesName | 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,ChiranjeeviOn Thu, Dec 28, 2017 at 5:41 PM, David Steele <david@pgmasters.net> wrote:On 12/28/17 5:15 PM, chiru r wrote: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 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)
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
Вложения
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:
Stephen is correct -- if you have the space to do full restores thenOn 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.
that will work until this is fixed.
--
-David
david@pgmasters.net