PostgreSQL 3D000 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 3D000: Invalid Catalog Name

PostgreSQL error 3D000 invalid_catalog_name occurs when a client attempts to connect to a database that does not exist on the target PostgreSQL server. In PostgreSQL terminology, a "catalog" refers to the database itself, and this error is thrown immediately at the connection stage before any query is executed. It is one of the most common connection-related errors and is almost always caused by misconfiguration rather than a server-side issue.

Top 3 Causes and Fixes

1. Connecting to a Non-Existent Database

The most common cause is simply specifying a database name that doesn't exist — due to a typo, wrong case, or using the wrong environment's config.

Diagnose it:

-- List all databases on the server
SELECT datname
FROM pg_database
WHERE datistemplate = false
ORDER BY datname;

Fix it:

-- Create the missing database
CREATE DATABASE myapp_prod
    WITH OWNER = myapp_user
    ENCODING = 'UTF8'
    TEMPLATE = template0;

-- Verify
SELECT datname FROM pg_database WHERE datname = 'myapp_prod';

2. Database Was Dropped or Never Created

In CI/CD pipelines or new environment setups, the database creation step is sometimes skipped. A dropped database in production is especially dangerous and requires immediate restore from backup.

-- After restoring from backup, verify the database exists
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datname = 'myapp_prod';

-- If no backup is available, recreate and reinitialize
CREATE DATABASE myapp_prod OWNER myapp_user;

\c myapp_prod

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

3. Wrong Environment Variable or Config File

In containerized environments (Docker, Kubernetes), an empty or placeholder PGDATABASE value is a frequent culprit.

-- After connecting, confirm you're on the right database
SELECT current_database(), current_user;

-- Create a reusable helper to validate DB existence
-- (run from the postgres maintenance database)
CREATE OR REPLACE FUNCTION check_database_exists(db_name TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM pg_database WHERE datname = db_name
    );
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT check_database_exists('myapp_prod');
-- Returns: true or false

Quick Fix Checklist

-- Step 1: Verify the target database exists
SELECT datname FROM pg_database WHERE datistemplate = false;

-- Step 2: Check your connection string format
-- Correct:   postgresql://user:pass@localhost:5432/myapp_prod
-- Incorrect: postgresql://user:pass@localhost:5432/   <- missing DB name

-- Step 3: Confirm current session context
SELECT current_database(), current_user, inet_server_addr(), inet_server_port();

Prevention Tips

Use idempotent database initialization scripts in your deployment pipeline to safely create the database only if it doesn't already exist:

-- Shell-friendly one-liner for CI/CD
-- psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname='myapp_prod'" \
--   | grep -q 1 || createdb -U postgres myapp_prod

Enable connection logging in postgresql.conf to catch 3D000 errors early and feed them into your monitoring stack:

-- Recommended postgresql.conf settings
-- log_connections = on
-- log_disconnections = on
-- log_min_error_statement = error

-- Monitor active connections in real time
SELECT client_addr, usename, datname, state, application_name
FROM pg_stat_activity
ORDER BY backend_start DESC;

Related Errors

Error Code	Name	Description
`28000`	invalid_authorization_specification	Wrong username at connection time
`28P01`	invalid_password	Correct DB name but wrong password
`08006`	connection_failure	Network or server unreachable
`42P04`	duplicate_database	DB already exists on `CREATE DATABASE`

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.