Databases, distributed SQL, and the road to AI data architecture

Tuesday, August 18, 2026

Setting Up a PostgreSQL MCP Server in VS Code (AWS RDS, Aurora & On-Prem)

The Model Context Protocol (MCP) lets AI coding assistants talk to external systems — including your databases — through a standard interface. Once a PostgreSQL MCP server is wired into VS Code, GitHub Copilot's agent mode can inspect schemas, run queries, analyze execution plans, and help with tuning, all in natural language against a live database.

This guide walks through configuring a PostgreSQL MCP server in VS Code that works against AWS RDS for PostgreSQL, Aurora PostgreSQL, and on-premises PostgreSQL. It applies to PostgreSQL versions 10 through the current release.

Prerequisites

Before starting, make sure you have:

VS Code 1.102 or later. Native MCP support (agent mode plus mcp.json) has been generally available since 1.102.
GitHub Copilot and Copilot Chat, signed in, with agent mode enabled. Agent mode is the MCP client inside VS Code — this is where MCP tools are surfaced.
A runtime for the server: either Python 3.12+ or Docker. You only need one; details are below.
Network reachability from your workstation to each database on the PostgreSQL port (default 5432). For cloud databases this usually means the security group allows your IP — and, for instances in a private subnet, a VPN, bastion, or Direct Connect path.
A database user for the MCP server. Use a least-privilege, read-only account for anything pointed at production.
psql (optional but recommended) for a quick connectivity test before wiring up MCP.

Step 1 — Choose an MCP server

Two things to know before you pick:

The original reference server, @modelcontextprotocol/server-postgres, was deprecated in 2025 after a SQL-injection vulnerability was found. Avoid it — its read-only mode is not a safe boundary.
The maintained, feature-rich choice is Postgres MCP Pro (crystaldba/postgres-mcp). It adds capabilities that matter for real database work: read-only and restricted execution modes, EXPLAIN plan analysis, index-tuning recommendations, and database health checks (bloat, cache-hit ratios, vacuum, replication lag). It is written in Python.

The rest of this guide uses Postgres MCP Pro.

Step 2 — Install the runtime and the server

Option A — pipx (recommended)

Postgres MCP Pro is a Python application, so it needs Python 3.12+. On Windows, install Python with winget:

powershell

winget install Python.Python.3.13

Then install the server with pipx (this puts a postgres-mcp executable on your PATH):

powershell

pip install pipx
pipx ensurepath
pipx install postgres-mcp

Reopen the terminal and verify:

powershell

python --version      # 3.12 or higher
postgres-mcp --help   # confirms the server is installed and on PATH

Option B — Docker

If you'd rather not install Python, pull the image instead:

bash

docker pull crystaldba/postgres-mcp

You'll reference docker as the command in mcp.json (shown in Step 4).

Step 3 — Prepare the database connection

The MCP server connects using a standard PostgreSQL connection URI. Get the connection working with psql first — it is much easier to debug connectivity here than through the MCP layer.

Connection string formats

On-premises PostgreSQL:

postgresql://USER:PASSWORD@HOST:5432/DBNAME

Add ?sslmode=require if the server is configured for SSL.

AWS RDS for PostgreSQL (SSL strongly recommended):

postgresql://USER:PASSWORD@your-instance.xxxxx.<region>.rds.amazonaws.com:5432/DBNAME?sslmode=verify-full&sslrootcert=/path/to/global-bundle.pem

Aurora PostgreSQL:

postgresql://USER:PASSWORD@your-cluster.cluster-xxxxx.<region>.rds.amazonaws.com:5432/DBNAME?sslmode=verify-full&sslrootcert=/path/to/global-bundle.pem

Notes on the cloud options:

SSL. Download the AWS RDS combined CA bundle (global-bundle.pem) once and point sslrootcert at it. sslmode=verify-full validates the server certificate; use sslmode=require if you only need encryption without certificate verification.
Aurora endpoints. Use the cluster (writer) endpoint if the tool needs to write. Use the reader endpoint (...cluster-ro-xxxxx...) for query and analysis work — it stays off the writer and is read-only at the database level, which is a safer default.

Test the connection

bash

psql "postgresql://USER:PASSWORD@HOST:5432/DBNAME?sslmode=require"

If psql connects, the MCP server will too.

Step 4 — Configure the server in VS Code

VS Code reads MCP server definitions from an mcp.json file. Place it in a workspace as .vscode/mcp.json so it applies to that project, or open your user-level config from the Command Palette: MCP: Open User Configuration.

Two important details:

VS Code uses the top-level key servers — not mcpServers. Configuration examples written for other clients (Claude Desktop, Cursor, Cline) often use mcpServers; paste those verbatim and VS Code will silently ignore them.
Keep credentials out of the file. Use an inputs block with a promptString so VS Code prompts for the connection URI (masked) at runtime instead of storing a password in plain text.

Complete example using the pipx-installed server:

json

{
  "inputs": [
    {
      "id": "pg-uri",
      "type": "promptString",
      "description": "PostgreSQL connection URI",
      "password": true
    }
  ],
  "servers": {
    "postgres": {
      "type": "stdio",
      "command": "postgres-mcp",
      "args": ["--access-mode=restricted"],
      "env": { "DATABASE_URI": "${input:pg-uri}" }
    }
  }
}

--access-mode=restricted limits the server to read-only transactions with resource limits — the right default for production. Switch to --access-mode=unrestricted only on databases where you intend to allow writes and schema changes.

Docker variant — if you installed with Docker, replace the server entry:

json

"servers": {
  "postgres": {
    "type": "stdio",
    "command": "docker",
    "args": ["run", "-i", "--rm", "-e", "DATABASE_URI", "crystaldba/postgres-mcp", "--access-mode=restricted"],
    "env": { "DATABASE_URI": "${input:pg-uri}" }
  }
}

Connecting to more than one database? Add one entry per database under servers (for example pg-onprem, pg-rds, pg-aurora), each with its own input, so you can query them side by side.

