PostgreSQL is a powerful, open-source relational database management system (RDBMS) known for its robustness, extensibility, and SQL compliance. Whether you’re a developer, database administrator, or systems engineer, setting up PostgreSQL on a Linux server is a fundamental skill. This guide will walk you through the process of installing PostgreSQL on a Linux server, configuring it for optimal performance, and implementing basic security measures.

Prerequisites

Before we begin, ensure you have the following:

  1. A Linux server (Ubuntu, CentOS, or any other major distribution).
  2. Sudo or root access to the server.
  3. Internet access to download the PostgreSQL packages.

Step 1: Install PostgreSQL

On Ubuntu

  1. Update the package list:
sudo apt update
  1. Install PostgreSQL:
sudo apt install postgresql postgresql-contrib

On CentOS

  1. Enable the PostgreSQL repository:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %rhel)-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  1. Install PostgreSQL:
sudo yum install -y postgresql13-server postgresql13-contrib
  1. Initialize the database:
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
  1. Enable and start the PostgreSQL service:
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13

Step 2: Configure PostgreSQL

Switch to the PostgreSQL User

PostgreSQL creates a default user called postgres. Switch to this user to perform administrative tasks:

sudo -i -u postgres

Access the PostgreSQL Prompt

Open the PostgreSQL prompt using the psql command:

psql

Create a New Database and User

  1. Create a new database:
CREATE DATABASE mydatabase;
  1. Create a new user with a password:
CREATE USER myuser WITH PASSWORD 'mypassword';
  1. Grant privileges to the new user:
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;
  1. Exit the PostgreSQL prompt:
\q

Step 3: Basic Security Configuration

Secure the PostgreSQL Configuration

Edit the pg_hba.conf file to configure client authentication. The location of this file may vary depending on your Linux distribution. Common locations include /etc/postgresql/13/main/pg_hba.conf (Ubuntu) and /var/lib/pgsql/13/data/pg_hba.conf (CentOS).

Open the file in your preferred text editor:

sudo nano /etc/postgresql/13/main/pg_hba.conf  # Ubuntu
sudo nano /var/lib/pgsql/13/data/pg_hba.conf  # CentOS

Modify the authentication methods to use md5 for password authentication:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Adjust PostgreSQL Configuration for Remote Access

To allow remote connections, edit the postgresql.conf file. The location of this file may also vary, commonly found at /etc/postgresql/13/main/postgresql.conf (Ubuntu) or /var/lib/pgsql/13/data/postgresql.conf (CentOS).

Open the file in your preferred text editor:

sudo nano /etc/postgresql/13/main/postgresql.conf  # Ubuntu
sudo nano /var/lib/pgsql/13/data/postgresql.conf  # CentOS

Uncomment and modify the listen_addresses setting:

listen_addresses = 'localhost'  # default is 'localhost'
# Change to:
listen_addresses = '*'

Restart PostgreSQL Service

After making these changes, restart the PostgreSQL service to apply the new configurations:

sudo systemctl restart postgresql  # Ubuntu
sudo systemctl restart postgresql-13  # CentOS

Step 4: Verify the Installation

Test Local Connection

Switch to the postgres user and access the PostgreSQL prompt to ensure you can connect locally:

sudo -i -u postgres
psql

Test Remote Connection

To test remote access, use a PostgreSQL client such as psql or a GUI tool like pgAdmin from a remote machine:

psql -h <server_ip> -U myuser -d mydatabase

Conclusion

Setting up PostgreSQL on a Linux server involves installing the necessary packages, configuring the database system, and securing it against unauthorized access. By following this guide, you should have a fully functional PostgreSQL installation ready for your applications. Remember to regularly update your PostgreSQL server and monitor its performance to ensure it runs optimally.

With PostgreSQL set up and configured, you can now leverage its powerful features for your data storage and management needs. Happy querying!