Connect with psql

You've installed PostgreSQL, maybe even created a database, but now comes the moment of truth: actually talking to the server. Without a reliable way to connect and run queries, all that setup is just dead weight. This lesson cuts through the confusion and shows you exactly how to connect with psql and run first queries — the command-line tool that is the Swiss Army knife for every PostgreSQL developer. By the end, you'll be querying like a pro, and you'll have the confidence to tackle more advanced topics.

The problem this lesson solves

Why is connecting with psql so important? Because psql is the direct, no-nonsense interface to your PostgreSQL database. While GUIs like pgAdmin or DataGrip are pretty, they can hide what's really happening. psql gives you raw access, making it perfect for debugging, scripting, and quick checks. Without it, you're stuck either fumbling with GUI clicks or writing whole applications just to run a simple SELECT.

The pain is real: you might be following a tutorial that says "type this command," but when you try it, you get psql: error: connection to server on socket ... failed. Or you might not even know how to launch psql in the first place. This lesson is your rescue. We'll cover everything from the simplest connection to troubleshooting common pitfalls, so you can get past the gate and start querying.

Core concept / mental model

Think of psql as a phone line to your database. The server (PostgreSQL) listens on a specific port (default 5432), and psql is the phone you pick up to make a call. When you dial, you need the right number (host and port), a valid caller ID (username and password), and a destination (database name).

Before you dial, you need to know a few terms:

• Database: A collection of tables and data. You can have many databases on one server.
• User (or role): A person or application that can connect. Each has permissions.
• Host: The machine where PostgreSQL runs. Could be localhost (your computer) or a remote server.
• Port: The network door to the server. Default is 5432.

A typical connection command looks like this:

psql -h localhost -p 5432 -U myuser mydb

Here, -h is host, -p is port (optional if it's 5432), -U is user, and the last argument is the database name. If you omit options, psql uses defaults from environment variables or configuration files, which can lead to confusion — but we'll clear that up.

How it works step by step

1. Check if PostgreSQL is running

You can't connect if the server isn't up. On a typical Linux setup, you'd run:

sudo systemctl status postgresql

Or on macOS, if you installed via Homebrew:

brew services list

Look for postgresql to be started or running. If not, start it with sudo systemctl start postgresql (Linux) or brew services start postgresql (macOS).

2. Find your connection defaults

psql uses several environment variables if you don't give explicit options:

• PGHOST — server host
• PGPORT — port (default 5432)
• PGUSER — user name
• PGDATABASE — database name

If these are not set, it falls back to your operating system username and tries to connect to a database with the same name. That's often why beginners get database \"yourname\" does not exist.

3. Make your first connection

Assuming PostgreSQL is running, open a terminal and try:

psql -U postgres

If the default user is postgres, you'll be prompted for a password. If you're on a fresh install, the password might be set during installation. If you can't remember it, you can reset it (we'll cover that in troubleshooting).

If successful, you'll see a prompt like:

postgres=#

That # means you're connected as a superuser. If you're a regular user, the prompt ends with >.

4. Run your first query

Type your SQL and end with a semicolon, then press Enter:

SELECT 'Hello, PostgreSQL!' AS greeting;

You'll see:

      greeting      
--------------------
 Hello, PostgreSQL!
(1 row)

You just ran your first query! The semicolon tells psql your command is complete. Press Enter without a semicolon will just wait for more input.

5. Navigate around

Some handy psql meta-commands (start with a backslash):

• \l — list all databases
• \dt — list tables in current database
• \du — list all users
• \q — exit psql

Try \l to see your databases:

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8  | en_US.utf8  | 
 template0 | postgres | UTF8     | en_US.utf8  | en_US.utf8  | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8  | en_US.utf8  | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Hands-on walkthrough

Let's put it together. This example assumes PostgreSQL is installed and running.

Step 1: Connect to the default database

psql -U postgres

If you get password authentication failed, you might need to set a password. On Ubuntu, you can become the postgres system user and change it:

sudo -u postgres psql

Inside psql, run:

ALTER USER postgres WITH PASSWORD 'your_secure_password';

Then exit (\q) and reconnect with the password.

Step 2: Create a practice database

Inside psql, run:

CREATE DATABASE practice;

Then connect to it:

psql -U postgres -d practice

You should see practice=#.

Step 3: Create a table and insert data

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary NUMERIC
);

INSERT INTO employees (name, salary) VALUES ('Alice', 75000), ('Bob', 82000);

Step 4: Run your first real query

SELECT * FROM employees;

Output:

 id |  name  | salary 
----+--------+--------
  1 | Alice  |  75000
  2 | Bob    |  82000
(2 rows)

You've not only connected but also created a table and queried it. That's the foundation you'll build on.

Compare options / when to choose what

You have several ways to connect to PostgreSQL. Here's a comparison:

For learning and daily database tasks, psql is the winner. It's fast, reliable, and you can pipe commands or use it in shell scripts. When you need to visualize data or work with schemas graphically, then a GUI makes sense, but for raw SQL and quick checks, psql is your friend.

Troubleshooting & edge cases

"psql: error: could not connect to server: No such file or directory"

This usually means the server isn't running or the socket file is missing. Check that PostgreSQL is started. On Linux, sudo systemctl status postgresql. If it's not running, start it.

"password authentication failed"

You're using the wrong password or the user isn't set up for password auth. As a superuser, you can reset it:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'newpass';"

"database \"user\" does not exist"

You didn't specify a database, and psql tried to use your OS username. Specify one:

psql -U postgres -d postgres

"role \"username\" does not exist"

The user doesn't exist. Create it with CREATE USER (as postgres):

CREATE USER myuser WITH PASSWORD 'mypass';

Connection timeout with remote host

If connecting to a remote server, ensure port 5432 is open. Use \connect inside psql or psql -h remote_host. Also, check pg_hba.conf to allow remote connections.

Pro tip: Never hardcode passwords in historical commands in production. Use ~/.pgpass file or environment variables. But for learning, it's fine.

What you learned & what's next

You now know how to connect with psql and run first queries. You can check the server status, understand connection defaults, run SELECT statements, and use meta-commands. You've also got a toolkit for troubleshooting common issues.

Next up, you'll dive deeper into SQL itself — querying tables, filtering with WHERE, and sorting with ORDER BY. This is where the real power of PostgreSQL shines. Get comfortable with psql, and the rest will be easier.

Keep practicing: create more tables, insert more data, and explore with different queries. The terminal is your playground.