Skip to content

pgsql_tmp in PAGE-type backups #246

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
beremour opened this issue Aug 11, 2020 · 15 comments
Closed

pgsql_tmp in PAGE-type backups #246

beremour opened this issue Aug 11, 2020 · 15 comments
Labels
bug
Milestone
2.4.4

Comments

@beremour
Copy link

It is just a question: doing backup on test installation, I've noticed that one of PAGE backups with ID "QEVARD 2020-08-11 00:43" has abnormal size16GB.

pg_probackup-12 show     -B /backup/pgsql

BACKUP INSTANCE 'main'
======================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode  WAL Mode  TLI    Time    Data   WAL  Zratio  Start LSN   Stop LSN    Status
======================================================================================================================================
 main      12       QEW83D  2020-08-11 12:38:28+03  PAGE  STREAM    1/1     34s  4102MB  32MB    1.00  1/52000028  1/531A4A50  OK
 main      12       QEW5BD  2020-08-11 11:38:30+03  PAGE  STREAM    1/1     31s  4115MB  32MB    1.00  1/4F000028  1/501A5740  OK
 main      12       QEW2JD  2020-08-11 10:38:30+03  PAGE  STREAM    1/1     33s  4102MB  32MB    1.00  1/4B000028  1/4C1A4DE8  OK
 main      12       QEVZRD  2020-08-11 09:39:00+03  PAGE  STREAM    1/1   1m:7s  8198MB  32MB    1.00  1/48000028  1/491A5AD0  OK
 main      12       QEVWZD  2020-08-11 08:38:55+03  PAGE  STREAM    1/1   1m:0s  8199MB  32MB    1.00  1/45000028  1/461A67D8  OK
 main      12       QEVU7D  2020-08-11 07:38:58+03  PAGE  STREAM    1/1     59s  8208MB  48MB    1.00  1/42000028  1/431A3E48  OK
 main      12       QEVRFD  2020-08-11 06:38:58+03  PAGE  STREAM    1/1   1m:7s  8199MB  32MB    1.00  1/3E000028  1/3F1A4B40  OK
 main      12       QEVOND  2020-08-11 05:38:39+03  PAGE  STREAM    1/1     39s  5126MB  32MB    1.00  1/3B000028  1/3C1A41B0  OK
 main      12       QEVLVD  2020-08-11 04:38:52+03  PAGE  STREAM    1/1     52s  8207MB  32MB    1.00  1/38000028  1/391A4ED0  OK
 main      12       QEVJ3D  2020-08-11 03:38:30+03  PAGE  STREAM    1/1     38s  4102MB  32MB    1.00  1/34000028  1/351A4540  OK
 main      12       QEVGBD  2020-08-11 02:38:31+03  PAGE  STREAM    1/1     39s  4102MB  32MB    1.00  1/31000028  1/321A5230  OK
 main      12       QEVDJD  2020-08-11 01:38:28+03  PAGE  STREAM    1/1     32s  4110MB  48MB    1.00  1/2E000028  1/2F1A5EE0  OK
 main      12       QEVARD  2020-08-11 00:43:33+03  PAGE  STREAM    1/1  5m:33s    16GB  32MB    1.00  1/2A000028  1/2B1A5620  OK
 main      12       QEV7ZD  2020-08-10 23:38:59+03  PAGE  STREAM    1/1   1m:1s  8207MB  32MB    1.00  1/27000028  1/281A6238  OK
 main      12       QEV57D  2020-08-10 22:39:00+03  PAGE  STREAM    1/1   1m:3s  8199MB  32MB    1.00  1/24000028  1/25A33EC0  OK
 main      12       QEV2FD  2020-08-10 21:38:55+03  PAGE  STREAM    1/1   1m:1s  8198MB  32MB    1.00  1/21000028  1/221A6888  OK
 main      12       QEUZND  2020-08-10 20:38:36+03  PAGE  STREAM    1/1     37s  5132MB  32MB    1.00  1/1E000028  1/1F1A7518  OK
 main      12       QEUWVD  2020-08-10 19:38:29+03  PAGE  STREAM    1/1     33s  4102MB  32MB    1.00  1/1A000028  1/1B1A6C20  OK
 main      12       QEUU3D  2020-08-10 18:38:29+03  PAGE  STREAM    1/1     33s  4108MB  32MB    1.00  1/17000028  1/181A7908  OK
 main      12       QEURBD  2020-08-10 17:38:30+03  PAGE  STREAM    1/1     34s  4102MB  48MB    1.00  1/14000028  1/151A8F40  OK
 main      12       QEUOJD  2020-08-10 16:38:33+03  PAGE  STREAM    1/1     33s  4107MB  32MB    1.00  1/11000028  1/121A3C40  OK
 main      12       QEULRD  2020-08-10 15:38:27+03  PAGE  STREAM    1/1     32s  4102MB  32MB    1.00  1/E000028   1/F1A32E8   OK
 main      12       QEUIZD  2020-08-10 14:39:01+03  PAGE  STREAM    1/1   1m:2s  8202MB  32MB    1.00  1/B000028   1/C1A4028   OK
 main      12       QEUG7D  2020-08-10 13:39:01+03  PAGE  STREAM    1/1   1m:2s  8201MB  32MB    1.00  1/8000028   1/91A5600   OK
 main      12       QEUDFD  2020-08-10 12:39:02+03  PAGE  STREAM    1/1   1m:4s  8200MB  32MB    1.00  1/5000028   1/61A4380   OK
 main      12       QEUAND  2020-08-10 11:38:58+03  PAGE  STREAM    1/1   1m:4s  8201MB  48MB    1.00  1/2000028   1/31A5070   OK
 main      12       QEU7VD  2020-08-10 10:38:31+03  PAGE  STREAM    1/1     32s  4106MB  32MB    1.00  0/FF000028  1/1A46D8    OK
 main      12       QEU53D  2020-08-10 09:38:58+03  PAGE  STREAM    1/1   1m:6s  8202MB  32MB    1.00  0/FC000028  0/FD1A5370  OK
 main      12       QEU2BD  2020-08-10 08:38:36+03  PAGE  STREAM    1/1     36s  4102MB  32MB    1.00  0/F9000028  0/FA1A49E0  OK
 main      12       QETZJD  2020-08-10 07:38:37+03  PAGE  STREAM    1/1     42s  4103MB  32MB    1.00  0/F6000028  0/F71A56D0  OK
 main      12       QETWRD  2020-08-10 06:38:59+03  PAGE  STREAM    1/1   1m:2s  8198MB  32MB    1.00  0/F3000028  0/F41A6C70  OK
 main      12       QETTZD  2020-08-10 05:38:31+03  PAGE  STREAM    1/1     33s  4105MB  32MB    1.00  0/F0000028  0/F11A5D58  OK
 main      12       QETR7D  2020-08-10 04:38:57+03  PAGE  STREAM    1/1     57s  8200MB  48MB    1.00  0/ED000028  0/EE1A6A98  OK
 main      12       QETOFD  2020-08-10 03:38:31+03  PAGE  STREAM    1/1     32s  4103MB  32MB    1.00  0/EA000028  0/EB1A60C0  OK
 main      12       QETLND  2020-08-10 02:38:33+03  PAGE  STREAM    1/1     33s  4102MB  32MB    1.00  0/E7000028  0/E81A6D68  OK
 main      12       QETIVD  2020-08-10 01:38:32+03  PAGE  STREAM    1/1     39s  4103MB  48MB    1.00  0/E4000028  0/E51A4448  OK
 main      12       QETG3D  2020-08-10 00:38:31+03  PAGE  STREAM    1/1     35s  4103MB  48MB    1.00  0/E1000028  0/E21A5130  OK
 main      12       QETDBE  2020-08-09 23:38:31+03  PAGE  STREAM    1/1     31s  4101MB  32MB    1.00  0/DE000028  0/DF1A66B8  OK
 main      12       QETBK1  2020-08-09 23:00:03+03  PAGE  STREAM    1/1      5s  2923kB  48MB    1.00  0/DB000028  0/DC0000B8  OK
 main      12       QETAJD  2020-08-09 22:38:27+03  PAGE  STREAM    1/1     32s  4098MB  32MB    1.00  0/D8000028  0/D91A5500  OK
 main      12       QET9XE  2020-08-09 22:25:56+03  PAGE  STREAM    1/1  1m:11s  8941MB  32MB    1.00  0/D5000028  0/D6001F60  OK
 main      12       QET9SV  2020-08-09 22:23:10+03  FULL  STREAM    1/0  1m:10s  7688MB  32MB    1.00  0/AD0000D8  0/AD000250  OK
