Learn Postgres - Top and Common Commands to Get Started

Key PostgreSQL Commands for New Users

Contents

1. About Postgres

2. Set Up

3. Application

4. Access

5. User Management

6. Basic Operations

7. Data Operations

8. Maintenance

9. Advanced

10. Security

11. Monitoring and Performance

---

About Postgres

Overview 

• PostgreSQL is an advanced, open-source relational database management system (RDBMS)

• Known for its robustness, extensibility, and standards compliance

• Supports both SQL and JSON querying

• Commonly used for web apps, enterprise applications, and geographical information systems (GIS)

Key Features

• ACID Compliance

  • Ensures atomicity, consistency, isolation, and durability of transactions

• Advanced Data Types

  • Supports a wide array of data types 

  • Extended data types include - Arrays, hstore, and JSONB for storing JSON data.

• Extensibility

  • Allows users to define custom functions, data types, operators, and more

• Full-Text Search

  • Integrated full-text search capabilities for efficient querying

• Concurrency Control

  • Implements Multiversion Concurrency Control (MVCC) to handle multiple transactions simultaneously

• Cross-Platform Support

  • Runs on various operating systems including Windows, Linux, and macOS

Comparison to Alternatives

• MySQL

  • Offers faster performance for read-heavy operations and simpler setup

  • PostgreSQL is more feature-rich and better for complex queries and transactions

• SQLite

  • Lightweight and serverless, ideal for embedded systems and small apps

  • PostgreSQL is more suitable for larger, multi-user applications with advanced requirements

• MariaDB

  • A fork of MySQL with similar performance and features

  • PostgreSQL is preferred for advanced data types, complex queries, and standards compliance

• MongoDB

  • A NoSQL database designed for high flexibility and scalability with unstructured data

  • PostgreSQL provides strong ACID compliance, advanced data types, and SQL support, making it better for structured data and complex querying

---

Set-Up

Check Installation and Version

To check if Postgres is installed and its version:

$ psql --version

Outputs:

psql (PostgreSQL) 14.12 (Ubuntu 14.12-0ubuntu0.22.04.1)

Installation 

Install Postgres on Ubuntu/Linux:

$ sudo apt update
$ sudo apt install postgresql postgresql-contrib

Uninstallation 

Uninstall Postgres:

$ sudo apt purge postgresql postgresql-contrib
$ sudo apt autoremove

---

Application 

Start-Up

Start the Postgres service:

$ sudo service postgresql start

Check Status

Check the status of the Postgres service:

$ sudo service postgresql status

Output:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enable>
     Active: active (exited) since Sat 2024-06-29 10:01:17 PDT; 1 day 23h ago
   Main PID: 1661 (code=exited, status=0/SUCCESS)
        CPU: 748us

Jun 29 10:01:17 eduardo-ubuntu systemd[1]: Starting PostgreSQL RDBMS...
Jun 29 10:01:17 eduardo-ubuntu systemd[1]: Finished PostgreSQL RDBMS.
lines 1-8/8 (END)

Check Port

Check which port Postgres is listening to (the default is 5432):

$ sudo netstat -tuln | grep 5432

Output:

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     

Power-Off

Stop the Postgres service:

$  sudo service postgresql stop

---

Access

Single Login into Database

Log in to Postgres as the default postgres user:

$ sudo -i -u postgres
$ psql

Persist Login into Database

To save Postgres credentials for quick login 

• Not recommended for security reasons

• What we’re doing here is creating a .pgpass file in our home directory

$ echo "hostname:port:database:username:password" > ~/.pgpass
$ chmod 600 ~/.pgpass

---

User Management

Create User

CREATE USER username 
WITH PASSWORD 'password';

Grant Privileges

GRANT ALL PRIVILEGES 
ON DATABASE database_name 
TO username;

Revoke Privileges

REVOKE ALL PRIVILEGES 
ON DATABASE database_name 
FROM username;

Modify User

ALTER USER username 
WITH PASSWORD 'new_password';

Delete User

DROP USER username;

---

Basic Operations

List Databases 

\l

Create Database

CREATE DATABASE database_name;

Example:

CREATE DATABASE company;
\c company

Clear Database Data

TRUNCATE TABLE table_name;

Delete Database

DROP DATABASE database_name;

---

Data Operations

Create Table

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Example:

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_email VARCHAR(100) UNIQUE,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Insert Data

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

INSERT INTO departments (dept_name) VALUES
    ('Finance'),
    ('Human Resources'),
    ('Marketing');
INSERT INTO employees (emp_name, emp_email, dept_id) VALUES
    ('John Doe', 'john.doe@example.com', 1),
    ('Jane Smith', 'jane.smith@example.com', 2),
    ('Michael Johnson', 'michael.johnson@example.com', 3);

Update Data

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE employees
SET emp_email = 'john.doe.new@example.com'
WHERE emp_name = 'John Doe';

Delete Data

DELETE 
FROM table_name
WHERE condition;

Example:

DELETE 
FROM employees
WHERE emp_name = 'Michael Johnson';

Query Data

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

SELECT emp_name, emp_email, dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;

---

Maintenance

Backup Database

To backup a database with ‘database_name’:

• Replace username with your username

• Replace database_name with your database name

• Replace the backup.tar with the path location and file name you desire

$ pg_dump -U username -W -F t database_name > backup.tar

Restore Database

To restore a database with ‘database_name’:

• Replace username with your username

• Replace database_name with your database name

• Replace the backup.tar with the path location and file name you desire

$ pg_restore -U username -W -d database_name backup.tar

Optimize Database

VACUUM is a garbage-collect and optimally analyzes a database:

• This command will reclaim storage occupied by dead tuples

• In Postgres, tuples that are deleted or obsoleted by an update are not physically removed from the table - which means that they continue to take up space - this c command eliminates them.

• There are also other VACUUM commands to further clean up your database

VACUUM FULL;

Check Logs

PostgreSQL logs are typically located in /var/log/postgresql/

You can view them using:

$ tail -f /var/log/postgresql/postgresql-12-main.log

---