Getting Started with PostgreSQL on Windows

A Step by Step Guide to Installing and Setting up Postgres on your Windows Machine.

From: Wallpaper Mania

So what is Postgres huh?😏

PostgreSQL, also known as Postgres, is a free and open-source relational database management system, emphasizing extensibility and technical standards compliance. It is designed to handle a range of workloads, from single machines to data warehouses or web services with many concurrent users. Basically, Postgres has superpowers; capable of handling very large datasets as though it was smashing peanuts.

Enough said, Who uses it?😒

Now, you are wondering who could possibly need all that. Well, thanks to its superpowers, a lot of firms use it. Including top-rank companies like Netflix, Uber, Spotify and etc.

From: Stackshare

Let’s get down to it!😎

I was on a project, that I needed to scale, and part of the business requirement was Postgres. It was super frustrating to get it started on a Windows machine, due to lack of efficient tutorials and knowledge-base even the official site and documentation were not forthcoming.
To avoid the same stress I went through, and therefore make life relatively easier for you, follow the steps without missing/jumping any.

Step 1. Grab some Chocolatey🍫:

Chocolatey is this badass software/package manager for Windows.
It lets you manage, install and uninstall packages like Postgres right from the Command Prompt (CMD)/PowerShell CLI.
To install Chocolatey, launch CMD as admin, run the following line, and let the magic begin.

@"%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"

When it’s done, CMD should return this line as one of the results:

Chocolatey (choco.exe) is now ready

We can also confirm that Chocolatey is now working by simply calling “choco” in CMD.
You should get the following result:

Chocolatey v0.10.15

Step 2. Now stir in Postgres🐘 with “choco”:

To grab and install Postgres;
Copy, paste and run the following line;
Note: Where it says ‘passwordgoeshere’, that’s the required password assigned to Postgres user, you can change it to a preferred password.

choco install postgresql10 --params '/Password:passwordgoeshere' -y

Now, this might take a while, depending on the on the speed of your PC and/or internet.
When it’s done, it might return some errors and failures, don’t be bothered.
To confirm that it has been installed;
This folder (“C:\Program Files\PostgreSQL\10”) should have been created, containing all necessary installation files as seen below:

Step 3. Setting up the environment variables:

Now that we have Postgres on our machine, it would be nice to directly use Postgres in CMD by calling ‘psql’ and/or ‘pg_ctl’(just as we use Chocolatey by calling ‘choco’).
To do this, you need to include Postgres in the system’s environment variables.
Windows+R should launch ‘Windows Run’ then you can swiftly type in ‘SystemPropertiesAdvanced’ and continue, using the images and corresponding captions as seen below:

Click ‘OK after typing

Click ‘Environment Variable’

Double click on ‘Path’

Add the highlighted paths and click ‘OK’

Click ‘New…’

Input the ‘Variable Name’ and ‘Variable Value’ as seen and click ‘OK’

The highlight Variable and value should now be included, click ‘OK’ to apply all changes

Click ‘OK’ here too and then RESTART your machine

Step 4. Kickstarting Postgres Database Cluster and Server:

After restarting your PC, navigate to the “C:\Program Files\PostgreSQL\10\data” folder. Delete all files and sub-folders in that folder, so that it is completely empty.
In order to make use of this folder, you need to give ownership and grant full access to Everyone(User Group). To do this;
Launch CMD as admin, then run the following command:

icacls "C:\Program Files\PostgreSQL\10\data" /t /grant Everyone:F

We can now initialize the Postgres database cluster and server,
To do this; run the following command:

pg_ctl init

When it’s done initializing, CMD should return this line as one of the results:

Success. You can now start the database server

Now that we have initialized, we can start the Postgres server,
To do this; run the following command:

pg_ctl start

Yuppie!,
It’s almost done.
Postgres server is now running.
Right out the box, Postgres creates three databases included ‘postgres’.

Step 5. Interesting commands to get you started:

To list out the available databases, run:

psql -l

To connect to a database in this case ‘postgres’ since it’s available, run:

psql postgres

To disconnect from a database, run:

\q

To create a database, run:

createdb yourdatabasenamegoeshere

To create a user, run:

createuser yourusernamegoeshere

Here is a concise list of some interesting commands

1. \? list all the commands
2. \l list databases
3. \conninfo display information about the current connection
4. \c [DBNAME] connect to the new database, e.g., \c template1
5. \dt list tables of the public schema
6. \dt <schema-name>.* list tables of certain schema, e.g., \dt public.*
7. \dt *.* list tables of all schemas
8. Then you can run SQL statements, e.g., SELECT * FROM my_table;(Note: a statement must be terminated with a semicolon ;)
9. \q quit psql

FINALLY! 🚀

You’re all set to use Postgres on your Machine.
Wondering why we didn’t do any GUI, or if a GUI exists for Postgres?
Well, yes a GUI exists, its called PGAdmin, the cool thing is that it comes as part of the installation we just did.

To use PGAdmin, just go to the Start menu and search ‘pgadmin', you should click the program with an elephant🐘 icon.
Note: It’s generally adviced that all database query including the creation of the database itself, is done from an SQL file, this is so that changes can be tracked by ‘git’ or any other version control packaged used.

WHAT NEXT? 🤔

Documentations are your friend, might not be expressly enticing but it gives you superpowers with the respective tool.
Postgres10 Official Doc: HERE

Have fun 🎉