Postgres backup query
will
will.adkin at lineal.co.uk
Thu Aug 2 09:21:33 EDT 2018
Thanks Paul,
Another way to achieve what we need - we'll look into it for sure.
Best regards,
Will
Will Adkin / Software Development Manager
will.adkin at lineal.co.uk <mailto:will.adkin at lineal.co.uk>
<mailto:will.adkin at lineal.co.uk>Lineal Software Solutions
01271 375999
Unit 7, Riverside Court, Castle Street
Barnstaple, Devon, EX31 1DR
www.lineal.co.uk <http://www.lineal.co.uk/>
> On 2 Aug 2018, at 01:51, Paul Mulroney <pmulroney at logicaldevelopments.com.au> wrote:
>
> Hi $all,
>
> We use something like the following to run a batch file and wait for it to complete. Basically wait for the batch file to flag that it's completed, by writing to a specific file. We keep trying to read the file until it's done.
>
> Note: Postgres can perform a backup concurrently, so you can initiate a backup and then keep doing other things - it uses some kind of pointer in time to base the backup on. You don't really need to wait, unless you plan to do something with the file later on...
>
> ; - Create the batch file that will be used to execute the pg_dump command
> Begin text block
> Text: SET PGPASSWORD=[vsPassword] (Platform newline)
> Text: [vsDrive] (Platform newline) ;; eg N:
> Text: cd "[vsDir]" (Platform newline) ;; eg \backups\dcBU01
> Text: SET HOUR=%time:~0,2% (Platform newline)
> Text: SET dtStamp9=%date:~-4%-%date:~7,2%-%date:~4,2%_0%time:~1,1%-%time:~3,2% (Platform newline)
> Text: SET dtStamp24=%date:~-4%-%date:~7,2%-%date:~4,2%_%time:~0,2%-%time:~3,2% (Platform newline)
> Text: if "%HOUR:~0,1%" == " " (SET dtStamp=%dtStamp9%) else (SET dtStamp=%dtStamp24%) (Platform newline)
> Text: echo Started backup %DATE% %TIME% > results.txt (Platform newline)
> Text: "[vsPgDumpCmd]" --username [vsUsername] --format custom --clean -c -v -f "[vsDatabase]_%dtStamp%.backup" [vsDatabase] >> results.txt 2>&1 (Platform newline) ;; Also append error output to the output. Backup all schemas
> Text: echo Ended backup %DATE% %TIME% >> results.txt (Platform newline)
> Text: echo DONE >> results.txt (Platform newline)
> End text block
> Get text block vsCommand
> Calculate vsBatchFileName as con(lBackupPath,'BackupDatabase.bat')
> Do FileOps.$deletefile(vsBatchFileName) Returns vnResult
> If (vnResult<>kFileOpsOK)&(vnResult<>kFileOpsFileNotFound) ;; Exclude No Error and File Not Found - this is an error we can't deal with
> Calculate vbError as kTrue
> Else
> Do voFileOps.$createfile(vsBatchFileName) Returns vnResult
> If vnResult=1 ;; Flag true -> file was able to be created
> Do voFileOps.$writefile(chartoutf8(vsCommand))
> Do voFileOps.$closefile()
> Calculate vsResultsPath as con(lBackupPath,'results.txt')
> Do FileOps.$deletefile(vsResultsPath) Returns vnResult
> If (vnResult<>kFileOpsOK)&(vnResult<>kFileOpsFileNotFound) ;; Exclude No Error and File Not Found - this is an error we can't deal with
> Calculate vbError as kTrue
> Else
> ; - Run the batch file and wait for the result
> Start program minimized {[vsBatchFileName]}
> End If ;; Delete the old results file
> End If ;; Create new batch file
> End If ;; Delete batch file
>
> if not(vbError)
> Calculate vbDone as kFalse
> Calculate vnNow as #CT ;; Clock ticks
> Repeat
> If FileOps.$doesfileexist(vsResultsPath)
> Do voFileOps.$openfile(vsResultsPath,kTrue) ;; open read only
> Do voFileOps.$readcharacter(kUniTypeUTF8,vsResults) ;; Read in as ASCII format
> Do voFileOps.$closefile()
> If pos('DONE',vsResults)<>0
> Calculate vbDone as kTrue
> Else If #CT>(vnNow+60000) ;; 600 seconds -> 10 minutes. That should be long enough to dump the database.
> Calculate vbError as kTrue
> Calculate vbDone as kTrue
> ; - Log to the error log.
> Do ioLogfile.$errorLog(con('Backup failed. Following is the batch file ',vsBatchFileName,':'))
> Do ioLogfile.$errorLog(vsCommand)
> Do ioLogfile.$errorLog('')
> Do ioLogfile.$errorLog('Following is the results of the batch file command: ')
> Do ioLogfile.$errorLog(vsResults)
> End If
> Else ;; File doesn't exist. It should have by now ...
> Calculate vbDone as kTrue
> Calculate vbError as kTrue
> End If
> Until vbDone
> End if
>
> Regards,
> Paul.
>
>> On 1 Aug 2018, at 7:58 pm, will via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
>>
>> Perfect
>>
>> Thanks Doug!
>>
>>
>> Best regards,
>>
>> Will
>>
>>
>> Will Adkin / Software Development Manager
>> will.adkin at lineal.co.uk <mailto:will.adkin at lineal.co.uk>
>>
>> <mailto:will.adkin at lineal.co.uk>Lineal Software Solutions
>> 01271 375999
>> Unit 7, Riverside Court, Castle Street
>> Barnstaple, Devon, EX31 1DR
>> www.lineal.co.uk <http://www.lineal.co.uk/>
>>
>>
>>> On 1 Aug 2018, at 12:51, Doug Easterbrook <doug at artsman.com> wrote:
>>>
>>> hi WIll.
>>>
>>> I’ll send you a mac and windows script that you can digest that does everything you want.
>>>
>>>
>>>
>>>
>>> Doug Easterbrook
>>> Arts Management Systems Ltd.
>>> mailto:doug at artsman.com <mailto:doug at artsman.com>
>>> http://www.artsman.com <http://www.artsman.com/>
>>> Phone (403) 650-1978
>>>
>>>> On Aug 1, 2018, at 4:46 AM, will via omnisdev-en <omnisdev-en at lists.omnis-dev.com <mailto:omnisdev-en at lists.omnis-dev.com>> wrote:
>>>>
>>>> Hi List,
>>>>
>>>> How do you tell when pg_dump has finished a backup on PC?
>>>>
>>>> We want to issue a backup DB command and then copy the file off site via ftp.
>>>>
>>>> We use a bat file to trigger the backup control, but on Windows terminal sever 2012 this only begins the backup process and control is immediately returned to Omnis before it has finished. We could add code to check the size of the file on a timer and continue when it stops changing, but perhaps there is a better way?
>>>>
>>>> On Mac the $runapplescript equivalent waits for the backup to complete before coming back to Onnis, so perhaps there is a way to ask the batch file to wait until completion?
>>>>
>>>> For us an example Bat file would be something like:
>>>>
>>>>
>>>> @echo off
>>>> setlocal
>>>> set PGPASSWORD=DBPassword
>>>> "C:\Program Files\PostgreSQL\10\bin\pg_dump" -U username -p 5432 -h localhost -Fc -w SW8_SQLWORKS_COMPANYDB > "C:\SQLWorksBackupFolder\backup.sql"
>>>>
>>>>
>>>> and we are running is with the following Omnis code (in 8.1.6):
>>>>
>>>>
>>>>
>>>> Get text block lvBackupScript
>>>> Do pths.$:PathTempFolder Returns lvBatFilePath
>>>> Calculate lvBatFilePath as con(lvBatFilePath,sys(9),'backuppg.bat')
>>>> Do lvFileOps.$createfile(lvBatFilePath) Returns lvErrorCode
>>>> If lvErrorCode<=1
>>>> ; Must convert text into utf8 when writing or will put null characters into the file and stop it from working
>>>> Do lvFileOps.$writefile(chartoutf8(lvBackupScript))
>>>> Do lvFileOps.$closefile()
>>>> Start program normal {[lvBatFilePath]}
>>>> Launch program [lvBatFilePath] Returns lvErrorCode
>>>> End If
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Best regards,
>>>>
>>>> Will
>>>>
>>>>
>>>> Will Adkin / Software Development Manager
>>>> will.adkin at lineal.co.uk <mailto:will.adkin at lineal.co.uk> <mailto:will.adkin at lineal.co.uk <mailto:will.adkin at lineal.co.uk>>
>>>>
>>>> <mailto:will.adkin at lineal.co.uk <mailto:will.adkin at lineal.co.uk>>Lineal Software Solutions
>>>> 01271 375999
>>>> Unit 7, Riverside Court, Castle Street
>>>> Barnstaple, Devon, EX31 1DR
>>>> www.lineal.co.uk <http://www.lineal.co.uk/> <http://www.lineal.co.uk/ <http://www.lineal.co.uk/>>
>>>>
>>>>
>>>> _____________________________________________________________
>>>> Manage your list subscriptions at http://lists.omnis-dev.com <http://lists.omnis-dev.com/>
>>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com <mailto:omnisdev-en at lists.omnis-dev.com>
>>>
>>
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>
>
> If you are wise you will only believe half of what you hear. if you are brilliant you will know which half to believe.
> --
> Paul W. Mulroney We Don't Do Simple Pty Ltd
> pmulroney at logicaldevelopments.com.au Trading as Logical Developments
> www.logicaldevelopments.com.au ACN 161 009 374
> Ph: +61 8 9458 3889 86 Coolgardie Street
> BENTLEY WA 6102
>
>
>
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
More information about the omnisdev-en
mailing list