Postgres switch database with the psql meta-command \c (or \connect) inside a session. At connection time, psql -d dbname sets the initial database. PGUSER and PGDATABASE environment variables set defaults. The team that uses these commands navigates between databases in the same psql session.
Table of contents
- The \c meta-command
- Connecting to a specific user
- Connecting at startup
- Connection info at any time
- Environment variables
- Why Postgres is different from MySQL
- FAQ
The \c meta-command
Inside psql:
postgres=# \c mydb
You are now connected to database "mydb" as user "you".
mydb=#The prompt changes to reflect the current database. The team that uses \c navigates within one psql session.
Connecting to a specific user
postgres=# \c mydb otheruserConnects as otheruser. The team that needs to switch users uses this.
Connecting at startup
psql -d mydb -U app_user -h db.example.comConnects directly to mydb as app_user. The team that uses this in scripts skips the \c step.
Connection info at any time
mydb=# \conninfoShows current connection details (database, user, host, port). The team that uses \conninfo confirms the connection state.
Environment variables
Postgres clients read:
PGDATABASE: default databasePGUSER: default userPGHOST: default hostPGPORT: default port
Set in shell or .pgpass:
export PGDATABASE=mydb
export PGUSER=app_user
export PGHOST=db.example.com
psqlThe team that uses env vars has one place to configure defaults.
Why Postgres is different from MySQL
MySQL has USE mydb; as SQL. Postgres does not - connections are tied to one database. The team that switches from MySQL to Postgres learns to use \c instead of USE.
FAQ
Can I switch databases with SQL in Postgres?
No - Postgres connections are tied to one database. Use psql \c or reconnect.
What’s the shortcut for \connect?
\c is the short form. The team that uses \c types less.
How do I list databases to choose one?
\l (lowercase L) lists databases. The team that uses \l finds the right one to connect to.
Can I switch users in the middle of a session?
Yes with \c dbname username. The team that needs different permissions per query uses this.
Where are the env vars documented?
Postgres docs on environment variables: https://www.postgresql.org/docs/current/libpq-envvars.html
If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.
Useful related references: