PostgreSQL directory format dump is only partially archived #643

New issue

Closed

opened 2023-02-20 10:46:21 +00:00 by Astropilot · 3 comments

Astropilot commented 2023-02-20 10:46:21 +00:00

Copy link

Trying to perform a directory format dump with PostgreSQL

Steps to reproduce (if a bug)

The following configuration used to dump the database:

location:
  repositories:
    - ssh://debian@[IP]/home/debian/testbackup_directory
postgresql_databases:
- name: postgres
hostname: devdb
username: postgres
password: [__confidential__]
format: directory

Actual behavior (if a bug)

Borgmatic do not produce any error, in fact it generate an archive that pass consistency check.

The problem lies in the fact that borgmatic does not seems to wait for pg_dump to finish backuping before starting borg create. I assume this is because it makes sense for plain/custom format with the pipe files.

But for the directory format it cause borg to start archiving a incomplete backup with only a part of the files generated by pg_dump.

Here are all the files generated by a manual pg_dump I did outside borgmatic:

4959.dat.gz  4997.dat.gz  5035.dat.gz  5073.dat.gz  5111.dat.gz  5149.dat.gz  5186.dat.gz
4961.dat.gz  4999.dat.gz  5037.dat.gz  5075.dat.gz  5113.dat.gz  5151.dat.gz  5188.dat.gz
4963.dat.gz  5001.dat.gz  5039.dat.gz  5077.dat.gz  5115.dat.gz  5153.dat.gz  5191.dat.gz
4965.dat.gz  5003.dat.gz  5041.dat.gz  5079.dat.gz  5117.dat.gz  5155.dat.gz  5193.dat.gz
4967.dat.gz  5005.dat.gz  5043.dat.gz  5081.dat.gz  5119.dat.gz  5157.dat.gz  5195.dat.gz
4969.dat.gz  5007.dat.gz  5045.dat.gz  5083.dat.gz  5121.dat.gz  5159.dat.gz  5197.dat.gz
4971.dat.gz  5009.dat.gz  5047.dat.gz  5085.dat.gz  5123.dat.gz  5161.dat.gz  5198.dat.gz
4973.dat.gz  5010.dat.gz  5049.dat.gz  5087.dat.gz  5124.dat.gz  5163.dat.gz  5200.dat.gz
4975.dat.gz  5012.dat.gz  5051.dat.gz  5089.dat.gz  5126.dat.gz  5165.dat.gz  5202.dat.gz
4977.dat.gz  5014.dat.gz  5053.dat.gz  5091.dat.gz  5128.dat.gz  5167.dat.gz  5204.dat.gz
4979.dat.gz  5016.dat.gz  5055.dat.gz  5093.dat.gz  5131.dat.gz  5169.dat.gz  5207.dat.gz
4981.dat.gz  5018.dat.gz  5057.dat.gz  5095.dat.gz  5133.dat.gz  5171.dat.gz  5209.dat.gz
4983.dat.gz  5020.dat.gz  5059.dat.gz  5097.dat.gz  5135.dat.gz  5173.dat.gz  5211.dat.gz
4985.dat.gz  5022.dat.gz  5061.dat.gz  5099.dat.gz  5137.dat.gz  5175.dat.gz  5213.dat.gz
4987.dat.gz  5025.dat.gz  5063.dat.gz  5101.dat.gz  5139.dat.gz  5177.dat.gz  5215.dat.gz
4989.dat.gz  5027.dat.gz  5064.dat.gz  5103.dat.gz  5141.dat.gz  5179.dat.gz  5217.dat.gz
4991.dat.gz  5029.dat.gz  5067.dat.gz  5105.dat.gz  5143.dat.gz  5181.dat.gz  5219.dat.gz
4993.dat.gz  5031.dat.gz  5069.dat.gz  5106.dat.gz  5145.dat.gz  5182.dat.gz  toc.dat
4995.dat.gz  5033.dat.gz  5071.dat.gz  5109.dat.gz  5147.dat.gz  5184.dat.gz

And here the files list of three archives I did with borgmatic with for each of them a bunch of missing files:

First archive:

toc.dat
4959.dat.gz
4961.dat.gz
4963.dat.gz
4965.dat.gz
4967.dat.gz
4969.dat.gz
4971.dat.gz
4973.dat.gz
4975.dat.gz
4977.dat.gz
4979.dat.gz
4981.dat.gz
4983.dat.gz
4985.dat.gz
4987.dat.gz
4989.dat.gz
4991.dat.gz
4993.dat.gz
4995.dat.gz
4997.dat.gz

