Reference Guide Detailed deployment notes with production context and concrete examples.

Flask + PostgreSQL Production Setup

If you're trying to run Flask with PostgreSQL in production, this guide shows you how to configure the database, connect your app safely, and validate that the deployment works. Use it to replace SQLite or development settings with a production-ready PostgreSQL setup.

Quick Fix / Quick Setup

Use this for the fastest working path when PostgreSQL runs on the same host as Flask:

bash

sudo -u postgres psql <<'SQL'
CREATE DATABASE flaskapp_prod;
CREATE USER flaskapp WITH PASSWORD 'change-this-password';
ALTER ROLE flaskapp SET client_encoding TO 'utf8';
ALTER ROLE flaskapp SET default_transaction_isolation TO 'read committed';
ALTER ROLE flaskapp SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE flaskapp_prod TO flaskapp;
SQL

python -m venv .venv
source .venv/bin/activate
pip install flask gunicorn psycopg2-binary flask-sqlalchemy flask-migrate

export DATABASE_URL='postgresql://flaskapp:change-this-password@127.0.0.1:5432/flaskapp_prod'
flask db upgrade

python - <<'PY'
import os
from sqlalchemy import create_engine, text
engine = create_engine(os.environ['DATABASE_URL'])
with engine.connect() as conn:
    print(conn.execute(text('SELECT 1')).scalar())
PY

Replace the password, move DATABASE_URL into a persistent environment file, and validate the same environment is loaded by systemd before sending traffic.

What’s Happening

PostgreSQL requires a valid connection URL, a real database user, correct privileges, and schema migrations that match the application code. Most production failures come from one of four places: invalid DATABASE_URL, authentication problems, missing PostgreSQL driver, or migrations not applied. If Flask starts but fails on requests, check both the app service environment and PostgreSQL logs.

Step-by-Step Guide

Install PostgreSQL server and client packages

bash

sudo apt update
sudo apt install -y postgresql postgresql-contrib libpq-dev

Enable and start PostgreSQL

bash

sudo systemctl enable --now postgresql
sudo systemctl status postgresql

Create a production database and dedicated database user

bash

sudo -u postgres psql <<'SQL'
CREATE DATABASE flaskapp_prod;
CREATE USER flaskapp WITH PASSWORD 'strong-password';
ALTER ROLE flaskapp SET client_encoding TO 'utf8';
ALTER ROLE flaskapp SET default_transaction_isolation TO 'read committed';
ALTER ROLE flaskapp SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE flaskapp_prod TO flaskapp;
SQL

If schema creation fails later, grant schema privileges
Some deployments also require explicit access to the public schema:

bash

sudo -u postgres psql -d flaskapp_prod <<'SQL'
GRANT ALL ON SCHEMA public TO flaskapp;
ALTER SCHEMA public OWNER TO flaskapp;
SQL

Create or activate the application virtual environment
bash
```
python -m venv .venv
source .venv/bin/activate
```

Install Flask and PostgreSQL dependencies

bash

pip install flask gunicorn psycopg2-binary flask-sqlalchemy flask-migrate

Set the production database URL

bash

export DATABASE_URL='postgresql://flaskapp:strong-password@127.0.0.1:5432/flaskapp_prod'

Configure Flask to read the database URL from the environment
Example configuration:

python

import os

SQLALCHEMY_DATABASE_URI = os.environ["DATABASE_URL"]
SQLALCHEMY_TRACK_MODIFICATIONS = False

If using an app factory:

python

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

db = SQLAlchemy()
migrate = Migrate()

def create_app():
    app = Flask(__name__)
    app.config["SQLALCHEMY_DATABASE_URI"] = os.environ["DATABASE_URL"]
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

    db.init_app(app)
    migrate.init_app(app, db)

    return app

Set the Flask app target for CLI commands if required
bash
```
export FLASK_APP='wsgi.py'
```
Or for a factory-based app:
bash
```
export FLASK_APP='myapp:create_app()'
```
Initialize migrations if the project does not already include them

bash

flask db init

Generate and apply schema migrations

bash

flask db migrate -m 'initial'
flask db upgrade

Test direct database connectivity from the same environment

bash

python -c "import os, psycopg2; conn = psycopg2.connect(os.environ['DATABASE_URL']); print('ok'); conn.close()"

Or with SQLAlchemy:

bash

python - <<'PY'
import os
from sqlalchemy import create_engine, text
engine = create_engine(os.environ['DATABASE_URL'])
with engine.connect() as conn:
    print(conn.execute(text('SELECT current_database(), current_user')).fetchone())
PY

Move secrets into a persistent environment file for systemd

bash

