Portable setup for PostgreSQL on Windows (Pg16 Edition)

The normal method for using PostgreSQL on your Windows machine is to download the installer and then set it up. It’s easy and will do all the necessary things that you need for it. It will also set up services and other things that you might need. On the other hand, if you’re someone who uses PostgreSQL only some times and do not want it running all the time, or if you want to use different versions and want to have them switched on from time to time, or if you just want a portable setup that you can copy to any computer and get it running, then this post is for you.

You should treat this only as a personal development server and not for sharing or deployment, etc. without securing.

Download the package

First, we need to download PostgreSQL. The main page for downloads sends you off to the page for Windows downloads which points you to the installer from EDB. However, we don’t want the installer binary – so, we head on to the page that hosts the ZIP archive instead.

Grab the version of the PostgreSQL database that you want – I picked version 16.2 since I want to try something on that (and this post is about Postgres 16). Save the archive to your computer. Let’s assume that it’s in your downloads folder.

Preparing a folder for the setup

Next, we need to create a place where we will unzip and store the binary files. For such usage, I created a folder called stack where I store different parts of the stacks that I want to play with. In my case then the base folder becomes d:\stack and I add things there. Before you proceed, I would like to recommend that you set the folder to compress contents to save disk space as explained in ‘Before we get started’ on this other post since it can save you quite a bit of space over time.

Let’s assume that we now have something like:

main stack folder: d:\stack
PostgreSQL 16.2 folder: d:\stack\pg-16.2 (avoid spaces in these directory names if you can)

Extract the files

You can now extract the files from the archive that you downloaded into the pg-16.2 – when you do that, you will probably get another folder called pgsql and that will actually hold the rest of the folders (bin, doc, include, etc.). I prefer to just cut and move all the files up one level so that the folders (bin, doc, include, etc.) are directly under d:\stack\pg-16.2 and then I delete the now empty pgsql folder.

Good – we now need to prepare some more things so that we can launch PostgreSQL.

Background – what we need

Before we run PostgreSQL (any version), we need to set a few environment variables that the executable requires. As long as these are set properly, it will start up fine:

PATH – this is our Windows path and we need the correct version of PostgreSQL to be first in the path
PGDIR – this is the base directory for the PostgreSQL setup. In our case, this is d:\stack\pg-16.2
PGDATA – this is the directory where we want PostgreSQL to store data (basically, this is where the ‘database’ is) – this can point either into the installation or to a folder path somewhere else (we will set under to a ‘data’ directory under PGDIR itself for simplicity)
PGLOCALEDIR – this where the locale files are (in the setup, this is PGDIR\share\locale)
PGPORT – this is the port on which the server will run. Port 5432 is traditional for PostgreSQL but feel free to change it. Each running installation needs to be on its own port. So, if you already have something running on 5432, you will need to change. I often use ports like 5440, 5441, etc.
PGDATABASE – this is the default database for postgres (we normally set this to ‘postgres’)
PGUSER – this is the admin user for the setup (again, normally ‘postgres’)
PGLOGS – finally, the log file path

Once these are in place, we need to use pg_ctl to control PostgreSQL – to start and stop the server.

However, before we can start and stop the server, we need to create a new PostgreSQL database cluster – this is also done using pg_ctl using the initdb sub-command. For this, we need to add 3 more parameters on the command line:

-A trust : this tells PostgreSQL to trust all local connections; see the note below
-U postgres : this sets the local super user as postgres (which is traditional)
-E UTF8 : I prefer to have the encoding for the server set as UTF-8 for simplicity and compatibility

Note: We are setting authentication to trust here – do not do this unless you trust the users on the local machine. Trust is convenient when you are doing this only on your personal computer while playing with things. You can change it to a different type of authentication by changing pg_hba.conf – search this up if you need it.

OK, let’s set up everything we need.

Our Scripts

So, what we will do next is to create 4 command files that we can use for this purpose:

pg-16-vars.cmd : this will just set the variables so that after this, you can use any other utilities from the command line that need these variables to be set. All the other command files will call this to set the variables before they execute
pg-16-start.cmd : this will start the server
pg-16-stop.cmd : this will stop the server
pg-16-initialize.cmd : this will initialise the cluster

The commands are all set up in this way:

@ECHO ON

:: Save the directory where we are
pushd %CD%

:: Shift directories to the correct path
%~d0
cd %~dp0

:: Set the variable using the common file
call pg16-vars.cmd

:: The actual command that needs to do something
"%PGDIR%\bin\pg_ctl" <---- the actual parameters: start, stop, initdb ----->

:: Restore the directory where we started from
popd

To allow the code to be run from anywhere, we do this:

First we save the current directory from where it is called (read more about pushd / popd here)
We then change to the directory where the executing script is stored
After that, we run the pg-16-vars.cmd by using call so that control returns to our commad file
Then, we execute the actual pg_ctl command that this script needs
Finally, we return back to the directory from where we ran this command

We will put these command files at base of the stack, i.e., at D:\Stack and in that case, the common configuration will be:

@SET PATH=%~dp0pg-16.2\bin;%PATH%
@SET PGDIR=%~dp0\pg-16.2
@SET PGDATA=%PGDIR%\data
@SET PGLOCALEDIR=%PGDIR%\share\locale
@SET PGPORT=5412
@SET PGDATABASE=postgres
@SET PGUSER=postgres
@SET PGLOGS=%~dp0\logs\pg-16.2\logfile