Windows note: if VS Code can't launch postgres-mcp, run where postgres-mcp and put the full path to the .exe in the command field.

Step 5 — Start and verify

Reload VS Code: Command Palette → Developer: Reload Window.
Command Palette → MCP: List Servers, start your server, then choose Show Output to watch the startup log. Connection and authentication errors show up here.
Open Copilot Chat, switch to agent mode, and confirm the Postgres tools appear.
Try a prompt such as "list the schemas in this database" or ask for a database health check.

Easier alternative: the Microsoft PostgreSQL extension

If you'd prefer a GUI and no hand-written mcp.json, Microsoft publishes an official PostgreSQL extension for VS Code with a built-in AI/MCP surface. Install it from the Extensions view (published by Microsoft), add a database connection, then right-click the database in the Connections tree and choose Connect AI to start an agent-mode session — or use the @pgsql chat participant in Copilot Chat.

It is excellent for schema browsing, query authoring, and general management, and it has a read-only access-mode setting. For deeper tuning work (hypothetical-index analysis, detailed health checks), Postgres MCP Pro is stronger. The two are not mutually exclusive — a common setup is the extension as a daily driver, plus the standalone server when you need its tuning tools.

Security checklist

Use a least-privilege database account for the MCP server — read-only for anything touching production. The account's grants are your real safety boundary.
Run in restricted access mode unless you specifically need writes.
Never store passwords in mcp.json. The inputs / promptString pattern keeps them out of the file and out of source control.
Enforce SSL on cloud databases with sslmode=verify-full and the CA bundle.
Optional tuning extensions: index-tuning and slow-query features rely on pg_stat_statements (enabled via the parameter group / shared_preload_libraries) and hypopg for hypothetical indexes. Both are available on RDS and Aurora; install them on-prem. Basic querying and health checks work without them.

A note on PostgreSQL versions

The PostgreSQL driver used by the server works across a wide range of server versions — comfortably from PostgreSQL 10 through the current major release, and newer majors once their client libraries ship. Very old versions (such as 10, which is past end-of-life) still connect, though production databases should stay on supported releases.

Sunday, August 16, 2026

Cutting LLM Token Costs for Database Workloads: A DBA's Guide

Once you start using an LLM for real database work. — parsing alert logs, interpreting AWR reports, generating shell scripts, drafting runbooks — the bill stops being a rounding error. A workflow that costs a few cents in testing can cost hundreds of dollars a month once it runs against a production fleet every day.

The good news is that most of that cost is avoidable, and the techniques are mechanical rather than clever. This post covers what actually moves the number, in the order I would implement them.

Pricing figures below are current as of August 2026 and are quoted per million tokens (MTok). Check the official pricing page before budgeting — rates change.

1. Understand what you are paying for

Every API call bills on two things: the tokens you send (input) and the tokens the model generates (output). A rough rule is that one token is about four characters, or three-quarters of an English word.

The asymmetry matters:

Model	Input / MTok	Output / MTok
Haiku 4.5	$1	$5
Sonnet 5	$2	$10
Opus 5	$5	$25

Output costs five times input on every tier. A verbose response is far more expensive than a verbose prompt. That single fact drives several of the decisions below.

2. Measure before you optimise

You cannot tune what you have not measured. This is the same discipline as looking at an AWR report before changing an init parameter.

Give each workload its own API key

The usage dashboard breaks spend down by key. One key for log analysis, one for script generation, one for ad-hoc work. Without this you know your total bill but not which job caused it.

Log the usage block from every response

Every API response returns token counts:

{
  "usage": {
    "input_tokens": 105,
    "output_tokens": 239,
    "cache_read_input_tokens": 7123,
    "cache_creation_input_tokens": 7345
  }
}

Capture that into a table alongside the job name and timestamp. After a week you will know exactly where the tokens go.

Count tokens before you send

Do not estimate token counts with a generic tokenizer. Libraries such as tiktoken are built for a different model family and undercount. Any budget or truncation logic built on them will be wrong. Use the API's own count_tokens endpoint against the model you are actually calling.

3. Right-size the model — the single biggest lever

Most teams pick one model and run everything through it. That is the equivalent of running every query on your largest database server.

Haiku costs one fifth of Opus on both input and output. For DBA workloads the split is usually clear:

Task type	Model	Why
Log line classification, error extraction, alert routing, output formatting	Haiku	Structured input, short output, no deep reasoning needed
Script generation, AWR interpretation, documentation, routine analysis	Sonnet	Most day-to-day work sits here
Complex root cause analysis, architecture decisions, difficult migrations	Opus	Worth the price when the reasoning is genuinely hard

A practical pattern is two-stage: use Haiku to filter and classify a large volume of input, then send only the interesting cases to Sonnet or Opus. Ninety percent of alert log lines are noise. There is no reason to pay premium rates to have them identified as noise.

4. Prompt caching — 90% off repeated context

This is the feature most people never configure, and it is usually the second biggest saving.

You mark a section of your prompt as cacheable. The first call writes it to cache; subsequent calls read it at a tenth of the standard input price.

Operation	Cost vs base input	Cache duration
5-minute cache write	1.25x	5 minutes
1-hour cache write	2x	1 hour
Cache read (hit)	0.1x	Same as the write

The break-even is quick: the 5-minute cache pays for itself after a single read, the 1-hour cache after two.

The structural requirement

Static content first, dynamic content last. Caching matches on a prefix, so anything that changes between calls must come after everything that does not. If you shuffle the order, the prefix stops matching and you pay full price without noticing.

For database work, the cacheable prefix is usually substantial:

The system prompt and role instructions
Your environment's naming conventions and standards
A schema definition or data dictionary extract
A runbook or troubleshooting playbook
Few-shot examples of good output

That block might be several thousand tokens, and it is identical on every call. Paying full price for it a thousand times a day is pure waste.

Example

import anthropic

