PostgreSQL For Beginners: 10 Essential Commands Every New DBA Should Know

Introduction

PostgreSQL has become the preferred relational database for countless organizations thanks to its robust features, reliability, and open-source nature. As someone who’s worked with PostgreSQL databases across various environments, I can tell you that mastering these fundamental commands will make your database journey significantly smoother.

In this guide, I’ll share the 10 most practical PostgreSQL commands I use daily, with clear examples and expected outputs to help you apply them immediately.

Getting Started

Before diving into specific commands, let’s establish how to connect to PostgreSQL:

psql -h hostname -p port -U username -d database_name

Example:

psql -h localhost -p 5432 -U postgres -d my_application

Output:

Password for user postgres: 
psql (15.4)
Type "help" for help.

my_application=#

Once connected, these meta-commands will make navigation easier:

• \? – Display all available psql commands
• \h – Get help on specific SQL syntax
• \q – Quit the psql session

1. Creating and Managing Databases

Creating a database

CREATE DATABASE database_name
    WITH 
    OWNER = role_name
    ENCODING = 'UTF8';

Example:

CREATE DATABASE customer_portal WITH OWNER = app_user ENCODING = 'UTF8';

Output:

CREATE DATABASE

Listing databases

\l

Output:

                                   List of databases
      Name       |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------------+----------+----------+---------+-------+-----------------------
 customer_portal | app_user | UTF8     | C       | C     | 
 postgres        | postgres | UTF8     | C       | C     | 
 template0       | postgres | UTF8     | C       | C     | =c/postgres          +
                 |          |          |         |       | postgres=CTc/postgres
 template1       | postgres | UTF8     | C       | C     | =c/postgres          +
                 |          |          |         |       | postgres=CTc/postgres
(4 rows)

Dropping a database (with safety check)

-- First check for active connections
SELECT pid, usename, state 
FROM pg_stat_activity
WHERE datname = 'database_to_drop';

-- Then terminate connections if needed
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'database_to_drop';

-- Finally drop the database
DROP DATABASE database_to_drop;

Output after successful drop:

DROP DATABASE

2. User Management

Creating database users

CREATE USER username WITH PASSWORD 'secure_password';

Example:

CREATE USER reporting_user WITH PASSWORD 'SecurePass123!';

Output:

CREATE ROLE

Checking user permissions

\du

Output:

                                   List of roles
    Role name    |                         Attributes                         | Member of 
-----------------+------------------------------------------------------------+-----------
 app_user        | Create DB                                                  | {}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 reporting_user  |                                                            | {}

3. Table Operations

Creating tables with constraints

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'pending'))
);

Output:

CREATE TABLE

Listing tables

\dt

Output:

          List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | customers | table | app_user
(1 row)

Altering table structure

-- Adding a column
ALTER TABLE customers ADD COLUMN phone VARCHAR(15);

Output:

ALTER TABLE

4. Basic Data Manipulation

Inserting records

INSERT INTO customers (first_name, last_name, email, status)
VALUES 
    ('John', 'Doe', 'john@example.com', 'active'),
    ('Jane', 'Smith', 'jane@example.com', 'active');

Output:

INSERT 0 2

Updating data with conditions

UPDATE customers
SET status = 'inactive'
WHERE last_name = 'Doe'
RETURNING customer_id, email;

Output:

 customer_id |      email       
-------------+------------------
           1 | john@example.com
(1 row)

UPDATE 1

Deleting with safety measures

-- First check what would be deleted
SELECT * FROM customers WHERE status = 'inactive';

-- Then perform the delete
DELETE FROM customers 
WHERE status = 'inactive'
RETURNING customer_id, email;

Output:

 customer_id |      email       
-------------+------------------
           1 | john@example.com
(1 row)

DELETE 1

5. Simple Queries

Basic SELECT with filtering

SELECT 
    customer_id,
    first_name,
    last_name,
    email
FROM 
    customers
WHERE 
    status = 'active'
ORDER BY 
    last_name;

