// database

PostgreSQL

Comprehensive guide for PostgreSQL database management, queries, and administration

postgresql · postgres · database · sql · rdbms

Connection and Basic Commands
Service Management
Database Operations
Schema Management
Table Operations
Data Manipulation
Joins
Aggregate Functions
User and Role Management
Backup and Restore
Indexes
Views
Functions and Stored Procedures
Triggers
Transactions
JSON and JSONB
Full-Text Search
Performance
Partitioning
Common PostgreSQL Commands
Best Practices
Troubleshooting
For MySQL DBAs Coming to PostgreSQL
Quick Reference

Connection and Basic Commands

Connect to PostgreSQL

# Connect as postgres user
sudo -u postgres psql

# Connect to specific database
psql -d database_name

# Connect with user and host
psql -U username -h hostname -d database_name

# Connect with all options
psql -h hostname -p 5432 -U username -d database_name

# Connect and execute command
psql -U username -d database_name -c "SELECT * FROM table"

# Execute SQL file
psql -U username -d database_name -f file.sql

# Exit psql
\q
exit

psql Meta-Commands

-- Help
\?              -- List all commands
\h              -- SQL help
\h CREATE TABLE -- Help on specific command

-- Database info
\l              -- List all databases
\l+             -- List with sizes and details
\c database     -- Connect to database
\conninfo       -- Current connection info

-- Table info
\dt             -- List tables
\dt+            -- List tables with sizes
\dt schema.*    -- List tables in schema
\d table        -- Describe table
\d+ table       -- Detailed table description
\di             -- List indexes
\dv             -- List views
\df             -- List functions
\dn             -- List schemas
\du             -- List users/roles

-- Display
\x              -- Toggle expanded display
\x auto         -- Auto expanded display
\timing         -- Toggle query timing

-- Output
\o filename     -- Send output to file
\o              -- Reset output to stdout

-- Execution
\i filename     -- Execute commands from file
\e              -- Edit query in editor

-- Variables
\set            -- List variables
\set var value  -- Set variable

Service Management

systemctl Commands (Linux)

# Check status
sudo systemctl status postgresql
sudo systemctl status postgresql@14-main  # Specific version

# Start service
sudo systemctl start postgresql

# Stop service
sudo systemctl stop postgresql

# Restart service
sudo systemctl restart postgresql

# Reload configuration (without restart)
sudo systemctl reload postgresql

# Enable service (start on boot)
sudo systemctl enable postgresql

# Disable service (don't start on boot)
sudo systemctl disable postgresql

# Check if service is enabled
sudo systemctl is-enabled postgresql

# Check if service is active
sudo systemctl is-active postgresql

Service Commands (Ubuntu/Debian)

# Using service command
sudo service postgresql status
sudo service postgresql start
sudo service postgresql stop
sudo service postgresql restart
sudo service postgresql reload

# Using pg_ctlcluster (Debian/Ubuntu specific)
pg_lsclusters                              # List clusters
sudo pg_ctlcluster 14 main start          # Start specific cluster
sudo pg_ctlcluster 14 main stop           # Stop specific cluster
sudo pg_ctlcluster 14 main restart        # Restart specific cluster
sudo pg_ctlcluster 14 main reload         # Reload config
sudo pg_ctlcluster 14 main status         # Check status

pg_ctl Commands (Direct Control)

# Initialize database cluster
initdb -D /var/lib/postgresql/data

# Start PostgreSQL
pg_ctl start -D /var/lib/postgresql/data
pg_ctl start -D /var/lib/postgresql/data -l logfile

# Stop PostgreSQL
pg_ctl stop -D /var/lib/postgresql/data
pg_ctl stop -D /var/lib/postgresql/data -m fast      # Fast shutdown
pg_ctl stop -D /var/lib/postgresql/data -m immediate # Immediate shutdown

# Restart PostgreSQL
pg_ctl restart -D /var/lib/postgresql/data

# Reload configuration
pg_ctl reload -D /var/lib/postgresql/data

# Check status
pg_ctl status -D /var/lib/postgresql/data

Check PostgreSQL Version

# From command line
psql --version
postgres --version

# From within psql
SELECT version();

# Check server version
psql -c "SELECT version();"

# Show all version info
SHOW server_version;
SHOW server_version_num;

Configuration Files

# Location of configuration files
psql -c "SHOW config_file;"              # postgresql.conf
psql -c "SHOW hba_file;"                 # pg_hba.conf
psql -c "SHOW data_directory;"           # Data directory

# Common locations
# Ubuntu/Debian: /etc/postgresql/14/main/
# RHEL/CentOS: /var/lib/pgsql/14/data/
# macOS (Homebrew): /usr/local/var/postgres/

# Edit main configuration
sudo nano /etc/postgresql/14/main/postgresql.conf

# Edit client authentication
sudo nano /etc/postgresql/14/main/pg_hba.conf

# After editing configs, reload
sudo systemctl reload postgresql

Logs

# View logs
sudo tail -f /var/log/postgresql/postgresql-14-main.log

# On Ubuntu/Debian
sudo journalctl -u postgresql
sudo journalctl -u postgresql -f        # Follow logs
sudo journalctl -u postgresql --since today

# Check log location from PostgreSQL
psql -c "SHOW log_directory;"
psql -c "SHOW log_filename;"

Auto-start Configuration

# Enable PostgreSQL to start on boot
sudo systemctl enable postgresql

# Disable auto-start
sudo systemctl disable postgresql

# Check auto-start status
sudo systemctl is-enabled postgresql

