Creating a PostgreSQL database on hosting and connecting your site

Most websites run on MySQL or MariaDB, but Django, some Laravel projects, GIS systems and reporting-heavy apps often need PostgreSQL, which is strong at transactions, JSON columns, full-text search and complex queries. This guide walks through where to create the database, how to secure it, how to connect and how to back it up.

Shared hosting or VPS?

On shared hosting the default database is usually MySQL; PostgreSQL exists only if the administrator installed it and linked it to the panel. If the control panel lets you choose PostgreSQL when creating a database, use the host, port and login it gives you. Otherwise ask about it in the sayt.uz cabinet or through support. Also make sure the PHP pdo_pgsql extension is enabled, or you will get could not find driver. For full control over version and extensions such as PostGIS, a VPS is easier; the commands below assume one.

Example: a clinic patient system

A private clinic in Samarkand ordered an internal booking and patient history system built with Django. Patient data is confidential, so the database must be unreachable from outside and the app must connect with its own limited role.

Creating the database and role

Never let the application connect as the postgres superuser; give each project its own role and database.

sudo apt install postgresql
sudo -u postgres psql

CREATE ROLE klinika_user WITH LOGIN PASSWORD 'Uzun_Murakkab_Parol_2026';
CREATE DATABASE klinika_db OWNER klinika_user ENCODING 'UTF8';
\q

Network access and authentication

pg_hba.conf decides who may connect, from where and how. With app and database on one server, listen on localhost only and keep port 5432 closed. Use scram-sha-256 for passwords; the version number in the path depends on your installation.

# /etc/postgresql/16/main/postgresql.conf
listen_addresses = 'localhost'

# /etc/postgresql/16/main/pg_hba.conf
# TYPE  DATABASE    USER           ADDRESS         METHOD
host    klinika_db  klinika_user   127.0.0.1/32    scram-sha-256

sudo systemctl reload postgresql

psql -h 127.0.0.1 -U klinika_user -d klinika_db -c "SELECT version();"

Connecting from PHP, Django and Laravel

PDO with prepared statements protects against SQL injection and looks almost the same as MySQL code.

<?php
$pdo = new PDO(
    'pgsql:host=127.0.0.1;port=5432;dbname=klinika_db',
    'klinika_user',
    getenv('DB_PASSWORD'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
$stmt = $pdo->prepare('SELECT id, full_name FROM patients WHERE phone = :p');
$stmt->execute(['p' => '+998901234567']);

Django needs the psycopg package; Laravel needs pdo_pgsql.

# settings.py (Django)
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'klinika_db',
        'USER': 'klinika_user',
        'PASSWORD': os.environ['DB_PASSWORD'],
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

# .env (Laravel)
DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=klinika_db
DB_USERNAME=klinika_user

Managing it from your computer

To use DBeaver or pgAdmin, open an SSH tunnel instead of exposing 5432, then connect to 127.0.0.1:5433.

ssh -L 5433:127.0.0.1:5432 user@misol.uz
# in another window:
psql -h 127.0.0.1 -p 5433 -U klinika_user -d klinika_db

Backup and restore

pg_dump copies a live database without stopping it, and the -Fc format is compressed and allows selective restores. Store the password in ~/.pgpass readable only by its owner.

# ~/.pgpass  (chmod 600 ~/.pgpass)
127.0.0.1:5432:klinika_db:klinika_user:Uzun_Murakkab_Parol_2026

# backup (compressed custom format)
pg_dump -Fc -h 127.0.0.1 -U klinika_user klinika_db > klinika_$(date +%F).dump

# restore
pg_restore --clean -h 127.0.0.1 -U klinika_user -d klinika_db klinika_2026-11-04.dump

Schedule it with cron, escaping %, copy dumps off the server and test restores regularly.

30 2 * * * pg_dump -Fc -h 127.0.0.1 -U klinika_user klinika_db > /backup/klinika_$(date +\%F).dump

Common errors

password authentication failed: wrong password or a pg_hba.conf method that does not match.
Peer authentication failed: no -h, so the unix socket and peer method were used; add -h 127.0.0.1.
Connection refused: the service is down or on another port.
permission denied for table: the table belongs to another role; change the owner or grant rights.

Coming from MySQL, expect double quotes instead of backticks and GENERATED ... AS IDENTITY or SERIAL instead of AUTO_INCREMENT. Tools like pgloader help with large migrations, but always verify the result in a test environment.