For a personal project in Java I was using the well-known H2 database (which is implemented in Java itself).

H2 is a great, easy-to-use db and personally I always like to keep things simple. However, if you are targeting the Cloud, sooner or later you will need advanced features like networked DB server, user management and connection pooling, so finally I decided to get a grip and move from H2 to something more Enterprise-level.

After some research about the DB platform to adopt, I opted for Postgres. In this post I will show an easy way to deploy an installer-less Postgres on Windows machines — something many people on the Internet call 'portable version'.

Install and configure 'portable' Postgres

On my Windows pc I like to micromanage the system and keep things tidy. If a software I need offers the 'zip' binary archive to download instead of any MSI/EXE installer, I usually go that way.

I put normal software blobs in a C:\opt folder, and if it is developement-related software, I use the C:\workspace\opt folder (as C:\workspace is the root of all my code). Yes yes I know, bad habits from Linux…​ moving on :P

It happens that Postgres offers precisely this: https://www.enterprisedb.com/download-postgresql-binaries. So time to download the Win-x86-64 blob for version 9.5.6 and unpack it.

The binary blob provides not only the classic Pgsql commands (initdb, dropdb, psql), but also the handy pgAdminIII frontend. In the root folder of the unpacked software (in my case, C:\workspace\opt\postgres-9.5.6\), then, create the batch file launch_pg.bat with this content:

REM contents
@echo off
set PATH="%~dp0\bin";%PATH%
set PGDATA=%~dp0\data
set PGDATABASE=postgres
set PGUSER=postgres
set PGPORT=5439
set PGLOCALEDIR=%~dp0\share\locale
"%~dp0\bin\initdb" -U postgres -A trust
"%~dp0\bin\pg_ctl" -D "%~dp0/data" -l logfile start
echo "#### Ready. Press space to shutdown..."
pause > null
"%~dp0\bin\pg_ctl" -D "%~dp0/data" stop

If you are wondering, %~dp0 variable return the current directory — that is, the directory where the .bat file is being executed.

WARNING: The first run of this script will initialize the database in the %PGDATA% directory with the initdb line. For the following launches, remember to comment that line, otherwise you’ll see some error message popping up.

At this point, I am basically done: I’ll just create Desktop launchers for both this .bat file and the pgAdmin.exe file that you can find in bin\. As you can understand my need is to launch and terminate the server on-demand.

However, other people may want to launch Postgres as system service or scheduled task (at startup). If that’s the case, I suggest to create a startup task in Windows' Task Scheduler in Control Panel.

What DB server to use? a small comparison

Few words on why I chose Postgres? Basically, because it is mantained and popular, it is the one I felt more confortable together with MySQL, with the advantage that installing it on Windows is relatively painless — as you can see. Also, apparently it is the most lightweight of all: my home machine is not that great — it’s a desktop-replacement laptop from 2012 with Windows 7, so no heavy loads thank you :-). In general the choice was limited to the followings:

  • MySQL / mariaDB

    • Pro: fast, stable, open-source

    • Con: large installation, complex on Windows

  • PostgreSQL (aka Postgres, aka Pg)

    • Pro: the most deployed, documented, and developed; open-source; the installation package is < 100 Mb for version 9.5

    • Con: Not very fast for big transactions

  • Microsoft SQLServer 2017 Developer

    • Pro: free, the most enterprise-grade probably

    • Con: super difficult to configure, non open, and with a laaaaarge installation

  • A NoSQL database

    • Pro: really fast developing field in the recent years

    • Con: it is a fragmented field, with many products specialized on different aspects (eg. ElasticSearch on clustered responsiveness, MongoDB on data reliability, ArangoDB on other features); also, generally the noSQL dbs are not lightweight

The drawbacks of using a SQL RDBMS instead of a NoSQL db is that it is more complicated. You have not only to manage relational data in SQL transactions (which don’t always map easily to object-oriented patterns, regardless of what you thing of ORM libraries), but also you have to account for Schema formatting and preparation in your app, database migrations and so on.

In a next blog post, I will discuss how I manage Postgres (via a connection-pool) using the canonical Java JDBC way. Stay tuned!