Skip to content

PostgreSQL Installation and Basic Usage

Installation

  1. Update Package List:

sh sudo apt update

  1. Install PostgreSQL:

sh sudo apt install postgresql postgresql-contrib

  1. Start PostgreSQL Service: sh sudo systemctl start postgresql

Basic Usage

Accessing PostgreSQL

  • Command Line: sh psql -U postgres Enter the password you set during installation.

Creating a Database

  1. Open psql: Access the PostgreSQL command line interface.
  2. Create Database: sql CREATE DATABASE mydb;

Creating a User

  1. Open psql: Access the PostgreSQL command line interface.
  2. Create User: sql CREATE USER myuser WITH PASSWORD 'mypassword';
  3. Grant Privileges: sql GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Basic SQL Commands

  • Create a Table:

sql CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(100), position VARCHAR(50), salary DECIMAL );

  • Insert Data:

sql INSERT INTO employees (name, position, salary) VALUES ('Alice', 'Developer', 70000);

  • Select Data:

sql SELECT * FROM employees;

  • Update Data:

sql UPDATE employees SET salary = 75000 WHERE name = 'Alice';

  • Delete Data: sql DELETE FROM employees WHERE name = 'Alice';

Exiting psql

  • Type \q and press Enter.

Essential tools and utilities

pgAdmin

  • Description: pgAdmin is a popular, open-source graphical management tool for PostgreSQL.
  • Features:
  • Visual query builder.
  • Data visualization and analysis.
  • Backup and restore capabilities.
  • Server and database monitoring.
  • Usage: Ideal for database administration and management.

psql

  • Description: psql is the PostgreSQL interactive terminal.
  • Features:
  • Execute SQL commands and scripts.
  • Database navigation and administration.
  • Scripting capabilities.
  • Usage: Powerful command-line tool for developers and DBAs.

pg_dump

  • Description: Utility for backing up a PostgreSQL database.
  • Features:
  • Dumps database content into a script or archive file.
  • Supports various formats (plain, custom, directory, tar).
  • Usage: sh pg_dump mydb > mydb_backup.sql

pg_restore

  • Description: Utility to restore a PostgreSQL database from an archive created by pg_dump.
  • Features:
  • Supports restoring from custom, directory, and tar formats.
  • Selective restoration of database objects.
  • Usage: sh pg_restore -d mydb mydb_backup.tar

pgBouncer

  • Description: Lightweight connection pooler for PostgreSQL.
  • Features:
  • Reduces database connection overhead.
  • Improves performance by reusing connections.
  • Usage: Suitable for high-concurrency environments.

PostGIS

  • Description: Spatial database extender for PostgreSQL.
  • Features:
  • Adds support for geographic objects.
  • Spatial queries and analysis.
  • Usage: Essential for GIS (Geographic Information Systems) applications.

pgAdmin4

  • Description: Web-based version of pgAdmin.
  • Features:
  • Modern UI with extensive management capabilities.
  • Multi-user support.
  • Usage: Web-based administration and monitoring tool.

DBeaver

  • Description: Universal database tool that supports various databases, including PostgreSQL.
  • Features:
  • Advanced SQL editor.
  • ERD generation.
  • Data import/export.
  • Usage: Cross-platform database management.

pg_activity

  • Description: Command-line tool for PostgreSQL server activity monitoring.
  • Features:
  • Live view of running queries.
  • Performance statistics.
  • Usage: Monitoring and troubleshooting database performance.

TimescaleDB

  • Description: Time-series database built on PostgreSQL.
  • Features:
  • Native PostgreSQL compatibility.
  • Optimized for time-series data.
  • Usage: Suitable for applications dealing with time-series data like monitoring, IoT, and analytics.

pg_repack

  • Description: Tool to remove bloat from tables and indexes.
  • Features:
  • Online, non-blocking table repacking.
  • Index rebuilding.
  • Usage: Maintenance tool to optimize database performance.

pgBadger

  • Description: Fast PostgreSQL log analyzer.
  • Features:
  • Generates detailed reports from log files.
  • Performance statistics and insights.
  • Usage: Performance tuning and auditing.

SchemaSpy

  • Description: Java-based tool for analyzing database metadata.
  • Features:
  • Generates visual diagrams of database schema.
  • Detailed documentation of database structures.
  • Usage: Database documentation and schema analysis.

Additional Resources