Managed PostgreSQL on Cloider

View as MarkdownOpen in ClaudeOpen in ChatGPT

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
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

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

Or in the dashboard, choose PostgreSQL v18 under Database Engine. See 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:

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 for where each value comes from.

Connect with psql

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:

cloider db connect orders-db

Or without installing anything, from Cloider itself:

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:

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.

Node.js

With node-postgres (npm install pg):

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 (pip install "psycopg[binary]"):

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 (go get github.com/jackc/pgx/v5):

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:

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:

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 (gem install pg):

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/schema.prisma

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

.env

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

npx prisma migrate deploy

Django

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,
    }
}

python manage.py migrate

SQLAlchemy

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

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

.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

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:

-- 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:

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 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.

-- 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 and SSL changes, 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 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

-- 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:

# 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. See 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
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 to a plan with more storage

More in database troubleshooting.

Next steps

• Build a full stack app with PostgreSQL
• Trusted sources
• Backups and restore
• Insights

Frequently asked questions

Which PostgreSQL version does Cloider run?

Cloider runs PostgreSQL 18. The version is set when the database is created.

What port does Cloider PostgreSQL use?

Each PostgreSQL database gets its own port when it is created, not 5432. The port is shown in the connection details and in cloider db get <database>.

Do I get superuser access to PostgreSQL on Cloider?

No. You connect as the database user created with the database. The postgres superuser is reserved for Cloider, so actions that need a superuser are not available.

Does Cloider PostgreSQL support SSL?

Yes. SSL is on by default for PostgreSQL. Use sslmode=require to encrypt, or sslmode=verify-full with the CA certificate from cloider db cert to also verify the server.

Can I use Prisma, Django or Rails with Cloider PostgreSQL?

Yes. Any PostgreSQL client works. Give the framework the connection string from the database's Overview tab, with the private host when the app runs on Cloider.

Does Cloider PostgreSQL have read replicas?

No. Each PostgreSQL database is a single primary server. Its backups can be restored into a new database, as of now or any second of the last 14 days.