Restore a PostgreSQL Dump

Restore a PostgreSQL Dump from Cloudflare R2 with Podman and DBeaver

This Windows/PowerShell guide covers:

Private Cloudflare R2 bucket
        ↓
Download with AWS CLI
        ↓
PostgreSQL 17 in Podman
        ↓
Restore with pg_restore or psql
        ↓
Connect and analyze with DBeaver

It also includes:

1. Replacing the complete disposable Podman database with another dump.
2. Replacing or merging one table from the restored dump into production.

[!IMPORTANT] Keep the R2 bucket private. Use an R2 API token with Object Read access limited to the backup bucket.

[!WARNING] A dump is a historical snapshot. Restoring old data into production can remove or overwrite changes made after the backup. Take a fresh production backup and test against a fresh production copy before changing production.

---

1. Prerequisites

Install:

• AWS CLI v2
• Podman Desktop or Podman CLI
• DBeaver
• A PostgreSQL image matching the source major version

Verify:

aws --version
podman --version

This guide assumes PostgreSQL 17 and uses:

AWS CLI profile:      r2-backups
Backup directory:     C:\Backups\Postgres
Podman container:     postgres-analysis
Podman volume:        postgres-analysis-data
PostgreSQL image:     docker.io/library/postgres:17
Host:                 localhost
Host port:            5433
Container port:       5432
Username:             postgres
Password:             localdev
Analysis database:    obaki_analysis

Change the local password on a shared machine.

---

2. Create private R2 credentials

Do not enable:

• The public r2.dev URL
• A public custom domain
• Anonymous bucket access

In Cloudflare:

R2 Object Storage
→ Manage R2 API Tokens
→ Create API Token

Use:

Name: Local PostgreSQL backup reader
Permission: Object Read
Bucket scope: Specific bucket only

Record the generated:

Access Key ID
Secret Access Key

Do not store these values in source control.

---

3. Configure AWS CLI

Create a dedicated profile:

aws configure --profile r2-backups

Enter:

AWS Access Key ID: <R2_ACCESS_KEY_ID>
AWS Secret Access Key: <R2_SECRET_ACCESS_KEY>
Default region name: auto
Default output format: json

Set reusable variables:

$AccountId = "<CLOUDFLARE_ACCOUNT_ID>"
$Bucket = "<R2_BUCKET_NAME>"
$Endpoint = "https://$AccountId.r2.cloudflarestorage.com"
$Profile = "r2-backups"
$BackupDirectory = "C:\Backups\Postgres"

Create the backup directory:

New-Item -ItemType Directory -Path $BackupDirectory -Force

List the private bucket:

aws s3 ls "s3://$Bucket/" `
  --recursive `
  --profile $Profile `
  --endpoint-url $Endpoint

List newest objects first:

aws s3api list-objects-v2 `
  --bucket $Bucket `
  --profile $Profile `
  --endpoint-url $Endpoint `
  --query "reverse(sort_by(Contents, &LastModified))[].[LastModified,Size,Key]" `
  --output table

---

4. Download a dump from R2

Set the object key:

$ObjectKey = "daily/obaki-2026-07-11.dump"
$DumpFileName = Split-Path $ObjectKey -Leaf
$LocalDump = Join-Path $BackupDirectory $DumpFileName

Download:

aws s3 cp `
  "s3://$Bucket/$ObjectKey" `
  $LocalDump `
  --profile $Profile `
  --endpoint-url $Endpoint

Verify:

Get-Item $LocalDump |
  Select-Object FullName, Length, LastWriteTime

Optional checksum:

Get-FileHash -Path $LocalDump -Algorithm SHA256

---

5. Initialize Podman on Windows

List Podman machines:

podman machine list

Create one if none exists:

podman machine init

Start it:

podman machine start

Verify:

podman info

---

6. Run PostgreSQL 17 in Podman

Create a persistent volume:

podman volume create postgres-analysis-data

