Basics of Database (PostgreSQL)
---
1. Introduction to PostgreSQL
Topic: Understand what PostgreSQL is, its features, and its advantages.
Steps:
Install PostgreSQL on your machine (using apt, yum, or downloading from the PostgreSQL website).
Verify installation by running psql --version to check the version.
Understand the PostgreSQL architecture (client-server, process model).
---
2. Database Creation and Management
Topic: Creating databases and managing them.
Steps:
Create a new database:
CREATE DATABASE mydb;
List databases:
\l
Connect to a database:
\c mydb
Drop a database:
DROP DATABASE mydb;
---
3. Tables and Schema Management
Topic: Creating and managing tables and schemas.
Steps:
Create a new schema:
CREATE SCHEMA myschema;
Create a table with columns and data types:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT,
hire_date DATE
);
Show tables in the current schema:
\dt
Describe table structure:
\d <table_name>
\d employees
Drop a table:
DROP TABLE employees;
---
4. Data Types
Topic: Understanding and using PostgreSQL data types.
Steps:
Common data types: INTEGER, SERIAL, VARCHAR, TEXT, BOOLEAN, DATE, TIMESTAMP, DECIMAL, NUMERIC.
Example of using different data types in a table:
CREATE TABLE product (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100),
price NUMERIC(10, 2),
available BOOLEAN,
release_date TIMESTAMP
);
---
5. Inserting Data
Topic: Inserting data into tables.
Steps:
Insert single row:
INSERT INTO employees (name, age, hire_date)
VALUES ('John Doe', 30, '2020-01-15');
Insert multiple rows:
INSERT INTO employees (name, age, hire_date)
VALUES
('Alice', 28, '2021-06-20'),
('Bob', 35, '2019-09-10');
---
6. Querying Data
Topic: Basic SELECT queries to retrieve data.
Steps:
Select all columns:
SELECT * FROM employees;
Select specific columns:
SELECT name, age FROM employees;
Use WHERE clause for filtering:
SELECT * FROM employees WHERE age > 30;
Limit the number of rows:
SELECT * FROM employees LIMIT 5;
---
7. Updating Data
Topic: Modifying existing data.
Steps:
Update a specific row:
UPDATE employees SET age = 31 WHERE name = 'John Doe';
Update multiple rows:
UPDATE employees SET age = age + 1 WHERE hire_date < '2021-01-01';
---
8. Deleting Data
Topic: Removing data from tables.
Steps:
Delete a specific row:
DELETE FROM employees WHERE name = 'Bob';
Delete all rows:
DELETE FROM employees;
---
9. Constraints and Keys
Topic: Understanding and applying constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL).
Steps:
Add a PRIMARY KEY:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT
);
Add a NOT NULL constraint:
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);
Add a UNIQUE constraint:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_code VARCHAR(50) UNIQUE
);
Create a FOREIGN KEY:
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
---
10. Altering Tables
Topic: Modifying the structure of existing tables.
Steps:
Add a new column:
ALTER TABLE employees ADD COLUMN salary NUMERIC(10, 2);
Drop a column:
ALTER TABLE employees DROP COLUMN salary;
Rename a column:
ALTER TABLE employees RENAME COLUMN salary TO annual_salary;
---
11. Indexing
Topic: Creating and using indexes for query performance.
Steps:
Create an index on a column:
CREATE INDEX idx_employee_name ON employees(name);
Drop an index:
DROP INDEX idx_employee_name;
---
12. Basic Joins
Topic: Combining data from multiple tables.
Steps:
INNER JOIN:
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
LEFT JOIN:
SELECT employees.name, orders.order_id
FROM employees
LEFT JOIN orders ON employees.employee_id = orders.employee_id;
---
13. Views
Topic: Creating views to simplify complex queries.
Steps:
Create a view:
CREATE VIEW employee_orders AS
SELECT employees.name, orders.order_id
FROM employees
JOIN orders ON employees.employee_id = orders.employee_id;
Query the view:
SELECT * FROM employee_orders;
---
14. Transactions
Topic: Understanding and using transactions.
Steps:
Start a transaction:
BEGIN;
Commit the transaction:
COMMIT;
Rollback the transaction:
ROLLBACK;
---
1. Database Management
Advanced Database Management
Create a Database with Template:
CREATE DATABASE mydb TEMPLATE template0 ENCODING 'UTF8';
Renaming a Database (after disconnecting users):
ALTER DATABASE mydb RENAME TO newdb;
Clone a Database (using pg_dump and pg_restore for high availability):
pg_dump mydb | psql clonedb
Database Size with Detailed Tablespaces Info:
SELECT pg_size_pretty(pg_database_size('mydb')), pg_tablespace_location(oid) FROM pg_database WHERE datname = 'mydb';
Set Maintenance Mode (Prevent New Connections):
UPDATE pg_database SET datallowconn = false WHERE datname = 'mydb';
---
2. User and Role Management
Advanced User Management
Role with Login and Inheritance:
CREATE ROLE myrole LOGIN INHERIT;
Grant Role to Another Role (Role Hierarchy):
GRANT role1 TO role2;
Alter User with Multiple Attributes:
ALTER USER username WITH PASSWORD 'newpassword' VALID UNTIL '2025-12-31' SUPERUSER;
Set Resource Limits for Users:
ALTER ROLE username SET statement_timeout TO '10min';
ALTER ROLE username SET work_mem TO '50MB';
Audit User Activities (pg_audit):
CREATE EXTENSION pgaudit;
SET pgaudit.log = 'read, write';
---
3. Schema and Table Management
Advanced Schema Management
Transfer Ownership of a Schema:
ALTER SCHEMA myschema OWNER TO new_owner;
Drop a Schema Cascade (with dependent objects):
DROP SCHEMA myschema CASCADE;
Set Schema Search Path:
SET search_path TO myschema, public;
Cluster Tables (for optimizing storage):
CLUSTER mytable USING my_index;
---
4. Backup and Restore
Advanced Backup Techniques
Point-in-Time Recovery (PITR) (Create a Base Backup):
pg_basebackup -D /backupdir -F tar -z -P -X stream
Take a Backup Using pg_dumpall for All Databases:
pg_dumpall > all_databases_backup.sql
Restore a Database to a Specific Point in Time (Using restore_command):
restore_command = 'cp /backupdir/%f %p'
Parallel Backup/Restore (for large databases):
pg_dump -j 4 -F d -f /backupdir mydb
pg_restore -j 4 -d mydb /backupdir/mydb
---
5. Monitoring and Logs
Advanced Monitoring
Track Disk Usage of Tables and Indexes:
SELECT
relname AS "Relation",
pg_size_pretty(pg_total_relation_size(relid)) AS "Total Size",
pg_size_pretty(pg_indexes_size(relid)) AS "Index Size"
FROM pg_catalog.pg_statio_user_tables;
Log File Analysis (Identifying Slow Queries):
SELECT * FROM pg_stat_activity WHERE state = 'active' AND query_start < NOW() - INTERVAL '5 min';
Real-time Query Performance Monitoring:
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
Monitor Query Performance with Detailed Execution Plans:
EXPLAIN ANALYZE SELECT * FROM employees WHERE name = 'John Doe';
Detect Deadlocks in Real Time:
SELECT * FROM pg_locks WHERE NOT granted;
---
6. Performance and Optimization
Advanced Performance Tuning
Set Max Parallel Workers (Adjust for complex queries):
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
Configure work_mem and shared_buffers for High Performance:
ALTER SYSTEM SET work_mem = '128MB';
ALTER SYSTEM SET shared_buffers = '8GB';
Monitor Autovacuum Activity (To prevent table bloat):
SELECT relname, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE autovacuum_count > 0;
Enable Query Caching (via pg_stat_statements):
CREATE EXTENSION pg_stat_statements;
Create Custom Indexes for Query Optimization:
CREATE INDEX idx_employee_name_age ON employees (name, age);
---
7. Security Management
Advanced Security Configuration
Configure SSL Encryption for Connections:
In postgresql.conf:
ssl = on
ssl_cert_file = '/etc/ssl/certs/mycert.crt'
ssl_key_file = '/etc/ssl/private/mykey.key'
Create and Manage SSL Certificates:
openssl req -new -newkey rsa:2048 -days 365 -nodes -keyout mykey.key -out mycert.crt
Enable Row-Level Security (RLS) for fine-grained access control:
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
CREATE POLICY employee_policy ON employees FOR SELECT USING (age > 30);
Audit and Log Database Access (pgAudit Extension):
CREATE EXTENSION pgaudit;
SET pgaudit.log = 'read, write, ddl';
---
8. Clustering & Replication
Advanced Replication Management
Set up Streaming Replication (Master-Slave):
Master postgresql.conf settings:
wal_level = replica
max_wal_senders = 5
hot_standby = on
On Slave:
standby_mode = on
primary_conninfo = 'host=master_ip_address port=5432 user=replica password=replica_password'
Promote a Replica to Master:
pg_ctl promote -D /var/lib/postgresql/data
Set Up Logical Replication (for selective data replication):
CREATE PUBLICATION mypublication FOR TABLE employees;
CREATE SUBSCRIPTION mysubscription CONNECTION 'host=master dbname=mydb user=replica' PUBLICATION mypublication;
How to list all tables?
\dtS
\dtS *
SELECT * FROM pg_catalog.pg_tables;
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
---
9. High Availability & Disaster Recovery
Setting Up Hot Standby with Replication
Start Standby Server (on Slave):
pg_ctl -D /data_directory start
Enable Automatic Failover (Using Patroni or Pacemaker):
Configure Patroni (for failover automation):
postgresql:
listen: 0.0.0.0
connect_address: 'postgresql://node1'
replication:
username: replication
password: replication_pass
---
10. Maintenance & Troubleshooting
Advanced Maintenance Operations
Monitor and Prevent Table Bloat (using pgstattuple extension):
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('employees');
Check for and Remove Orphaned Objects (useful for cleanup):
SELECT relname FROM pg_stat_user_tables WHERE n_tup_ins = 0;
Force a Database to Be Consistent (in case of system crash):
pg_rewind --target-pgdata=/target_pg_data --source-server='host=primary_db_address port=5432'
Handle Long-Running Queries (and terminate them if necessary):
SELECT pid, query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 minutes';
SELECT pg_terminate_backend(pid);
No comments:
Post a Comment