How to run PG_DUMP on OSX

Wed Mar 1 01:17:16 UTC 2023

Hi Mike I have written a window with the postgres utilities in it, with the exception of MAC coding, it works ok on Windows and Linux, and how I did this was to mimic the PGAdmin4 scripts. I could send you the window and object and you could finish the MAC bit. If this helps give me a email.

-----Original Message-----
From: omnisdev-en [mailto:omnisdev-en-bounces at lists.omnis-dev.com] On Behalf Of Mike Matthews - Omnis via omnisdev-en
Sent: Wednesday, 1 March 2023 4:13 AM
To: OmnisDev List - English
Cc: Mike Matthews - Omnis
Subject: Re: How to run PG_DUMP on OSX

Final question on this:

How else can I get Terminal to run this command, ie, avoid the $AppleScript command?  On Windows, we can create a batch file and run it, but I’m guessing we could do the same, but how to run that shell script file with the use of $AppleScript?

Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:mike.matthews at lineal.co.uk>

www.lineal.co.uk<http://www.lineal.co.uk/>

www.sqlworks.co.uk<http://www.sqlworks.co/>

On 28 Feb 2023, at 17:57, Mike Matthews - Omnis via omnisdev-en <omnisdev-en at lists.omnis-dev.com<mailto:omnisdev-en at lists.omnis-dev.com>> wrote:

Caution: This is a message which has originated from outside the organisation. Ensure the sender is trusted and the content is safe before opening links or attachments.

Update on this trouble.

Run the script from Terminal, same time to run, same size of file, but no memory loss in Activity Monitor, which points to the $AppleScript command.  I’ll pass back to Omnis support now.

Thank you :)

Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:omnis at lineal.co.uk><mailto:mike.matthews at lineal.co.uk>

www.lineal.co.uk<http://www.lineal.co.uk><http://www.lineal.co.uk/>

www.sqlworks.co.uk<http://www.sqlworks.co.uk><http://www.sqlworks.co/>

On 28 Feb 2023, at 17:13, Mike Matthews - Omnis via omnisdev-en <omnisdev-en at lists.omnis-dev.com<mailto:omnisdev-en at lists.omnis-dev.com><mailto:omnisdev-en at lists.omnis-dev.com>> wrote:

Caution: This is a message which has originated from outside the organisation. Ensure the sender is trusted and the content is safe before opening links or attachments.

Great idea, run the script from terminal!

Our Macs only have 16gb, and the M1 MacMini only has 8gb, which to date has been more than enough.  They are so powerful compared to the Intel types, really impressive.  So long as all native applications though, that makes a huge difference.

Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:omnis at lineal.co.uk><mailto:omnis at lineal.co.uk><mailto:mike.matthews at lineal.co.uk>

www.lineal.co.uk<http://www.lineal.co.uk><http://www.lineal.co.uk><http://www.lineal.co.uk/>

www.sqlworks.co.uk<http://www.sqlworks.co.uk><http://www.sqlworks.co.uk><http://www.sqlworks.co/>

On 28 Feb 2023, at 16:50, Doug Easterbrook <doug at artsman.com<mailto:doug at artsman.com><mailto:doug at artsman.com><mailto:doug at artsman.com>> wrote:

Caution: This is a message which has originated from outside the organisation. Ensure the sender is trusted and the content is safe before opening links or attachments.

hi Mike.

you’ll have received the backup script by now as an option.

but the question I have is:

if you run the command using terminal, do you run out of memory?
—  if so, then it is pg_dump.
— if not, then it is applescript nonsense that apple needs to fix.  (this is what I suspect)

I also have an M1 mac and I can routinely dump and restore 3 or 4 databases at a time (large ones at that.   100 gb .backup files) and still use omnis to write code while these backups are running in terminal sessions, sometimes for a few hours.

I have not had the unpleasant experience of running out or memory.

I’m using monterey and have 64 gigs ram on my m1 machine.

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com<http://www.artsman.com/>
Phone (403) 650-1978

On Feb 28, 2023, at 8:20 AM, Mike Matthews - Omnis <omnis at lineal.co.uk<mailto:omnis at lineal.co.uk>> wrote:

Hello Doug,

Some great advice. We do already encrypt/hash passwords with that level so we can store inside the database, and then run it under our control.  The key is stored away from the library as well, so it can’t fall into the wrong hands.

I also use HexFiend, so understand that point, all is safe as far as we know.  ;)

After we make the backup, we encrypt it with keys, and then we SFTP to our secure SFTP servers using the OW3 worker.