Second archive:

toc.dat
4959.dat.gz
4961.dat.gz
4963.dat.gz

Third archive:

toc.dat
4959.dat.gz
4961.dat.gz
4963.dat.gz
4965.dat.gz
4967.dat.gz
4969.dat.gz
4971.dat.gz
4973.dat.gz
4975.dat.gz
4977.dat.gz
4979.dat.gz
4981.dat.gz
4983.dat.gz
4985.dat.gz
4987.dat.gz
4989.dat.gz
4991.dat.gz
4993.dat.gz
4995.dat.gz
4997.dat.gz
4999.dat.gz
5001.dat.gz
5003.dat.gz
5005.dat.gz
5007.dat.gz
5009.dat.gz
5010.dat.gz
5012.dat.gz
5014.dat.gz
5016.dat.gz
5018.dat.gz
5020.dat.gz
5022.dat.gz
5025.dat.gz
5027.dat.gz
5029.dat.gz
5031.dat.gz
5033.dat.gz
5035.dat.gz

Expected behavior (if a bug)

If the format is directory, borgmatic should wait for pg_dump process to finish before starting archiving with borg.

Other notes / implementation ideas

In my case I have to backup multiples databases with the same format for each of them. But I do not know if it is easy for borgmatic to handle multiples format with directory mixed within when you need to wait for pg_dump for some of them.

Environment

borgmatic version: 1.7.6

borgmatic installation method: Docker container (Docker Borgmatic)

Borg version: 1.2.3

Python version: 3.11.1

Database version (if applicable): PostgreSQL 15

operating system and version: Alpine 3.17 (within docker image)