client = anthropic.Anthropic()

RUNBOOK = open("oracle_alert_runbook.txt").read()   # static, large

response = client.messages.create(
    model="claude-haiku-4-5-20251001",
    max_tokens=500,
    system=[
        {
            "type": "text",
            "text": "You analyse Oracle alert log entries. "
                    "Classify severity and identify the ORA error.",
        },
        {
            "type": "text",
            "text": RUNBOOK,
            "cache_control": {"type": "ephemeral"},
        },
    ],
    messages=[
        {"role": "user", "content": log_excerpt}   # dynamic, small, LAST
    ],
)

Confirm it is working by checking cache_read_input_tokens in the response. If it stays at zero across repeated calls, your prefix is not matching — something dynamic has crept in ahead of the breakpoint.

5. Batch API — 50% off anything not interactive

The Batch API processes requests asynchronously at half price on both input and output. A large share of DBA automation does not need an answer in two seconds:

Overnight alert log analysis across a fleet
Bulk documentation or runbook generation
Classifying a backlog of tickets or incidents
Scanning a month of logs for a pattern
Reviewing scripts in a repository

Batch and caching discounts stack, which is where the numbers get interesting.

Two things that bite: batch results come back in any order — match on your custom_id, never on position. And because a batch can take longer than five minutes, use the 1-hour cache duration or your cache expires mid-run.

6. Trim the payload

Because output costs five times input, response discipline matters most.

Cap max_tokens at what the task actually needs. If you want a severity classification, you need twenty tokens, not two thousand.
Ask for structured output. JSON or a table instead of prose. "Return only the ORA error code and a one-line cause" beats an open request every time.
Strip boilerplate before sending. Repeated headers, banner text and duplicate stack traces are pure cost. A short pre-processing step in shell or Python pays for itself immediately.
Summarise conversation history rather than resending every turn in a long-running session.

Do not over-trim. Cutting context past the point of sufficiency degrades the answer and causes retries that cost more than the context you saved. After any aggressive reduction, validate output quality against real cases before rolling it out.

7. Watch the server-side tool costs

If your workflow uses built-in tools, they bill separately from tokens:

Web search is charged per search on top of token cost, and the results count as input tokens in that turn and every following turn of the conversation.
Web fetch has no surcharge, but the fetched content becomes input tokens. A large PDF can run to six figures of tokens. Cap it with max_content_tokens.
Tool definitions themselves add a few hundred input tokens per request. If you are passing twenty tools and using three, trim the list.

8. Worked example

An illustrative daily workload: 500 alert log excerpts analysed per day, each roughly 2,000 tokens of log text, sharing a 3,000-token runbook prefix, producing about 300 tokens of output each.

Configuration	Approx. daily cost	Reduction
Everything on Opus, no caching, real time	$16.25	baseline
Routed to Haiku	$3.25	80%
Haiku + prompt caching on the runbook	$1.93	88%
Haiku + caching + Batch API	$0.98	94%

Note where the saving actually comes from. Model routing does most of the work; caching and batching compound on top. If you only do one thing, make it the routing.

Your own numbers will differ — the ratio of shared prefix to unique input is what determines how much caching helps. Measure your workload first (section 2) rather than assuming these ratios transfer.

9. Implementation order

Measure. Separate API keys, log the usage block, establish a baseline.
Route by task. Highest impact. Move classification and extraction work down to the cheapest model that does the job well.
Add caching. Low effort — restructure the prompt so static content comes first, add one field. Verify with cache_read_input_tokens.
Migrate to batch. Highest effort because it changes your workflow, but a flat 50% on everything eligible.
Trim payloads. Ongoing refinement once the structural work is done.
Re-measure. Compare against the baseline from step 1 and validate output quality, not just cost.

References

Pricing reference — authoritative rates and how discounts stack
Prompt caching — breakpoints, durations, implementation
Batch processing — submit and poll patterns

Conclusion

Token cost optimisation is closer to database tuning than to software development. You measure first, find the expensive operation, and fix that rather than micro-optimising everywhere. The same instinct that stops you adding indexes before reading an execution plan applies here.

The order that matters: route work to the right model, cache what repeats, batch what can wait. Everything else is refinement.

Principle: treat the token bill like any other resource consumption metric. Baseline it, alert on it, and review it after every change to the workload.

Docker Administration

Docker has become increasingly useful for database administrators for development, testing, proof-of-concept environments, automation, and database tooling. This guide covers practical Docker administration commands with a DBA focus, particularly for PostgreSQL containers.

Throughout this guide, CONTAINER_ID stands for your actual container ID or container name. Substitute it in every command.

Important: Docker containers are not a replacement for database backup, high availability, or disaster recovery. For production databases, persistent storage, backup, recovery, security, monitoring and HA architecture must be designed separately.

1. Basic concepts

Docker object	Description
Image	Read-only template used to create containers
Container	Running or stopped instance created from an image
Volume	Persistent storage managed by Docker
Network	Communication between containers and the outside
Dockerfile	File used to build an image
Compose	Tool to define and manage multiple containers
Registry	Repository where images are stored

For a DBA the chain that matters is:

PostgreSQL container
    |
    +-- PostgreSQL process
    |
    +-- /var/lib/postgresql/data
    |       |
    |       +-- Docker volume
    |
    +-- Port 5432
    |
    +-- Container logs

2. Verify Docker version

docker version

Shorter output:

docker --version

Engine and host details:

docker info

Use docker info when investigating the storage driver, Docker root directory, container runtime, logging configuration, and available CPU and memory.

3. List containers

Running containers:

docker ps

Sample output:

CONTAINER ID   IMAGE         COMMAND                  STATUS
efca1e07d460   postgres:13   "docker-entrypoint..."   Up 2 hours

All containers including stopped ones:

docker ps -a

This is one of the most useful DBA commands, because a database container may have stopped due to startup failure, configuration errors, permission problems, disk space, out-of-memory conditions, wrong environment variables, or failed initialization.