# Ubuntu/Debian specific
sudo update-rc.d postgresql enable
sudo update-rc.d postgresql disable

Multiple PostgreSQL Versions

# List all installed versions (Ubuntu/Debian)
pg_lsclusters

# Start specific version
sudo systemctl start postgresql@14-main
sudo systemctl start postgresql@15-main

# Stop specific version
sudo systemctl stop postgresql@14-main

# Switch default version
sudo pg_createcluster 15 main
sudo pg_dropcluster 14 main

Database Operations

Create and Manage Databases

-- Create database
CREATE DATABASE database_name;

-- Create with owner
CREATE DATABASE database_name OWNER username;

-- Create with encoding
CREATE DATABASE database_name
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

-- List all databases
\l
SELECT datname FROM pg_database;

-- Show current database
SELECT current_database();

-- Drop database
DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;

-- Rename database
ALTER DATABASE old_name RENAME TO new_name;

-- Change owner
ALTER DATABASE database_name OWNER TO new_owner;

-- Database size
SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

-- Single database size
SELECT pg_size_pretty(pg_database_size('database_name'));

Schema Management

Create and Manage Schemas

-- Create schema
CREATE SCHEMA schema_name;
CREATE SCHEMA IF NOT EXISTS schema_name;

-- Create schema with owner
CREATE SCHEMA schema_name AUTHORIZATION username;

-- List schemas
\dn
SELECT schema_name FROM information_schema.schemata;

-- Set search path
SET search_path TO schema_name, public;
SHOW search_path;

-- Drop schema
DROP SCHEMA schema_name;
DROP SCHEMA schema_name CASCADE;  -- Drop with all objects

-- Rename schema
ALTER SCHEMA old_name RENAME TO new_name;

Table Operations

Create Tables

-- Basic table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with constraints
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER DEFAULT 1 CHECK (quantity > 0),
    price NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Create table with check constraint
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0),
    category VARCHAR(50),
    CONSTRAINT unique_name_category UNIQUE (name, category)
);

-- Create table from query
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE condition;

-- Create table like another (structure only)
CREATE TABLE new_table (LIKE old_table INCLUDING ALL);

-- Temporary table
CREATE TEMPORARY TABLE temp_table (
    id INTEGER,
    name VARCHAR(50)
);

-- Unlogged table (faster, no crash recovery)
CREATE UNLOGGED TABLE logs (
    id SERIAL,
    message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Alter Tables

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 18;

-- Drop column
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone;

-- Rename column
ALTER TABLE users RENAME COLUMN old_name TO new_name;

-- Change column type
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);

-- Set default
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- Drop default
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- Set not null
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Drop not null
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

-- Add constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT check_quantity CHECK (quantity > 0);

-- Drop constraint
ALTER TABLE users DROP CONSTRAINT unique_email;

-- Add foreign key
ALTER TABLE orders
    ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) REFERENCES users(id);

-- Rename table
ALTER TABLE old_name RENAME TO new_name;

-- Change owner
ALTER TABLE table_name OWNER TO new_owner;

-- Set tablespace
ALTER TABLE table_name SET TABLESPACE tablespace_name;

Show Table Information

-- List tables
\dt
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- Describe table
\d table_name
\d+ table_name  -- More details

-- Show table size
SELECT
    pg_size_pretty(pg_total_relation_size('table_name'));

-- All tables with sizes
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Show indexes
\di
SELECT indexname FROM pg_indexes WHERE tablename = 'table_name';

-- Show constraints
SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'table_name'::regclass;

Drop Tables

-- Drop table
DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

-- Drop multiple tables
DROP TABLE table1, table2, table3;

-- Drop with cascade
DROP TABLE table_name CASCADE;

-- Truncate table
TRUNCATE TABLE table_name;
TRUNCATE TABLE table_name RESTART IDENTITY;  -- Reset sequences
TRUNCATE TABLE table_name CASCADE;  -- Truncate related tables

Data Manipulation

INSERT

-- Insert single row
INSERT INTO users (username, email)
VALUES ('john', 'john@example.com');

-- Insert multiple rows
INSERT INTO users (username, email) VALUES
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com');

-- Insert returning
INSERT INTO users (username, email)
VALUES ('dave', 'dave@example.com')
RETURNING id, created_at;

-- Insert from select
INSERT INTO new_users (username, email)
SELECT username, email FROM users WHERE created_at > '2024-01-01';

-- Insert on conflict (upsert)
INSERT INTO users (id, username, email)
VALUES (1, 'john', 'john@example.com')
ON CONFLICT (id) DO UPDATE SET
    username = EXCLUDED.username,
    email = EXCLUDED.email;

-- Insert on conflict do nothing
INSERT INTO users (username, email)
VALUES ('john', 'john@example.com')
ON CONFLICT (username) DO NOTHING;

SELECT

-- Basic select
SELECT * FROM users;
SELECT username, email FROM users;

-- Where clause
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE age > 18 AND status = 'active';

-- Like and ilike (case-insensitive)
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE email ILIKE '%@GMAIL.COM';

-- Similar to (regex)
SELECT * FROM users WHERE username SIMILAR TO '(john|jane)%';

-- Regular expressions
SELECT * FROM users WHERE username ~ '^[A-Z]';  -- Case-sensitive
SELECT * FROM users WHERE username ~* '^[a-z]'; -- Case-insensitive

-- IN operator
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE status IN ('active', 'pending');

-- Between
SELECT * FROM orders WHERE price BETWEEN 10 AND 100;

-- Null checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- Order by
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC, created_at DESC;
SELECT * FROM users ORDER BY created_at DESC NULLS LAST;