Start PostgreSQL:

podman run `
  --name postgres-analysis `
  --detach `
  --env POSTGRES_USER=postgres `
  --env POSTGRES_PASSWORD=localdev `
  --env POSTGRES_DB=postgres `
  --publish 127.0.0.1:5433:5432 `
  --volume postgres-analysis-data:/var/lib/postgresql/data `
  docker.io/library/postgres:17

The local-only port mapping is:

127.0.0.1:5433 → container:5432

Check startup:

podman ps
podman logs postgres-analysis

Readiness test:

podman exec postgres-analysis `
  pg_isready `
  --username postgres `
  --dbname postgres

Expected:

/var/run/postgresql:5432 - accepting connections

---

7. Restore the dump

Create the analysis database:

podman exec postgres-analysis `
  createdb `
  --username postgres `
  obaki_analysis

If it already exists, recreate it:

podman exec postgres-analysis `
  psql `
  --username postgres `
  --dbname postgres `
  --command "DROP DATABASE IF EXISTS obaki_analysis WITH (FORCE);"

podman exec postgres-analysis `
  createdb `
  --username postgres `
  obaki_analysis

Copy the dump into the container:

podman cp `
  $LocalDump `
  postgres-analysis:/tmp/database.dump

Verify:

podman exec postgres-analysis ls -lh /tmp/database.dump

Detect the format

podman exec postgres-analysis `
  pg_restore `
  --list `
  /tmp/database.dump

If an archive table of contents appears, use pg_restore:

podman exec postgres-analysis `
  pg_restore `
  --username postgres `
  --dbname obaki_analysis `
  --no-owner `
  --no-privileges `
  --exit-on-error `
  --verbose `
  /tmp/database.dump

If it reports that the input is a text-format dump, use psql:

podman exec postgres-analysis `
  psql `
  --username postgres `
  --dbname obaki_analysis `
  --set ON_ERROR_STOP=on `
  --file /tmp/database.dump

Check the exit code:

$LASTEXITCODE

Expected:

0

Remove the temporary copy:

podman exec postgres-analysis rm -f /tmp/database.dump

---

8. Connect with DBeaver

Create a PostgreSQL connection:

Host: localhost
Port: 5433
Database: obaki_analysis
Username: postgres
Password: localdev

Rename it:

LOCAL — Restored PostgreSQL Dump

Use a connection color different from production.

Browse:

Schemas
└── public
    └── Tables

Refresh the connection if objects do not appear.

Verify in DBeaver:

SELECT
    current_database(),
    current_user,
    version();

List tables and estimated row counts:

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC, relname;

Database size:

SELECT pg_size_pretty(pg_database_size(current_database()));

---

9. Completely replace the local Podman database

Use this when a newer dump should fully replace the disposable local obaki_analysis database.

Download the new dump:

$ObjectKey = "daily/obaki-2026-07-12.dump"
$DumpFileName = Split-Path $ObjectKey -Leaf
$LocalDump = Join-Path $BackupDirectory $DumpFileName

aws s3 cp `
  "s3://$Bucket/$ObjectKey" `
  $LocalDump `
  --profile $Profile `
  --endpoint-url $Endpoint

Disconnect the local DBeaver connection.

Drop and recreate the database:

podman exec postgres-analysis `
  psql `
  --username postgres `
  --dbname postgres `
  --command "DROP DATABASE IF EXISTS obaki_analysis WITH (FORCE);"

podman exec postgres-analysis `
  createdb `
  --username postgres `
  obaki_analysis

Copy the new dump:

podman cp `
  $LocalDump `
  postgres-analysis:/tmp/database.dump

Restore an archive dump:

podman exec postgres-analysis `
  pg_restore `
  --username postgres `
  --dbname obaki_analysis `
  --no-owner `
  --no-privileges `
  --exit-on-error `
  --verbose `
  /tmp/database.dump

For a plain SQL dump, use:

