# 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 snapshots with 3-day retention, plus continuous backups with 30-day 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](https://docs.danubedata.ro/databases-overview#storage-autoscaling)
- **In-place upgrades** - Move to a newer PostgreSQL version without recreating
- **Security** - Encrypted connections and data

Every managed PostgreSQL instance also includes [SQL Studio](https://docs.danubedata.ro/databases-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:

1. Open the **Updates** tab on your instance
2. Choose an available target version
3. Start the upgrade — a snapshot is taken automatically first, so you can roll back if needed
4. 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

1. Navigate to **Databases**
2. Click **Create Database Instance**
3. Select **PostgreSQL** as engine
4. Choose version (18 recommended)
5. Select resource profile
6. Configure settings:
   - Instance name
   - Database name
   - Master username
   - Master password
   - Region
7. Optional: Parameter group, firewall
8. 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

```bash
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)**:
```python
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)**:
```python
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)**:
```javascript
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)**:
```java
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
<?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**:
```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 managed PostgreSQL clusters (currently PostgreSQL 18+), 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 rotate the `postgres` password via SQL.** Statements like
> `ALTER ROLE postgres PASSWORD '…'` will break automated failover and
> backups managed by the DanubeData platform. Contact support if you need
> to rotate this credential.

## Database Management

### Create Databases

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

```sql
-- 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
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

-- List users
\du

-- Drop user
DROP USER olduser;
```

### Create Tables

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

1. Open the **Connectivity** tab on your PostgreSQL instance
2. 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.

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

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

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

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

```sql
-- 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)

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

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

```sql
-- 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 `0A000` errors after a migration, contact support — we can recycle the pooler connections immediately rather than waiting for the automatic turnover. Prepared-statement support can also be disabled per instance on request.

**Application-level pooling** (complements the proxy):
```python
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

```sql
-- 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 at 2 AM UTC
- 3-day retention for automated snapshots
- Continuous backups with 30-day retention
- Point-in-time recovery
- No performance impact

### Manual Snapshots

Create on-demand:
1. Go to database page
2. **Snapshots** tab
3. **Create Snapshot**
4. Name and create

### Export Database

```bash
# 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

```bash
# 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

1. Go to database page
2. Click **Add Replica**
3. Select node and profile
4. Click **Create**

### Benefits

- Scale read operations
- Reporting without impacting primary
- Load balancing via the reader endpoint
- High availability

### Using Replicas

```python
# 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

Always use SSL:

```bash
# psql with SSL
psql "postgresql://postgres:password@postgres-abc123.danubedata.ro:5432/production?sslmode=require"
```

**Connection modes**:
- `sslmode=require` - Require SSL
- `sslmode=verify-ca` - Verify certificate
- `sslmode=verify-full` - Full verification

### User Permissions

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

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

```sql
-- 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
1. Use appropriate data types
2. Add foreign keys
3. Create necessary indexes
4. Normalize when appropriate
5. Use constraints

### Query Optimization
1. Use EXPLAIN ANALYZE
2. Add indexes for frequent queries
3. Avoid SELECT *
4. Use LIMIT for large results
5. Optimize JOINs

### Maintenance
1. Regular VACUUM ANALYZE
2. Monitor index usage
3. Review slow queries
4. Update statistics
5. Monitor disk space

## Next Steps

- [SQL Studio](https://docs.danubedata.ro/databases-sql-studio)
- [Database Replicas](https://docs.danubedata.ro/databases-replicas)
- [Parameter Groups](https://docs.danubedata.ro/databases-parameters)
- [MySQL Guide](https://docs.danubedata.ro/databases-mysql)
- [Backups](https://docs.danubedata.ro/databases-backups)

Need help? Contact support through the dashboard.