-- Limit and offset
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Distinct
SELECT DISTINCT status FROM orders;
SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC;

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT status) FROM orders;

UPDATE

-- Basic update
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Update multiple columns
UPDATE users
SET username = 'newname', email = 'new@example.com'
WHERE id = 1;

-- Update with returning
UPDATE users SET status = 'inactive' WHERE id = 1
RETURNING *;

-- Update with calculation
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

-- Update from another table
UPDATE users u
SET status = 'verified'
FROM profiles p
WHERE u.id = p.user_id AND p.verified = true;

-- Update with current timestamp
UPDATE users SET updated_at = NOW() WHERE id = 1;

DELETE

-- Delete specific rows
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < '2020-01-01';

-- Delete with returning
DELETE FROM users WHERE id = 1 RETURNING *;

-- Delete with using clause
DELETE FROM order_items
USING orders
WHERE order_items.order_id = orders.id
AND orders.status = 'cancelled';

Joins

Types of Joins

-- Inner join
SELECT u.username, o.id AS order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Left join
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Right join
SELECT u.username, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Full outer join
SELECT u.username, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Cross join
SELECT u.username, p.product_name
FROM users u
CROSS JOIN products p;

-- Self join
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- Lateral join (like apply in SQL Server)
SELECT u.*, recent_orders.*
FROM users u
LEFT JOIN LATERAL (
    SELECT * FROM orders o
    WHERE o.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 5
) recent_orders ON true;

Aggregate Functions

Common Aggregates

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT email) FROM users;

-- Sum
SELECT SUM(price) FROM orders;
SELECT SUM(quantity * price) AS total FROM order_items;

-- Average
SELECT AVG(price) FROM products;
SELECT AVG(age) FROM users WHERE status = 'active';

-- Min/Max
SELECT MIN(price), MAX(price) FROM products;

-- String aggregation
SELECT STRING_AGG(username, ', ' ORDER BY username) FROM users;

-- Array aggregation
SELECT ARRAY_AGG(username ORDER BY username) FROM users;

-- JSON aggregation
SELECT JSON_AGG(row_to_json(users)) FROM users;
SELECT JSON_OBJECT_AGG(id, username) FROM users;

-- Group by
SELECT status, COUNT(*) FROM orders GROUP BY status;
SELECT user_id, SUM(price) FROM orders GROUP BY user_id;

-- Having
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

-- Window functions
SELECT
    username,
    salary,
    AVG(salary) OVER () AS avg_salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- Grouping sets
SELECT category, brand, SUM(sales)
FROM products
GROUP BY GROUPING SETS ((category, brand), (category), ());

User and Role Management

Create and Manage Users

-- Create user
CREATE USER username WITH PASSWORD 'password';
CREATE USER username WITH ENCRYPTED PASSWORD 'password';

-- Create role
CREATE ROLE rolename;
CREATE ROLE rolename WITH LOGIN PASSWORD 'password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
GRANT SELECT, INSERT, UPDATE ON table_name TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;

-- Grant on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO username;

-- Grant role
GRANT rolename TO username;

-- Revoke privileges
REVOKE INSERT, UPDATE ON table_name FROM username;
REVOKE ALL ON DATABASE database_name FROM username;

-- Show grants
\du
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';

-- Alter user
ALTER USER username WITH PASSWORD 'newpassword';
ALTER USER username WITH SUPERUSER;
ALTER USER username WITH NOSUPERUSER;
ALTER USER username RENAME TO newname;

-- Drop user
DROP USER username;
DROP USER IF EXISTS username;

-- List users
\du
SELECT usename FROM pg_user;

-- Show user permissions
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'table_name';

Backup and Restore

pg_dump

# Backup single database
pg_dump database_name > backup.sql
pg_dump -U username database_name > backup.sql

# Backup with custom format (compressed)
pg_dump -Fc database_name > backup.dump

# Backup specific tables
pg_dump -t table_name database_name > table_backup.sql
pg_dump -t 'schema.table*' database_name > tables_backup.sql

# Backup schema only
pg_dump -s database_name > schema.sql

# Backup data only
pg_dump -a database_name > data.sql

# Backup with verbose output
pg_dump -v database_name > backup.sql

# Backup all databases
pg_dumpall > all_backup.sql

# Backup only globals (users, roles)
pg_dumpall --globals-only > globals.sql

# Remote backup
pg_dump -h remote_host -U username database_name > backup.sql

Restore

# Restore SQL dump
psql database_name < backup.sql
psql -U username -d database_name -f backup.sql

# Restore custom format
pg_restore -d database_name backup.dump

# Restore with verbose output
pg_restore -v -d database_name backup.dump

# Restore specific table
pg_restore -t table_name -d database_name backup.dump

# Restore and clean first
pg_restore -c -d database_name backup.dump

# Parallel restore (faster)
pg_restore -j 4 -d database_name backup.dump

# Create database and restore
createdb new_database
pg_restore -d new_database backup.dump

Indexes

Create and Manage Indexes

-- Create index
CREATE INDEX idx_username ON users(username);

-- Create unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Create composite index
CREATE INDEX idx_name_email ON users(username, email);

-- Create partial index
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';

-- Create expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- Create GiST index (for full-text search)
CREATE INDEX idx_content ON articles USING GiST(to_tsvector('english', content));

-- Create GIN index (for arrays, JSONB)
CREATE INDEX idx_tags ON posts USING GIN(tags);
CREATE INDEX idx_metadata ON documents USING GIN(metadata);

