Introduction to PostgreSQL Administration

What is PostgreSQL?

PostgreSQL is an open-source relational database management system (RDBMS) that emphasizes extensibility and SQL compliance. It is often used for its advanced features, performance, and reliability.

Installation

Step-by-Step Installation

Update the package list:

sudo apt update

Install PostgreSQL:

sudo apt install postgresql postgresql-contrib

Start PostgreSQL service:

sudo systemctl start postgresql

Enable PostgreSQL to start on boot:

sudo systemctl enable postgresql

Configuration

Basic Configuration

The main configuration file for PostgreSQL is postgresql.conf, typically located in the /etc/postgresql//main/ directory.

Common Parameters:

listen_addresses: Sets the IP addresses PostgreSQL listens to.
port: The port number for the PostgreSQL server (default is 5432).
max_connections: Determines the maximum number of concurrent connections.

To apply changes, restart the service:

sudo systemctl restart postgresql

Maintenance

Regular Maintenance Tasks

Backup your database regularly using pg_dump.
Monitor database performance using tools like pg_stat_statements.
Run VACUUM and ANALYZE commands to recover space and update statistics.

Note: Ensure you have a rollback strategy in place before performing major updates or changes.

FAQ

How do I create a new database?

Use the following command:

CREATE DATABASE your_database_name;

How do I list all databases?

Execute the command:

SELECT datname FROM pg_database;

What is the default port for PostgreSQL?

The default port is 5432.