Now I think we pass the PCI controls for all of that, and we recently went through ISO 9001 and ISO 27001 at Lineal, and passed.  Quite a thorough process indeed.  The CE Plus as well for the whole business.  Passed.

The reason I ask is that on M1 MacMini, as I run the AppleScript, the memory usage for Omnis goes through the roof during PG_DUMP.  From 550Mb to 7.0Gb, yes GB!.

Open Activity monitor and see what happens if you use AppleScript to do this.  Open Activity monitor anyway and see what happens anyway, I'd be interested, as now we get the Mac actually showing ‘I’m running out of memory' warnings.  Never seen that before.

Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:mike.matthews at lineal.co.uk>

www.lineal.co.uk<http://www.lineal.co.uk/>

www.sqlworks.co.uk<http://www.sqlworks.co/>

On 28 Feb 2023, at 16:07, Doug Easterbrook <doug at artsman.com<mailto:doug at artsman.com>> wrote:

Caution: This is a message which has originated from outside the organisation. Ensure the sender is trusted and the content is safe before opening links or attachments.

hi mike:

there are some things I wouldn’t do — like put it in an application.

there are some things you are assuming
1) that the machine has a copy of pg_dump on it.  (i.e. the app is not portable)
2) that its a good idea to provide the pg_Password within the application.

if you are going to do that, I would ENCRYPT all passwords with AES 256 and prior to using them, decrypt them using a pass key (that is also encrypted with AES256

why?    if bad guys want access, the first thing I’d do is look for tell tale strings in the library.lbs using a hex editor (like hexFiend).

so to get the pg password you might want to have something like

pgpassword = 12351abec952ef52351abec952. <— this is aes 256 encrypted
key = 1abec95212351abec952ef5235.  <— this is also aes 256 encrypted

decryptkey = mechanism for decrypting ‘key’.    (we use tmobjs, so I don’t have the omnis built in commands offhand)

decryptedPgPassword = mechanism for decrypting pgPassword (using decrypteKey)

in other words, double decrypt two keys to get pg password.    EVEN BETTER is if the ‘key' is particular to the specific database like a decrypted customer number kind of thing :) that people can’t see when reading the database.

this is the sort of obfuscation we’ve had to put in place to be PCI compliant on credit card encryption.  s o we use the mental process to guard any passwords ..   and especially to make sure that they are not clear text in the application.

3) it requires user intervention to make it happen, so is susceptible to nobody using the app, or people forgetting

I’m not sure why you are using omnis to dump the database — except if you want it done under user control.    I’m more likely take it out of user control and give it to the OS as a scheduel task, that way backups are regular.

4) its missing some key things like getting a backup off the machine in case of machine failure

eg, do you want to sftp a backup to another machine, or your cloud backup location??

I’ll send you a script we’ve used for ages to automate things.

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com<http://www.artsman.com/>
Phone (403) 650-1978

On Feb 28, 2023, at 7:18 AM, Mike Matthews - Omnis via omnisdev-en <omnisdev-en at lists.omnis-dev.com<mailto:omnisdev-en at lists.omnis-dev.com>> wrote:

Hello All,

How do you all run PG_DUMP to get a backup out of a Postgres database.

We use an AppleScript to do ours on OSX like this:

Begin text block
Text:tell application "Finder"(Carriage return)
Text:do shell script "
Text:PGPASSWORD='[lvSessionRow.superuserpassword]' /Library/PostgreSQL/[lvVersion]/bin/pg_dump [databasename] >
Text: '[pDestinationPathForSQLDBExport]/[pFileName].sql' -U [lvSessionRow.superusername] -p ‘5432' -h [lvSessionRow.hostname] -Fc
Text:"(Carriage return)
Text:end tell(Carriage return)
End text block

Get text block lvScript

Do $runapplescript(lvScript,lvScriptResult,lvAppleScriptError) Returns lvErrorCode

Are we making any mistakes or wrong assumptions here?

Is this the best way to get PG_DUMP to run?

So far, we have used this to create backups, which we have been able to re-import back into Postgres without obvious issue over the past 5 years.

Thanks

Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:omnis at lineal.co.uk><mailto:mike.matthews at lineal.co.uk>

www.lineal.co.uk<http://www.lineal.co.uk/><http://www.lineal.co.uk/>

www.sqlworks.co.uk<http://www.sqlworks.co.uk/><http://www.sqlworks.co/>

_____________________________________________________________
Manage your list subscriptions at https://lists.omnis-dev.com<https://lists.omnis-dev.com/>
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at https://lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at https://lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at https://lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 