How to Install and Configure PostgreSQL Database

Introduction

PostgreSQL is a powerful, open-source relational database system. This guide covers installation and initial configuration.

Step 1: Install PostgreSQL

Install PostgreSQL server and client from the default repository.

sudo apt update
sudo apt install postgresql postgresql-contrib

Step 2: Start PostgreSQL Service

Ensure PostgreSQL service is running and enabled.

sudo systemctl start postgresql
sudo systemctl enable postgresql

Step 3: Access PostgreSQL

Switch to the postgres user and access the PostgreSQL prompt.

sudo -i -u postgres
psql

Step 4: Create Database and User

Create a new database and user for your application.

CREATE DATABASE myapp;
CREATE USER myappuser WITH ENCRYPTED PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE myapp TO myappuser;
\q

Step 5: Configure Remote Access

If needed, configure PostgreSQL to allow remote connections.

sudo nano /etc/postgresql/*/main/postgresql.conf
# Set: listen_addresses = '*'
sudo nano /etc/postgresql/*/main/pg_hba.conf
# Add: host all all 0.0.0.0/0 md5
sudo systemctl restart postgresql

Conclusion

PostgreSQL is now installed and configured. Remember to use strong passwords and configure firewall rules appropriately.