The Connection Failure
You are trying to connect to your PostgreSQL instance, perhaps during a deployment or while setting up a local development environment, and the connection is immediately rejected with this message:
FATAL: database "myapp_production" does not exist
This error is frustrating because it sounds like a permissions issue or a server configuration problem, but in reality, PostgreSQL is simply telling you that it cannot find a database with the exact name you provided in its internal catalog. Whether you are using psql, a Node.js node-postgres client, or a Rails database.yml, the root cause is almost always a mismatch between expectations and reality.
Common Scenarios
- The database was never created: You've installed PostgreSQL but haven't run the
CREATE DATABASEcommand yet. - Typoes in Configuration: A small spelling error in your
.envfile or application config. - Case Sensitivity: You created a database with uppercase letters (e.g., "MyApp") but are searching for "myapp".
- Default User Behavior: Running
psqlwithout arguments defaults to a database name matching your system username. - Docker Initialization: The container started, but the initialization scripts failed or haven't run yet.
Step 1: Verify Existing Databases
Before assuming the database exists, check the server's actual list of databases. Connect to the default postgres database to see what is currently available:
psql -U postgres -d postgres -l
The -l flag lists all databases. If you are already inside a psql session, use the meta-command:
\l
Look closely at the output. If "myapp_production" is missing from the list, you have confirmed that the database does not exist on this specific PostgreSQL instance.
Step 2: Create the Missing Database
If the database is missing, you need to create it. You can do this via the command line or an SQL prompt.
Using the Command Line (Shell)
The createdb utility is a wrapper around the SQL command:
createdb -U postgres myapp_production
Using SQL
Connect to the postgres database and run:
CREATE DATABASE myapp_production;
Warning on Case Sensitivity: If you use quotes like CREATE DATABASE "MyApp";, PostgreSQL will preserve the case. If you connect later using -d myapp, it will fail. Always prefer lowercase names without quotes for simplicity.
Step 3: Debugging Connection Strings and Environment Variables
If the database does exist in the list but your application still throws the error, check your connection string structure. A typical URI looks like this:
postgresql://user:password@localhost:5432/myapp_production
Ensure there are no trailing spaces or hidden characters in your .env file. For example, in Bash or Docker environments, a trailing space in an environment variable can be interpreted as part of the string:
# Incorrect
DB_NAME=myapp_production
# Correct
DB_NAME=myapp_production
Step 4: Handling the "Default User" Trap
One of the most common ways this error appears is when running psql by itself. By default, PostgreSQL attempts to connect to a database named after your current OS username. If your user is ubuntu, running psql is equivalent to:
psql -d ubuntu
If you haven't created a database named ubuntu, you will get FATAL: database "ubuntu" does not exist. To avoid this, always specify the database name explicitly:
psql -d postgres
Step 5: Docker-Specific Fixes
If you are using the official PostgreSQL Docker image, ensure your POSTGRES_DB environment variable matches what your application is looking for. If you change this variable after the container has already been initialized (i.e., after the /var/lib/postgresql/data directory is created), it will have no effect.
To fix this in Docker, you must either:
- Wipe the volume and restart (Note: This deletes all data):
docker-compose down -v - Connect to the running container and create the database manually:
docker exec -it my_postgres_container psql -U postgres -c "CREATE DATABASE myapp_production;"
Verification
Once you believe the fix is applied, verify it using a direct connection test. Run this command from the machine where the application is hosted:
psql -h localhost -U your_user -d myapp_production -c "SELECT 1;"
If you see a table with a single 1, the connection is successful. If you still see the FATAL error, double-check that you are connecting to the correct host and port (e.g., you might be connecting to a local Postgres instance instead of a remote RDS or Docker instance).
Lessons Learned
Database names in PostgreSQL are strictly checked. To prevent this error in the future:
- Include a database creation step in your CI/CD pipeline or setup scripts.
- Always use lowercase for database names to avoid case-sensitivity headaches.
- Explicitly define the
PGDATABASEenvironment variable in your shell if you usepsqlfrequently. - Check your application's logs for the specific connection string it is using at runtime to catch dynamic configuration errors.