-- Create BRIN index (for large tables with natural ordering)
CREATE INDEX idx_created ON logs USING BRIN(created_at);

-- Concurrent index (doesn't lock table)
CREATE INDEX CONCURRENTLY idx_username ON users(username);

-- Show indexes
\di
SELECT indexname FROM pg_indexes WHERE tablename = 'users';

-- Drop index
DROP INDEX idx_username;
DROP INDEX CONCURRENTLY idx_username;

-- Reindex
REINDEX INDEX idx_username;
REINDEX TABLE users;
REINDEX DATABASE database_name;

Views

Create and Manage Views

-- Create view
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 'active';

-- Create or replace view
CREATE OR REPLACE VIEW user_orders AS
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Materialized view (cached results)
CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) AS order_count, SUM(price) AS total_spent
FROM orders
GROUP BY user_id;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW user_stats;
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

-- Create updatable view
CREATE VIEW recent_users AS
SELECT * FROM users WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
WITH CHECK OPTION;

-- List views
\dv
SELECT viewname FROM pg_views WHERE schemaname = 'public';

-- Show view definition
\d+ view_name

-- Drop view
DROP VIEW active_users;
DROP VIEW IF EXISTS active_users CASCADE;

Functions and Stored Procedures

Functions

-- Create function
CREATE OR REPLACE FUNCTION get_user_name(user_id INTEGER)
RETURNS VARCHAR AS $$
BEGIN
    RETURN (SELECT username FROM users WHERE id = user_id);
END;
$$ LANGUAGE plpgsql;

-- Use function
SELECT get_user_name(1);

-- Function returning table
CREATE OR REPLACE FUNCTION get_user_orders(user_id INTEGER)
RETURNS TABLE(order_id INTEGER, price NUMERIC, status VARCHAR) AS $$
BEGIN
    RETURN QUERY
    SELECT id, price, status FROM orders WHERE user_id = user_id;
END;
$$ LANGUAGE plpgsql;

-- Use table function
SELECT * FROM get_user_orders(1);

-- Function with default parameter
CREATE OR REPLACE FUNCTION get_users(status_filter VARCHAR DEFAULT 'active')
RETURNS SETOF users AS $$
BEGIN
    RETURN QUERY SELECT * FROM users WHERE status = status_filter;
END;
$$ LANGUAGE plpgsql;

-- List functions
\df
SELECT proname FROM pg_proc WHERE pronamespace = 'public'::regnamespace;

-- Drop function
DROP FUNCTION get_user_name(INTEGER);

Stored Procedures

