psql switch database

Guri Bee

2 min read

·

21-10-2024

1

0

Share

Switching Databases in psql: A Comprehensive Guide

The psql command-line interface is a powerful tool for interacting with PostgreSQL databases. One common task is switching between different databases within a single psql session. This article will guide you through the process, explaining the different methods and their nuances.

Understanding the \c command:

The primary command for switching databases in psql is \c, short for "connect". Let's delve into the common use cases:

1. Connecting to a Different Database:

• Syntax: \c [database_name]
• Example: To connect to the database named "my_new_db", you would use: \c my_new_db
• Explanation: This command instructs psql to disconnect from the current database (if any) and connect to the specified database_name.

2. Connecting with a User and Host (Optional):

• Syntax: \c [database_name] [user] [host]
• Example: To connect to "my_new_db" as user "my_user" on host "my_host", use: \c my_new_db my_user my_host
• Explanation: This provides flexibility to connect with different user accounts and specify a specific host if needed.

3. Connecting with a Port:

• Syntax: \c [database_name] [user] [host] [port]
• Example: To connect to "my_new_db" on port 5433: \c my_new_db my_user my_host 5433
• Explanation: This allows connecting to a PostgreSQL server listening on a specific port, often useful for managing multiple instances.

Additional Tips and Considerations:

• Confirmation Prompt: When you use \c to connect to a different database, psql will confirm the action. Press "y" to proceed or "n" to cancel.
• Database List: If unsure of the database name, you can use the \l (list) command to display a list of available databases.
• Current Database: To check the currently connected database, use the command \d (describe) or \dt (describe tables).

Example Scenario:

Let's imagine you are working on a PostgreSQL database named "my_project". You want to switch to a temporary database "test_data" for some testing and then return to "my_project".

psql -h localhost -d my_project -U postgres
# Now connected to "my_project"

\c test_data   # Switch to "test_data"

-- Perform testing in the "test_data" database

\c my_project  # Switch back to "my_project"

Practical Applications:

• Database Management: Switching between databases is essential for managing multiple projects or environments.
• Testing and Development: Use separate databases for testing new features without impacting production data.
• Data Migration: Connect to different databases to move data between them.

Conclusion:

The \c command in psql offers a simple and powerful way to switch databases. By understanding the different options and their implications, you can efficiently manage your PostgreSQL database environment.

Attribution:

This article incorporates information and examples from the following GitHub repository:

• https://github.com/postgres/postgres - PostgreSQL source code and documentation.

Note: Always double-check the information against official PostgreSQL documentation for the most accurate and up-to-date guidelines.