PostgreSQL based desktop app distribution

I plan to make a Xojo Desktop app based on PostgreSQL to manage data in my App.

Is it easy to distribute my app with PostgreSQL on the macStore ? How can I manage postgressql installation on the final user machine ?

Will the purchaser be storing their data on YOUR PostgreSQL server or their own?

  1. If your PostgreSQL server, then you will need to inform the purchaser that their data will not be stored on the PC/device and opt-in their permission.
  2. If their PostgreSQL server, then you will need to give them full instructions to enter the host, port, username and password. You will need to provide a sample login to Apple so their reviewers can test it.

Purchaser will use their own database locally on their own machine. My question is about : Does PostgreSQL if already install on all macOS machine ?

No.

Why don’t you use SQLite ?

I prefer PostgreSQL because I want to provide a multi user access to the database (over final user personal network).

Will the PostgreSQL db on the host’s machine accessible fro other clients on the network, meaning it will be at the same time one user’s client and a postgres server ?

Yes absolutely

I know of one (french) accounting app named cogilog that uses a local postgres database
it is NOT available from the app store …

there is an open source app “postgres app”

may be you could explore the source to see how it works ?

2 Likes

I use Cogilog Suit it’s a vey nice and stable app. I will recommend it to all French users^^

I’m now envisaging to use a cloud server maybe with Heroku or Xojo Cloud (prices look similar) but I’m afraid with data security any idea to secure user personal data in PostreSQL on Heroku or XojoCloud?

Just spitballing here, but since you’re targeting only macOS, if you’re willing to limit to macOS 11 or newer, you could use the new virtualization framework. Apple Developer Documentation

You’d prepare a lightweight Linux vm image (2 actually, one for Intel and one for ARM) with pg installed. When your app launches, you fire up the vm and can then connect to the server.

This is entirely theory, and I’m pretty confident user data would be stored inside the vm.

yes but why you want to have local pg db and a remote one ? or they are different dbs ? you want to sync local /remote ? if your local users will acess your local db, why not only put everything on cloud somewhere ?

If I understand your model correctly, your client will install the database on one given workstation (let’s call it dhe DB Server) and use the application on multiple workstations. Then, the customer will have to manage PostgreSQL permissions, server firewall permissions, etc.

The above suggests that someone will have to install PostgreSQL and do the necessary configuration before installing and using the application. While it is not overly difficult once you did it once or twice, you need to deal with varying levels of IT proficiency. The simplest approach would be to use a PostgreSQL installer that will hold the user’s hand throughout the process… There are a few out there. I once used the EnterpriseDB installer to install PostgreSQL on Windows. (I prefer Linux now) Take a look here: Download PostgreSQL (enterprisedb.com). Perhaps linking to one of the installers on their site is the best way forward. Or linking to one of the other installers available on the Web.

on the cogilog accounting app mentionned above,
when you install the first user, the app installs a postgres server on the user computer
then when you install the other users, it detects there is a server on the network and automagically installs a client app that connects to the first computer.
completely simple and transparent. no db admin needed.

That is neat. sounds like a custom installer and a single application userid in the database. Nice and simple.