#### Trying to perform a directory format dump with PostgreSQL #### Steps to reproduce (if a bug) The following configuration used to dump the database: ```yml location: repositories: - ssh://debian@[IP]/home/debian/testbackup_directory postgresql_databases: - name: postgres hostname: devdb username: postgres password: [__confidential__] format: directory ``` #### Actual behavior (if a bug) Borgmatic do not produce any error, in fact it generate an archive that pass consistency check. The problem lies in the fact that borgmatic does not seems to wait for `pg_dump` to finish backuping before starting `borg create`. I assume this is because it makes sense for plain/custom format with the pipe files. But for the directory format it cause borg to start archiving a incomplete backup with only a part of the files generated by pg_dump. Here are all the files generated by a manual pg_dump I did outside borgmatic: ``` 4959.dat.gz 4997.dat.gz 5035.dat.gz 5073.dat.gz 5111.dat.gz 5149.dat.gz 5186.dat.gz 4961.dat.gz 4999.dat.gz 5037.dat.gz 5075.dat.gz 5113.dat.gz 5151.dat.gz 5188.dat.gz 4963.dat.gz 5001.dat.gz 5039.dat.gz 5077.dat.gz 5115.dat.gz 5153.dat.gz 5191.dat.gz 4965.dat.gz 5003.dat.gz 5041.dat.gz 5079.dat.gz 5117.dat.gz 5155.dat.gz 5193.dat.gz 4967.dat.gz 5005.dat.gz 5043.dat.gz 5081.dat.gz 5119.dat.gz 5157.dat.gz 5195.dat.gz 4969.dat.gz 5007.dat.gz 5045.dat.gz 5083.dat.gz 5121.dat.gz 5159.dat.gz 5197.dat.gz 4971.dat.gz 5009.dat.gz 5047.dat.gz 5085.dat.gz 5123.dat.gz 5161.dat.gz 5198.dat.gz 4973.dat.gz 5010.dat.gz 5049.dat.gz 5087.dat.gz 5124.dat.gz 5163.dat.gz 5200.dat.gz 4975.dat.gz 5012.dat.gz 5051.dat.gz 5089.dat.gz 5126.dat.gz 5165.dat.gz 5202.dat.gz 4977.dat.gz 5014.dat.gz 5053.dat.gz 5091.dat.gz 5128.dat.gz 5167.dat.gz 5204.dat.gz 4979.dat.gz 5016.dat.gz 5055.dat.gz 5093.dat.gz 5131.dat.gz 5169.dat.gz 5207.dat.gz 4981.dat.gz 5018.dat.gz 5057.dat.gz 5095.dat.gz 5133.dat.gz 5171.dat.gz 5209.dat.gz 4983.dat.gz 5020.dat.gz 5059.dat.gz 5097.dat.gz 5135.dat.gz 5173.dat.gz 5211.dat.gz 4985.dat.gz 5022.dat.gz 5061.dat.gz 5099.dat.gz 5137.dat.gz 5175.dat.gz 5213.dat.gz 4987.dat.gz 5025.dat.gz 5063.dat.gz 5101.dat.gz 5139.dat.gz 5177.dat.gz 5215.dat.gz 4989.dat.gz 5027.dat.gz 5064.dat.gz 5103.dat.gz 5141.dat.gz 5179.dat.gz 5217.dat.gz 4991.dat.gz 5029.dat.gz 5067.dat.gz 5105.dat.gz 5143.dat.gz 5181.dat.gz 5219.dat.gz 4993.dat.gz 5031.dat.gz 5069.dat.gz 5106.dat.gz 5145.dat.gz 5182.dat.gz toc.dat 4995.dat.gz 5033.dat.gz 5071.dat.gz 5109.dat.gz 5147.dat.gz 5184.dat.gz ``` And here the files list of three archives I did with borgmatic with for each of them a bunch of missing files: First archive: ``` toc.dat 4959.dat.gz 4961.dat.gz 4963.dat.gz 4965.dat.gz 4967.dat.gz 4969.dat.gz 4971.dat.gz 4973.dat.gz 4975.dat.gz 4977.dat.gz 4979.dat.gz 4981.dat.gz 4983.dat.gz 4985.dat.gz 4987.dat.gz 4989.dat.gz 4991.dat.gz 4993.dat.gz 4995.dat.gz 4997.dat.gz ``` Second archive: ``` toc.dat 4959.dat.gz 4961.dat.gz 4963.dat.gz ``` Third archive: ``` toc.dat 4959.dat.gz 4961.dat.gz 4963.dat.gz 4965.dat.gz 4967.dat.gz 4969.dat.gz 4971.dat.gz 4973.dat.gz 4975.dat.gz 4977.dat.gz 4979.dat.gz 4981.dat.gz 4983.dat.gz 4985.dat.gz 4987.dat.gz 4989.dat.gz 4991.dat.gz 4993.dat.gz 4995.dat.gz 4997.dat.gz 4999.dat.gz 5001.dat.gz 5003.dat.gz 5005.dat.gz 5007.dat.gz 5009.dat.gz 5010.dat.gz 5012.dat.gz 5014.dat.gz 5016.dat.gz 5018.dat.gz 5020.dat.gz 5022.dat.gz 5025.dat.gz 5027.dat.gz 5029.dat.gz 5031.dat.gz 5033.dat.gz 5035.dat.gz ``` #### Expected behavior (if a bug) If the format is `directory`, borgmatic should wait for `pg_dump` process to finish before starting archiving with borg. #### Other notes / implementation ideas In my case I have to backup multiples databases with the same format for each of them. But I do not know if it is easy for borgmatic to handle multiples format with `directory` mixed within when you need to wait for `pg_dump` for some of them. #### Environment **borgmatic version:** 1.7.6 **borgmatic installation method:** Docker container ([Docker Borgmatic](https://github.com/borgmatic-collective/docker-borgmatic)) **Borg version:** 1.2.3 **Python version:** 3.11.1 **Database version (if applicable):** PostgreSQL 15 **operating system and version:** Alpine 3.17 (within docker image)

witten commented 2023-02-20 17:56:04 +00:00

Owner

Copy link

This is concerning! Thanks so much for taking the time to file it and provide details. I think your intuition is probably correct here—that borgmatic isn't waiting for pg_dump to finish because, for non-directory dump formats, it streams the dump directly to Borg as it's running. Let me dig into this and get back to you.

This is concerning! Thanks so much for taking the time to file it and provide details. I think your intuition is probably correct here—that borgmatic isn't waiting for `pg_dump` to finish because, for *non*-directory dump formats, it streams the dump directly to Borg as it's running. Let me dig into this and get back to you.

👍 1

witten added the

bug

data loss

labels 2023-02-20 17:56:12 +00:00

witten referenced this issue from a commit 2023-02-20 23:19:14 +00:00