A note on the logs: I normally prefer to send the logs to a common path under d:\stack rather than into the individual installations (it’s easier to clean when working casually). For this reason, you’ll see that the path to the logfile is under d:\stack\logs\pg-16.2 rather than under d:\stack\pg-16.2\logs – set this as you feel you want it.

The different pg_ctl lines need to be:

For initialisation: Add the additional parameters as we discussed above.

"%PGDIR%\bin\initdb" -U postgres -A trust -E UTF8

For Start: Tell it where the database should be and what our log file is, and ask to start

"%PGDIR%\bin\pg_ctl" -D "%PGDATA%" -l "%PGLOGS%" start

For Stop: Tell it where the database should be and what our log file is, and ask to stop

"%PGDIR%\bin\pg_ctl" -D "%PGDATA%" -l "%PGLOGS%" stop

Running our Scripts

Now that we have our four command files, we are ready to get started.

Initialisation

First, we run the initialisation command.

$ pg16-initialize.cmd

$ pushd D:\stack

$ D:

$ cd D:\stack\

$ call pg16-vars.cmd

$ ECHO Preparing for PostgreSQL 16.2
Preparing for PostgreSQL 16.2

$ "D:\stack\pg-16.2\bin\initdb" -U postgres -A trust -E UTF8
The files belonging to this database system will be owned by user "mohit".
This user must also own the server process.

The database cluster will be initialized with locale "English_Singapore.1252".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory D:/stack/pg-16.2/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... windows
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Kuala_Lumpur
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    ^"D^:^\stack^\pg^-16^.2^\bin^\pg^_ctl^" -D ^"D^:^\stack^\pg^-16^.2^\data^" -l logfile start

All of this looks good and as we expect. If you now run set pg in the same command window, you will see that the environment variables were set for PostgreSQL 16.2 as we could have expected.

$ set pg
PGDATA=D:\stack\pg-16.2\data
PGDATABASE=postgres
PGDIR=D:\stack\pg-16.2
PGLOCALEDIR=D:\stack\pg-16.2\share\locale
PGLOGS=D:\stack\logs\pg-16.2\logfile
PGPORT=5412
PGUSER=postgres

You should also be able to do where and see the PostgreSQL binary on the path.

$ where pg_ctl
D:\stack\pg-16.2\bin\pg_ctl.exe

Start the Server

Next, we can start the server. So, we run it – and it fails (!!)

$ pg16-start.cmd

$ pushd D:\stack

$ D:

$ cd D:\stack\

$ call pg16-vars.cmd

$ ECHO Preparing for PostgreSQL 16.2
Preparing for PostgreSQL 16.2

$ "D:\stack\pg-16.2\bin\pg_ctl" -D "D:\stack\pg-16.2\data" -l "D:\stack\logs\pg-16.2\logfile" start
waiting for server to start....The system cannot find the path specified.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

Relax! This is expected – we did not create the logs directory although we expect PostgreSQL to use it. Just create the directory D:\stack\logs\pg-16.2 (or whatever you set it to) and try again. This time, you see this as expected.

$ ECHO Preparing for PostgreSQL 16.2
Preparing for PostgreSQL 16.2

$ "D:\stack\pg-16.2\bin\pg_ctl" -D "D:\stack\pg-16.2\data" -l "D:\stack\logs\pg-16.2\logfile" start
waiting for server to start.... done
server started

If you open the Task Manager, you should see that postgres is running.

Connect to the Server

PostgreSQL now ships with the opinion-dividing PgAdmin4. This used to be at pgAdmin 4\bin\pgadmin4.exe but is now at pgAdmin 4\runtime\pgAdmin4.exe – this will start up a web based GUI with which you can connect to the server. As it starts, it will show you this screen and then show a note saying that it’s taking longer than usual.

In my case, this resulted in an error as below:

At the end of the list, it showed an error with an existing database file saved to my profile.

PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: 
  'C:\\Users\\mohit\\AppData\\Roaming\\pgAdmin\\pgadmin4.db' -> 
  'C:\\Users\\mohit\\AppData\\Roaming\\pgAdmin\\pgadmin4.db.20240504173853'

Although the installation of the server works without any state and is portable, it appears that PgAdmin4 does have some dependencies and state that it saves to the PC which makes it a bit more difficult to launch it again. I don’t like it but if you rename the folder C:\Users\mohit\AppData\Roaming\pgAdmin (adjust for your user name) and start pgAdmin4 again, it will work. In any case, if you do end up using a separate database browser client, this won’t matter.

Now that it’s loaded, you can click on [Add new server]. First, we fill in some stuff to identify the server:

Then, we enter the details of our server as we set them in the environment:

Host name/ address: localhost
Port: 5412
Maintenance database: postgres
User: postgres

Click on save, and it will add the server to the left panel – expand it and you’ll see that the server is running! It’s Live!

Stop the Server

Nothing exciting here. Just run pg16-stop.cmd and we are done.

$ "D:\stack\pg-16.2\bin\pg_ctl" -D "D:\stack\pg-16.2\data" -l "D:\stack\logs\pg-16.2\logfile" stop
waiting for server to shut down.... done
server stopped

Also, you can just close the PgAdmin4 GUI when you’re done.

Notepad.ONGHU