How to psql Specify Database

To specify a database when using the psql command-line utility, you most commonly use the -d or --dbname flag followed by the name of the database, for example: psql -d target_database. Alternatively, you can use a connection URI or the \c meta-command once the session has already started.

The PostgreSQL interactive terminal, known as psql, is the primary interface for managing and querying PostgreSQL databases. As of 2026, despite the proliferation of graphical user interfaces and web-based management tools, psql remains the gold standard for database administrators and developers due to its speed, scriptability, and presence in almost every environment, from local development containers to massive cloud-native clusters. Knowing exactly how to direct your connection to the correct database is the foundational skill required to avoid accidental data manipulation in the wrong environment.

Understanding the Role of the Database Name in psql

When you execute the psql command, the utility attempts to establish a connection to a specific database within a PostgreSQL instance. A single PostgreSQL instance, or "cluster," can host multiple independent databases. If you do not explicitly specify which one you want to access, psql follows a specific set of fallback rules. By default, it looks for a database with the same name as your current operating system username. If that database does not exist, the connection will fail unless you provide an alternative.

Specifying the database is not just about choosing where your queries run; it is also about context. In PostgreSQL, permissions, extensions, and schemas are often bound to the specific database level. Therefore, connecting to the correct database ensures that your search paths are correct and that you have the necessary privileges to execute your intended tasks.

Method 1: Using Command Line Flags

The most explicit way to specify a database is through the use of command-line flags. This method is preferred for scripts and one-off terminal commands because it is clear and unambiguous.

The -d and --dbname Flags

The -d flag is the short-form version of --dbname. When you use this flag, you are telling the libpq library (the underlying connection library for PostgreSQL) exactly which database name to pass to the server during the handshake phase.

Example: psql -d accounting_db

In this example, psql will attempt to connect to the database named accounting_db. If you need to specify other connection parameters, such as the user or the host, you can combine these flags:

Example: psql -h localhost -U admin_user -d sales_records

Using Positional Arguments

PostgreSQL allows for a shorthand where the database name is provided as the final argument without a specific flag. If you type psql my_db, the utility interprets the string my_db as the database name, provided it does not conflict with other flags. However, using the -d flag is considered better practice in 2026 for readability and to avoid shell expansion issues, especially when working with complex connection strings.

Method 2: Connection URIs and Strings

A more modern approach, which has become the standard in cloud environments and application configuration files, is the use of a Connection URI. This format allows you to specify the protocol, username, password, host, port, and database name all in a single string.

The URI Format

The syntax for a PostgreSQL URI is as follows:

postgresql://[user[:password]@]host[:port][/dbname][?param1=value1&...]

To specify the database using this method, you include the database name after the final forward slash. For example, to connect to a database named analytics on a remote server:

psql postgresql://db_user:secure_password@db.example.com:5432/analytics

The primary advantage of the URI method is its portability. You can copy the same string used in a Python or Node.js application and paste it directly into your terminal. This reduces the risk of connection errors caused by mismatched parameters between different tools. Furthermore, URIs allow you to specify additional parameters, such as SSL modes, directly in the string:

psql "postgresql://user@localhost/mydb?sslmode=require"

Note that when using URIs containing special characters like question marks or ampersands, you should wrap the entire string in double quotes to prevent the shell from interpreting them.

Method 3: Switching Databases Within an Active Session

Often, you may find yourself already logged into a PostgreSQL session and realize you need to work on a different database. Rather than exiting and restarting psql, you can use internal meta-commands.

The \c and \connect Meta-Commands

The \c command (short for \connect) allows you to switch your current connection to a new database. The syntax is:

\c database_name [ username ] [ host ] [ port ]

If you are currently connected to postgres and want to move to production_data, you simply type:

\c production_data

Once executed, psql closes the previous connection and opens a new one. It will provide a confirmation message such as: "You are now connected to database 'production_data' as user 'your_user'."

This command is also useful for switching users without leaving the shell. For example, if you need to perform an administrative task that requires superuser privileges, you could use:

\c production_data postgres

This will prompt you for the postgres user's password and then reconnect you to the same database under the new identity.

Method 4: Utilizing Environment Variables

For developers who work on a single project for extended periods, repeatedly typing the database name can be tedious. PostgreSQL supports several environment variables that psql will check if flags are not provided.

The PGDATABASE Variable

If you set the PGDATABASE environment variable in your shell, psql will use its value as the default database.

To set this in a Linux or macOS terminal (Bash/Zsh): export PGDATABASE=project_alpha_db

To set this in Windows (PowerShell): $Env:PGDATABASE = "project_alpha_db"

Once this variable is set, simply typing psql will automatically attempt to connect to project_alpha_db. This is highly effective for local development environments where you are frequently opening and closing terminal windows. Other relevant variables include PGUSER, PGHOST, and PGPORT.

Comparison of Methods for Specifying a Database