sudo mkdir -p /etc/flask
sudo tee /etc/flask/myapp.env > /dev/null <<'EOF'
DATABASE_URL=postgresql://flaskapp:strong-password@127.0.0.1:5432/flaskapp_prod
FLASK_ENV=production
EOF
sudo chmod 600 /etc/flask/myapp.env

Reference the environment file in the Gunicorn systemd unit

Example unit:

ini

[Unit]
Description=Gunicorn instance for myapp
After=network.target

[Service]
User=www-data
Group=www-data
WorkingDirectory=/var/www/myapp
EnvironmentFile=/etc/flask/myapp.env
ExecStart=/var/www/myapp/.venv/bin/gunicorn --workers 3 --bind unix:/run/myapp.sock wsgi:app

[Install]
WantedBy=multi-user.target

Reload systemd and restart the application service

bash

sudo systemctl daemon-reload
sudo systemctl restart myapp
sudo systemctl status myapp

Validate requests through Gunicorn and Nginx

Load a route that touches the database, such as a login route, health endpoint, or model query route. If you have not set up Gunicorn and Nginx yet, use the main deployment flow in Deploy Flask with Nginx + Gunicorn (Step-by-Step Guide).

Validate PostgreSQL access with psql using the same credentials

bash

psql 'postgresql://flaskapp:strong-password@127.0.0.1:5432/flaskapp_prod' -c 'SELECT current_database(), current_user;'

Common Causes

Wrong connection string format → Flask cannot connect or parses invalid settings → use postgresql://user:password@host:5432/dbname and verify the exact value loaded by systemd.
PostgreSQL user lacks privileges → migrations or table creation fail → grant database privileges and schema privileges on public.
Missing PostgreSQL driver → app crashes on import → install psycopg2-binary or another supported PostgreSQL driver in the same virtual environment Gunicorn uses.
Migrations not applied → app starts but fails on first query because tables are missing → run flask db upgrade in the production environment.
Local socket vs TCP mismatch → app tries a socket path while PostgreSQL expects TCP or vice versa → explicitly use 127.0.0.1:5432 in DATABASE_URL.
Environment variables not loaded under systemd → app works in shell but fails as a service → define EnvironmentFile= or Environment= in the service unit and restart it.
Authentication method mismatch in pg_hba.conf → password auth fails even with correct credentials → review pg_hba.conf and reload PostgreSQL.
Firewall or remote-host restrictions → connection timeouts to a managed PostgreSQL instance → allow the host or network and verify listen and bind settings.

Debugging Section

Check the application service:

bash

sudo journalctl -u myapp -n 200 --no-pager
sudo journalctl -u myapp -f
sudo systemctl cat myapp
sudo systemctl status myapp

Check PostgreSQL:

bash

sudo systemctl status postgresql
sudo tail -n 200 /var/log/postgresql/postgresql-*.log
sudo ss -ltnp | grep 5432

Inspect roles and databases:

bash

sudo -u postgres psql -c '\du'
sudo -u postgres psql -c '\l'

Test database login directly:

bash

psql 'postgresql://flaskapp:strong-password@127.0.0.1:5432/flaskapp_prod' -c 'SELECT current_database(), current_user;'

Test database access from the application environment:

bash

source .venv/bin/activate
python -c "import os, psycopg2; conn = psycopg2.connect(os.environ['DATABASE_URL']); print('ok'); conn.close()"
flask db upgrade

What to look for:

password authentication failed
database does not exist
relation does not exist
could not connect to server
ModuleNotFoundError
permission denied for schema public

If shell tests pass but the service fails, compare:

service user
virtual environment path
WorkingDirectory
EnvironmentFile
loaded DATABASE_URL

For environment loading and secret handling, see Flask Environment Variables and Secrets Setup. For direct connection failures, see Flask Database Connection Errors in Production.

Checklist

For broader production validation, use Flask Production Checklist (Everything You Must Do).

FAQ

Q: Should production Flask use SQLite?
No. Use PostgreSQL or another production database for concurrency, durability, and operational safety.

Q: Should I use psycopg2-binary in production?
It works for many deployments, but some teams prefer source-built drivers or psycopg3 packages for tighter control.

Q: Where should DATABASE_URL be stored?
In a systemd environment file, secret manager, or deployment platform configuration, not hardcoded in the repository.

Q: Do I need migrations if tables already exist?
Yes, if your app uses Alembic or Flask-Migrate for schema control. Migration state must match the real database before future deploys.

Q: Can PostgreSQL run on another host?
Yes. Update the host in DATABASE_URL, allow network access, and secure credentials, SSL settings, and firewall rules as required.

Final Takeaway

A production Flask + PostgreSQL setup depends on four things: a correct DATABASE_URL, an installed driver, applied migrations, and systemd or Gunicorn loading the same environment you tested manually. If shell tests pass but the app fails, check service environment loading, PostgreSQL authentication, and schema state first.