Complete PostgreSQL Terminal Guide

Posted on May 23, 2026 May 23, 2026 • by Ruman Ahmed • 4 min read

PostgreSQL is one of the most powerful open-source relational database systems. In this guide, you will learn how to install PostgreSQL, connect through the terminal, create databases and tables, run queries, manage users, backup databases, and use advanced terminal commands.

1. What is PostgreSQL?

PostgreSQL is an advanced open-source relational database management system (RDBMS) known for reliability, scalability, and SQL compliance.

2. Installing PostgreSQL

Ubuntu / Debian

sudo apt update
sudo apt install postgresql postgresql-contrib

Arch Linux

sudo pacman -S postgresql

Fedora

sudo dnf install postgresql-server postgresql-contrib

macOS (Homebrew)

brew install postgresql

Windows

Download the installer from the PostgreSQL official website and follow the installation wizard.

During installation, remember your PostgreSQL password.

3. Starting PostgreSQL Service

Linux

sudo systemctl start postgresql
sudo systemctl enable postgresql

Check Status

sudo systemctl status postgresql

4. Accessing PostgreSQL Terminal

Switch to PostgreSQL User

sudo -i -u postgres

Open PostgreSQL Shell

psql

Exit PostgreSQL Shell

\q

5. Basic PostgreSQL Commands

Command	Description
\l	List databases
\c dbname	Connect to database
\dt	List tables
\d table	Describe table structure
\du	List users/roles
\q	Quit terminal

6. Creating a Database

CREATE DATABASE mydb;

List Databases

\l

Connect to Database

\c mydb

7. Creating Users and Roles

Create User

CREATE USER myuser WITH PASSWORD 'mypassword';

Grant Database Access

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Make User Superuser

ALTER USER myuser WITH SUPERUSER;

8. Creating Tables

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email TEXT UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

List Tables

\dt

Describe Table

\d users

9. Inserting Data

INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 25);

Insert Multiple Rows

INSERT INTO users (name, email, age)
VALUES
('Alice', 'alice@example.com', 22),
('Bob', 'bob@example.com', 30);

10. Selecting Data

Select All

SELECT * FROM users;

Select Specific Columns

SELECT name, email FROM users;

Filter Data

SELECT * FROM users WHERE age > 20;

Sorting

SELECT * FROM users ORDER BY age DESC;

11. Updating Data

UPDATE users
SET age = 26
WHERE name = 'John Doe';

12. Deleting Data

DELETE FROM users
WHERE id = 1;

Delete All Rows

TRUNCATE TABLE users;

13. Altering Tables

Add Column

ALTER TABLE users ADD COLUMN phone TEXT;

Rename Column

ALTER TABLE users RENAME COLUMN phone TO mobile;

Delete Column

ALTER TABLE users DROP COLUMN mobile;

14. PostgreSQL Data Types

Type	Description
SERIAL	Auto increment integer
INT	Integer number
TEXT	Unlimited text
BOOLEAN	True or false
TIMESTAMP	Date and time
JSONB	JSON storage

15. Joins

INNER JOIN

SELECT users.name, orders.total
FROM users
INNER JOIN orders
ON users.id = orders.user_id;

LEFT JOIN

SELECT users.name, orders.total
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;

16. Transactions

BEGIN;

UPDATE users SET age = 50 WHERE id = 1;

COMMIT;

Rollback Example

ROLLBACK;

17. Importing CSV Data

COPY users(name, email, age)
FROM '/path/users.csv'
DELIMITER ','
CSV HEADER;

18. Exporting CSV Data

COPY users TO '/path/export.csv'
DELIMITER ','
CSV HEADER;

19. Backup and Restore

Backup Database

pg_dump -U postgres mydb > backup.sql

Restore Database

psql -U postgres mydb < backup.sql

20. Useful PostgreSQL Terminal Commands

\?              -- Show help
\h              -- SQL command help
\timing         -- Toggle query timing
SELECT version();
SELECT current_database();
SELECT current_user;

21. Common Errors and Fixes

Permission Denied

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

PostgreSQL Service Not Running

sudo systemctl restart postgresql

Password Authentication Failed

Verify username, password, and pg_hba.conf settings.

22. Best Practices

Always backup your database.
Use indexes for large tables.
Use transactions for critical operations.
Never use SUPERUSER unnecessarily.
Use environment variables for passwords.

23. Final Thoughts

PostgreSQL is an excellent database system for beginners and professionals. Learning terminal commands gives you full control over database administration, automation, and development workflows.

Practice the commands regularly and build small projects like blog systems, inventory management systems, or authentication systems to improve your PostgreSQL skills.