podman exec postgres-analysis `
  psql `
  --username postgres `
  --dbname obaki_analysis `
  --set ON_ERROR_STOP=on `
  --file /tmp/database.dump

Reconnect and refresh DBeaver.

This replacement affects only the local Podman database.

---

10. Replace or merge a specific table into production

Do not restore a selected table directly from the archive into production.

Use this safer flow:

Restored local database
        ↓
Export selected table
        ↓
Import into a production staging table
        ↓
Compare and validate
        ↓
Run a controlled transaction

Mandatory safeguards

Before changing production:

1. Take a fresh production backup.
2. Record the current production row count.
3. Test the procedure against a fresh local production copy.
4. Stop application writes to the affected table.
5. Review foreign keys, triggers, generated columns, and sequences.
6. Use explicit column lists.
7. Run the final transaction with ROLLBACK first.
8. Use COMMIT only after reviewing the result.

Inspect the local source table

Replace public.target_table and the sample columns:

SELECT COUNT(*) AS local_source_count
FROM public.target_table;

Check duplicate keys:

SELECT
    id,
    COUNT(*)
FROM public.target_table
GROUP BY id
HAVING COUNT(*) > 1;

Inspect columns:

SELECT
    ordinal_position,
    column_name,
    data_type,
    is_nullable,
    column_default,
    is_identity,
    is_generated
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'target_table'
ORDER BY ordinal_position;

Inspect production dependencies

Run against production.

Tables referencing the target:

SELECT
    conrelid::regclass AS referencing_table,
    conname AS constraint_name,
    pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'f'
  AND confrelid = 'public.target_table'::regclass
ORDER BY conrelid::regclass::text;

Foreign keys owned by the target:

SELECT
    conname,
    pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'f'
  AND conrelid = 'public.target_table'::regclass
ORDER BY conname;

Non-internal triggers:

SELECT
    tgname,
    pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgrelid = 'public.target_table'::regclass
  AND NOT tgisinternal;

Do not use TRUNCATE ... CASCADE as a shortcut. It may erase related tables.

Export the table from local Podman

Use explicit columns:

podman exec postgres-analysis `
  psql `
  --username postgres `
  --dbname obaki_analysis `
  --command "\copy (SELECT id, title, status, created_at, updated_at FROM public.target_table ORDER BY id) TO '/tmp/target_table.csv' WITH (FORMAT csv, HEADER true)"

Copy it to Windows:

podman cp `
  postgres-analysis:/tmp/target_table.csv `
  "C:\Backups\Postgres\target_table.csv"

Remove the temporary container file:

podman exec postgres-analysis rm -f /tmp/target_table.csv

Create a staging table in production

Connect through a clearly marked DBeaver connection:

PRODUCTION — PostgreSQL

Disable auto-commit and use a distinct production color.

Create the staging table:

CREATE SCHEMA IF NOT EXISTS restore_stage;

DROP TABLE IF EXISTS restore_stage.target_table;

CREATE TABLE restore_stage.target_table AS
SELECT
    id,
    title,
    status,
    created_at,
    updated_at
FROM public.target_table
WITH NO DATA;

This copies only the selected column structure. It does not copy production triggers, indexes, or foreign keys.

Import the CSV with DBeaver

In DBeaver:

restore_stage.target_table
→ Right-click
→ Import Data
→ CSV

Select:

C:\Backups\Postgres\target_table.csv

Verify:

• Header row is enabled
• Each column maps correctly
• Timestamps parse correctly
• Null values are handled correctly
• No column is silently skipped

Validate the staged data

SELECT COUNT(*) AS staged_count
FROM restore_stage.target_table;

Check duplicate keys:

SELECT
    id,
    COUNT(*)
FROM restore_stage.target_table
GROUP BY id
HAVING COUNT(*) > 1;

Compare counts:

SELECT
    (SELECT COUNT(*) FROM public.target_table) AS production_count,
    (SELECT COUNT(*) FROM restore_stage.target_table) AS staged_count;

Rows only in production:

SELECT p.id
FROM public.target_table AS p
LEFT JOIN restore_stage.target_table AS s
    ON s.id = p.id
WHERE s.id IS NULL
ORDER BY p.id;

Rows only in the dump:

SELECT s.id
FROM restore_stage.target_table AS s
LEFT JOIN public.target_table AS p
    ON p.id = s.id
WHERE p.id IS NULL
ORDER BY s.id;

Changed rows:

SELECT
    p.id,
    p.title AS production_title,
    s.title AS dump_title,
    p.status AS production_status,
    s.status AS dump_status,
    p.updated_at AS production_updated_at,
    s.updated_at AS dump_updated_at
FROM public.target_table AS p
JOIN restore_stage.target_table AS s
    ON s.id = p.id
WHERE ROW(
        p.title,
        p.status,
        p.created_at,
        p.updated_at
      ) IS DISTINCT FROM ROW(
        s.title,
        s.status,
        s.created_at,
        s.updated_at
      )
ORDER BY p.id;

Do not continue until the differences are understood.

---

Scenario A: Exact table replacement

Use this only when:

• The dump should become the exact table contents.
• Deleting rows created after the dump is acceptable.
• Foreign-key dependencies have been reviewed.
• Application writes are stopped.
• A fresh production backup exists.
• The procedure succeeded against a fresh production copy.

Run a rollback test first:

BEGIN;

LOCK TABLE public.target_table IN ACCESS EXCLUSIVE MODE;

SELECT COUNT(*) AS production_before
FROM public.target_table;

DELETE FROM public.target_table;

INSERT INTO public.target_table
(
    id,
    title,
    status,
    created_at,
    updated_at
)
SELECT
    id,
    title,
    status,
    created_at,
    updated_at
FROM restore_stage.target_table;

SELECT COUNT(*) AS production_after
FROM public.target_table;

ROLLBACK;

Review the result. If correct, repeat with COMMIT:

BEGIN;

LOCK TABLE public.target_table IN ACCESS EXCLUSIVE MODE;

DELETE FROM public.target_table;

INSERT INTO public.target_table
(
    id,
    title,
    status,
    created_at,
    updated_at
)
SELECT
    id,
    title,
    status,
    created_at,
    updated_at
FROM restore_stage.target_table;

COMMIT;

If id uses a serial-backed sequence:

SELECT pg_get_serial_sequence(
    'public.target_table',
    'id'
);

Reset it:

SELECT setval(
    pg_get_serial_sequence('public.target_table', 'id'),
    COALESCE((SELECT MAX(id) FROM public.target_table), 1),
    EXISTS (SELECT 1 FROM public.target_table)
);

Do not run this for UUID keys or when pg_get_serial_sequence returns NULL.

DELETE is used instead of TRUNCATE because it is a safer generic example around foreign keys. For a large isolated table, TRUNCATE may be suitable only after dependency review.

---

Scenario B: Safer key-based merge

Use this when production may contain newer or unrelated rows.

This keeps production rows absent from the dump:

BEGIN;

LOCK TABLE public.target_table IN SHARE ROW EXCLUSIVE MODE;

INSERT INTO public.target_table
(
    id,
    title,
    status,
    created_at,
    updated_at
)
SELECT
    id,
    title,
    status,
    created_at,
    updated_at
FROM restore_stage.target_table
ON CONFLICT (id)
DO UPDATE SET
    title = EXCLUDED.title,
    status = EXCLUDED.status,
    created_at = EXCLUDED.created_at,
    updated_at = EXCLUDED.updated_at;

SELECT COUNT(*) AS production_after_merge
FROM public.target_table;

ROLLBACK;

Review it, then replace ROLLBACK with COMMIT.

To avoid overwriting newer production rows:

BEGIN;

INSERT INTO public.target_table
(
    id,
    title,
    status,
    created_at,
    updated_at
)
SELECT
    id,
    title,
    status,
    created_at,
    updated_at
FROM restore_stage.target_table
ON CONFLICT (id)
DO UPDATE SET
    title = EXCLUDED.title,
    status = EXCLUDED.status,
    created_at = EXCLUDED.created_at,
    updated_at = EXCLUDED.updated_at
WHERE public.target_table.updated_at <= EXCLUDED.updated_at;

ROLLBACK;

For an older dump, the condition normally preserves newer production records.

Deleting production rows absent from the dump is optional and destructive:

DELETE FROM public.target_table AS p
WHERE NOT EXISTS
(
    SELECT 1
    FROM restore_stage.target_table AS s
    WHERE s.id = p.id
);

Run that only when production-only rows are confirmed to be unwanted.

Clean up staging

After verification:

DROP TABLE IF EXISTS restore_stage.target_table;

Remove the schema only when empty:

DROP SCHEMA IF EXISTS restore_stage;

Recommended production sequence

1. Restore the historical dump locally.
2. Analyze the target table.
3. Take a fresh production backup.
4. Restore that fresh backup into a second local database.
5. Test the replacement or merge there.
6. Stop production writes to the affected feature.
7. Import the historical table into a staging table.
8. Validate counts, keys, timestamps, and relationships.
9. Run the final transaction with ROLLBACK.
10. Review the result.
11. Run the reviewed transaction with COMMIT.
12. Verify the application.
13. Remove the staging table.

---

11. Common errors

R2 AccessDenied

Verify:

• Token permission is Object Read
• Token is scoped to the correct bucket
• Account ID is correct
• Bucket name is correct
• Correct AWS CLI profile is used

Cannot connect to the R2 endpoint

The endpoint must be:

https://<ACCOUNT_ID>.r2.cloudflarestorage.com

Do not add the bucket name to this AWS CLI endpoint.

Port 5433 is already in use

Check:

Get-NetTCPConnection -LocalPort 5433 -ErrorAction SilentlyContinue

Use another host port:

--publish 127.0.0.1:5434:5432

Then configure DBeaver to use 5434.

Podman container name already exists

podman ps --all
podman start postgres-analysis

Or remove only the container:

podman rm --force postgres-analysis

The named volume remains unless explicitly deleted.

role "..." does not exist

For archive dumps, include:

--no-owner --no-privileges

Plain SQL dumps may contain explicit owner or grant statements. Inspect the script before loading it.

input file appears to be a text format dump

Use psql, not pg_restore.

unsupported version in file header

Use a PostgreSQL image matching or newer than the pg_dump version that created the archive.

DBeaver cannot connect

Check:

podman ps

The port mapping should resemble:

127.0.0.1:5433->5432/tcp

Run:

podman exec postgres-analysis `
  pg_isready `
  --username postgres `
  --dbname obaki_analysis

Exact table replacement fails on foreign keys

Do not disable constraints or use TRUNCATE ... CASCADE without impact analysis.

Use:

• A key-based merge
• A dependency-aware multi-table restore
• A tested application-specific migration

---

12. Start, stop, and remove the environment

Stop:

podman stop postgres-analysis

Start:

podman start postgres-analysis

Logs:

podman logs postgres-analysis

Remove the container but retain the volume:

podman rm --force postgres-analysis

Delete the complete local environment:

podman rm --force postgres-analysis
podman volume rm postgres-analysis-data

This affects only the local Podman environment. It does not affect R2 or production.

---

13. Official references

• Cloudflare R2 — AWS CLI
• Cloudflare R2 — S3-compatible API
• Cloudflare R2 — API tokens
• Podman — podman run
• Podman — podman volume create
• PostgreSQL — pg_restore
• PostgreSQL — pg_dump
• PostgreSQL — psql
• PostgreSQL — COPY
• PostgreSQL — TRUNCATE