Container IDs only:

docker ps -aq

4. Verify container status

Filter for one container:

docker ps -a --filter id=CONTAINER_ID

Example:

docker ps -a --filter id=efca1e07d460

Full detail:

docker inspect CONTAINER_ID

docker inspect returns container state, IP address, port mappings, mounted volumes, environment variables, restart policy, network configuration, startup command and resource limits.

Just the IP address:

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' CONTAINER_ID

5. Container logs

Logs are the first place to look when a database container misbehaves.

docker logs CONTAINER_ID

Example:

docker logs efca1e07d460

For a PostgreSQL container you may see:

database system is ready to accept connections

or:

FATAL:  role "root" does not exist

The second message does not mean PostgreSQL is down. It means something tried to connect using the OS user root, and no PostgreSQL role called root exists. Connect as the correct database user instead:

docker exec -it CONTAINER_ID psql -U postgres

6. Recent log lines

docker logs --tail 100 CONTAINER_ID

Example:

docker logs --tail 200 efca1e07d460

Useful for recent startup failures, connection or authentication errors, crash and restart events, and extension failures.

7. Follow logs in real time

docker logs --follow CONTAINER_ID

or:

docker logs -f CONTAINER_ID

Useful while restarting the database, testing application connections, reproducing a problem, or watching initialization scripts run.

Press Ctrl+C to stop following. This stops the log display only — it does not stop the container.

8. Log timestamps

docker logs -t CONTAINER_ID

or:

docker logs --timestamps CONTAINER_ID

Timestamps let you correlate Docker events with PostgreSQL logs, application logs, monitoring alerts and OS events. Combine with --tail:

docker logs --timestamps --tail 100 CONTAINER_ID

9. Logs from a specific time

Last hour:

docker logs --since 1h CONTAINER_ID

Last 30 minutes:

docker logs --since 30m CONTAINER_ID

After a specific timestamp:

docker logs --since "2026-08-16T10:00:00" CONTAINER_ID

Combined with follow:

docker logs --since 10m -f CONTAINER_ID

During an incident this avoids scanning the entire log history.

10. Check container processes

docker top CONTAINER_ID

For a PostgreSQL container you should see the expected background processes:

postgres
postgres: checkpointer
postgres: background writer
postgres: walwriter
postgres: autovacuum launcher
postgres: logical replication launcher

If these are absent, the container is up but the database is not.

11. Execute a command inside a container

docker exec CONTAINER_ID COMMAND

Example:

docker exec efca1e07d460 ps -ef

Interactive shell:

docker exec -it CONTAINER_ID bash

If bash is not present in the image:

docker exec -it CONTAINER_ID sh

Once inside, the usual checks apply:

ps -ef
df -h
free -m
ls -ltr /var/lib/postgresql/data

12. Connect to PostgreSQL inside the container

docker exec -it CONTAINER_ID psql -U postgres

Specific database:

docker exec -it CONTAINER_ID psql -U postgres -d postgres

Single query without an interactive session:

docker exec -it CONTAINER_ID psql -U postgres -d postgres -c "SELECT version();"

Example:

docker exec -it efca1e07d460 psql -U postgres -c "SELECT version();"

13. Check database readiness

docker exec CONTAINER_ID pg_isready

Expected output:

/var/run/postgresql:5432 - accepting connections

With explicit host and port:

docker exec CONTAINER_ID pg_isready -h localhost -p 5432

This is the simplest health check for a PostgreSQL container.

14. Check database version

docker exec CONTAINER_ID psql -U postgres -c "SELECT version();"

or:

docker exec CONTAINER_ID psql -U postgres -c "SHOW server_version;"

Worth confirming after deployment, image upgrade, patch testing, migration, restore, or an environment refresh.

15. Start a stopped container

docker start CONTAINER_ID

Start and attach to output:

docker start -a CONTAINER_ID

A useful sequence after starting a database container:

docker start CONTAINER_ID
docker ps
docker logs --tail 100 CONTAINER_ID

16. Stop a running container

docker stop CONTAINER_ID

Docker sends a termination signal to the main process and waits for the configured timeout before forcing termination. Prefer docker stop over docker kill for databases — graceful shutdown lets the database complete its normal shutdown processing.

17. Restart a database container

docker restart CONTAINER_ID

Then validate, in order:

docker ps
docker logs --tail 100 CONTAINER_ID
docker exec CONTAINER_ID pg_isready
docker exec CONTAINER_ID psql -U postgres -c "SELECT now();"

18. Force stop a container

docker kill CONTAINER_ID

This terminates the container process immediately. Avoid it for databases unless the container is completely unresponsive, graceful shutdown has already failed, or emergency recovery requires it.

19. Container resource usage

docker stats

For one container:

docker stats CONTAINER_ID

Shows CPU percentage, memory usage against limit, network I/O, block I/O and process count. Useful when investigating high database CPU, memory pressure, OOM-related restarts, or heavy disk I/O.

For a single non-streaming sample:

docker stats --no-stream CONTAINER_ID

20. Container storage usage

docker system df

More detail:

docker system df -v

Identifies unused images, stopped containers, unused volumes and reclaimable storage.

Be careful cleaning Docker storage on database hosts: never remove a volume because it appears unused without first confirming it holds no database data.

21. Verify database volumes

docker volume ls

Inspect one:

docker volume inspect postgres_data

A PostgreSQL volume is typically mounted at /var/lib/postgresql/data.

The principle to hold on to:
Container = disposable. Database data = persistent.

If PostgreSQL data exists only in the writable container layer, removing the container destroys the database.

22. Verify container mounts

docker inspect -f '{{json .Mounts}}' CONTAINER_ID

Confirm that /var/lib/postgresql/data is backed by persistent storage before running docker rm or docker system prune.

23. Check network configuration

docker network ls

Inspect a network:

docker network inspect NETWORK_NAME

