PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Filtering FORFILES in batch files

I’m trying to write a batch file to run on Task Scheduler on Windows server to delete some PostgreSQL archived files.

Thought I use FORFILES and the following will list all the .backup files in the archived folders.

SET "log=%USERPROFILE%tempdelete.log"
SET "target.dir=\UNC_pathpgsql_arch"

pushd "%target.dir%"
FORFILES /S /D -30 /M *.backup /C "cmd /c echo @file"
popd

The *.backup file is a 1kb and in PostgreSQL, it looks like information of what the backup contains. I do not want to delete those – what I’m trying to get is the the last file of the 30th day so I can pass into pg_archivecleanup to let it do its thing.

For example, today is 2/22/2024 – so essentially I’m trying to get the .backup filename for 1/21/2024 to pass into pg_archivecleanup to delete all archived files older than 1/21/2024.

Question, is it possible to filter the list that FORFILES returns to just 1 single filename? If so, how can I do that?

I welcome any other suggestions. This is windows and it’s a single PostgreSQL to support 3rd party vendor product.

Thank you

Tried

SET "log=%USERPROFILE%tempdelete.log"
SET "target.dir=\UNC_pathpgsql_arch"

pushd "%target.dir%"
FORFILES /S /D -30 /M *.backup /C "cmd /c echo @file"
popd

Expecting:
Need just single row from FORFILES results.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Final working script in Powershell for future visitors to clean PostgreSQL Archived Location if you don't have replicas. Hope it helps someone.

    Clear-Host
$archdir='\uncpathpgsql_arch'
$filename= Get-ChildItem -Path $archdir -filter *.backup | 
           Sort-Object -Property LastWriteTime -Descending |
           Where-Object {$_.lastwritetime -gt (get-date).addDays(-31) -and -not $_.PSIsContainer} | 
           Select -ExpandProperty Name -last 1 
Write-Output "$archdir $filename"
$script="$archdir $filename"
Start-Process -FilePath "Your_PostgreSQL_bin_pathpg_archivecleanup.exe" -ArgumentList "-d $script" -Wait

  2. 0

    This turns out to be a bit tricky, because you probably want the newest of your outdated files. FORFILES returns an unsorted list of files, so you cannot just get the first line only.

    Here is what I came up with to achieve the sorting using two file listings and cross-matching them. It would be easier to use SORT, but this suffers from different locales for the date (i.e. 22.02.2024 vs 2/22/2024).

    @echo off 

set ext=*.backup
set age=-30

rem clear temporary files
if exist allfiles.txt del allfiles.txt
if exist forfiles.txt del forfiles.txt
if exist oldfiles.txt del oldfiles.txt

rem get a list of files sorted by time (newest first)
dir /b /od %ext% >allfiles.txt

rem get an unsorted list of files older than X 
forfiles /D %age% /M %ext% >forfiles.txt 

rem loop through the files and check for a match
for /f "delims=" %%B in (allfiles.txt) do ( 
  findstr /C:"%%B" forfiles.txt >>oldfiles.txt
)

rem get only the first line of our sorted matches
for /f "delims=" %%A in (oldfiles.txt) do (
   echo %%A
   exit /b
   rem this causes the loop to run only once
)

rem clear temporary files
if exist allfiles.txt del allfiles.txt
if exist forfiles.txt del forfiles.txt
if exist oldfiles.txt del oldfiles.txt

rem unset variables
set age=
set ext=

    Feel free to add any degree of complexity, i.e. by inlining the DIR command into the FOR statement. You might also reduce this to run in a single loop by checking the output of FINDSTR directly. Unfortunately, it does not return an ERRORLEVEL that would give an indication of whether there was a match or not…

    Creating three temporary files is surely not the most elegant way to solve this, but this approach seemed fairly straightforward to me.

    PS: If you want to check if the sorting is correct, just insert echo %%~tB >>oldfiles.txt after the findstr command.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search