How to connect & manage a PostgreSQL database

The psql command-line tool allows you to interact with a PostgreSQL database from the terminal. With psql you can run queries, manage databases and automate tasks with scripts.

Connect to PostgreSQL with psql

1. Open a terminal and log into your server via SSH if needed.
2. Enter the following, replacing dbname with your database name and username with your PostgreSQL username:

   psql dbname username

3. Enter the password when prompted. If the credentials are correct you will see the psql prompt.

Check Your Connection

Once in psql, check your connection to make sure you are in the right database by running:

\conninfo

If you need to switch to a different database before running queries, use:

\c database_name

Run SQL Commands

Once in the correct database, you can start querying data. To see what tables exist before running a query, use:

\dt

To see the structure of a table before retrieving its data, use:

\d table_name

Then you can retrieve all rows from a table with:

SELECT * FROM table_name;

For help with SQL commands in psql type:

\h

Quit psql

When you are done in psql type:

\q