How to connect to PostgreSQL with psql

The psql utility lets you interact with PostgreSQL databases directly from the terminal. You can run queries, manage database objects and execute administrative commands without needing a graphical interface.

You will connect to PostgreSQL using SSH, authenticate with your database credentials and run SQL commands through the psql prompt. This method works for both local and remote database connections.

Before you begin

You need SSH access to your VPS. Add your SSH key if you have not already done so.
PostgreSQL must be installed and running on your server.
You need your database name and username credentials.

Connect to PostgreSQL with psql

You will establish an SSH connection to your server and then use psql to access your PostgreSQL database. The connection method varies depending on whether you are using the default postgres superuser or a specific database user.

Connect to your VPS with SSH.
Open your terminal and establish an SSH connection to your server using your credentials.
Connect to your database.
Run the following command, replacing dbname with your database name and user with your PostgreSQL username:
sudo -u postgres psql dbname
This connects you to the specified database as the postgres system user.
Enter your password if prompted.
If your PostgreSQL user requires password authentication, type your password when requested. The prompt changes to show your database name once connected successfully.

Terminal window showing successful PostgreSQL connection with psql prompt displaying database name — A successful psql connection displays the database prompt.

If you need to access PostgreSQL as the default postgres superuser without specifying a database, switch to the postgres user first and then run psql.

Verify your connection

You should confirm which database you are connected to before running queries. This prevents accidental changes to the wrong database.

Check your connection details.
Run the following command at the psql prompt:
\conninfo
This displays your current database name, username, connection method and server details.

Terminal output showing PostgreSQL connection information including database name, user and connection socket — The \conninfo command displays your active connection details.

Switch databases if needed.
If you need to work with a different database, run:
\c database_name
Replace database_name with the target database. The prompt updates to reflect your new connection.

Run SQL commands

Once connected, you can execute SQL queries and use psql meta-commands to inspect your database structure. Meta-commands start with a backslash and do not require a semicolon.

View all tables in your current database:

\dt

Examine the structure of a specific table:

\d table_name

Retrieve all rows from a table:

SELECT * FROM table_name;

Get help with SQL syntax:

\h

View available psql commands:

\?

Exit psql and return to your shell:

\q

Wrapping up

You can now connect to PostgreSQL databases using psql and execute SQL commands from the terminal. You established an SSH connection, authenticated with your database credentials and verified your connection details.

If you need to import data into your PostgreSQL database, you can import database files via command line using similar principles. For users working with multiple database systems, you can also connect to MySQL with SSH using comparable connection methods. Our VPS hosting includes full root access for database management.