Use PostgreSQL in django project

Django provides default sqlite db, but it is not recommended for production. PostgreSQL is recommended database for production Django project.

1 Create a local postgresql db instance over pgAdmin

Download "pgADmin 4"(or the newest version) tool.

Open the app, create a server like shown here - https://www.youtube.com/watch?v=oWsAYx2R9RI

Take note of these variables when you are creating the server:

host name/address - localhost port - 5432 username - postgres password - yourStrongPassword1

2 Preparation for connection for both Linux and Windows

2.1 Install psycopg2 python package

Different on windows and linux because of this - https://stackoverflow.com/questions/35104097/how-to-install-psycopg2-with-pg-config-error

On windows:

pip install psycopg2

On linux:

pip install psycopg2-binary

2.2 Modify `settings.py` of the Django app

Add postgresql entry next to the default sqlite database:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "db.sqlite3",
    }
}

Like so:

DATABASES = {
    # "default": {
    #     "ENGINE": "django.db.backends.sqlite3",
    #     "NAME": BASE_DIR / "db.sqlite3",
    # },
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": os.getenv("POSTGRESQL_LOCAL_DB_NAME"),
        "USER": os.getenv("POSTGRESQL_LOCAL_DB_USER"),
        "PASSWORD": os.getenv("POSTGRESQL_LOCAL_DB_PASSWORD"),
        "HOST": os.getenv("POSTGRESQL_LOCAL_DB_HOST"),
        "PORT": os.getenv("POSTGRESQL_LOCAL_DB_PORT"),
    },
}

2.3 Set up the .env file:

Make sure you write the correct values of the database from the step above into your .env file:

POSTGRESQL_LOCAL_DB_NAME="django_starter"
POSTGRESQL_LOCAL_DB_USER="postgres"
POSTGRESQL_LOCAL_DB_PASSWORD="yourStrongPassword1"
POSTGRESQL_LOCAL_DB_HOST="localhost"
POSTGRESQL_LOCAL_DB_PORT="5432"

3 Connect to a local PostgreSQL instance over Windows

3.0.1 Connect to the postgresql db over windows terminal

The db can be locally running.

Go to postgresql installation folder, here - C:\Program Files\PostgreSQL\16\bin, you should see psql.exe here.

Open cmd in this windows and then type:

psql -h localhost -U db_user -d db_name

You will get prompted for your windows password. Then might ask db_user password also.

Enter them and you will be in!

Run a few commands, then quit.

\c
SELECT * FROM pg_catalog.pg_tables;
\q

3.0.2 Run a Django app

On windows it's easy, since pgadmin4 is installed on windows - you will connect to it easily, because db_host is specified as localhost.

If the above connection works, running Django app should work just fine also.

4 Connect to a local PostgreSQL instance over Linux(NixOS)

First, add postgresql to installed apps of NixOS.

Over WSL and NixOS, I am not able to say(in the .env file) that my posgresql db is running on localhost since it's not true. If I try to use windows ip instead - it still does not work.

Tried to connect to railway instead from nixos - worked! So that means that nixos is installed correctly, is able to connect to postgresql db.

So now the question is how to reach the posgresql db that is running on WSL host - Windows, over NixOS.

Modify the .env file the, change the DB_HOST to the windows host ip POSTGRESQL_LOCAL_DB_HOST="172.21.32.1"

If I try to run Django app now from wsl, I am getting such error:

django.db.utils.OperationalError: connection to server at "172.21.32.1", port 5432 failed: Connection timed out Is the server running on that host and accepting TCP/IP connections?

Checked C:\Program Files\PostgreSQL\16\data\posgresql.conf, it already has listen_addresses = '*' set there.

Tried pinging to windows from linux, it could not, same like here - https://github.com/microsoft/WSL/issues/4171

Solution to ping problem is here - https://github.com/microsoft/WSL/issues/4171#issuecomment-942203237

After enabling the firewall rule "Virtual Machine Monitoring(Echo Request-ICMPv4-In)", I can ping host from wsl2 now.

Okay, I can now ping to Windows from Linux. What happens when I try to run Django app now that is configured to use the PostgreSQL db that runs on the host windows machine?

Getting the same error:

django.db.utils.OperationalError: connection to server at "172.21.32.1", port 5432 failed: Connection timed out Is the server running on that host and accepting TCP/IP connections?

Tried to do telnet 172.21.32.1 543 and it did not work, connection timed out.

Make sure C:\Program Files\PostgreSQL\16\data\posgresql.conf, it already has listen_addresses = '*' set there
Ensure that your Windows Firewall is not blocking the connection to PostgreSQL. Follow these steps to allow traffic on port 5432:
1. Open the Windows Control Panel.
2. Go to "System and Security" > "Windows Defender Firewall" > "Advanced settings".
3. In the left pane, click on "Inbound Rules".
4. In the right pane, click "New Rule…".
5. Select "Port" and click "Next".
6. Choose "TCP" and specify port 5432.
7. Allow the connection and click "Next".
8. Select when this rule applies (Domain, Private, Public) and click "Next".
9. Name your rule (e.g., "Allow PostgreSQL") and click "Finish".
Try to do telnet 172.21.32.1 543 again, should work, great

Finally a different error:

django.db.utils.OperationalError: connection to server at "172.21.32.1", port 5432 failed: FATAL: no pg_hba.conf entry for host "172.21.43.187", user "postgres", database "django_starter", no encryption

Add an entry to C:\Program Files\PostgreSQL\<version>\data\pg_hba.conf, this covers the range of our WSL:

# Allow connections from the specific IP address
host    all             all             172.21.43.187/32           md5

# Or allow connections from a range of IP addresses
host    all             all             172.21.43.0/24             md5

I chose to add for a range of IP addresses.

Restart postgresql over Services in windows. Stop it, start it again.

Now if you do runserver, your Django app should start WHILE connected to postgresql db that runs on WSL host machine - Windows.

5 Connect to a remote PostgreSQL instance that runs on Railway

Did not find any simpler remote development than this - https://railway.app/

PostgreSQL on railway tutorial

Their "Hobby Plan" includes $5 for free usage every month.

In short:

pip install psycopg2-binary (on nixos) or psycopg2 (windows) to your virutal environment
Create a postgresql service on railway
Copy the dbname, username, password, host, port
Add them to your environment variables
Fetch them from the settings
Run server
Should run, runs both on windows and on nixos