Fix for potential data loss (data not getting backed up) when dumping large "directory" format PostgreSQL/MongoDB databases (#643).

witten commented 2023-02-20 23:21:23 +00:00

Owner

Copy link

This should be fixed in master now. As far as I can tell, this was a long-standing problem, but it likely didn't manifest for smaller "directory" format database dumps. Thanks again for reporting this!

I'll follow up here when it's released.

This should be fixed in master now. As far as I can tell, this was a long-standing problem, but it likely didn't manifest for smaller "directory" format database dumps. Thanks again for reporting this! I'll follow up here when it's released.

witten closed this issue 2023-02-20 23:21:23 +00:00

witten commented 2023-02-20 23:39:27 +00:00

Owner

Copy link

Okay, this fix has been released in borgmatic 1.7.7.

Okay, this fix has been released in borgmatic 1.7.7.

Sign in to join this conversation.

No Branch/Tag specified

Branches Tags

main

parallel-borgmatic

command-hook-steps

2.1.7

2.1.6

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.1.0

2.0.13

2.0.12

2.0.11

2.0.10

2.0.9

2.0.8

2.0.7

2.0.6

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

1.9.14

1.9.13

1.9.12

1.9.11

1.9.10

1.9.9

1.9.8

1.9.7

1.9.6

1.9.5

1.9.4

1.9.3

1.9.2

1.9.1

1.9.0

1.8.14

1.8.13

1.8.12

1.8.11

1.8.10

1.8.9

1.8.8

1.8.7

1.8.6

1.8.5

1.8.4

1.8.3

1.8.2

1.8.1

1.8.0

1.7.15

1.7.14

1.7.13

1.7.12

1.7.11

1.7.10

1.7.9

1.7.8

1.7.7

1.7.6

1.7.5

1.7.4

1.7.3

1.7.2

1.7.1

1.7.0

1.6.6

1.6.5

1.6.4

1.6.3

1.6.2

1.6.1

1.6.0

1.5.24

1.5.23

1.5.22

1.5.21

1.5.20

1.5.19

1.5.18

1.5.17

1.5.16

1.5.15

1.5.14

1.5.13

1.5.12

1.5.11

1.5.10

1.5.9

1.5.8

1.5.7

1.5.7.dev0

1.5.6

1.5.5

1.5.4

1.5.3

1.5.2

1.5.1

1.5.0

1.4.22

1.4.21

1.4.20

1.4.19

1.4.18

1.4.17

1.4.16

1.4.15

1.4.14

1.4.13

1.4.12

1.4.11

1.4.10

1.4.9

1.4.8

1.4.7

1.4.6

1.4.5

1.4.4

1.4.3

1.4.2

1.4.1

1.4.0

1.3.26

1.3.25

1.3.24

1.3.23

1.3.22

1.3.21

1.3.20

1.3.19

1.3.18

1.3.17

1.3.16

1.3.15

1.3.14

1.3.13

1.3.12

1.3.11

1.3.10

1.3.9

1.3.8

1.3.7

1.3.6

1.3.5

1.3.4

1.3.3

1.3.2

1.3.1

1.3.0

1.2.18

1.2.17

1.2.16

1.2.15

1.2.14

1.2.13

1.2.12

1.2.11

1.2.10

1.2.9

1.2.8

1.2.7

1.2.6

1.2.5

1.2.4

1.2.3

1.2.2

1.2.1

1.2.0

1.1.15

1.1.14

1.1.13

1.1.12

1.1.11

1.1.10

1.1.9

1.1.7

1.1.6

1.1.5

1.1.4

1.1.3

1.1.2

1.1.1

1.1.0

1.0.3

1.0.2

1.0.1

1.0.0

0.1.8

0.1.7

0.1.6

0.1.5

0.1.4

0.1.3

0.1.2

0.1.1

0.1.0

0.0.7

0.0.6

0.0.5

0.0.4

0.0.3

0.0.2

Labels

Clear labels   

blocked

  

breaking

  

bug

  

data loss

  

design finalized

  

good first issue

  

new feature area

  

question / support

  

security

  

waiting for response

No labels

blocked

breaking

bug

data loss

design finalized

good first issue

new feature area

question / support

security

waiting for response

Milestone

Clear milestone

No items

No milestone

Assignees

Clear assignees

No assignees

2 participants

Notifications

Subscribe

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference

borgmatic-collective/borgmatic#643

No description provided.