How to Install PostgreSQL on Debian 11

Debian 11 is a great choice for running production database workloads. And PostgreSQL 13 stands out as a feature-rich open source relational database. With optimization, it can handle demanding applications with ease.

In this extensive 2600+ word guide, we will go through everything – from installation methods to configuration best practices – for optimal PostgreSQL 13 performance on Debian 11.

Overview of PostgreSQL Features and Benefits

Let‘s start by looking at some of the capabilities that make PostgreSQL well-suited for high performance deployments:

Advanced Concurrency Support

PostgreSQL uses multi-version concurrency control (MVCC) for managing contention and enhancing performance. This allows PostgreSQL to support a high degree of concurrency without locking the entire database for updates.

Specific capabilities include:

• Row versioning – Multiple live versions of a row can exist simultaneously
• Snapshot isolation – All queries see a consistent snapshot of the database at the moment their statement starts
• No reader blocking – Readers don‘t block writers and vice versa
• Optimistic concurrency control techniques

Together these allow thousands of concurrent sessions with good transaction throughput and response times.

Sophisticated Locking Mechanisms

PostgreSQL offers multiple lock modes to control concurrent access for both reads and writes:

• Exclusive lock – Only one transaction can obtain this lock
• Share lock – Multiple transactions can read, but none can update
• Update lock – Used to prevent conflicts during row updates
• Key share lock – Allows concurrent reads but not updates

There are also numerous lock partitioning methods like table partitioning, index partitioning etc. This allows locking specific table portions instead of everything.

Deadlocks are automatically detected and broken. Overall, PostgreSQL prevents long lock waits and minimizes transaction rollbacks.

Point-in-Time Recovery

PostgreSQL supports recovering database contents to a user-defined point in time with write ahead logging (WAL). This allows recreating corrupted or lost data from a backup.

Main capabilities are:

• Continuous archiving of WAL files
• Recovery to precise timestamp
• Works with replication for disaster recovery

This is invaluable for meeting demanding RPO and RTO requirements.

Asynchronous Replication

With built-in asynchronous streaming replication, PostgreSQL delivers great high availability and data redundancy:

• Support for cascading replicas and complex topologies
• Replication slots for managing WAL resource usage
• Sync and async modes for different needs
• Read scale-out capability

Overall, PostgreSQL replication is enterprise grade – battle tested and reliable.

These enterprise-level features and capabilities make PostgreSQL a great choice for business critical workloads. Next we‘ll see the installation process on Debian 11.

Installing PostgreSQL 13 on Debian 11

There are two recommended methods for installation – using the main Debian repositories or the official PostgreSQL repositories. Let‘s look at both in detail:

Method 1 – Installing PostgreSQL from Debian Repositories

This method uses packages from the default Debian "bullseye" repositories:

1. Prepare System and Update Repositories

   sudo apt update
   sudo apt upgrade
   

   Update installed packages as well.

2. Check for any existing PostgreSQL installations:

   dpkg -l | grep postgres
   

   If any exist, remove them before proceeding:

    
   sudo apt remove postgresql*
   

   This prevents version conflicts.

3. Install PostgreSQL 13 and additional components:

   sudo apt install postgresql-13 postgresql-client-13 postgresql-contrib-13
   

   The contrib package contains useful additional modules.

4. Estimate disk space requirements. A production setup requires additional storage for:
   • Database files
   • Write-Ahead Log files
   • Backups

   Use conscious capacity planning to allow for growth.

5. Configure access control by editing pg_hba.conf. Set authentication for different connection types:

   local   all             postgres                                peer
   local   all             all                                     md5
   host    all             all             127.0.0.1/32            md5
   host    all             all             ::1/128                 md5
   

   Restart PostgreSQL to apply changes.

This installs the latest PostgreSQL 13 available in the Debian repositories.

Method 2 – Installing from Official PostgreSQL Repositories

For newer PostgreSQL versions, use the official PostgreSQL APT repositories:

1. Import the Repository Signing Key:

    
   wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgres.gpg
   

   This allows apt to verify package integrity.

2. Add PostgreSQL 13 Repository:

   echo "deb [signed-by=/usr/share/keyrings/postgres.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" |sudo tee /etc/apt/sources.list.d/pgdg.list
   

   This uses the Debian codename (bullseye) for version compatibility.

3. Install PostgreSQL 13:

   sudo apt update
   sudo apt install postgresql-13 
   

   The same optional packages can also be installed:

   sudo apt install postgresql-client-13 postgresql-contrib-13
   

This setup uses updated packages direct from the PostgreSQL project.

Initializing the PostgreSQL Database Cluster

With PostgreSQL installed, we now need to initialize the database cluster:

sudo pg_createcluster 13 main --start

This creates version 13 data directories under /var/lib/postgresql and starts the database server.

To automatically start PostgreSQL on reboot:

sudo systemctl enable postgresql@13-main

The database is now ready for basic usage. Further configuration is still required for production workloads.

Creating Roles and Databases

Let‘s look at using the postgres role to create additional users and databases.

First switch to the postgres OS user:

sudo su - postgres

Next access the PostgreSQL interactive terminal:

  
psql  

We can now run SQL statements directly.

To create a new database user role:

CREATE ROLE myuser WITH LOGIN PASSWORD ‘password123‘;

This allows user myuser to login with the specified password.

Next create a database owned by this role:

  
CREATE DATABASE mydb OWNER myuser;

We can switch over and connect to this database:

\c mydb

Let‘s create a sample table:

CREATE TABLE books (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  author VARCHAR(50)  
);

We can grant all privileges on this table to myuser:

GRANT ALL ON books TO myuser; 

This allows the user full access to that table. More complex permission schemes are also possible.

That concludes basic database creation and setup!

Guidelines for PostgreSQL Optimization on Debian

To run large scale production workloads, PostgreSQL configuration tuning is vital for performance. Here are key optimization areas:

Memory Configuration

Being an in-memory database, the starting point is allocating enough RAM. Two key parameters are:

• shared_buffers – Sets memory reserved for cached disks pages. Use 25% of available RAM.
• work_mem – Memory for internal sort and hash operations. Set as high as possible.

There are several other memory related parameters for fine tuning.

Connections and Concurrency

Parameters that influence connectivity and concurrency:

• max_connections – Maximum concurrent connections. Calculate based on workload.
• max_worker_processes – Number of background processes. Set to 2 x number of CPU cores.
• max_parallel_workers_per_gather – For parallel query execution. Defaults to 2.

Write-Ahead Log Configuration

The main WAL parameters are:

• wal_level – Level of WAL detail. Set to replica for production.
• wal_buffers – Sets WAL cache memory size. Start with 16MB.
• checkpoint_timeout – WAL checkpoint trigger interval. Default is 5min.

There are many more parameters that can be tuned. Refer to the PostgreSQL documentation for full details.

Monitoring and Statistics

Managing workloads requires visibility into database activity. Useful open source tools include:

• pgmetrics – Collects and visualizes time-series metrics.
• pgHero – Provides performance insights and queries.
• pgwatch2 – Alerts on defined thresholds.

On Debian, these can be easily installed for health monitoring.

Conclusion

PostgreSQL offers stellar performance, reliability, and stability for production systems – especially when properly configured. Installing on Debian 11 is straightforward, and comprehensive tuning delivers great results.

We went over everything from setting up repositories, installing packages, creating databases and roles – to optimization guidelines and monitoring best practices. These steps provide a production-ready PostgreSQL platform.

Coupled with amazing concurrency support, sophisticated locking, replication capabilities and robust recovery – PostgreSQL can readily handle demanding workloads. This makes it a top choice open source database for business critical needs.