For database troubleshooting, verify the container IP, network name, port mappings, application-to-database connectivity, DNS resolution and network isolation.

24. Check port mapping

docker port CONTAINER_ID

Example output:

5432/tcp -> 0.0.0.0:5432

The host port does not have to match the container port. A mapping of 15432:5432 means:

Docker host port 15432
    |
    v
container port 5432
    |
    v
PostgreSQL

25. Check environment variables

docker inspect -f '{{range .Config.Env}}{{println .}}{{end}}' CONTAINER_ID

Common PostgreSQL variables:

POSTGRES_USER
POSTGRES_PASSWORD
POSTGRES_DB

Security note: avoid exposing passwords in screenshots, blog posts, shell history or configuration files. For production, use Docker secrets or an enterprise secret-management solution rather than plain-text environment variables.

26. Container restart policy

docker inspect -f '{{.HostConfig.RestartPolicy.Name}}' CONTAINER_ID

Common policies:

no
always
unless-stopped
on-failure

Change it:

docker update --restart unless-stopped CONTAINER_ID

A restart policy improves availability but is not database HA. Understand why a database container stopped before relying on automatic restart.

27. Identify why a container stopped

docker ps -a
docker inspect -f '{{.State.Status}}' CONTAINER_ID
docker inspect -f '{{.State.ExitCode}}' CONTAINER_ID
docker inspect -f '{{.State.Error}}' CONTAINER_ID

An exit code of 1 indicates the process terminated with an error. Always correlate the exit code with docker logs — the code alone rarely explains the root cause.

28. Troubleshooting flow for an unavailable database

Step 1 — Is the container running?

docker ps -a

Step 2 — What do recent logs show?

docker logs --tail 200 CONTAINER_ID

Step 3 — What is the container state?

docker inspect -f '{{.State.Status}} {{.State.ExitCode}} {{.State.Error}}' CONTAINER_ID

Step 4 — Resource consumption?

docker stats --no-stream CONTAINER_ID

Step 5 — Storage?

docker exec CONTAINER_ID df -h

Step 6 — Is the database process running?

docker top CONTAINER_ID

Step 7 — Is PostgreSQL ready?

docker exec CONTAINER_ID pg_isready

Step 8 — Does a query succeed?

docker exec -it CONTAINER_ID psql -U postgres -c "SELECT now();"

This gives a structured path instead of immediately restarting the container.

29. PostgreSQL log examples

Database ready:

database system is ready to accept connections

Role problem:

FATAL:  role "root" does not exist

Check which roles exist:

docker exec -it CONTAINER_ID psql -U postgres -c "\du"

Port already in use — the container fails to start because the host port is taken. Check on the Docker host:

docker ps
ss -lntp

Disk space — check both sides:

docker exec CONTAINER_ID df -h
df -h

A database can fail even when the container looks healthy, if the underlying host filesystem is full.

30. Remove a container

docker rm CONTAINER_ID

Force removal:

docker rm -f CONTAINER_ID

Before removing a database container, verify: where the data is stored, whether it is in a Docker volume, whether a backup exists, whether the container is still needed, and whether another container uses the same volume.

Removing a container and removing its volume are separate operations, but careless cleanup still causes data loss.

31. Remove unused resources

docker system prune

Review what will be removed first:

docker system df

Be especially careful with:

docker volume prune

Docker volumes may contain database data. On production database hosts, avoid blanket cleanup commands unless they are part of an approved procedure.

32. Backup PostgreSQL from Docker

Plain SQL dump:

docker exec CONTAINER_ID pg_dump -U postgres -d mydatabase > mydatabase.sql

Custom format:

docker exec CONTAINER_ID pg_dump -U postgres -d mydatabase -Fc > mydatabase.dump

All databases:

docker exec CONTAINER_ID pg_dumpall -U postgres > all_databases.sql

Production backup procedures also need retention, encryption, off-host storage, validation, restore testing, defined RPO and RTO, and monitoring.

A Docker volume is not a backup.

33. Restore PostgreSQL into a container

Plain SQL dump:

cat mydatabase.sql | docker exec -i CONTAINER_ID psql -U postgres -d mydatabase

Custom-format dump:

cat mydatabase.dump | docker exec -i CONTAINER_ID pg_restore -U postgres -d mydatabase

Note the -i flag rather than -it: input is piped, so there is no TTY.

Before a production restore, validate the target database and version, required extensions, available disk space, existing objects, role requirements and backup integrity.

34. Copy files between host and container

Host to container:

docker cp backup.sql CONTAINER_ID:/tmp/backup.sql

Container to host:

docker cp CONTAINER_ID:/tmp/backup.sql ./backup.sql

Example:

docker cp efca1e07d460:/var/log/postgresql/postgresql.log .

Do not manually copy database data files while PostgreSQL is running. Use pg_dump, pg_dumpall or pg_basebackup depending on the recovery requirement.

35. Docker images

docker images
docker image ls

Pull an image:

docker pull postgres:16

Image details:

docker image inspect postgres:16

Remove an image:

docker image rm IMAGE_ID

After a database upgrade, do not immediately delete the previous image — it is useful for rollback planning, reproducing issues and version comparison. Balance retention against security and storage requirements.

36. Container naming

docker run --name postgres-dev \
  -e POSTGRES_PASSWORD='ExamplePassword' \
  -d postgres:16

Commands then become readable:

docker logs postgres-dev
docker exec -it postgres-dev psql -U postgres
docker stats postgres-dev

Meaningful names beat randomly generated container IDs.

37. DBA command cheat sheet

