{"id":8821,"date":"2022-03-30T13:29:08","date_gmt":"2022-03-30T18:29:08","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=8821"},"modified":"2022-03-30T13:29:08","modified_gmt":"2022-03-30T18:29:08","slug":"analyzing-postgresql-tmp-files","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=8821","title":{"rendered":"Analyzing Postgresql Tmp Files"},"content":{"rendered":"<p>Postgresql stores temporary files for in-flight queries &#8212; these don&#8217;t normally hang around for long, but sorting a large amount of data or building a large hash can create a lot of temp files. A dead query that was sorting a large amount of data or &#8230;. well, we&#8217;ve gotten <em>terabytes<\/em> of temp files associated with multiple backend process IDs. The file names are algorithmic &#8212; a string &#8220;pgsql_tmp followed by the backend PID, a period, and then some other number. Thus, I can extract the PID from each file name and provide a summary of the processes associated with temp files.<\/p>\n<p>To view a summary of the temp files within the pgsql_tmp folder, run the following command to print a count then a PID number:<br \/>\n<tt>ls \/path\/to\/pgdata\/base\/pgsql_tmp | sed -nr 's\/pgsql_tmp([0-9]*)\\.[0-9]*\/\\1\/p' | sort | uniq -c<\/tt><\/p>\n<p>A slightly longer command can be used to reverse the columns \u2013 producing a list of process IDs followed by the count of files for that PID \u2013 too:<br \/>\n<tt>ls \/path\/to\/pgdata\/base\/pgsql_tmp | sed -nr 's\/pgsql_tmp([0-9]*)\\.[0-9]*\/\\1\/p' | sort | uniq -c | sort -k2nr | awk '{printf(\"%s\\t%s\\n\",$2,$1)}END{print}'<\/tt><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Postgresql stores temporary files for in-flight queries &#8212; these don&#8217;t normally hang around for long, but sorting a large amount of data or building a large hash can create a lot of temp files. A dead query that was sorting a large amount of data or &#8230;. well, we&#8217;ve gotten terabytes of temp files associated &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1522,30],"tags":[632,1349],"class_list":["post-8821","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-system-administration","tag-bash","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/8821","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8821"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/8821\/revisions"}],"predecessor-version":[{"id":8822,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/8821\/revisions\/8822"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8821"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8821"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8821"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}