How to connect & manage a PostgreSQL database

You can use the psql utility to interact with your PostgreSQL database directly from the terminal. With psql similar to the mysql you can run queries, manage databases and automate tasks with scripts over the command-line.

Connect to PostgreSQL with psql

1. Connect to your VPS with SSH. If you haven’t already add your SSH key to your VPS.
2. Use the following command to login to PostgreSQL replacing the dbname and user as appropriate.

   sudo -u postgres psql template1

3. Moving on if you’re using password authenticated users, enter your password when prompted if you have connected successfully it will look similar to the example below.

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

To exit PostgreSQL you will need to enter the command shown below.

\q