Requirement	Command
List running containers	`docker ps`
List all containers	`docker ps -a`
Inspect container	`docker inspect CONTAINER_ID`
View logs	`docker logs CONTAINER_ID`
Last 100 log lines	`docker logs --tail 100 CONTAINER_ID`
Follow logs	`docker logs -f CONTAINER_ID`
Logs with timestamps	`docker logs -t CONTAINER_ID`
Logs from last hour	`docker logs --since 1h CONTAINER_ID`
Container processes	`docker top CONTAINER_ID`
Execute a command	`docker exec CONTAINER_ID COMMAND`
Open a shell	`docker exec -it CONTAINER_ID bash`
Start container	`docker start CONTAINER_ID`
Stop container	`docker stop CONTAINER_ID`
Restart container	`docker restart CONTAINER_ID`
Resource usage	`docker stats CONTAINER_ID`
List volumes	`docker volume ls`
Inspect volume	`docker volume inspect VOLUME_NAME`
List networks	`docker network ls`
Inspect network	`docker network inspect NETWORK_NAME`
Check port mapping	`docker port CONTAINER_ID`
Docker disk usage	`docker system df`
Copy files	`docker cp SOURCE DESTINATION`
Remove stopped container	`docker rm CONTAINER_ID`
List images	`docker image ls`

38. Troubleshooting checklist

When a database inside Docker is reported down, check in order:

Is the Docker service running?
Is the database container running?
Did the container recently restart?
What is the container exit code?
What do the latest logs show?
Is the database process running?
Is the database accepting connections?
Is the expected port exposed?
Is the Docker network working?
Is the database volume mounted correctly?
Is sufficient disk space available?
Is the container hitting its memory limit?
Is CPU usage unusually high?
Are database credentials correct?
Do the required roles exist?
Are configuration files present?
Is the database version correct?
Are backups available and tested?
Is the restart policy appropriate?

39. Best practices

Keep database data outside the container layer. Use persistent volumes or external storage.
Never treat a container as a backup. A container can be recreated; the data needs an independent backup strategy.
Monitor the Docker host. CPU, memory, disk latency, disk capacity, network and the storage driver all affect database performance.
Monitor container resources with docker stats, and feed Docker metrics into your monitoring platform.
Avoid plain-text passwords in environment variables. Use approved secret management for production.
Pin image versions. Use postgres:16 rather than postgres:latest, or a more specific tag where the environment requires it.
Test upgrades before production. An image upgrade can also be a database version upgrade — validate application compatibility, extensions, parameters, backup and restore, performance, and the rollback path.
Do not manipulate database files directly from the host or container shell. Use database-native tools.
Separate application and database concerns using Docker networks and clear service boundaries.
Treat production database containers as infrastructure: monitoring, patching, backup, recovery, security, capacity planning, access control, HA, DR and change management all still apply.

40. Quick health check sequence

docker ps
docker inspect -f '{{.State.Status}} {{.State.ExitCode}}' CONTAINER_ID
docker logs --tail 50 CONTAINER_ID
docker stats --no-stream CONTAINER_ID
docker exec CONTAINER_ID df -h
docker exec CONTAINER_ID pg_isready
docker exec CONTAINER_ID psql -U postgres -c "SELECT version();"
docker exec CONTAINER_ID psql -U postgres -c "SELECT now();"

That walks the full stack:

container -> process -> resources -> storage -> PostgreSQL -> connectivity

Conclusion

Docker gives DBAs a lightweight, repeatable way to build database environments for development, testing, automation, migration rehearsal and proof-of-concept work. It does not remove any traditional DBA responsibility.

The commands that matter day to day:

docker ps
docker inspect
docker logs
docker exec
docker stats
docker top
docker start
docker stop
docker restart
docker volume
docker network
docker cp

Combined with PostgreSQL-native tools — psql, pg_isready, pg_dump, pg_dumpall, pg_restore, pg_basebackup — that is a practical foundation for administering PostgreSQL containers.

DBA principle: Docker manages the container lifecycle. The DBA remains responsible for the database lifecycle, data protection, recoverability, security and operational reliability.

Backup and Recovery in YugabyteDB Using Distributed Snapshots: A Step-by-Step Guide

===================================================================================

A distributed snapshot is a consistent cut of the data taken across every node

in a YugabyteDB cluster at the same logical point in time. It is the fastest

backup method the database offers, and it behaves very differently from an

Oracle RMAN backup or a PostgreSQL pg_dump.

The reason it is fast: creating a snapshot does not copy any data. YugabyteDB

creates hard links to the existing SST files on the same storage volumes where

the data already lives. Both backup and restore are close to instantaneous

regardless of database size.

The trade-off follows directly from that. Because the snapshot lives on the

same disks as the data, it protects you from logical errors - a bad DELETE, a

wrong UPDATE, a failed deployment - but not from file system corruption or

hardware failure. For that you must move the snapshot off the cluster, which is

covered in section D.

Environment used in this guide: the three-node cluster built in the previous

post - ybnode1 (10.0.0.11), ybnode2 (10.0.0.12), ybnode3 (10.0.0.13), all

running YugabyteDB with masters on port 7100.

SCOPE

-----

- Creating and listing in-cluster snapshots

- Restoring a database from an in-cluster snapshot

- Understanding what a snapshot restore does NOT recover

- Moving a snapshot to external storage and restoring it on another cluster

- Point-in-time recovery using snapshot schedules

- YCQL equivalents and common errors

BACKUP METHOD COMPARISON

------------------------

Method Speed Protects against Off-cluster

--------------------- ---------- ------------------- -----------

In-cluster snapshot Instant Logical errors No

Snapshot + export Fast Logical + hardware Yes

PITR schedule Instant Logical errors, No

to any point in

the retention window

ysql_dump Slow Everything Yes

In practice you use more than one. A PITR schedule covers the "someone ran

DELETE without a WHERE clause ten minutes ago" case. An exported snapshot on

object storage covers the "we lost the data centre" case.

A. THE SNAPSHOT LIFECYCLE

create_database_snapshot -> snapshot in COMPLETE state

| |

| +--> restore_snapshot (in-place)

| |

| +--> export_snapshot ---+

| |

+--> delete_snapshot v

external storage

(S3 / NFS / tape)

import_snapshot on new cluster

restore_snapshot

B. CREATE AN IN-CLUSTER SNAPSHOT

