PostgreSQL Database Instances
Complete guide to creating and managing PostgreSQL database instances on DanubeData.
Overview
PostgreSQL is a powerful, open-source object-relational database system. DanubeData provides fully managed PostgreSQL instances with:
- Automated backups - Daily base backups and continuous WAL archiving with 30-day default retention
- High availability - Optional synchronized standby with automatic failover
- Performance monitoring - Real-time metrics and alerts
- Easy scaling - Vertical scaling, read replicas, and optional storage autoscaling
- In-place upgrades - Move to a newer PostgreSQL version without recreating
- Security - TLS-encrypted connections, with an option to require TLS
Every managed PostgreSQL instance also includes SQL Studio — an in-browser data studio for browsing, editing, and managing your schema, with no client to install.
Supported Versions
- PostgreSQL 18 - Latest, recommended
- PostgreSQL 17 - Current stable
- PostgreSQL 16 - Previous stable
- PostgreSQL 15 - Mature release
Version Selection
PostgreSQL 18 (Recommended):
- Latest features
- Asynchronous I/O (io_uring)
- Virtual generated columns
- Native OAuth support (requires manual configuration)
PostgreSQL 17:
- Stable and mature
- Improved partitioning
- Better JSON support
PostgreSQL 16:
- Wide adoption
- Logical replication improvements
PostgreSQL 15:
- Very stable
- Good for production
- Mature ecosystem
Upgrading Versions
You can move an existing PostgreSQL instance to a newer version in place — no need to recreate it or dump and reload:
- Open the Updates tab on your instance
- Choose an available target version
- Start the upgrade — a snapshot is taken automatically first, so you can roll back if needed
- Track progress live; the instance shows an Updating status while it runs
Take a manual snapshot first if you want an extra restore point.
Creating a PostgreSQL Instance
Step-by-Step
- Navigate to Databases
- Click Create Database Instance
- Select PostgreSQL as engine
- Choose version (18 recommended)
- Select resource profile
- Configure settings:
- Instance name
- Database name
- Master username
- Master password
- Region
- Optional: Parameter group, firewall
- Click Create Database
Creation Time: 2-3 minutes
Configuration Tips
Instance Name: prod-postgres-users
Database Name: production (initial database)
Master Username: postgres (default) or custom
Master Password: Auto-generated or custom (min 8 chars)
Connecting to PostgreSQL
Connection Information
After creation:
- Endpoint:
postgres-abc123.danubedata.ro - Port:
5432 - Username: Your master username
- Password: Master password
psql Command Line
psql -h postgres-abc123.danubedata.ro \
-p 5432 \
-U postgres \
-d production
# Or with full connection string
psql "postgresql://postgres:password@postgres-abc123.danubedata.ro:5432/production"
Connection String
postgresql://username:password@host:port/database
postgresql://postgres:mypassword@postgres-abc123.danubedata.ro:5432/production
From Applications
Python (psycopg2):
import psycopg2
conn = psycopg2.connect(
host="postgres-abc123.danubedata.ro",
port=5432,
database="production",
user="postgres",
password="your_password"
)
cur = conn.cursor()
cur.execute("SELECT version()")
version = cur.fetchone()
print(f"PostgreSQL version: {version[0]}")
cur.close()
conn.close()
Python (SQLAlchemy):
from sqlalchemy import create_engine
engine = create_engine(
'postgresql://postgres:password@postgres-abc123.danubedata.ro:5432/production'
)
with engine.connect() as conn:
result = conn.execute("SELECT 1")
print(result.fetchone())
Node.js (pg):
const { Client } = require('pg');
const client = new Client({
host: 'postgres-abc123.danubedata.ro',
port: 5432,
database: 'production',
user: 'postgres',
password: 'your_password',
});
client.connect();
client.query('SELECT NOW()', (err, res) => {
console.log(err ? err.stack : res.rows[0]);
client.end();
});
Java (JDBC):
import java.sql.*;
public class PostgreSQLExample {
public static void main(String[] args) {
String url = "jdbc:postgresql://postgres-abc123.danubedata.ro:5432/production";
String user = "postgres";
String password = "your_password";
try {
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println("Connected to PostgreSQL");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT version()");
while (rs.next()) {
System.out.println(rs.getString(1));
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
PHP (PDO):
<?php
$host = 'postgres-abc123.danubedata.ro';
$port = 5432;
$dbname = 'production';
$user = 'postgres';
$password = 'your_password';
try {
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname";
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->query('SELECT version()');
$version = $stmt->fetchColumn();
echo "PostgreSQL version: $version\n";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
Go:
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
func main() {
// A public *.danubedata.ro hostname is reached through the shared load
// balancer, where this instance has its own port — copy it from the
// dashboard (or the API's connection_port), it is not 5432. Use 5432 only
// with the private-network hostname.
connStr := "host=postgres-abc123.danubedata.ro port=YOUR_PUBLIC_PORT user=postgres password=your_password dbname=production sslmode=require"
db, err := sql.Open("postgres", connStr)
if err != nil {
panic(err)
}
defer db.close()
var version string
err = db.QueryRow("SELECT version()").Scan(&version)
if err != nil {
panic(err)
}
fmt.Println("PostgreSQL version:", version)
}
Two sets of credentials (managed PostgreSQL)
For every operator-managed PostgreSQL cluster, the dashboard exposes two distinct credentials:
| Role | Database | Use for | Privileges |
|---|---|---|---|
pguser | pgdb | Day-to-day application traffic | Standard owner of pgdb — full access inside it |
postgres | postgres | Admin tasks: CREATE DATABASE, installing extensions, role management | Full PostgreSQL superuser |
Most applications should use pguser / pgdb. Connect as postgres only when you need cluster-wide admin capabilities.
Do not change the
postgrespassword with SQL. The cluster re-applies the password it stores, soALTER ROLE postgres PASSWORD '…'is undone at the next reconcile, and anything you pointed at your SQL-set password stops working in the meantime. The dashboard and API keep showing the stored password. Rotate it from the dashboard or the API instead — see below.
Rotating passwords
Both credentials can be replaced whenever you need to, for example if one may have been exposed. Each rotates on its own: rotating one leaves the other working, and each can have one rotation running at a time.
Rotating the pguser password
- Open the database and find Connect to your database.
- Select Rotate password and confirm the dialog, then confirm it's you when asked.
- Wait for the new password to appear. This usually takes less than a minute.
What to expect:
- Applications still using the old password can't open new connections until you update them, so update your connection settings as soon as the new password appears.
- Connections that are already open stay open. Tick Disconnect existing sessions in the confirmation dialog to close them too.
- If the database doesn't confirm the new password, the rotation is marked as failed and the previous password keeps working.
- We email the account that asked as soon as the rotation starts, so one you didn't ask for can't go unnoticed. The email never contains the password.
- Team owners and admins get an email when it finishes, and the change appears in the activity log.
Rotation is available for managed PostgreSQL databases that are running.
Rotate
pguserfrom the dashboard or the API, not withALTER ROLE. The platform re-applies the stored password when the database restarts, so a password changed in SQL stops working later and the dashboard keeps showing the old one.
Rotating the postgres (superuser) password
- Open the database and find Admin Credentials (Superuser).
- Select Rotate password and confirm the dialog, then confirm it's you when asked.
- Wait for the new password to appear.
The same expectations apply, plus two that are specific to this role:
- SQL Studio connects as
postgres. Open SQL Studio sessions drop and queries running there stop; reopen SQL Studio afterwards and it picks up the new password on its own. - Backups, failover and monitoring are unaffected: they do not use this password.
If the database never confirms the new password, the previous one is put back and the rotation is marked as failed.
Rotating with the API
curl -X POST https://danubedata.ro/api/v1/database/{id}/credentials/rotate \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-d '{"disconnect_sessions": false}'
The request returns 202 Accepted. Poll GET /api/v1/database/{id}/credentials until credential_rotation.status is succeeded or failed; the new password is in application_user.password. The API token needs the database:write permission to start the rotation, and database:credentials to poll it and read the new password. A token has no console session to confirm in, so it rotates directly, and we email the account that owns it when the rotation starts.
Add "role": "superuser" to rotate the postgres password instead:
curl -X POST https://danubedata.ro/api/v1/database/{id}/credentials/rotate \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-d '{"role": "superuser", "disconnect_sessions": false}'
Follow that one with superuser_credential_rotation.status in the same response; the new password is the top-level password. While it runs, that field and connection_info come back null.
Database Management
Create Databases
-- Create database
CREATE DATABASE myapp_production
WITH ENCODING 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;
-- Use database
\c myapp_production
-- List databases
\l
-- Drop database
DROP DATABASE old_database;
Create Users
-- Create user
CREATE USER appuser WITH PASSWORD 'strong_password';
-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_production TO appuser;
-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
-- Grant on future tables YOU create after running this
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;
-- List users
\du
-- Drop user
DROP USER olduser;
ALTER DEFAULT PRIVILEGES only applies to objects created afterward by the role that runs the statement — it doesn't retroactively cover existing tables (use the GRANT ... ON ALL TABLES statement above for those), and it won't cover tables a different role creates later. If your migrations run as another role, target that role explicitly:
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;
Granting privileges doesn't transfer ownership. To let appuser ALTER or DROP a table outright, it must own that table:
ALTER TABLE mytable OWNER TO appuser;
Avoid REASSIGN OWNED BY pguser — it would also reassign ownership of the pgdb database itself, not just the tables in it.
Create Tables
-- Create table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
-- List tables
\dt
-- Describe table
\d users
-- Show create statement
\d+ users
PostgreSQL-Specific Features
PostGIS (One-Click Enable)
Turn a managed PostgreSQL database into a full geospatial database without any manual extension setup:
- Open the Connectivity tab on your PostgreSQL instance
- Use the Enable PostGIS card
One click sets up the PostGIS extension for geometry types, spatial indexes, and GIS functions. It's available on PostgreSQL 15 through 18. Your data and settings are preserved — single-node instances restart briefly, while high-availability clusters roll with near-zero downtime.
-- After enabling, PostGIS is ready to use
SELECT PostGIS_Version();
CREATE TABLE places (
id SERIAL PRIMARY KEY,
name TEXT,
geom GEOGRAPHY(Point, 4326)
);
CREATE INDEX idx_places_geom ON places USING GIST (geom);
JSONB Support
-- Create table with JSONB
CREATE TABLE events (
id SERIAL PRIMARY KEY,
data JSONB NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
-- Insert JSON data
INSERT INTO events (data) VALUES
('{"user_id": 123, "action": "login", "ip": "192.168.1.1"}');
-- Query JSON
SELECT * FROM events WHERE data->>'action' = 'login';
-- JSON indexing
CREATE INDEX idx_events_action ON events ((data->>'action'));
-- GIN index for full JSON search
CREATE INDEX idx_events_data ON events USING GIN (data);
Full-Text Search
-- Add tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;
-- Update search vector
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);
-- Create GIN index
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);
-- Search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & database');
-- Rank results
SELECT *, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query
ORDER BY rank DESC;
Array Types
-- Create table with array
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
tags TEXT[]
);
-- Insert with array
INSERT INTO products (name, tags)
VALUES ('Laptop', ARRAY['electronics', 'computers', 'portable']);
-- Query array
SELECT * FROM products WHERE 'electronics' = ANY(tags);
-- Array contains
SELECT * FROM products WHERE tags @> ARRAY['computers'];
Window Functions
-- Rank employees by salary
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;
-- Running total
SELECT
date,
amount,
SUM(amount) OVER (ORDER BY date) as running_total
FROM sales;
Common Table Expressions (CTEs)
-- Recursive CTE for hierarchy
WITH RECURSIVE org_chart AS (
-- Base case
SELECT id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive case
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;
Performance Optimization
Indexes
-- B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- GIN index for full-text search
CREATE INDEX idx_articles_content ON articles USING GIN(to_tsvector('english', content));
-- List indexes
\di
-- Index usage stats
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
Query Optimization
-- Analyze query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
-- Analyze table
ANALYZE users;
-- Vacuum table
VACUUM ANALYZE users;
-- Full vacuum (requires maintenance window)
VACUUM FULL users;
Connection Pooling
Instances with the connection proxy enabled include built-in pgBouncer endpoints — you don't need to run your own pooler:
<instance>-proxy-rw— routes to the primary (writes)<instance>-proxy-ro— routes to read replicas only (HA instances)<instance>-proxy-r— routes to any instance (primary or replica)
The pools run in transaction mode with prepared-statement support enabled (max_prepared_statements = 200). Pooled server connections are recycled after at most one hour (server_lifetime = 3600, server_idle_timeout = 120).
Transaction pooling semantics: consecutive transactions from one client connection may execute on different server connections, so session state — LISTEN/NOTIFY, advisory locks held across statements, SET, temporary tables — does not carry over between transactions. Connect to the direct instance endpoint instead of the proxy if you need session-level features.
Prepared statements and DDL migrations: named prepared statements are cached on the pooler's server connections, which outlive your application's connections. DDL that changes a statement's result shape — an ALTER TABLE ... ADD COLUMN when a cached SELECT * targets that table, or an ALTER COLUMN ... TYPE for any cached statement returning that column — causes subsequent executions of the cached statement to fail with cached plan must not change result type (SQLSTATE 0A000). Restarting your application does not clear this: the affected connections belong to the pooler, not your app, and recycle naturally within at most one hour. To avoid it:
- Run result-shape-changing migrations using the simple query protocol, or with your driver's prepared-statement cache disabled.
- Avoid
SELECT *in hot query paths, so an added column cannot change a cached result shape. - If you hit
0A000errors after a migration, contact support — we can recycle the pooler connections immediately rather than waiting for the automatic turnover.
Application-level pooling (complements the proxy):
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool
engine = create_engine(
'postgresql://postgres:password@postgres-abc123.danubedata.ro:5432/production',
poolclass=QueuePool,
pool_size=10,
max_overflow=20,
pool_pre_ping=True
)
Monitoring & Metrics
Key Metrics
Monitor in dashboard:
- CPU Usage
- Memory Usage
- Connections (active/max)
- Queries per second
- Cache hit ratio
- Replication lag
Database Statistics
-- Database size
SELECT pg_size_pretty(pg_database_size('production'));
-- Table sizes
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;
-- Connection stats
SELECT count(*), state
FROM pg_stat_activity
GROUP BY state;
-- Long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
AND now() - pg_stat_activity.query_start > interval '5 minutes'
ORDER BY duration DESC;
-- Cache hit ratio
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;
Backup & Recovery
Automated Backups
- Daily base backups at a per-instance time between 00:00 and 05:00 UTC; the Backups tab shows the exact time
- Continuous WAL archiving for point-in-time recovery
- 30-day default retention; a backup policy sets the schedule and retention (1 to 30 days)
- Point-in-time recovery
- No performance impact
Manual Snapshots
Create on-demand:
- Go to database page
- Snapshots tab
- Create Snapshot
- Name and create
Export Database
# Export entire database
pg_dump -h postgres-abc123.danubedata.ro \
-U postgres \
-d production \
-F custom \
-f backup.dump
# Export with compression
pg_dump -h postgres-abc123.danubedata.ro \
-U postgres \
-d production \
| gzip > backup.sql.gz
# Export schema only
pg_dump -h postgres-abc123.danubedata.ro \
-U postgres \
-d production \
--schema-only \
-f schema.sql
# Export specific tables
pg_dump -h postgres-abc123.danubedata.ro \
-U postgres \
-d production \
-t users -t orders \
-f tables.sql
Import Database
# Import from custom format
pg_restore -h postgres-abc123.danubedata.ro \
-U postgres \
-d production \
-F custom \
backup.dump
# Import from SQL
psql -h postgres-abc123.danubedata.ro \
-U postgres \
-d production \
< backup.sql
# Import compressed
gunzip < backup.sql.gz | psql -h postgres-abc123.danubedata.ro \
-U postgres \
-d production
Read Replicas
Create Replica
- Go to database page
- Click Add Replica
- Select node and profile
- Click Create
Benefits
- Scale read operations
- Reporting without impacting primary
- Load balancing via the reader endpoint
- High availability
Using Replicas
# Primary for writes
primary_engine = create_engine('postgresql://postgres:pass@primary-host:5432/db')
# Replica for reads
replica_engine = create_engine('postgresql://postgres:pass@replica-host:5432/db')
# Write to primary
with primary_engine.connect() as conn:
conn.execute("INSERT INTO users ...")
# Read from replica
with replica_engine.connect() as conn:
users = conn.execute("SELECT * FROM users").fetchall()
Security
SSL/TLS Connections
Use TLS for database connections. For server identity verification, use sslmode=verify-full with the database's current CA certificate and the exact DNS hostname shown in Connectivity. The certificate covers the direct database and pgBouncer pooler DNS endpoints. Copy the port for the endpoint you selected; a public endpoint's assigned port is not necessarily 5432. The CA management controls below apply to operator-managed PostgreSQL clusters; legacy instances retain their existing certificate ZIP download.
Download the CA certificate (PEM) from Connectivity, or use the CLI:
danube database ca-certificate <name-or-id> --output current-ca.pem
psql "host=YOUR_ENDPOINT_DNS port=YOUR_ENDPOINT_PORT dbname=production user=pguser sslmode=verify-full sslrootcert=/absolute/path/current-ca.pem"
The CLI creates a new file and refuses to overwrite one. Use a fresh output filename when refreshing a certificate, then replace your installed copy.
The API equivalent is GET /api/v1/database/{id}/ca-certificate, with a token that has database:read. It returns the public PEM bundle in data.ca_certificate, plus expires_at and fingerprint_sha256. No private key is returned. During a CA change, keep the whole returned bundle, which may contain more than one CA certificate; the metadata describes its first CA.
sslmode=requireencrypts the connection without verifying the server's identity.sslmode=verify-caalso verifies its certificate chain.sslmode=verify-fullverifies the chain and hostname. Use the DNS endpoint, rather than its IP address.
CA lifetime and renewal
The default operator-managed CA has a roughly 90-day certificate lifetime. Automatic renewal keeps its signing key, but a downloaded PEM file does not update itself: its original expiry still applies. Refresh the file in every client before it expires, even when the CA's signing key has not changed.
CA management offers an optional DanubeData managed CA (10 years). It keeps the existing CA subject and signing key, extends the certificate lifetime, and automatically renews with the same key when at most 180 days remain. Keep track of the expiry of the PEM installed in your clients and refresh that copy before it expires. Automatic renewal on the server cannot extend a file already installed in an application.
Use my own CA accepts one self-signed ECDSA root certificate and its matching, unencrypted P-256 or P-384 private key in PEM format. The root must allow certificate signing, be valid now, and have at least 100 days remaining. RSA roots and intermediate CA chains are not supported in this initial scope. You manage the lifetime and replacement of your own CA. Its private key is stored encrypted and is never returned by the console, API or CLI.
Stage, distribute, then activate
When CA management is available in Connectivity > Certificate authority:
- Choose DanubeData managed CA (10 years) or Use my own CA, then select Stage replacement CA. Staging alone changes neither the active CA nor the certificates served to clients.
- Download the trust bundle (PEM). It contains the current and staged CAs. Install this bundle in every application, job, monitoring tool and other database client before activation. Reload client trust configuration where necessary and test a new
verify-fullconnection through each endpoint used. - Select Activate staged CA, check the displayed fingerprint, and confirm that all clients trust it. The console also asks you to confirm it's you when needed.
- Wait for CA change completed and test new direct and pooled connections. A queued or applying operation is not confirmation that the new CA is active.
The staged CA must still have at least seven days remaining at activation. Staging does not pause automatic renewal of the current DanubeData CA. If the current CA renews after staging, discard the stale stage and stage again.
Activation is designed to reload certificates without restarting database pods or closing existing connections. Validation so far covers a limited canary, not every client or topology, so this is not a universal no-interruption guarantee. Clients that do not trust the selected CA cannot open verified new connections.
Keep the old CA in client trust stores while rollback may be needed. Roll back to the previous CA requires clients to trust that CA too. Rollback is refused when the previous CA has fewer than seven days remaining. Returning to the default CA retains automatic renewal with its original 90-day lifetime. If an operation fails, retain the overlap bundle and check the current fingerprint before retrying or rolling back. Discard staged CA removes only the staged choice.
For API automation, read GET /api/v1/database/{id}/tls for the current revision, active CA, pending public trust_bundle, previous CA and operation phase. Use POST /tls/ca/stage, POST /tls/ca/activate, DELETE /tls/ca/stage or POST /tls/ca/rollback under the same database API path. Mutations require database:write and the last expected_revision; activation also requires the exact stage_id, fingerprint_sha256 and clients_trust_ca: true. Rollback uses the previous fingerprint and the same trust acknowledgement. Poll status until the operation is completed or failed; see the API reference for request schemas.
The CLI exposes the same workflow under danube database tls: status, ca-stage, ca-activate, ca-cancel and ca-rollback, each followed by the database name or ID. ca-stage --output trust-bundle.pem saves the staged overlap bundle; activation and rollback require --clients-trust-ca alongside the revision and target fingerprint. Use each command's --help for its flags.
User Permissions
-- Application user
CREATE USER app_user WITH PASSWORD 'strong_pass';
GRANT CONNECT ON DATABASE production TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- Read-only user
CREATE USER readonly WITH PASSWORD 'strong_pass';
GRANT CONNECT ON DATABASE production TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
Troubleshooting
Connection Issues
-- Check max connections
SHOW max_connections;
-- Check current connections
SELECT count(*) FROM pg_stat_activity;
-- Kill idle connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND now() - state_change > interval '10 minutes';
Performance Issues
-- Find slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;
-- Table bloat
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
-- Run VACUUM
VACUUM ANALYZE;
Best Practices
Schema Design
- Use appropriate data types
- Add foreign keys
- Create necessary indexes
- Normalize when appropriate
- Use constraints
Query Optimization
- Use EXPLAIN ANALYZE
- Add indexes for frequent queries
- Avoid SELECT *
- Use LIMIT for large results
- Optimize JOINs
Maintenance
- Regular VACUUM ANALYZE
- Monitor index usage
- Review slow queries
- Update statistics
- Monitor disk space
Next Steps
Need help? Contact support through the dashboard.