> ## Documentation Index
> Fetch the complete documentation index at: https://docs.jethings.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL 17 Setup

> Install and configure PostgreSQL 17 on Ubuntu 24.04

## Add the PGDG repository

```bash theme={null}
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

sudo apt update
```

## Install PostgreSQL 17

```bash theme={null}
sudo apt install -y postgresql-17 postgresql-client-17

sudo systemctl enable postgresql
sudo systemctl start postgresql
```

<Check>
  Verify the version:

  ```bash theme={null}
    psql --version
    # psql (PostgreSQL) 17.7 (Ubuntu 17.7-3.pgdg24.04+1)
  ```
</Check>

## Set password and create app user

```bash theme={null}
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'CHANGE_ME';"
sudo -u postgres psql -c "CREATE USER prod_user WITH PASSWORD 'CHANGE_ME';"
sudo -u postgres psql -c "CREATE DATABASE prod_db OWNER prod_user;"
```

<Tip>
  Grant ownership/full privileges explicitly if Prisma migrations fail with permission errors:

  ```bash theme={null}
    sudo -u postgres psql -d prod_db -c "GRANT ALL PRIVILEGES ON DATABASE prod_db TO prod_user;"
    sudo -u postgres psql -d prod_db -c "ALTER DATABASE prod_db OWNER TO prod_user;"
  ```
</Tip>

## Remote access (optional)

Only do this if you need TCP access from outside localhost (e.g. Docker bridge, remote tooling):

```bash theme={null}
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" /etc/postgresql/17/main/postgresql.conf
echo "host    all             all             0.0.0.0/0               scram-sha-256" | sudo tee -a /etc/postgresql/17/main/pg_hba.conf
sudo systemctl restart postgresql
```

<Warning>
  Don't forget the port flag if connecting via Docker: `-p 5432:5432`. Missing it is a common cause of "connection refused" errors.
</Warning>

## Useful checks

```bash theme={null}
sudo systemctl status postgresql --no-pager
sudo -u postgres psql -c "SELECT version();"
ls /usr/lib/postgresql/   # list installed major versions
```