# Managed PostgreSQL on Cloider

> Use managed PostgreSQL 18 on Cloider. Connect with psql, Node.js, Python, Go, PHP, Java and Ruby, Prisma, Django, SQLAlchemy, GORM and Laravel, with TLS.

Cloider runs managed **PostgreSQL 18**, the open source relational database. Each PostgreSQL database is its own server with its own storage, a database and a user created for you, backups with point-in-time restore and optional TLS. This page covers what is specific to PostgreSQL on Cloider and shows how to connect from the command line and from code.

## At a glance

| | |
| --- | --- |
| **Version** | PostgreSQL 18 |
| **Topology** | One primary server, no read replicas |
| **Port** | Assigned per database (not 5432) |
| **Database and user** | A database named after your Cloider database (for example `orders-db`) and the user you chose (for example `orders_db_user`) |
| **Superuser** | Not given out. The `postgres` user is reserved, and you connect as your database user |
| **SSL** | On by default. The server offers TLS with a certificate signed by Cloider's CA |
| **Client** | `psql` |
| **Plans** | Hobby (0.25 vCPU, 0.25 GB RAM, 1 GB storage) to Pro (2 vCPU, 4 GB RAM, 16 GB storage), see [plans](/databases/overview#postgresql-and-mysql-plans) |
| **Backups** | A full backup every 14 days plus every change recorded, restored into a new database as of now or any second of the last 14 days |

## Create a PostgreSQL database

```bash
cloider db create orders-db --engine postgresql --plan standard --wait
```

Or in the dashboard, choose **PostgreSQL v18** under **Database Engine**. See [create a database](/databases/create-a-database) for every tool and option.

## Connection details

| Detail | Example |
| --- | --- |
| Public host | `orders-db-p0q9zm.db.cloider.com` |
| Private host (Cloider apps and clusters) | `orders-db-p0q9zm.db.cloider.internal` |
| Port | `20417` |
| Database | `orders-db` |
| User | `orders_db_user` |
| Password | `cloider db password orders-db` |

The connection string, with SSL on:

```text
postgresql://orders_db_user:PASSWORD@orders-db-p0q9zm.db.cloider.com:20417/orders-db?sslmode=require
```

Most PostgreSQL tools also accept the `postgres://` scheme. See [connect](/databases/connect) for where each value comes from.

## Connect with psql

```bash
PGPASSWORD="$(cloider db password orders-db)" PGSSLMODE=require \
  psql -h orders-db-p0q9zm.db.cloider.com -p 20417 -U orders_db_user -d orders-db
```

Or let the CLI do it, with `psql` installed:

```bash
cloider db connect orders-db
```

Or without installing anything, from Cloider itself:

```bash
cloider db shell orders-db                     # interactive psql
cloider db exec orders-db "SELECT now();"      # one query
cloider db exec -i orders-db < schema.sql      # a script, stops at the first error
```

To verify the server certificate, download the CA certificate and use `verify-full`:

```bash
cloider db cert orders-db -f ca.crt
psql "postgresql://orders_db_user@orders-db-p0q9zm.db.cloider.com:20417/orders-db?sslmode=verify-full&sslrootcert=ca.crt"
```

### Useful psql commands

| Command | What it does |
| --- | --- |
| `\l` | List databases |
| `\dt` | List tables in the current schema |
| `\d orders` | Describe the table `orders` |
| `\du` | List roles |
| `\dx` | List installed extensions |
| `\conninfo` | Show the connection, including SSL |
| `\timing on` | Show how long each query takes |
| `\q` | Quit |

## Connect from code

The examples read the connection string from the `DATABASE_URL` environment variable. From a Cloider app or cluster, set it with the **private** host, see [connect from a Cloider app or cluster](/databases/connect#connect-from-a-cloider-app-or-kubernetes-cluster).

**Node.js**

With [node-postgres](https://node-postgres.com/) (`npm install pg`):

```js title="db.js"
import fs from "node:fs";
import pg from "pg";

const pool = new pg.Pool({
  // postgresql://orders_db_user:PASSWORD@orders-db-p0q9zm.db.cloider.com:20417/orders-db
  connectionString: process.env.DATABASE_URL,
  // Verify the server with Cloider's CA (cloider db cert orders-db -f ca.crt)
  ssl: { ca: fs.readFileSync("ca.crt", "utf8") },
  max: 5,
});

const { rows } = await pool.query("SELECT id, status FROM orders WHERE id = $1", [42]);
console.log(rows);
```

Leave `sslmode` out of the URL when you pass `ssl` as an option, so the two don't conflict.

**Python**

With [psycopg 3](https://www.psycopg.org/psycopg3/) (`pip install "psycopg[binary]"`):

```python title="db.py"
import os
import psycopg

# postgresql://orders_db_user:PASSWORD@orders-db-p0q9zm.db.cloider.com:20417/orders-db?sslmode=require
with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT id, status FROM orders WHERE id = %s", (42,))
        print(cur.fetchone())
```

To verify the certificate, use `?sslmode=verify-full&sslrootcert=ca.crt` in the URL. The same URL works with psycopg2.

**Go**

With [pgx](https://github.com/jackc/pgx) (`go get github.com/jackc/pgx/v5`):

```go title="main.go"
package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	ctx := context.Background()

	// postgresql://orders_db_user:PASSWORD@orders-db-p0q9zm.db.cloider.com:20417/orders-db?sslmode=verify-full&sslrootcert=ca.crt
	pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		panic(err)
	}
	defer pool.Close()

	var status string
	if err := pool.QueryRow(ctx, "SELECT status FROM orders WHERE id = $1", 42).Scan(&status); err != nil {
		panic(err)
	}
	fmt.Println(status)
}
```

**PHP**

With PDO and the `pdo_pgsql` extension:

```php title="db.php"
<?php
$pdo = new PDO(
    'pgsql:host=orders-db-p0q9zm.db.cloider.com;port=20417;dbname=orders-db;sslmode=verify-full;sslrootcert=ca.crt',
    'orders_db_user',
    getenv('DB_PASSWORD'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$stmt = $pdo->prepare('SELECT id, status FROM orders WHERE id = ?');
$stmt->execute([42]);
print_r($stmt->fetch(PDO::FETCH_ASSOC));
```

**Java**

With the [PostgreSQL JDBC driver](https://jdbc.postgresql.org/):

```java title="Main.java"
import java.sql.*;

public class Main {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:postgresql://orders-db-p0q9zm.db.cloider.com:20417/orders-db"
                + "?sslmode=verify-full&sslrootcert=ca.crt";

        try (Connection conn = DriverManager.getConnection(url, "orders_db_user", System.getenv("DB_PASSWORD"));
             PreparedStatement st = conn.prepareStatement("SELECT id, status FROM orders WHERE id = ?")) {
            st.setLong(1, 42);
            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
                    System.out.println(rs.getLong("id") + " " + rs.getString("status"));
                }
            }
        }
    }
}
```

**Ruby**

With the [pg gem](https://github.com/ged/ruby-pg) (`gem install pg`):

```ruby title="db.rb"
require "pg"

# postgresql://orders_db_user:PASSWORD@orders-db-p0q9zm.db.cloider.com:20417/orders-db?sslmode=require
conn = PG.connect(ENV.fetch("DATABASE_URL"))
conn.exec_params("SELECT id, status FROM orders WHERE id = $1", [42]) do |result|
  result.each { |row| puts row }
end
```

## Use PostgreSQL with frameworks and ORMs

**Prisma**

```prisma title="prisma/schema.prisma"
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
```

```bash title=".env"
DATABASE_URL="postgresql://orders_db_user:PASSWORD@orders-db-p0q9zm.db.cloider.com:20417/orders-db?sslmode=require"
```

```bash
npx prisma migrate deploy
```

**Django**

```python title="settings.py"
import os

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "HOST": os.environ["DB_HOST"],          # orders-db-p0q9zm.db.cloider.com
        "PORT": os.environ["DB_PORT"],          # 20417
        "NAME": "orders-db",
        "USER": "orders_db_user",
        "PASSWORD": os.environ["DB_PASSWORD"],
        "OPTIONS": {"sslmode": "require"},
        "CONN_MAX_AGE": 60,
    }
}
```

```bash
python manage.py migrate
```

**SQLAlchemy**

```python title="db.py"
import os
from sqlalchemy import create_engine, text

# postgresql+psycopg://orders_db_user:PASSWORD@orders-db-p0q9zm.db.cloider.com:20417/orders-db?sslmode=require
engine = create_engine(os.environ["DATABASE_URL"], pool_size=5, pool_pre_ping=True)

with engine.connect() as conn:
    print(conn.execute(text("SELECT count(*) FROM orders")).scalar())
```

The `postgresql+psycopg://` scheme selects psycopg 3. With psycopg2, use `postgresql+psycopg2://`.

**GORM**

```go title="db.go"
package db

import (
	"os"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

func Open() (*gorm.DB, error) {
	// host=orders-db-p0q9zm.db.cloider.com port=20417 user=orders_db_user dbname=orders-db sslmode=require password=...
	return gorm.Open(postgres.Open(os.Getenv("DATABASE_URL")), &gorm.Config{})
}
```

**Laravel**

```bash title=".env"
DB_CONNECTION=pgsql
DB_HOST=orders-db-p0q9zm.db.cloider.com
DB_PORT=20417
DB_DATABASE=orders-db
DB_USERNAME=orders_db_user
DB_PASSWORD=PASSWORD
```

In `config/database.php`, set `'sslmode' => 'require'` on the `pgsql` connection, then run `php artisan migrate`.

**Rails**

```yaml title="config/database.yml"
production:
  adapter: postgresql
  url: <%= ENV["DATABASE_URL"] %>
  pool: 5
```

With `DATABASE_URL` set to the connection string with `?sslmode=require`, run `bin/rails db:migrate`.

## PostgreSQL features and limits on Cloider

### The database and the user

Cloider creates one database, named after your Cloider database, and one user. Your application connects as that user. The `postgres` superuser is reserved for Cloider and its password isn't given out, so commands that need a superuser, such as `ALTER SYSTEM` or creating untrusted extensions, aren't available.

### Database names with hyphens

A Cloider database name can contain hyphens (`orders-db`). In connection strings and client flags it works as is. Inside SQL, quote it as an identifier:

```sql
-- Switch to it in psql
\c "orders-db"

-- Refer to it as a string where SQL expects a name as text
SELECT pg_size_pretty(pg_database_size('orders-db'));
```

### Extensions

PostgreSQL extensions add data types and functions, for example `pgcrypto` for hashing or `citext` for case-insensitive text. List what the server ships and what is installed:

```sql
SELECT name, default_version, installed_version
FROM pg_available_extensions
ORDER BY name;
```

Install one with `CREATE EXTENSION IF NOT EXISTS pgcrypto;`. Extensions that need a superuser to install can't be added, because the database user isn't one.

### Connections

The server accepts a limited number of connections at a time. The [Insights tab](/databases/insights) shows the current count and the maximum (`SHOW max_connections;` shows it too). Use a connection pool in your application (node-postgres `Pool`, `pgxpool`, SQLAlchemy's pool, Django's `CONN_MAX_AGE`) and keep its size small on small plans.

Cloider's monitoring connects with the `application_name` `cloider-metrics`, which you may see in `pg_stat_activity`. Its connections aren't counted in Insights.

```sql
-- Who is connected
SELECT pid, usename, application_name, client_addr, state, query_start
FROM pg_stat_activity
WHERE datname = 'orders-db';
```

### Single server

A PostgreSQL database on Cloider is one primary server without read replicas or automatic failover. Plan for short restarts during [resizes](/databases/resize) and [SSL changes](/databases/ssl-tls), and let your connection pool reconnect.

### Backups and restore

Cloider takes a full backup of every PostgreSQL database every 14 days and records every change in between from its write-ahead log (WAL). A [restore](/databases/backups-and-restore) creates a new database with your data as it is now, or as it was at any second of the last 14 days, for example right before a bad migration. It brings back the whole server: every database, role and grant.

## Common tasks

```sql
-- Size of the database and of the largest tables
SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- Reclaim space and refresh statistics after large deletes
VACUUM (ANALYZE) orders;

-- Find slow running queries
SELECT pid, now() - query_start AS runtime, state, query
FROM pg_stat_activity
WHERE state <> 'idle' AND datname = current_database()
ORDER BY runtime DESC;

-- Cancel one of them
SELECT pg_cancel_backend(12345);
```

Run any of these with `cloider db exec orders-db "<query>"`.

## Import an existing PostgreSQL database

Dump the source database and load it into the Cloider one:

```bash
# From the old server
pg_dump -h old-db.example.com -U app -d app -Fc --no-owner --no-privileges -f app.dump

# Into Cloider
PGPASSWORD="$(cloider db password orders-db)" \
  pg_restore -h orders-db-p0q9zm.db.cloider.com -p 20417 -U orders_db_user -d orders-db \
  --no-owner --no-privileges --jobs 4 app.dump
```

`--no-owner --no-privileges` makes every object belong to your database user, since the roles of the old server don't exist on Cloider. Your IP must be in the [trusted sources](/databases/trusted-sources). See [migrate to Cloider](/guides/migrate-to-cloider) for a complete walkthrough.

## Troubleshooting

| Error | Cause and fix |
| --- | --- |
| `connection to server ... failed: Connection timed out` | Your IP isn't trusted. `cloider db trusted-sources orders-db --add-my-ip` |
| `password authentication failed for user "orders_db_user"` | Wrong password, or a wrong user name. Copy both again from the Overview tab |
| `database "orders_db" does not exist` | The database name has a hyphen, not an underscore: `orders-db` |
| `server does not support SSL, but SSL was required` | SSL is off on the database. Turn it on, or remove `sslmode=require` |
| `certificate verify failed` or `self-signed certificate in certificate chain` | The client verifies the certificate without Cloider's CA. Pass `sslrootcert=ca.crt` |
| `sorry, too many clients already` or `remaining connection slots are reserved` | The connection limit is reached. Lower your pool sizes or [resize](/databases/resize) |
| `permission denied to create extension` | The extension needs a superuser, which isn't available |
| `could not extend file ... No space left on device` | The storage is full. [Resize](/databases/resize) to a plan with more storage |

More in [database troubleshooting](/databases/troubleshooting).

## Next steps

- [Build a full stack app with PostgreSQL](/guides/full-stack-app-with-postgresql)
- [Trusted sources](/databases/trusted-sources)
- [Backups and restore](/databases/backups-and-restore)
- [Insights](/databases/insights)