OT Advice and suggestions needed

[NickFriend]

Hi Karl,

Further to this, if PostgreSQL is very easy to install and maintain, I'm wondering if there's any reason why we shouldn't use it on single-user machines as well. Does it have much of a footprint? Does it run as a service or can it be embedded in some way with the app if it's on the local machine?

Thanks

Nick

[FFF]

Nick,
even i succeeded in install - there are two installer sets available, i use the one of EnterpriseDB. Install is about 2min...

At the moment on my 6 years old desktop i have three versions running, 9.6, 10.1. and 11.1, the actual size of e.g. my 11.1 install is about 500MB.
I don't notice any impact on my system, so, i'm with you re "why not"

One thing i saw: my banking software sits on SQLite, a query with two params over some 20k rows needs about 4 sec. I exported the data to "access" format, imported into PG, asked the same questions and got my result in milliseconds. Ok, i suppose, the app may be to blame, but nevertheless, i'm impressed...

[NickFriend]

Thanks Karl, so far I'm liking what I'm hearing.

Nick

[wriedmann]

Hi Nick,

I've decided the "document way" because each order is a document by itself, and is not connected to any other order, and it simplifies the distribution of these documents a lot.
In a similar system, there are imports end exports needed - with my single file solution the program in the background retrieves the most recent document from the central server and opens it. When it is changed, it uploads it again. And the best is that I also have a versioning this way....

Wolfgang

[NickFriend]

Just throwing it out there, has anyone used Firebird?

Nick

[NickFriend]

Hi Wolfgang,

Ok. Ours is slightly different, because when a quote is accepted and goes for production, it's returned to the manufacturer and all the quote line items have to be "reconnected" to the primary data in the manufacturers database to be able to access internal production info, technical documents, etc. to handle the physical production.

Nick

[wriedmann]

Hi Nick,
originally we had selected Firebird as default database for our applications, both Windows and web, but it worked not so well, and it needed special managment tools that were costing more than the universal tool we use for all other databases (Navicat Premium Essentials).
Fortunately we could change before investing too much in Firebird.
Wolfgang

[NickFriend]

Good information Wolfgang, thanks.

[lumberjack]

Hi Nick,

Has anyone used it with Entity Framework?

Have not yet used it, but I know from personal experience, Francisco Figueiredo Jr. and his support and that of the team is on par what we experience from Robert and Team.

Bit off topic to your original question, one of the nice features of PG "INSERT INTO tbl (columns) VALUES (row1), (row2), (row3) RETURNING *" You can thus run your statement via a DataReader and it will return the inserted rows with all server side populated columns e.g AutoNumbers. It has a 20x speed increase compared to a "normal" insert/retrieve.

The other extension
CREATE TABLE GeneralLedger....

CREATE TABLE GL2019 INHERIT GeneralLedger CHECK txnDate >= 2019-01-01 AND txnDate <= 2019-12-31

ALTER TABLE GL2018 UNINHERIT GeneralLedger

Now select * from GeneralLedger will only look in GL2019 for data or will be clever enough to know which inherited tables to look in if a txnDate is included in the WHERE clause.

Also love the Master to Slave replication (Slony), scheduled jobs (pgAgent), GIS (PostGIS). Can also use the Foreign Data Wrappers during porting to use a PG server with FDW to talk to other DB's (Oracle_FDW, SqlServer_FDW) until you have all inside of the PG DB's. You can even use this to set a referential constraint between a SqlServer DB and an Oracle DB as long as you populate via the PG server.

Regards,

As Karl stated multiple versions on single server with just the listening port different, solid as a rock,

[NickFriend]

Thanks for the suggestions and input. I'm definitely tending towards PostgreSQL and to run it on both individual machines and on servers to keep things simple for development.

The PostgreSQL page has downloads of installers, but also has the option to download the binaries so you can integrate them into your own installer, which is what we'd do. However after an initial look through the docs, I can't find instructions on what the install process should be when working with the binaries.

Can anyone give me any pointers, even better an InnoSetup script, or point me towards the documentation for this.

Thanks

Nick