Thank you, that pointed me in the right direction. That query succeeded, but took a very long time. I did "explain analyze" on it, and saw that it was using much more than than the allotted working memory (using 334Mb, where work_mem in postgres was set to 4Mb), which caused postgres to use an external merge rather than quicksort. I set work_mem to 512MB, which brought the execution time from 224 s to 38 s and allowed the Bareos restore interface to function.
On Mon, Oct 28, 2024 at 6:59 AM Bruno Friedmann (bruno-at-bareos) < [email protected]> wrote: > Hi Toma, > > It look like the query building the tree didn't finish or vanish. > > This may happen when your PostgreSQL cluster doesn't have enough free > space to store tmp queries/tables.... During a very limited time, the disk > space become full and the connection is then cleared, PG dropping the > running worker, free space reappear just after. > > You may want to try the following query (issue from your trace) and watch > carefully the free space available where you PG cluster reside and build > its temp data > > SELECT DISTINCT ON (PathId) 'D', SpecialDir.PathId, SpecialDir.Path, > JobId, LStat, FileId FROM ( SELECT 2711925 AS PathId, '.' AS Path UNION > SELECT PPathId AS PathId, '..' AS Path FROM PathHierarchy WHERE PathId = > 2711925 ) AS SpecialDir LEFT JOIN ( SELECT PathId, JobId, LStat, FileId > FROM File WHERE File.Name = '' AND File.JobId IN > (46255,46283,46436,46623,46653,51205,51516,51538,51560,51582,51604) ORDER > BY PathId ASC, JobId DESC ) AS DirAttribute ON (SpecialDir.PathId = > DirAttribute.PathId) UNION ( SELECT DISTINCT ON (Path) 'D', PathId, Path, > JobId, LStat, FileId FROM ( SELECT Path1.PathId AS PathId, Path1.Path AS > Path, lower(Path1.Path) AS lpath, listfile1.JobId AS JobId, listfile1.LStat > AS LStat, listfile1.FileId AS FileId FROM ( SELECT listpath1.PathId AS > PathId FROM ( SELECT DISTINCT PathHierarchy1.PathId AS PathId FROM > PathHierarchy AS PathHierarchy1 INNER JOIN Path AS Path2 ON > (PathHierarchy1.PathId = Path2.PathId) INNER JOIN PathVisibility AS > PathVisibility1 ON (PathHierarchy1.PathId = PathVisibility1.PathId) WHERE > PathHierarchy1.PPathId = 2711925 AND PathVisibility1.JobId IN > (46255,46283,46436,46623,46653,51205,51516,51538,51560,51582,51604) ) AS > listpath1 LEFT JOIN ( SELECT PVD1.PathId AS PathId FROM ( SELECT PV1.PathId > AS PathId, MAX(JobId) AS MaxJobId FROM PathVisibility AS PV1 WHERE JobId IN > (46255,46283,46436,46623,46653,51205,51516,51538,51560,51582,51604) GROUP > BY PathId ) AS PVD1 INNER JOIN File AS F2 ON (F2.PathId = PVD1.PathId AND > F2.JobId = PVD1.MaxJobId AND F2.FileIndex = 0 AND F2.Name = '') ) AS > listpath2 ON (listpath1.PathId = listpath2.PathId) WHERE listpath2.PathId > IS NULL ) AS listpath3 INNER JOIN Path AS Path1 ON (listpath3.PathId = > Path1.PathId) LEFT JOIN ( SELECT File1.PathId AS PathId, File1.JobId AS > JobId, File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 > WHERE File1.Name = '' AND File1.JobId IN > (46255,46283,46436,46623,46653,51205,51516,51538,51560,51582,51604) ) AS > listfile1 ON (listpath3.PathId = listfile1.PathId) ) AS A ORDER BY Path > ASC, JobId DESC ) ORDER BY Path ASC,JobId DESC LIMIT 1000 OFFSET 0 > > > On Friday 25 October 2024 at 10:37:27 UTC+2 Toma Morris wrote: > >> Here is the debug trace during attempted restore, resulting in the same >> error condition. Thank you! >> >> -Toma >> >> On Thursday, October 24, 2024 at 12:40:24 AM UTC-4 Sebastian Sura wrote: >> >>> Hi Toma, >>> >>> could you try doing a debug trace on the director when you are trying >>> the restore ? >>> You can enable the debug log by writing >>> >>> setdebug level=200 trace=1 director >>> >>> in the bconsole. Once you have reproduced the issue, you can turn it >>> off with e.g. setdebug level=10 trace=0 director again. >>> Having this debug trace would be immensely helpful. >>> >>> Kind Regards, >>> >>> Sebastian Sura >>> Am 23.10.24 um 19:47 schrieb Toma Morris: >>> >>> Hi, >>> >>> I am trying to restore files from one of my backups. In the Restore tab >>> of the webui, I select the client and then after a delay I get the >>> following error message: >>> >>> {"id":"#","xhr":{"readyState":4,"responseText":"Decoding failed: Syntax >>> errorError: API 2 not available on director.\n Please >>> upgrade to verison 15.2.2 or greater and/or compile with jansson >>> support.","status":200,"statusText":"OK"} >>> >>> Bareos is running on an Ubuntu 20.04 server with hostname cscisbu01, and >>> I am trying to access a backup from a client with hostname csspfile01. I >>> initially got this error while on 23.0.4~pre169.6644119b5-114. I upgraded >>> bareos on both the server and client to the most recent version via apt, >>> which brought me up to 23.0.5~pre146.7e91df1c0-130 with no apparent change >>> in behavior. >>> >>> I have successfully restored files within the last couple of months on >>> this server from the same client, and have not changed any configurations >>> since then. >>> >>> I've attached my apache2 error logs, which contain some >>> possibly-relevant php errors. I didn't see anything relevant in >>> /var/log/bareos/bareos.log or /var/log/bareos/bareos-audit.log, but can >>> provide those or anything else if needed. >>> >>> Thanks, >>> >>> -Toma >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "bareos-users" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To view this discussion visit >>> https://groups.google.com/d/msgid/bareos-users/4ea3f42e-9b0e-4c3f-b28d-a2f382b3d81an%40googlegroups.com >>> <https://groups.google.com/d/msgid/bareos-users/4ea3f42e-9b0e-4c3f-b28d-a2f382b3d81an%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >>> -- >>> Sebastian Sura [email protected] >>> Bareos GmbH & Co. KG Phone: +49 221 630693-0 >>> https://www.bareos.com >>> Sitz der Gesellschaft: Köln | Amtsgericht Köln: HRA 29646 >>> Komplementär: Bareos Verwaltungs-GmbH >>> Geschäftsführer: Stephan Dühr, Jörg Steffens, Philipp Storz >>> >>> -- > You received this message because you are subscribed to a topic in the > Google Groups "bareos-users" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/bareos-users/t7mBaL-hNKI/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To view this discussion visit > https://groups.google.com/d/msgid/bareos-users/cb1770ec-d5b0-4744-9d56-46eb8a30ccfdn%40googlegroups.com > <https://groups.google.com/d/msgid/bareos-users/cb1770ec-d5b0-4744-9d56-46eb8a30ccfdn%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "bareos-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/bareos-users/CAKfWHboTykT6rJ_7kv%2BXHjE91NBZ2R_tS4c-xbw%3DoFJy%3Dc_M%3DQ%40mail.gmail.com.