-bash-4.2$

Directory with backup ..../QEVARD/database/base/pgsql_tmp contains large "pgsql_tmp" subdir.

ls -lh /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/*
-rw-------. 1 postgres postgres 1.0G Aug 11 00:38 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.0
-rw-------. 1 postgres postgres 1.0G Aug 11 00:38 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.1
-rw-------. 1 postgres postgres 1.0G Aug 11 00:41 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.10
-rw-------. 1 postgres postgres 1.0G Aug 11 00:41 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.11
-rw-------. 1 postgres postgres 1.0G Aug 11 00:42 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.12
-rw-------. 1 postgres postgres 1.0G Aug 11 00:42 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.13
-rw-------. 1 postgres postgres 1.0G Aug 11 00:42 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.14
-rw-------. 1 postgres postgres 1.0G Aug 11 00:43 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.15
-rw-------. 1 postgres postgres 1.1M Aug 11 00:38 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.16
-rw-------. 1 postgres postgres 1.0G Aug 11 00:42 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.2
-rw-------. 1 postgres postgres 1.0G Aug 11 00:42 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.3
-rw-------. 1 postgres postgres 1.0G Aug 11 00:39 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.4
-rw-------. 1 postgres postgres 1.0G Aug 11 00:43 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.5
-rw-------. 1 postgres postgres 1.0G Aug 11 00:39 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.6
-rw-------. 1 postgres postgres 1.0G Aug 11 00:40 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.7
-rw-------. 1 postgres postgres 1.0G Aug 11 00:41 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.8
-rw-------. 1 postgres postgres 1.0G Aug 11 00:42 /vol/pgsql/backups/main/QEVARD/database/base/pgsql_tmp/pgsql_tmp19299.9
Press any key to continue...

Is it OK, that "pgsql_tmp*" files goes to PAGE backup ?
@gsmolk
Copy link
Contributor

gsmolk commented Aug 11, 2020

Hello!
That is strange, pgsql_tmp should be excluded during backup:
https://github.com/postgrespro/pg_probackup/blob/master/src/dir.c#L40

What version are you using? Is it backup via ssh?

@beremour
Copy link
Author

Hello! Thank you for reply!

yum list "postgresql12"
postgresql12.x86_64         12.3-5PGDG.rhel7

yum list "pg_probackup-12"
pg_probackup-12.x86_64   2.4.2-1.8db55b42aeece064

No ssh used. Just backup from main instance to NFS share.

@gsmolk
Copy link
Contributor

gsmolk commented Aug 11, 2020

Hm, looks like a bug to me, I`m going to try to reproduce.

@beremour
Copy link
Author

My command line for PAGE backup was:

pg_probackup-12 backup   -B /backup/pgsql -U backup --instance main -b PAGE --stream --temp-slot --merge-expired --delete-expired --delete-wal -j 4

Also I've set:

pg_probackup-12 set-config  -B /backup/pgsql --instance main --retention-redundancy=1 --retention-window=1

@beremour
Copy link
Author

I am still running test environment and noticed that pgsql_tmp files exists in every my PAGE-backup. And sizes are different.

-bash-4.2$ ls -lh /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/*
-rw-------. 1 postgres postgres 1.0G Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.0
-rw-------. 1 postgres postgres 1.0G Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.1
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.10
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.11
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.12
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.13
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.14
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.15
-rw-------. 1 postgres postgres 1.1M Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.16
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.2
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.3
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.4
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.5
-rw-------. 1 postgres postgres 1.0G Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.6
-rw-------. 1 postgres postgres 1.0G Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.7
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.8
-rw-------. 1 postgres postgres    0 Aug 12 13:38 /backup/pgsql/backups/main/QEY5JD/database/base/pgsql_tmp/pgsql_tmp11245.9
-bash-4.2$

@gsmolk
Copy link
Contributor

gsmolk commented Aug 14, 2020 •
edited
Loading

Hm, that is really strange - I've failed to reproduce the issue.
Can you run backup with --log-level-file=VERBOSE --log-filename=testrun.log options and attach testrun.log file?

@gsmolk
Copy link
Contributor

gsmolk commented Aug 14, 2020

Are FULL backups affected too?

@beremour
Copy link
Author

pg_probackup-page.log

Log attached

@beremour
Copy link
Author

Just FYI: My test environment inserts 10'000 records per 10 minutes using cron job. Do these scrips interesting for you ?

@gsmolk
Copy link
Contributor

gsmolk commented Aug 14, 2020

No, thanks!
I`ve managed to reproduce the issue, it is a bug all right.
Thank you for reporting it!

gsmolk added a commit that referenced this issue Aug 14, 2020
@gsmolk
[Issue #246] added tests.exclude.ExcludeTest.test_exclude_temp_files
b7a4439
@beremour
Copy link
Author

pg_probackup-full-200814.txt

Log of FULL backup

@beremour
Copy link
Author

and YES - full backup has this also.

@gsmolk gsmolk added bug and removed question labels Aug 14, 2020
@gsmolk
Copy link
Contributor

gsmolk commented Aug 31, 2020

That is a regression introduced by 6ed7350.

gsmolk added a commit that referenced this issue Aug 31, 2020
@gsmolk
[Issue #246] fix tests.exclude.ExcludeTest.test_exclude_temp_files
28dfccd
gsmolk added a commit that referenced this issue Aug 31, 2020
@gsmolk
[Issue #246] correctly exclude nested directories such as 'pgsql_tmp'
46c7dba
@gsmolk
Copy link
Contributor

gsmolk commented Aug 31, 2020

Fixed.
Fix will be shipped with 2.4.3 release on this week.
Thank you for a feedback!

@beremour
Copy link
Author

thank you very much!

@gsmolk gsmolk added this to the 2.4.3 milestone Aug 31, 2020
@gsmolk gsmolk closed this as completed Sep 2, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug
Projects
None yet
Milestone
2.4.4
Development

No branches or pull requests
2 participants
@gsmolk @beremour