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 dbname

If you need to access PostgreSQL as the default postgres superuser, you may need to switch users in Linux first:

sudo -u postgres psql

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

View available tables

Before running queries, check which tables exist in your database:

\dt

Examine table structure

View the structure of a specific table:

\d table_name

Query data

Retrieve all rows from a table:

SELECT * FROM table_name;

Get help

For help with SQL commands:

\h

For help with psql-specific commands:

\?

Managing database files

If you need to import data into your PostgreSQL database, you can import database files via command line using similar principles to MySQL imports. For users familiar with MySQL, you can also connect to MySQL with SSH using comparable connection methods.

Exit psql

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

\q