Verify your database and role after reconnecting

We will check where our SQL runs after a successful reconnect and after a failed one. Use the notes_app database created earlier in this course and an interactive psql session connected to postgres.

The PostgreSQL database-switching reference explains \connect and its \c shortcut, with a screenshot of the command.

Record the starting connection

\conninfo
SELECT current_database(), current_user;
CREATE TEMP TABLE connection_probe (message text);
INSERT INTO connection_probe VALUES ('original session');
SET application_name = 'connection_probe';

The query should report postgres and the role you used to connect. The temporary table and setting belong to this session.

Reconnect and check the session

\connect notes_app
\conninfo
SELECT current_database(), current_user;
SHOW application_name;
SELECT * FROM connection_probe;

Check that the database is now notes_app. The temporary table should be missing, and the custom application_name setting should be gone. A successful switch creates a new connection, so session settings, temporary tables and open transactions do not move with you. Finish any transaction before reconnecting.

If you set a custom search_path, set it again in the new connection.

Deliberately fail a switch

Use a database name you know does not exist:

\connect notes_app_missing
\conninfo
SELECT current_database(), current_user;

Interactive psql reports a failed connection and Previous connection kept. Verify that you are still in notes_app before running another statement. In a non-interactive script, a failed connection is handled differently: the old connection is closed. Do not rely on this interactive recovery when writing scripts.

Check an application role

Once you create the notes_app role in the permissions module, repeat the check with:

\connect notes_app notes_app
\conninfo
SELECT current_database(), current_user;

The first argument names the database; the second names the role. If the role lacks CONNECT, PostgreSQL refuses the connection. Read the error, then verify your current connection before continuing.

You have finished when you can show the database and role after each attempt, and explain why the temporary table disappeared only after the successful reconnect.