--------------------------------

Step 1 - Create some test data

Connect with ysqlsh and build a database we can safely damage:

./bin/ysqlsh -h 10.0.0.11 -p 5433 -U yugabyte

yugabyte=# CREATE DATABASE snaptest;

yugabyte=# \c snaptest

snaptest=# CREATE TABLE employees (

emp_id int PRIMARY KEY,

emp_name text,

dept text);

snaptest=# INSERT INTO employees VALUES

(1,'Ashok','DBA'),

(2,'Ravi','Apps'),

(3,'Meena','Cloud');

snaptest=# SELECT count(*) FROM employees;

Step 2 - Create the snapshot

Snapshots for YSQL are taken at database level. Backing up an individual table

is not supported on the YSQL side - note the ysql. prefix on the database name,

which is what tells yb-admin this is a YSQL database and not a YCQL keyspace.

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

create_database_snapshot ysql.snaptest

Output:

Started snapshot creation: 0d4b4935-2c95-4523-95ab-9ead1e95e794

Record that UUID. It is how you check, restore, export or delete the snapshot.

Step 3 - Confirm the snapshot completed

The create command returns immediately, but the snapshot itself completes

asynchronously. Never treat the returned UUID as proof of a usable backup -

always verify the state:

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

list_snapshots

Output:

Snapshot UUID State Creation Time

0d4b4935-2c95-4523-95ab-9ead1e95e794 COMPLETE 2026-08-16 09:20:38.214201

Only a snapshot in COMPLETE state is restorable. If it is still CREATING, wait

and run list_snapshots again. This command also shows any restore operations

and their states, which is how you monitor a restore in progress.

C. RESTORE FROM AN IN-CLUSTER SNAPSHOT

--------------------------------------

Step 1 - Simulate the failure

snaptest=# DELETE FROM employees;

DELETE 3

snaptest=# SELECT count(*) FROM employees;

count

-------

Step 2 - Restore

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

restore_snapshot 0d4b4935-2c95-4523-95ab-9ead1e95e794

Output:

Started restoring snapshot: 0d4b4935-2c95-4523-95ab-9ead1e95e794

Restoration id: 5a9bc559-2155-4c38-ac8b-b6d0f7aa1af6

The restore is in-place. It rolls the existing database in the same cluster

back to its state at snapshot time. There is no separate target - anything

written after the snapshot is discarded.

Step 3 - Confirm the restore finished

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

list_snapshots

The restoration appears with its own state. Wait for RESTORED before you let

applications reconnect.

Step 4 - Verify the data

snaptest=# SELECT * FROM employees;

emp_id | emp_name | dept

--------+----------+-------

1 | Ashok | DBA

2 | Ravi | Apps

3 | Meena | Cloud

Step 5 - Delete the snapshot when no longer needed

Snapshots never expire. They are retained for the life of the cluster and each

one holds disk space that would otherwise be reclaimed by compaction, so an

unmanaged pile of snapshots slowly inflates your storage bill.

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

delete_snapshot 0d4b4935-2c95-4523-95ab-9ead1e95e794

IMPORTANT LIMITATION - SCHEMA CHANGES ARE NOT RESTORED

------------------------------------------------------

The in-cluster restore reverts data changes, not schema changes. If you take a

snapshot, DROP a table, and then restore the snapshot, the table does not come

back.

This surprises DBAs coming from RMAN, where a restore returns the whole

database to its earlier state. Plan around it in one of two ways:

- Export the snapshot to external storage (section D), which carries the schema

in a separate dump file, or

- Use point-in-time recovery (section E), which does handle schema rollback.

Either way: take a snapshot immediately after every schema change, and keep an

independent schema dump. A snapshot whose schema you cannot reproduce is only

half a backup.

D. MOVE A SNAPSHOT TO EXTERNAL STORAGE

--------------------------------------

This is the procedure that turns a snapshot into a real backup - one that

survives losing the cluster, and can be restored onto a different cluster.

Step 1 - Record the catalog version

snaptest=# SELECT yb_catalog_version();

yb_catalog_version

--------------------

Note this number. You will compare against it in step 4.

Step 2 - Create the in-cluster snapshot

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

create_database_snapshot ysql.snaptest

Wait for COMPLETE via list_snapshots.

Step 3 - Dump the YSQL schema

./postgres/bin/ysql_dump \

-h 10.0.0.11 \

--include-yb-metadata \

--serializable-deferrable \

--create \

--schema-only \

--dbname snaptest \

--file snaptest_schema.sql

Flag by flag:

--include-yb-metadata

Emits YugabyteDB-specific attributes such as tablet split points and

colocation settings. Without it the restored schema loses its distribution

properties and you get a functionally correct but differently sharded

database.

--serializable-deferrable

Takes the dump at a consistent snapshot without blocking writers.

--create

Includes the CREATE DATABASE statement, so the target cluster does not need

the database pre-created.

--schema-only

Structure only. The data comes from the snapshot files, not from this dump.

Step 4 - Re-check the catalog version

snaptest=# SELECT yb_catalog_version();

If this does not match what you recorded in step 1, a DDL statement ran during

the backup and the snapshot is not guaranteed to be consistently restorable.

Start the whole procedure again. Do not skip this check - it is the only thing

standing between you and a backup that fails on restore day.

Step 5 - Export the snapshot metadata

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

export_snapshot 0d4b4935-2c95-4523-95ab-9ead1e95e794 snaptest.snapshot

This writes a metadata file describing which tables and tablets belong to the

snapshot. It does not contain the data.

Step 6 - Copy the tablet snapshot data off the nodes

This is the manual part. The snapshot files live under each tablet server's

data directory, in this structure:

<fs_data_dir>/yb-data/tserver/data/rocksdb/

table-<table_id>/

tablet-<tablet_id>.snapshots/

<snapshot_id>/

Example:

cp -r /yugabyte01/YUGABYTE/data1/yb-data/tserver/data/rocksdb/ \