Example Output:

 customer_id | first_name | last_name |      email       
-------------+------------+-----------+------------------
           2 | Jane       | Smith     | jane@example.com
(1 row)

Aggregating data

SELECT 
    status,
    COUNT(*) as customer_count
FROM 
    customers
GROUP BY 
    status;

Example Output:

 status  | customer_count 
---------+----------------
 active  |              1
(1 row)

6. Backup and Restore

Creating a full database backup

pg_dump -h localhost -U postgres -d my_database -F c -f /backups/my_database_20250220.backup

Output:

Password: 

Restoring from backup

pg_restore -h localhost -U postgres -d my_database_restored /backups/my_database_20250220.backup

Example Output:

Password: 

7. Monitoring Database Size

Checking overall database size

SELECT pg_size_pretty(pg_database_size(current_database()));

Example Output:

 pg_size_pretty 
----------------
 8192 kB
(1 row)

Finding the largest tables

SELECT 
    table_name,
    pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS total_size
FROM 
    information_schema.tables
WHERE 
    table_schema = 'public'
ORDER BY 
    pg_total_relation_size(quote_ident(table_name)) DESC
LIMIT 5;

Example Output:

  table_name  | total_size 
--------------+------------
 customers    | 16 kB
(1 row)

8. Index Management

Creating a basic index

CREATE INDEX idx_customers_email ON customers(email);

Output:

CREATE INDEX

Checking index usage

SELECT
    indexrelname AS index_name,
    idx_scan as index_scans
FROM
    pg_stat_user_indexes
WHERE
    schemaname = 'public'
ORDER BY
    idx_scan DESC
LIMIT 5;

Example Output:

       index_name        | index_scans 
-------------------------+-------------
 customers_pkey          |           3
 idx_customers_email     |           1
(2 rows)

9. Vacuum Operations

Running basic vacuum

VACUUM (VERBOSE, ANALYZE) customers;

Example Output:

INFO:  vacuuming "public.customers"
INFO:  "customers": found 0 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 737
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  analyzing "public.customers"
INFO:  "customers": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows...
DETAIL:  1 rows in table contain 6 live tuples and 0 dead tuples.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

Finding tables needing vacuum

SELECT
    relname,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup + 1), 2) AS dead_percentage
FROM
    pg_stat_user_tables
WHERE
    n_dead_tup > 0
ORDER BY
    dead_percentage DESC
LIMIT 5;

10. Performance Monitoring

Finding slow queries

SELECT
    substring(query, 1, 50) AS short_query,
    calls,
    round(total_exec_time::numeric, 2) AS total_time,
    round(mean_exec_time::numeric, 2) AS avg_time
FROM
    pg_stat_statements
ORDER BY
    total_exec_time DESC
LIMIT 5;

Example Output:

           short_query            | calls | total_time | avg_time 
---------------------------------+-------+------------+----------
 SELECT * FROM customers WHERE s      |     4 |      10.32 |     2.58
 UPDATE customers SET status = 'i     |     1 |       5.47 |     5.47
 INSERT INTO customers (first_nam     |     1 |       3.21 |     3.21
(3 rows)

Checking for long-running transactions

SELECT
    pid,
    usename,
    now() - xact_start AS transaction_runtime,
    substr(query, 1, 50) AS query_preview
FROM
    pg_stat_activity
WHERE
    state != 'idle'
    AND (now() - xact_start) > '5 minutes'::interval
ORDER BY
    transaction_runtime DESC;

Conclusion

These PostgreSQL commands form the foundation of effective database management. I use these daily in production environments, and they’ve consistently helped me maintain healthy databases.

With these commands in your toolkit, you’ll be well-prepared to handle most routine PostgreSQL administration tasks. As you grow more comfortable with these basics, you can explore more advanced features like table partitioning, replication, and advanced performance tuning.

What PostgreSQL commands do you find most useful in your daily work? Share in the comments!

---

Coming next: “5 PostgreSQL Configuration Settings That Dramatically Improve Performance”