How to Switch Databases in psql
Chat2DB TeamIf you are coming from MySQL, the first thing you try in psql is USE otherdb;, and the first thing PostgreSQL does is reject it with a syntax error. PostgreSQL has no USE statement because a server connection is bound to exactly one database for its whole lifetime. To work with a different database you open a new connection, and psql wraps that in a single meta-command: \c (short for \connect). This guide covers every practical way to switch or connect to another database in psql, what happens to your session when you do, how to run one-off commands against other databases from the shell, why cross-database queries need an extension, and how to read the error messages you will inevitably hit along the way.
Why there is no USE database in PostgreSQL
In MySQL a connection belongs to a server, and the "current database" is just a default schema that USE changes. In PostgreSQL the database is part of the connection itself: the backend process that serves you is started for one database, with its own system catalogs, and it cannot be re-pointed. That is why every tool, including psql, switches databases by disconnecting and reconnecting.
postgres=# USE app;
ERROR: syntax error at or near "USE"
LINE 1: USE app;
^The PostgreSQL concept that is closest to MySQL's USE is the schema search path, which is covered later in this article; it changes which schema unqualified table names resolve to, but stays inside the same database.
Switching with the connect meta-command
Basic form
Inside an interactive psql session, type \c followed by the database name. psql closes the current connection and opens a new one to the target database, reusing the same user, host and port.
postgres=# \c app
You are now connected to database "app" as user "postgres".
app=#The prompt changes from postgres=# to app=#, which is the quickest way to confirm which database you are on. \connect app is exactly equivalent; \c is just the abbreviation.
Switching user, host and port at the same time
The full syntax accepts up to four positional arguments: database, user, host and port. A single hyphen means "keep the current value".
app=# \c app reporting
Password for user reporting:
You are now connected to database "app" as user "reporting".
app=> \c analytics - db-replica.internal 5433
You are now connected to database "analytics" as user "reporting" on host "db-replica.internal" at port "5433".Note the prompt suffix: # means the current role is a superuser, > means it is not. That is another visual cue that a \c with a different user took effect.
Connecting with a URI or a key-value string
\c also accepts a full connection URI or a libpq keyword string, which is convenient when you need SSL settings or an application name:
app=# \c postgresql://reporting@db-replica.internal:5433/analytics?sslmode=require
app=# \c "dbname=analytics user=reporting host=db-replica.internal sslmode=require"When you pass a URI or keyword string, psql does not reuse any of the previous connection's parameters unless you add -reuse-previous=on right after \c. Conversely, \c -reuse-previous=off app forces a clean connection using only the positional arguments you gave.
Checking where you are
conninfo
\conninfo prints the current connection details, including whether you are on a Unix socket or a TCP host:
app=# \conninfo
You are connected to database "app" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432".The same information is available through SQL if you need it in a script:
SELECT current_database(), current_user, inet_server_addr(), inet_server_port();Listing databases
\l (or \list) shows every database on the server along with its owner and encoding. Add a + for sizes and descriptions, or a pattern to filter:
app=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+----------+----------+---------+-------+-----------------------
analytics | postgres | UTF8 | C | C |
app | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
app=# \l an*You can also query the catalog directly with SELECT datname FROM pg_database WHERE NOT datistemplate;.
What happens to your session on reconnect
Because \c establishes a new server connection, everything that lived in the old backend is gone:
- Session settings made with
SET(such asSET search_pathorSET timezone) are reset to the defaults for the new database and role. - Temporary tables, prepared statements, cursors and advisory locks disappear.
- An open transaction is rolled back; psql warns you if you run
\cinside a transaction block.
Client-side state is preserved: psql variables set with \set, the query history, and display settings such as \x and \timing all survive the switch.
Re-authentication, passwords and .pgpass
The new connection authenticates from scratch. If the server requires a password for the target database or role, psql prompts for it again. To avoid prompts in interactive or scripted sessions, use one of the standard libpq mechanisms:
cat >> ~/.pgpass <<'PGPASS'
localhost:5432:app:postgres:s3cret
localhost:5432:analytics:reporting:r3port
PGPASS
chmod 0600 ~/.pgpassThe .pgpass format is hostname:port:database:username:password, and * is a wildcard for any field. Alternatively, export PGPASSWORD for the current shell, keeping in mind that environment variables are visible to other processes on the machine and appear in shell history if set inline:
export PGPASSWORD='s3cret'
psql -h localhost -U postgres -d appUsing the connect command in scripts
With a file executed by -f or i
\c works inside SQL script files, which is useful for setup scripts that touch several databases:
-- setup.sql
\c postgres
CREATE DATABASE app;
CREATE DATABASE analytics;
\c app
CREATE TABLE users (id serial PRIMARY KEY, email text NOT NULL);
\c analytics
CREATE TABLE events (id bigserial PRIMARY KEY, occurred_at timestamptz NOT NULL);Run it from the shell, or include it from an existing session:
psql -h localhost -U postgres -f setup.sqlpostgres=# \i setup.sqlThe file must use meta-commands on their own lines; you cannot embed \c in the middle of a multi-statement SQL string, and it is not available to drivers such as psycopg or JDBC, which speak the wire protocol directly and never see psql meta-commands.
Behavior on failure
What \c does when the new connection fails depends on how psql is running:
- Interactively, psql keeps the previous connection open and tells you so, so a typo does not throw you out of your session.
- Non-interactively (reading from a file or a pipe), a failed
\cis fatal: psql prints the error and exits, because continuing with the old connection would run the remaining statements against the wrong database.
app=# \c nosuchdb
connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: database "nosuchdb" does not exist
Previous connection kept
app=#For scripts, pair this with -v ON_ERROR_STOP=1 so that SQL errors also stop execution rather than letting later statements run in a half-applied state.
Running commands against another database from the shell
You do not always need an interactive switch. The -d flag (or the first positional argument) selects the database, and -c runs a single command:
psql -h localhost -U postgres -d analytics -c "SELECT count(*) FROM events;"
psql -h localhost -U postgres -d app -c "VACUUM ANALYZE users;" -c "SELECT now();"Multiple -c flags run in order on the same connection. To loop over every database on a server, combine -At (unaligned, tuples only) output with a shell loop:
for db in $(psql -h localhost -U postgres -At -c "SELECT datname FROM pg_database WHERE NOT datistemplate;"); do
echo "== $db =="
psql -h localhost -U postgres -d "$db" -At -c "SELECT pg_size_pretty(pg_database_size(current_database()));"
doneConnecting to a specific database on startup
All of the following open psql directly on the app database; pick whichever matches your habits or your tooling:
psql -U postgres -h localhost -p 5432 -d app
psql -U postgres -h localhost app
psql postgresql://postgres@localhost:5432/app
psql "host=localhost port=5432 dbname=app user=postgres sslmode=prefer"If you omit -d, psql connects to a database named after your OS user, which is the source of the classic database "alice" does not exist error on fresh installs. Setting PGDATABASE=app in your shell profile changes that default.
Cross-database queries: dblink and postgres_fdw
Switching connections does not let you join a table in app with a table in analytics in one statement. PostgreSQL simply does not support cross-database references; SELECT * FROM analytics.public.events fails with cross-database references are not implemented. You have two extension-based options.
dblink for ad-hoc queries
CREATE EXTENSION IF NOT EXISTS dblink;
SELECT u.email, e.event_count
FROM users u
JOIN dblink('dbname=analytics user=postgres host=localhost',
'SELECT user_id, count(*) FROM events GROUP BY user_id')
AS e(user_id int, event_count bigint)
ON e.user_id = u.id;dblink sends the query string to the other database and returns the result as a row set; you must declare the column list and types yourself.
postgres_fdw for permanent foreign tables
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER analytics_srv FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'analytics', port '5432');
CREATE USER MAPPING FOR postgres SERVER analytics_srv
OPTIONS (user 'postgres', password 's3cret');
IMPORT FOREIGN SCHEMA public LIMIT TO (events)
FROM SERVER analytics_srv INTO public;
SELECT count(*) FROM events; -- now a foreign table in the app databaseOnce imported, the foreign table behaves like a local one in queries, and the planner can push down filters and joins to the remote side.
Switching schema is not switching database
Developers sometimes reach for \c when they actually want a different schema in the same database. Schemas are namespaces inside a database, and unqualified table names are resolved through search_path:
SHOW search_path;
-- "$user", public
SET search_path TO reporting, public;
SELECT * FROM monthly_totals; -- resolves to reporting.monthly_totalsSET lasts for the session (and is lost on \c, as noted earlier). To make it permanent for a role or a database, use ALTER ROLE reporting SET search_path = reporting, public; or ALTER DATABASE app SET search_path = ...;. Use \dn to list schemas and \dt reporting.* to list the tables in one.
Switching databases inside Docker
When PostgreSQL runs in a container, the same rules apply; you just need to reach psql inside the container first:
docker exec -it pg psql -U postgres -d app
docker exec -it pg psql -U postgres -d analytics -c "SELECT current_database();"Inside the session, \c analytics works exactly as on a native install. If you have the client installed on the host and the container publishes port 5432, you can also skip docker exec and connect with psql -h localhost -p 5432 -U postgres -d app.
Switching databases in a GUI client
In a graphical client the reconnect is hidden behind a dropdown. Chat2DB (opens in a new tab), for example, lists every database on a PostgreSQL connection in the sidebar and lets you pick the active database for the SQL console without re-entering credentials; the client manages the separate connection per database for you. That is handy when you spend the day hopping between an application database and an analytics database and do not want to retype \c or maintain a long .pgpass.
Common errors
database "X" does not exist
FATAL: database "myapp" does not existThe name is wrong, the database lives on a different server, or you forgot -d and psql defaulted to your OS username. Run \l (or psql -l from the shell) to see the exact names; database names are case-sensitive when quoted, so MyApp and myapp are different databases.
password authentication failed
FATAL: password authentication failed for user "reporting"The credentials for the new role are wrong or missing. Check the matching line in ~/.pgpass (host, port and database must all match, and the file must be mode 0600 or libpq ignores it), verify the role exists with \du, and confirm that pg_hba.conf allows password authentication for that host and database. If you switched users with \c dbname user, remember that the password prompt is for the new user, not the one you started as.
connection to server failed
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?The host or port in the \c arguments is wrong, the server is down, or it is listening only on a Unix socket. Check listen_addresses in postgresql.conf, confirm the port with pg_lsclusters or docker ps, and try omitting the host to use the local socket.
FAQ
How do I change database in psql without leaving the session?
Use \c dbname or \connect dbname. psql opens a new connection to that database and closes the old one; your psql variables and history are kept, but server-side session state is reset.
Can I connect to another database as a different user in one command?
Yes: \c dbname username switches both at once. Add host and port as the third and fourth arguments, or pass a full URI such as \c postgresql://user@host:5432/dbname.
How do I see which database I am currently connected to?
Look at the prompt (it shows the database name), run \conninfo, or execute SELECT current_database();.
Can I query two databases in one SQL statement?
Not natively. Install dblink for one-off remote queries or postgres_fdw to mount tables from the other database as foreign tables and join them like local ones.
What is the difference between switching database and setting search_path?
\c opens a new connection to a different database. SET search_path stays inside the current database and only changes which schema unqualified names resolve to.