OT Advice and suggestions needed

[lumberjack]

Hi Nick,

The interactive installer is found at https://www.enterprisedb.com/software-d ... s-postgres that is the official Windows download page.
For zipped binaries https://www.enterprisedb.com/download-p ... l-binaries

Just make sure you download the standard version. As part of the installation you can select to have StackBuilder also run after the installation. The rest is quite easy. Just one warning.

The default installation will create the data directory in "Program FilesPostgreSQL<version>data", change that to e.g. C:PostgreSQL<version>data

I believe Wolfgang have used an unattended installation as part of software installation. I normally do on-site installation of the software myself hence never had to do anything more than the above, but the zipped binaries are available for doing it via e.g. InnoSetup.

However, this is probably the best describing the zip installation and should assist in doing it via InnoSetup:

https://feilerdev.wordpress.com/2017/12 ... installer/

Let us know if you have any issues. I would suggest maybe do a 11.2 installation via the interactive GUI, and 11.1 via zip. Just use two different listening ports (5432, 5433).

HTH and good luck!

[wriedmann]

Hi Johan,

until now, I had no needs to do an unattended installation of PostgreSQL as I use it mostly on central Linux servers through a http(s) interface, for VO, X# WPF and X# VOGUI applications.
For local data currently I use only SQLite.
And for many projects I work on I have no database choice, but have to use what other software producers used, often MS SQL, Oracle and MySQL.
Wolfgang

[lumberjack]

Hi Wolfgang,
Thanks. I think the link I posted in my last response shows it should be quite easy using the guidelines stipulated. The nice thing about the zip/binary download is that you just unzip it into a folder e.g G:PostgreSQL<version> and start the service as indicated which should be quite easy to do via InnoSetup. Same goes for doing a local GUI installation and just zip your local installation which can then be deployed via the same process in another environment. Obviously having to make sure the .config files is correct for the installation machine.
Regards,

[NickFriend]

Thanks again all for your suggestions and info. Johan, that was a great link for the standalone installation instructions – concise and to the point.

I’ve been able to make very rapid progress, and in case anyone else is interested in this, I’m going to document what I’ve found. I imagine most of this is old news for those of you already using PostgreSQL, but for anyone else hopefully it’ll save some time.

I’d already downloaded the auto installer and installed one instance, called PostgreSQL and running on port 5432 as by default. I now downloaded the zipped binary folder and simply copied all the subfolders to a new folder on my hard drive, which I called QWPostgreSQL. I didn’t bother with the pgAdmin 4 folder as this is the administrator program which was already installed previously. I’m guessing some of the other subfolders are also unnecessary, but this far I’m playing it safe. This gives a footprint of about 250Mb, which compares well with SQL Server (which varies from around 280Mb-800Mb depending on the version).

The database now has to be initialised which is done with the command line utility initdb in the bin folder:
C:QWPostgreSQLbininitdb.exe -D ../data --username=postgres --auth=trust
-D is the path to where your data will be
--username is the name of the superuser
--auth=trust means you don’t need to specify a password as it’ll trust anyone who can access the computer (obviously there are stricter options available, but this makes installation easy).

This creates the data folders in the specified location and prepares the database for use.

By default this is going to use port 5432, but in my case as I already had one instance installed with defaults, I had to go and manually edit the config file C:QWPostgreSQLdatapostgresql.conf and add a line “port = 5433” to change it.

Now you have two options of how to run the server, manually:
C:QWPostgreSQLbinpg_ctl -D ../data start
C:QWPostgreSQLbinpg_ctl -D ../data stop

Or by installing it as a service:
C:QWPostgreSQLbinpg_ctl register -N "QWPostgreSQL" -U "NT AUTHORITYNetworkService" -D ../data –wait
-N is the name of the service you’ll see in the Windows services list.
-U is the Windows account to use
--wait simply tells pg_ctl to wait until the process has completed before returning

In each case the -D option is used to tell the command which instance we’re directing the command at. Clearly you could use manual start/stop for an installation on a single machine, so the application simply starts and stops the server as part of the application startup and shutdown, and on a server you can use the service option.

Very easy and very impressive so far. I’m going to start looking at how to access it via Entity Framework Core using the Npgsql provider. If anyone's interested I can post info about that as I progress - hopefully!