The following table compares the different ways to specify a database in psql, highlighting their primary use cases and advantages.

Practical Prerequisites and Requirements

Before you can successfully specify and connect to a database, several technical conditions must be met. Even with the correct syntax, a connection will fail if the underlying infrastructure is not configured properly.

1. Database Existence

It may seem obvious, but the database must already exist on the server. Unlike some NoSQL databases that create a database upon the first write, PostgreSQL requires an explicit CREATE DATABASE command. You can check the list of available databases on a server you are already connected to by using the \l (list) command.

2. User Permissions and pg_hba.conf

The PostgreSQL server uses a configuration file called pg_hba.conf (Host-Based Authentication) to control which users can connect to which databases from which IP addresses. If your psql command specifies a database that your user is not authorized to access in pg_hba.conf, the server will reject the connection with a "no pg_hba.conf entry" error.

3. Network Connectivity

If the database is hosted remotely, you must ensure that the host name or IP address is reachable and that the PostgreSQL port (defaulting to 5432) is open on the server’s firewall and any intermediate network security groups (such as AWS Security Groups or Azure NSGs).

Troubleshooting Common Connection Errors

When specifying a database, you may encounter specific errors. Understanding these helps in resolving issues quickly.

• FATAL: database "xyz" does not exist: This occurs when there is a typo in your -d flag or the database has been dropped. Double-check the spelling and case sensitivity.
• FATAL: role "user" does not have permission to access database "xyz": This indicates that while the database exists, your specific PostgreSQL user (role) has not been granted CONNECT privileges. An administrator must run GRANT CONNECT ON DATABASE xyz TO user;.
• psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: This usually means the PostgreSQL server is not running locally or you are trying to connect to the wrong host.

Advanced Usage: Using Service Files

As of 2026, many organizations use pg_service.conf files to manage complex connection details. Instead of remembering the database name, host, and port for a dozen different environments, you can define a "service."

In your ~/.pg_service.conf file, you might have:

[prod_reporting] host=prod.example.com port=5432 dbname=reporting_v3 user=analyst

You can then specify the database and all its associated parameters using a single service flag:

psql service=prod_reporting

This method is superior for managing database specifications in enterprise environments where database names might change during migrations or version upgrades, as it allows you to update the configuration file without changing the scripts that call psql.

Security Considerations

When specifying a database, particularly in shared environments or CI/CD pipelines, security must be a priority.

1. Avoid Passwords in Command Lines: Avoid including passwords in URIs within your bash history. Instead, use a .pgpass file or the PGPASSWORD environment variable (though .pgpass is more secure).
2. Use SSL: When connecting to a database, especially over the public internet, always specify sslmode=verify-full or verify-ca in your connection string to ensure you are connecting to the legitimate server and that your data is encrypted.
3. Audit Logs: Remember that the database name you specify is logged by the PostgreSQL server. Avoid putting sensitive information in database names themselves.

Frequently Asked Questions

How do I list all available databases so I know what to specify? If you are already connected to a PostgreSQL server, you can type \l or \list in the psql prompt. This will show you a table containing the name, owner, encoding, and access privileges for every database in the cluster. If you are not yet connected, you can connect to the default postgres database first using psql -d postgres and then run the list command.

Can I specify a database and run a single query without staying in the psql shell? Yes, you can combine the database specification with the -c flag. For example: psql -d my_db -c "SELECT * FROM users LIMIT 10;". This will connect to my_db, execute the query, print the results to your terminal, and immediately disconnect. This is highly useful for cron jobs and automated reporting.

What is the default database if I don't use -d? By default, psql will look for a database with the same name as your current OS username. If that is not found, many systems are configured to fallback to the postgres database, which is the default administrative database created when PostgreSQL is installed.

How do I specify a database name that contains spaces or capital letters? If a database name has capital letters or special characters, it must be quoted in SQL commands, but in the psql command line, you usually just need to wrap it in double quotes to prevent the shell from splitting the argument. For example: psql -d "My Custom Database". Inside the shell, when using \c, you would use \c "My Custom Database".

Is there a way to specify the database using a configuration file instead of a flag? Yes, you can use the PGDATABASE environment variable in your shell configuration (like .bashrc or .zshrc), or you can use a pg_service.conf file. These methods allow you to define the database name once and have it applied automatically whenever you run psql.

Summary and Practical Recommendation

The most reliable and standard way to specify a database in psql remains the -d flag. For modern workflows, adopting Connection URIs provides the best balance of flexibility and compatibility across different tools. If you are managing multiple environments, it is highly recommended to set up a pg_service.conf file to abstract the database names and connection details away from the command line, reducing the risk of errors and improving security.

When working in 2026's fast-paced DevOps environments, always verify your connection by checking the prompt in psql, which usually displays the current database name. This simple habit can prevent costly mistakes on production systems. For further reading, the official PostgreSQL documentation on libpq connection strings remains the definitive source for all supported parameters and syntax variations.