PostgreSQL Database Instances

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"

postgresql://username:password@host:port/database
postgresql://postgres:mypassword@postgres-abc123.danubedata.ro:5432/production

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

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

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();
});

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
$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();
}
?>

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

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

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

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

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

-- 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'];

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

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

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

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

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
)

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

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

# 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()

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

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

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

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