Postgres-ing, locally

Steps to install and connect to Postgres locally and test it from Python

🤔 First of all, what is Postgres and why do I care?

Over the course of our working with data, we come across a lot of ways of storing data - cache, in-mem, files, file based databases, SQL databases, NoSQL databases etc. Each option has a specific set of usecases that are best satisfied by said storage mechanism.

Postgres DB is a relational database, which is one type of storage mechanism. Technically, you could go your entire life without knowing about Postgres. However, it Has been around for about 35 years, it has amassed a huge community, robust documentation, high performance, many features, and, above all, it is open-source. Therefore, it is an excellent candidate for consideration when choosing a database for our architecture.

🧪Installation -

There are two ways to set up a Postgres instance locally and use it -

a) As a standalone server on your Windows/Linux machine
b) As a docker container that can be spun up on demand

⚙️ Option 1 : As a standalone server on your Windows/Linux machine

The setup process is fairly simple on Windows. You just have to download the EXE installer and walk through the executable’s steps - https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Note the password you enter for the postgres superuser.

One step that is missing from the installer is adding the path to the psql.exe executable in your PATH environment variable .

⚙️ Option 2 : As a docker container that can be spun up on demand

Put the following contents in a file called docker-compose.yml

services:
  thanos:
    image: postgres
    restart: always
    shm_size: 128mb
    environment:
      POSTGRES_USER: abhi
      POSTGRES_DB: postgres
      POSTGRES_PASSWORD: abhi
    ports:
      - "6543:5432"

Breaking this file down,

• The first line - Based on what service we want to start, we can specify that particular service in this section that is called, well, "service".

  • thanos is the name of the container we want to start. While I used a quirky name just so it would stand out, in production, typically this is more relevant to the type of container that we'd want to start . In this case, the container would be better off being called something like db.

  • The image we're using is that of postgres . We can get a whole host of what images are valid from https://hub.docker.com/

Assuming docker has been installed, start the docker container using :

C:\Users\Admin\Desktop>docker-compose up -d

Resulting in -

If Docker desktop has been installed, you might even be able to see its creation there -

🐍Accessing the Postgres server

From CMD:

psql.exe -U abhi -d postgres -p 5432

The port flag can be set depending on where your postgres server starts.

From pgAdmin 4:

Install pgAdmin 4.
Setup a Server group and a Server. Here specify the details from the setup we’ve done earlier .

host : localhost
database : postgres
user : abhi
password : abhi
port : 5432

🐍Connecting to Postgres from Python -

• First, create a virtual environment using virtualenv (or conda or uv etc.) because we never pollute the global python environment with additional, specific-use packages

• Activate this virtual environment

• Install the psycopg2-binary package using pip install psycopg2-binary

• Test the postgres connection and the creation of the postgres container using this code snippet

  

  Upon execution of this script that is intended to

  • First, connect to a postgres instance running on port 6543 with user abhi

  • Next, get the version of postgres that is running

This is the response -

👋In summary, in this article we've seen how to install Postgres locally and connect to it and test its connection from Python. In the next article, I will explore more about Postgres.

🔭Side notes :

Earlier this week, I asked a bunch of folks on social media how they prefer their Postgres installation. And across networks, out of 23 votes, 11 of them preferred to setup Postgres as an installation/service and 12 of them preferred to use Docker. Now obviously, this is a factor of personal preference and the amount of transparency you want to see in your installation vs the amount of abstraction you’d prefer, the amount of memory you have, to spare to Docker etc. I also went down a parallel rabbit hole while reading more about Postgres and found out that Zerodha used to have a single master-replica setup for a while before they scaled up to a multiple sharded setup. This talk by Kailash Nadh was also super interesting where he discusses his liking for self-hosted software (that includes Postgres).

---

Thank you for reading another edition of Everything Python!