-- Create procedure
CREATE OR REPLACE PROCEDURE update_user_status(
    user_id INTEGER,
    new_status VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users SET status = new_status WHERE id = user_id;
    COMMIT;
END;
$$;

-- Call procedure
CALL update_user_status(1, 'inactive');

-- Drop procedure
DROP PROCEDURE update_user_status(INTEGER, VARCHAR);

Triggers

Create Triggers

-- Create trigger function
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER update_user_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

-- Create audit trigger
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_audit (user_id, action, old_data, new_data, changed_at)
    VALUES (
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        row_to_json(OLD),
        row_to_json(NEW),
        NOW()
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_changes();

-- List triggers
SELECT tgname FROM pg_trigger WHERE tgrelid = 'users'::regclass;

-- Drop trigger
DROP TRIGGER update_user_timestamp ON users;

Transactions

Transaction Control

-- Start transaction
BEGIN;
START TRANSACTION;

-- Commit
COMMIT;

-- Rollback
ROLLBACK;

-- Savepoint
BEGIN;
INSERT INTO users (username) VALUES ('test1');
SAVEPOINT sp1;
INSERT INTO users (username) VALUES ('test2');
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

-- Transaction isolation levels
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Show isolation level
SHOW transaction_isolation;

-- Advisory locks
SELECT pg_advisory_lock(123);
SELECT pg_advisory_unlock(123);

JSON and JSONB

Working with JSON

-- Create table with JSONB
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

-- Insert JSON
INSERT INTO documents (data)
VALUES ('{"name": "John", "age": 30, "city": "NYC"}');

-- Query JSON fields
SELECT data->>'name' AS name FROM documents;
SELECT data->'age' AS age FROM documents;

-- Query nested JSON
SELECT data->'address'->>'city' FROM documents;

-- JSON array
SELECT data->'tags'->>0 FROM documents;

-- Search in JSONB
SELECT * FROM documents WHERE data @> '{"name": "John"}';
SELECT * FROM documents WHERE data->>'age' = '30';

-- Check if key exists
SELECT * FROM documents WHERE data ? 'email';

-- Update JSONB
UPDATE documents
SET data = jsonb_set(data, '{email}', '"john@example.com"')
WHERE id = 1;

-- Remove key from JSONB
UPDATE documents SET data = data - 'age' WHERE id = 1;

-- JSONB operators
-- @> contains
-- <@ is contained by
-- ? key exists
-- ?| any key exists
-- ?& all keys exist

Full-Text Search

Text Search

-- Create text search column
ALTER TABLE articles ADD COLUMN tsv tsvector;

-- Update text search column
UPDATE articles SET tsv = to_tsvector('english', title || ' ' || content);

-- Create GIN index
CREATE INDEX idx_tsv ON articles USING GIN(tsv);

-- Search
SELECT * FROM articles WHERE tsv @@ to_tsquery('english', 'postgresql & database');

-- Search with ranking
SELECT title, ts_rank(tsv, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & database') query
WHERE tsv @@ query
ORDER BY rank DESC;

-- Highlight results
SELECT ts_headline('english', content, to_tsquery('postgresql'))
FROM articles
WHERE tsv @@ to_tsquery('postgresql');

Performance

Query Analysis

-- Explain query
EXPLAIN SELECT * FROM users WHERE username = 'john';

-- Explain analyze (actually runs query)
EXPLAIN ANALYZE SELECT * FROM users WHERE id > 1000;

-- Explain with options
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders;

-- Vacuum
VACUUM;
VACUUM ANALYZE;
VACUUM FULL;  -- Reclaim space, locks table
VACUUM VERBOSE table_name;

-- Analyze
ANALYZE;
ANALYZE table_name;

-- Show table statistics
SELECT * FROM pg_stat_user_tables WHERE relname = 'users';

-- Show index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

-- Show slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Configuration

-- Show configuration
SHOW ALL;
SHOW max_connections;
SHOW shared_buffers;

-- Set configuration
ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET shared_buffers = '256MB';

-- Reload configuration
SELECT pg_reload_conf();

-- Show current settings
SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%buffer%';

Partitioning

Table Partitioning

-- Create partitioned table
CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER,
    created_at TIMESTAMP,
    price NUMERIC
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- List partitions
SELECT
    schemaname,
    tablename
FROM pg_tables
WHERE tablename LIKE 'orders_%';

Common PostgreSQL Commands

Database Maintenance

# Vacuum database
vacuumdb -d database_name

# Vacuum all databases
vacuumdb --all

# Analyze database
vacuumdb --analyze -d database_name

# Reindex database
reindexdb -d database_name

# Cluster table
psql -c "CLUSTER table_name"

Import/Export

# Export to CSV
psql -d database_name -c "COPY table_name TO '/path/to/file.csv' CSV HEADER"

# Import from CSV
psql -d database_name -c "COPY table_name FROM '/path/to/file.csv' CSV HEADER"

# Export query to CSV
psql -d database_name -c "COPY (SELECT * FROM table WHERE condition) TO '/path/to/file.csv' CSV HEADER"

Best Practices

Security

-- Use strong passwords
ALTER USER username WITH PASSWORD 'StrongP@ssw0rd!';

-- Principle of least privilege
GRANT SELECT ON table_name TO readonly_user;

-- Use SSL connections
-- Edit postgresql.conf: ssl = on

-- Restrict host access (pg_hba.conf)
-- host    database    user    address    method
-- host    all         all     127.0.0.1/32    scram-sha-256

-- Row-level security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_policy ON users
FOR SELECT
USING (id = current_user_id());

Performance Tips

Use appropriate indexes - B-tree, GIN, GiST, BRIN
Analyze queries - Use EXPLAIN ANALYZE
Vacuum regularly - Prevent bloat
Use connection pooling - PgBouncer, pgpool
Partition large tables - Improve query performance
Use JSONB over JSON - Better performance
Tune configuration - shared_buffers, work_mem, etc.
Monitor pg_stat_statements - Find slow queries
Use materialized views - For expensive queries
Proper data types - Use appropriate sizes

Data Types

-- Numeric
SMALLINT        -- 2 bytes (-32768 to 32767)
INTEGER         -- 4 bytes
BIGINT          -- 8 bytes
DECIMAL(p,s)    -- Exact numeric
NUMERIC(p,s)    -- Same as DECIMAL
REAL            -- 4 bytes float
DOUBLE PRECISION -- 8 bytes float

-- Serial (auto-increment)
SMALLSERIAL     -- Auto-incrementing SMALLINT
SERIAL          -- Auto-incrementing INTEGER
BIGSERIAL       -- Auto-incrementing BIGINT

-- String
CHAR(n)         -- Fixed length
VARCHAR(n)      -- Variable length
TEXT            -- Unlimited length

-- Date/Time
DATE            -- Date only
TIME            -- Time only
TIMESTAMP       -- Date and time
TIMESTAMPTZ     -- Timestamp with timezone
INTERVAL        -- Time interval

-- Boolean
BOOLEAN         -- TRUE, FALSE, NULL

-- Binary
BYTEA           -- Binary data

-- JSON
JSON            -- JSON data (stored as text)
JSONB           -- Binary JSON (faster, indexable)

-- Arrays
INTEGER[]       -- Array of integers
TEXT[]          -- Array of text

-- UUID
UUID            -- Universally unique identifier

-- Network
INET            -- IP address
CIDR            -- Network address
MACADDR         -- MAC address

-- Geometric
POINT           -- Point on a plane
LINE            -- Infinite line
LSEG            -- Line segment
BOX             -- Rectangular box
CIRCLE          -- Circle

-- Other
ENUM            -- Enumerated type
XML             -- XML data

Troubleshooting

Common Issues

-- Check active connections
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query
FROM pg_stat_activity;

-- Kill connection
SELECT pg_terminate_backend(pid);

-- Check locks
SELECT * FROM pg_locks WHERE NOT granted;

-- Check blocking queries
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- Check database size
SELECT pg_size_pretty(pg_database_size(current_database()));

-- Check table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Replication status
SELECT * FROM pg_stat_replication;

For MySQL DBAs Coming to PostgreSQL

If you already know MySQL, most of PostgreSQL feels familiar — same SQL, same relational model, similar tooling. The trouble is the small differences that quietly bite. This section maps the parts that are not the same.

Mental Model: The Big Differences

Concept	MySQL	PostgreSQL
Top-level container	A database (schemas are an alias for databases)	A cluster holds many databases; each database has many schemas (default: `public`)
Cross-database queries	Yes, with `db.table` syntax	No — one connection talks to one database; use schemas instead
Storage engines	InnoDB, MyISAM, MEMORY, etc. (`ENGINE=InnoDB`)	None — PostgreSQL has one storage engine; the choice does not exist
Users	`'user'@'host'` (user + host pair)	A role (no host pair); host-based access is controlled in `pg_hba.conf`
Auto-increment	`AUTO_INCREMENT` column attribute	`SERIAL` / `BIGSERIAL`, or `GENERATED ... AS IDENTITY` (SQL standard)
Default port	3306	5432
DDL inside transactions	Implicit commit on DDL	Fully transactional — wrap `CREATE TABLE`, `ALTER TABLE`, etc. in `BEGIN…COMMIT` and roll them back if needed

The schema-vs-database distinction trips up almost every MySQL DBA on day one. In MySQL, “database” and “schema” are the same thing. In PostgreSQL they are not: a cluster contains databases, and each database contains schemas (the default is public).

CLI and Connection

# MySQL                                # PostgreSQL
mysql -u root -p                       psql -U postgres
mysql -u user -p -h host db            psql -U user -h host -d db
mysql -u user -p db -e "SELECT ..."    psql -U user -d db -c "SELECT ..."
mysql -u user -p db < script.sql       psql -U user -d db -f script.sql
exit | quit                            \q

Meta-Commands: `SHOW …` → `\…`

PostgreSQL’s psql uses backslash meta-commands instead of MySQL’s SHOW statements:

MySQL	PostgreSQL (psql)	Pure SQL alternative
`SHOW DATABASES;`	`\l`	`SELECT datname FROM pg_database;`
`USE db;`	`\c db`	— (connection-level, no SQL form)
`SHOW TABLES;`	`\dt`	`SELECT tablename FROM pg_tables WHERE schemaname = 'public';`
`DESCRIBE table;` / `SHOW COLUMNS FROM table;`	`\d table`	Query `information_schema.columns`
`SHOW CREATE TABLE table;`	`\d+ table` (closest)	Use `pg_dump -s -t table` for exact DDL
`SHOW INDEX FROM table;`	`\di` (all)	`SELECT * FROM pg_indexes WHERE tablename = 'table';`
`SHOW GRANTS FOR user;`	`\du` (roles) + `\dp` (object privileges)	Query `information_schema.role_table_grants`
`SHOW PROCESSLIST;`	—	`SELECT * FROM pg_stat_activity;`
`SHOW VARIABLES;`	`SHOW ALL;`	`SELECT * FROM pg_settings;`
`SHOW STATUS;`	—	`SELECT * FROM pg_stat_database;`
`SHOW ENGINE INNODB STATUS;`	(no equivalent — single engine)	—

Data Type Mapping

MySQL	PostgreSQL	Notes
`TINYINT`	`SMALLINT`	No 1-byte int in PG
`TINYINT(1)` (boolean idiom)	`BOOLEAN`	PG has a real boolean type — use it
`SMALLINT`	`SMALLINT`	Same
`MEDIUMINT`	`INTEGER`	No 3-byte int in PG
`INT` / `INTEGER`	`INTEGER`	Same
`BIGINT`	`BIGINT`	Same
`INT AUTO_INCREMENT`	`SERIAL` or `GENERATED ALWAYS AS IDENTITY`	Prefer `IDENTITY` for new code (SQL standard)
`BIGINT AUTO_INCREMENT`	`BIGSERIAL` or `GENERATED ALWAYS AS IDENTITY`
`DECIMAL(p,s)` / `NUMERIC`	`NUMERIC(p,s)`	Same
`FLOAT` / `DOUBLE`	`REAL` / `DOUBLE PRECISION`
`CHAR(n)`	`CHAR(n)`	Same
`VARCHAR(n)`	`VARCHAR(n)`	Same; in PG, `TEXT` is often preferred — no perf penalty
`TEXT` / `MEDIUMTEXT` / `LONGTEXT`	`TEXT`	One unbounded type covers all of them
`BLOB` / `LONGBLOB`	`BYTEA`	PG uses one binary type
`DATETIME`	`TIMESTAMP`
`TIMESTAMP` (auto-updating)	`TIMESTAMPTZ` + trigger	PG `TIMESTAMP` does not auto-update; use a `BEFORE UPDATE` trigger
`JSON`	`JSONB`	Prefer `JSONB` — indexable and faster than `JSON`
`ENUM('a','b')` (inline)	`CREATE TYPE color AS ENUM ('a','b');`	PG enums are first-class types, not inline column definitions
`SET('a','b')`	`TEXT[]` or a separate table	PG has no SET type

Identifier and String Quoting

This is one of the most common silent bugs when porting MySQL code:

-- MySQL: backticks for identifiers, single or double quotes for strings
SELECT `user_id` FROM `users` WHERE `name` = "John";

-- PostgreSQL: double quotes for identifiers, single quotes ONLY for strings
SELECT "user_id" FROM "users" WHERE "name" = 'John';
--                                            ^^^^^^
-- "John" with double quotes is an IDENTIFIER, not a string —
-- PG will error: column "John" does not exist

Identifier case is also different. In PostgreSQL, unquoted identifiers are folded to lowercase. SELECT * FROM Users reads from a table named users. If you quoted the table as "Users" when creating it, you must keep quoting it that way forever.

Auto-Increment and Sequences

-- MySQL
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));
INSERT INTO users (name) VALUES ('alice');
SELECT LAST_INSERT_ID();

-- PostgreSQL (modern, SQL-standard)
CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO users (name) VALUES ('alice') RETURNING id;
                                          -- ^^^^^^^^^ note: RETURNING beats LAST_INSERT_ID()

-- PostgreSQL (older but still common)
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50));
-- SERIAL is sugar for: INTEGER + a sequence + DEFAULT nextval(...)
SELECT currval(pg_get_serial_sequence('users', 'id'));   -- like LAST_INSERT_ID

