Logo ← PostgreSQL Blog

Postgres 101

PostgreSQL is one of the most advanced open-source relational database systems available today. It powers everything from small personal…

Postgres 101

PostgreSQL is one of the most advanced open-source relational database systems available today. It powers everything from small personal projects to large-scale enterprise applications, thanks to its strong reliability, flexibility, and performance.If you’re new to PostgreSQL or want a quick reference for common tasks, this guide covers the essentials from creating databases and tables to managing backups, tuning performance, and securing your system.

1. Database Operations

1.1 Creating a Database

To create a new database:

CREATE DATABASE dbname;

This command can be run by the database owner or an administrator.

1.2 Creating a User

Add a new user with an optional password:

CREATE USER username WITH PASSWORD 'password';

1.3 Granting User Permissions

Grant specific privileges (like SELECT, INSERT, UPDATE, DELETE) to a user:

GRANT SELECT, INSERT ON TABLE table_name TO username;

2. Table Operations

2.1 Creating a Table

Define the table structure with column names and data types:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype
);

2.2 Dropping a Table

Permanently remove a table:

DROP TABLE table_name;

2.3 Adding a Column

Add a new column to an existing table:

ALTER TABLE table_name ADD COLUMN column_name datatype;

2.4 Creating an Index

Improve query performance with indexes:

CREATE INDEX index_name ON table_name (column_name);

3. Data Operations

3.1 Inserting Data

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

3.2 Updating Data

UPDATE table_name SET column1 = value1 WHERE condition;

3.3 Deleting Data

DELETE FROM table_name WHERE condition;

3.4 Querying Data

SELECT column1, column2 FROM table_name WHERE condition;

4. Backup Strategies

4.1 Logical Backups with pg_dump

Create an SQL script backup:

pg_dump -p 5432 -U postgres -f "/home/postgres/backup/backup.sql" dbname

Custom format (for pg_restore):

pg_dump -Fc -p 5432 -U postgres -f "/backup/backup.dump" dbname

Tar format:

pg_dump -Ft -p 5432 -U postgres -f "/backup/backup.tar" dbname


pg_dump --create --clean --if-exists --format=d --jobs=4 --verbose --file=16042025dbname.dump postgres://postgres:dummy@**.**.**.**:****/dbname

Dump specific tables:

pg_dump -v -Fd -p 5432 --jobs=4 -U postgres -f "/a" \
-t schema1.table1 -t schema2.table2 -d dbname

4.2 Physical Backups with pg_basebackup

Ideal for large or streaming backups:

pg_basebackup -U username -D /postgres0/backup/directory -Ft -Xs -z

5. Recovery Procedures

5.1 Restoring from a Logical Backup

SQL file restore:

psql -U username dbname < backup.sql

From custom format:

pg_restore -v -Fc -C -d postgres "/backup/akom.bak"

Restore a specific schema:

pg_restore -v --jobs=4 -d edbstore "/backup/akom.bak" --schema=schemaname

Restore specific tables:

pg_restore -v --jobs=4 -d dbmaster "/backup/edbuser_tables" -c

6. Performance Tuning

6.1 Using EXPLAIN

Understand how PostgreSQL plans to execute a query:

EXPLAIN SELECT * FROM table_name WHERE condition;

6.2 Query Optimization

  • VACUUM: Reclaims space by removing dead tuples.
VACUUM table_name;

VACUUM FULL table_name; -- Clean dead tuple # use pg_repack minumum lock
  • ANALYZE: Updates planner statistics for better execution plans.
ANALYZE table_name;

7. Monitoring Your Database

  • pg_stat_activity: View current sessions and queries.
SELECT * FROM pg_stat_activity;
  • pg_stat_statements: Track query performance metrics.
SELECT * FROM pg_stat_statements;

8. Security and Access Control

8.1 Role-Based Access

Control privileges with GRANT and REVOKE:

GRANT SELECT, INSERT ON table_name TO username;
REVOKE SELECT ON table_name FROM username;

8.2 Client Authentication via pg_hba.conf

Control who can connect and how they authenticate:

host all all **.**.**.**.**/32 scram-256