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 bbinary – 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 12.5 since I want to try something on that. 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:
- PostgreSQL 12.5 folder:
d:\stack\pg-12.5(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-12.5 – 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-12.5 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
- 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.
So, what we will do next is to create 4 command files that we can use for this purpose:
- pg-12-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-12-start.cmd : this will start the server
- pg-12-stop.cmd : this will stop the server
- pg-12-init.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 pg12-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
- We then change to the directory where the executing script is stored
- After that, we run the pg-12-vars.cmd by using call so that control returns to our commad file
- Then, we execute the actual
pg_ctlcommand 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="%~dp0\pg-12.5\bin";%PATH% @SET PGDIR=%~dp0\pg-12.5 @SET PGDATA=%PGDIR%\data @SET PGLOCALEDIR=%PGDIR%\share\locale @SET PGPORT=5412 @SET PGDATABASE=postgres @SET PGUSER=postgres @SET PGLOGS=%~dp0\logs\pg-12.5\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-12.5 rather than under
d:\stack\pg-12.5\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.
First, we run the initialisation command.
D:\stack>pg12-initialize.cmd D:\stack>pushd D:\stack D:\stack>D: D:\stack>cd D:\stack\ D:\stack>call pg12-vars.cmd D:\stack>ECHO Preparing for PostgreSQL 12.5 Preparing for PostgreSQL 12.5 D:\stack>"D:\stack\\pg-12.5\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. creating directory D:/stack/pg-12.5/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-12.5/bin/pg_ctl -D ^"D^:^\stack^\^\pg^-12^.5^\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 12.5 as we could have expected.
D:\stack>set pg PGDATA=D:\stack\\pg-12.5\data PGDATABASE=postgres PGDIR=D:\stack\\pg-12.5 PGLOCALEDIR=D:\stack\\pg-12.5\share\locale PGLOGS=D:\stack\\logs\pg-12.5\logfile PGPORT=5412 PGUSER=postgres
Start the Server
Next, we can start the server. So, we run it – and it fails (!!)
D:\stack>"D:\stack\\pg-12.5\bin\pg_ctl" -D "D:\stack\\pg-12.5\data" -l "D:\stack\\logs\pg-12.5\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-12.5 (or whatever you set it to) and try again. This time, you see this as expected.
D:\stack>ECHO Preparing for PostgreSQL 12.5 Preparing for PostgreSQL 12.5 D:\stack>"D:\stack\\pg-12.5\bin\pg_ctl" -D "D:\stack\\pg-12.5\data" -l "D:\stack\\logs\pg-12.5\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 and that can be run by running the executable at
D:\stack\pg-12.5\pgAdmin 4\bin\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 (I snapped it a bit late so it was also suggesting that it’s taking longer than usual – clicking on the notification made it go away).
However, it opened a new browser window with PgAdmin loaded and asking to set a master password. You’ll need to do this before you can access servers, etc. using it.
Once you have created that, 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
pg12-stop.cmd and we are done.
D:\stack>"D:\stack\\pg-12.5\bin\pg_ctl" -D "D:\stack\\pg-12.5\data" -l "D:\stack\\logs\pg-12.5\logfile" stop waiting for server to shut down.... done server stopped
If you did run PgAdmin4, you might miss that it added a server into the notifications area. You will need to right click on that and ‘shut down server’ – it’s easy to miss that one the first time.
These pages are helpful:
- The description of pg_ctl is at the PostgreSQL docs site – it includes all the stuff that you can do with it and also has the explanations for the main parameters that we set.
- Similarly, there is a page that describes initdb
As always, this is for me to be able to remember how to do it but if it helps someone, that’s great! Also, if you have some comments, please add below so that I can reflect changes here.