INSERT … RETURNING is one of the genuinely better features PG has over MySQL — it works for UPDATE and DELETE too. Reach for it.

Upserts: `ON DUPLICATE KEY UPDATE` → `ON CONFLICT`

-- MySQL
INSERT INTO users (id, name, email) VALUES (1, 'alice', 'a@x.com')
ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);

-- PostgreSQL
INSERT INTO users (id, name, email) VALUES (1, 'alice', 'a@x.com')
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email;

-- Skip the insert if it would conflict (MySQL: INSERT IGNORE)
INSERT INTO users (id, name) VALUES (1, 'alice')
ON CONFLICT DO NOTHING;

Two key things: you must name the conflict target (ON CONFLICT (id) — usually the unique constraint), and EXCLUDED.col is how you refer to the “would-be-inserted” value (the MySQL VALUES(col) equivalent).

`REPLACE INTO` Has No Direct Equivalent

MySQL’s REPLACE INTO (delete-then-insert) does not exist in PostgreSQL. Use INSERT … ON CONFLICT DO UPDATE for the upsert intent. If you genuinely need delete-then-insert semantics (e.g., to reset row data including columns not in the insert list), do it as a transaction:

BEGIN;
DELETE FROM users WHERE id = 1;
INSERT INTO users (id, name) VALUES (1, 'alice');
COMMIT;