Nick

[FFF]

Nick,
one thing on top:
when doing initdb, add:
-k
--data-checksums
Use checksums on data pages to help detect corruption by the I/O system that would otherwise
be silent. Enabling checksums may incur a noticeable performance penalty. This option can only
be set during initialization, and cannot be changed later. If set, checksums are calculated for all
objects, in all databases.
to the paramlist. Performance impact is IMHO neglectable, and you get another "security net" added...

That's one of my few grudges with the regular installer, that they don't do this by default...

Karl

[NickFriend]

Thanks Karl, more good info.

This link also suggests the performance hit is negligible

https://www.endpoint.com/blog/2015/12/31/postgres-checksum-performance-impact

Nick

[ic2]

Replying on #20:

Hello Nick,

It looks like you made up your mind but I understand you have not done much with PostgreSQL and it looks easy but in case you do need an alternative at some point in time:

The easiest off line solution using techniques you know is a combination of WCF, DBF & ADS. You can use the free ADS Local server and a few DBF files. We use WCF (as you basically explained us how to use in the Devshares) to share data between SQL databases and DBF files. Changes are written as ready to use queries; WCF access the queries and ADS runs them to fill DBF files and WCF sets the queries to processed afterwards.

Should you want to evaluate this just mail me for some code I use.

Dick

[lumberjack]

Hi Nick,

Thanks again all for your suggestions and info. Johan, that was a great link for the standalone installation instructions – concise and to the point.

You welcome, I was also taught something I have never used before. Maybe just a suggestion as your folder layout shown is not clear.

Program Installation:

Code: Select all

<Program Files>PostgreSQL<version>

Data folder:

Code: Select all

<Optional Separate Drive>PG<version>data

It makes it quite easy to even just copy a datafolder over to another machine and have the database available.

in case anyone else is interested in this, I’m going to document what I’ve found. I imagine most of this is old news for those of you already using PostgreSQL, but for anyone else hopefully it’ll save some time.

Please share, we always discover new things from such post. Old habits are difficult to die, hence fresh insight is always good.

I didn’t bother with the pgAdmin 4 folder as this is the administrator program which was already installed previously.

Yes you are correct, I believe you only need the bin folder. A single pgAdmin (latest version) will suffice since you can create connections from it to older versions even remote servers. I connect with pgAdmin even back to V8.4 although it does warn that there might be incompatibilities which I have not yet found.

--auth=trust means you don’t need to specify a password as it’ll trust anyone who can access the computer (obviously there are stricter options available, but this makes installation easy).

Have a look at the documentation, you can also set trust to only localhost/127.0.0.1 in the .config file, it also can be configured to only allow localhost/127.0.0.1 connections. For a localhost installation you can also set the number of concurrent connections to e.g. 1 or 2 since PG uses about 4K of memory per allowed concurrent connection.

Very easy and very impressive so far. I’m going to start looking at how to access it via Entity Framework Core using the Npgsql provider. If anyone's interested I can post info about that as I progress - hopefully!

Please share your experience. In general my belief is that it is as easy as maintaining an MS-Access database.
Good luck!

[NickFriend]

Hi Dick,

Thanks for the input, but I'm steering clear of ADS as I explained to Stefan.... we need a solution for the long-term and I don't think ADS can provide that any more. Also I've got so used to relying on a "proper" RDBMS that I don't want to go back to DBF in any form now!

Nick

[NickFriend]

Hi Johan,

Yes it looks to be very straightforward to locate the data files anywhere you want, just using the -D option.

As regards Entity Framework, I love it. We already use it extensively in another project. I realise that the performance is nowhere near as good as raw SQL, but as someone who hates SQL (the hatred born of ignorance!) I love the level of abstraction that EF provides.

The manufacturers portion of the new system I'm working on is already 99% complete and uses EF6 and MS SQL Server. The PostgreSQL EF provider is for EF Core (they have an EF6 version but it looks to be largely ignored). So I'm currently porting that application to use EF Core (which is the actively developed cross-platform version of EF), after which I'll experiment with swapping out the MS SQL provider for the PostgreSQL one.

There are some difference between MS and PostgreSQL, but EF abstracts these out, so theoretically swapping from MS to Postgres should be very simple once I've got the base version working with EF Core...

We shall see!

Nick