table-00004000000030008000000000004003/ \

tablet-b0de9bc6a4cb46d4aaacf4a03bcaf6be.snapshots/ \

0d4b4935-2c95-4523-95ab-9ead1e95e794/ \

/backup/snaptest/

Two things that save time here:

- You only need the leader tablet on each node. Every replica holds identical

data, so copying all three replicas triples your backup size for nothing.

- Get the table_id values from the master admin UI at

http://10.0.0.11:7000/tables - they are UUIDs, not table names.

If your cluster has several data directories in --fs_data_dirs, repeat for each

one.

Step 7 - Copy the metadata files

Move both snaptest_schema.sql and snaptest.snapshot to the same external

location as the tablet data. All three parts are required for a restore.

At this point you can safely delete the in-cluster snapshot to reclaim space.

E. RESTORE FROM EXTERNAL STORAGE ONTO ANOTHER CLUSTER

-----------------------------------------------------

Step 1 - Make sure the target database does not exist

yugabyte=# DROP DATABASE IF EXISTS snaptest;

Step 2 - Recreate the schema

./bin/ysqlsh -h 10.0.0.11 --echo-all --file=snaptest_schema.sql

This is the step that recovers schema changes - the piece the in-cluster

restore cannot do.

Step 3 - Import the snapshot metadata

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

import_snapshot snaptest.snapshot snaptest

Output includes the ID mapping, which you need for the next step:

Successfully applied snapshot.

Object Old ID New ID

Keyspace 000040000000300080000000000000 000040000000300080000000000000

Table 000040000000300080000000004003 000040000000300080000000004001

Tablet 0 b0de9bc6a4cb46d4aaacf4a03bcaf6be 50046f422aa6450ca82538e919581048

Snapshot 0d4b4935-2c95-4523-95ab-... 6beb9c0e-52ea-4f61-89bd-...

The new cluster assigned fresh table, tablet and snapshot IDs. Keep this

mapping in front of you.

Step 4 - Copy the tablet data into the new locations

Using the mapping above, place each old tablet's snapshot contents into the

directory named for the corresponding new tablet ID:

scp -r /backup/snaptest/table-00004000000030008000000000004003/ \

tablet-b0de9bc6a4cb46d4aaacf4a03bcaf6be.snapshots/ \

0d4b4935-2c95-4523-95ab-9ead1e95e794/* \

10.0.0.11:/yugabyte01/YUGABYTE/data1/yb-data/tserver/data/rocksdb/ \

table-00004000000030008000000000004001/ \

tablet-50046f422aa6450ca82538e919581048.snapshots/ \

6beb9c0e-52ea-4f61-89bd-c160ec02c729/

Copy the contents of the snapshot folder, not the folder itself. Repeat for

every tablet peer, and for any read replica cluster.

Step 5 - Restore

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

restore_snapshot 6beb9c0e-52ea-4f61-89bd-c160ec02c729

Use the NEW snapshot ID from the import output, not the original one.

F. POINT-IN-TIME RECOVERY WITH SNAPSHOT SCHEDULES

-------------------------------------------------

Manual snapshots only let you go back to the moments you happened to take one.

A snapshot schedule takes them automatically and lets you restore to any point

inside the retention window - including schema state.

Step 1 - Create a schedule

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

create_snapshot_schedule 60 1440 ysql.snaptest

The two numbers are minutes: take a snapshot every 60 minutes, retain for 1440

minutes (24 hours). Tighter intervals give a finer recovery granularity at the

cost of more retained snapshots and more disk.

The command returns a schedule ID - store it with your runbooks.

Step 2 - List schedules

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

list_snapshot_schedules

Step 3 - Restore to a point in time

Relative time - "put it back to five minutes ago":

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

restore_snapshot_schedule <schedule_id> minus 5m

Absolute time, using Unix microseconds, when you know exactly when the bad

statement ran.

Before restoring, confirm no other restore is already running against the same

database - concurrent restores on one keyspace produce unpredictable results.

Step 4 - Remove a schedule

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

delete_snapshot_schedule <schedule_id>

Choose your restore target as close to the incident as you can. Everything

written between your target time and now is lost - that gap is your real RPO,

not the schedule interval.

G. YCQL EQUIVALENTS

-------------------

The YCQL side supports table-level granularity, which YSQL does not.

Whole keyspace:

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

create_keyspace_snapshot my_keyspace

Single table with its indexes:

./bin/yb-admin \

--master_addresses 10.0.0.11:7100,10.0.0.12:7100,10.0.0.13:7100 \

create_snapshot my_keyspace my_table

list_snapshots, restore_snapshot, delete_snapshot, export_snapshot and

import_snapshot behave identically for both APIs.

TROUBLESHOOTING QUICK REFERENCE

-------------------------------

Symptom Most likely cause

-------------------------------- ---------------------------------------

Snapshot stuck in CREATING A tablet server is down or unreachable;

check list_all_tablet_servers

Restore completes, table missing Schema change after the snapshot -

in-cluster restore cannot recover DDL

Catalog version changed DDL ran during the backup; restart the

export procedure

import_snapshot fails on target Schema not applied first, or applied

without --include-yb-metadata

Restore appears to do nothing Used the old snapshot ID instead of the

new one from import_snapshot output

Disk usage keeps climbing Old snapshots never deleted; they pin

SST files against compaction

PRACTICAL RECOMMENDATIONS

-------------------------

- Run a PITR schedule on every production database. It is the cheapest

insurance against human error.

- Export a snapshot to external storage on a fixed cadence. In-cluster

snapshots share the fate of the disks they sit on.

- Take a manual snapshot immediately before and after any schema change or

application release.

- Keep an independent ysql_dump schema backup alongside every exported

snapshot.

- Script the delete_snapshot cleanup. Nothing expires on its own.

- Rehearse the external restore on a test cluster before you need it. The ID

mapping step in section E is where an untested procedure fails.