`LIMIT` in `UPDATE` and `DELETE`

MySQL allows UPDATE … LIMIT 10. PostgreSQL does not. Use a subquery on the primary key:

-- MySQL
DELETE FROM logs WHERE level = 'debug' LIMIT 1000;

-- PostgreSQL
DELETE FROM logs WHERE id IN (
    SELECT id FROM logs WHERE level = 'debug' LIMIT 1000
);

GROUP BY Strictness

By default, PostgreSQL enforces the SQL standard: every non-aggregated column in the SELECT list must appear in GROUP BY. MySQL has historically been permissive (and ONLY_FULL_GROUP_BY is now the default in modern MySQL too, but lots of old code relies on the looser rule). Expect to add columns to your GROUP BY clause, or rewrite with DISTINCT ON or window functions.

Boolean Type

Use it. Don’t reach for TINYINT(1) muscle memory:

-- MySQL idiom
is_active TINYINT(1) DEFAULT 1
-- WHERE is_active = 1

-- PostgreSQL
is_active BOOLEAN DEFAULT TRUE
-- WHERE is_active        (and: WHERE NOT is_active)

User and Permission Management

-- MySQL                                    -- PostgreSQL
CREATE USER 'alice'@'%'                     CREATE ROLE alice WITH LOGIN
  IDENTIFIED BY 'pw';                         PASSWORD 'pw';
                                            -- (CREATE USER is an alias for ROLE with LOGIN)

GRANT ALL ON db.* TO 'alice'@'%';           GRANT ALL ON DATABASE db TO alice;
GRANT SELECT ON db.t TO 'alice'@'%';        GRANT SELECT ON t TO alice;
                                            -- (PG also has schema-level GRANTs:
                                            --  GRANT USAGE ON SCHEMA public TO alice;)

FLUSH PRIVILEGES;                           -- not needed in PG (changes take effect immediately)

SHOW GRANTS FOR 'alice'@'%';                \du alice                  -- role attributes
                                            \dp                        -- object privileges

Important: in MySQL, host-based access is part of the user ('alice'@'%'). In PostgreSQL, it lives in pg_hba.conf — a separate file that controls which clients can connect, from where, and how they authenticate. Granting on a role is not enough; the role must also have a matching pg_hba.conf line.

# pg_hba.conf example
# TYPE  DATABASE    USER    ADDRESS         METHOD
host    mydb        alice   10.0.0.0/24     scram-sha-256

Backup and Restore

MySQL	PostgreSQL
`mysqldump db > b.sql`	`pg_dump db > b.sql`
`mysqldump --all-databases > b.sql`	`pg_dumpall > b.sql`
`mysqldump -d db` (schema only)	`pg_dump -s db`
`mysqldump -t db` (data only)	`pg_dump -a db`
`mysql db < b.sql`	`psql -d db -f b.sql`
(no compact native format)	`pg_dump -Fc db > b.dump` → restore with `pg_restore` (compressed, parallel-restorable)
`mysqldump --single-transaction`	`pg_dump` is consistent by default (MVCC snapshot)

Use the custom format (-Fc) for new backup pipelines — it’s compressed, supports parallel restore, and lets you restore individual tables.

Replication and the Write-Ahead Log

MySQL	PostgreSQL
Binary log (`binlog`)	Write-Ahead Log (WAL)
`mysqlbinlog`	`pg_waldump` (less commonly used)
Statement / row / mixed-based replication	Physical (WAL-based) or logical replication
`SHOW SLAVE STATUS`	`SELECT * FROM pg_stat_replication;` (on primary)
`RESET MASTER`	— (handled via WAL retention settings)
Async by default; semi-sync available	Async by default; sync replication available (`synchronous_standby_names`)

Common Gotchas Translated

A short list of things that have surprised real MySQL DBAs on their first week with Postgres:

utf8 in MySQL is not real UTF-8 (it’s 3-byte; you needed utf8mb4). PostgreSQL’s UTF8 encoding is the real thing — no conversion needed.
No INSERT IGNORE — use ON CONFLICT DO NOTHING.
No ENGINE=…, ROW_FORMAT=…, COLLATE=… table options of the MySQL kind. Collation is set at the database/column level.
NOW() and CURRENT_TIMESTAMP return the start time of the current transaction in PG, not the wall-clock at statement time. Use clock_timestamp() if you want the latter.
String concatenation is ||, not + or CONCAT() only. CONCAT() exists, but 'a' || 'b' is the idiomatic form.
|| is NOT logical OR. Logical OR is OR. (|| is concatenation.)
No DESCRIBE as SQL — it’s a psql meta-command (\d).
information_schema exists in both, but PG also has pg_catalog (its own internal catalog) which is often richer for admin queries.
information_schema.tables lookups need WHERE table_schema = 'public' — otherwise you’ll get PG’s own catalog tables too.
VACUUM is a real, recurring concern (MVCC dead tuples). MySQL/InnoDB does this implicitly; in PG, autovacuum should be tuned and never disabled in production.
Connection-per-process model — PG forks a backend per connection. Use PgBouncer for high connection counts; do not try to scale 10,000 raw connections like you might with MySQL’s thread model.
Case-sensitive LIKE — use ILIKE for case-insensitive matching (MySQL LIKE is case-insensitive by default depending on collation).
No LIMIT … , … syntax — PG only supports LIMIT n OFFSET m, not LIMIT m, n.

One-Page Translation Cheat

-- Connect
mysql -u u -p db          →  psql -U u -d db

-- List things
SHOW DATABASES            →  \l
SHOW TABLES               →  \dt
DESCRIBE t                →  \d t
SHOW INDEX FROM t         →  \di

-- Auto-increment & last id
INT AUTO_INCREMENT        →  INTEGER GENERATED ALWAYS AS IDENTITY
LAST_INSERT_ID()          →  INSERT ... RETURNING id

-- Upsert
ON DUPLICATE KEY UPDATE   →  ON CONFLICT (col) DO UPDATE SET x = EXCLUDED.x
INSERT IGNORE             →  ON CONFLICT DO NOTHING

-- Quoting
`identifier`              →  "identifier"
"string"                  →  'string'

-- Booleans
TINYINT(1)                →  BOOLEAN

-- Concatenation
CONCAT('a','b')           →  'a' || 'b'

-- Case-insensitive search
LIKE 'foo%' (often)       →  ILIKE 'foo%'

-- Backup / restore
mysqldump db > b.sql      →  pg_dump db > b.sql
mysql db < b.sql          →  psql -d db -f b.sql

-- Processlist & kill
SHOW PROCESSLIST          →  SELECT * FROM pg_stat_activity;
KILL <id>                 →  SELECT pg_terminate_backend(<pid>);

Quick Reference

# Connection
psql -U postgres
psql -d database_name

# Database
CREATE DATABASE db_name;
\c db_name
DROP DATABASE db_name;

# Table
CREATE TABLE table_name (id SERIAL PRIMARY KEY);
\d table_name
DROP TABLE table_name;

# Data
INSERT INTO table VALUES (1, 'value');
SELECT * FROM table WHERE condition;
UPDATE table SET col = 'value' WHERE condition;
DELETE FROM table WHERE condition;

# User
CREATE USER username WITH PASSWORD 'password';
GRANT ALL ON DATABASE db TO username;

# Backup
pg_dump db_name > backup.sql
pg_restore -d db_name backup.dump

Table of Contents

Connection and Basic Commands

Connect to PostgreSQL

psql Meta-Commands

Service Management

systemctl Commands (Linux)

Service Commands (Ubuntu/Debian)

pg_ctl Commands (Direct Control)

Check PostgreSQL Version

Configuration Files

Logs

Auto-start Configuration

Multiple PostgreSQL Versions

Database Operations

Create and Manage Databases

Schema Management

Create and Manage Schemas

Table Operations

Create Tables

Alter Tables

Show Table Information

Drop Tables

Data Manipulation

INSERT

SELECT

UPDATE

DELETE

Joins

Types of Joins

Aggregate Functions

Common Aggregates

User and Role Management

Create and Manage Users

Backup and Restore

pg_dump

Restore

Indexes

Create and Manage Indexes

Views

Create and Manage Views

Functions and Stored Procedures

Functions

Stored Procedures

Triggers

Create Triggers

Transactions

Transaction Control

JSON and JSONB

Working with JSON

Full-Text Search

Text Search

Performance

Query Analysis

Configuration

Partitioning

Table Partitioning

Common PostgreSQL Commands

Database Maintenance

Import/Export

Best Practices

Security

Performance Tips

Data Types

Troubleshooting

Common Issues

For MySQL DBAs Coming to PostgreSQL

Mental Model: The Big Differences

CLI and Connection

Meta-Commands: SHOW … → \…

Data Type Mapping

Identifier and String Quoting

Auto-Increment and Sequences

Upserts: ON DUPLICATE KEY UPDATE → ON CONFLICT

REPLACE INTO Has No Direct Equivalent

LIMIT in UPDATE and DELETE

GROUP BY Strictness

Boolean Type

User and Permission Management

Backup and Restore

Replication and the Write-Ahead Log

Meta-Commands: `SHOW …` → `\…`

Upserts: `ON DUPLICATE KEY UPDATE` → `ON CONFLICT`

`REPLACE INTO` Has No Direct Equivalent

`LIMIT` in `UPDATE` and `DELETE`