PostgreSQL Installation and Configuration Guide on Ubuntu

Installing PostgreSQL on Ubuntu: A Complete Guide

PostgreSQL, a powerful open-source relational database, is widely used on Ubuntu servers for its robustness and scalability. This guide covers the steps to install PostgreSQL on Ubuntu, manage databases and users, and secure your installation.

Introduction to PostgreSQL on Ubuntu:

Ubuntu, as one of the most popular Linux distributions, provides a highly compatible environment for PostgreSQL. Installing PostgreSQL on Ubuntu enables you to run and manage databases with high efficiency, making it suitable for various applications from small development setups to large production environments.

Steps to Install PostgreSQL on Ubuntu

This guide will walk you through installing PostgreSQL on Ubuntu, configuring it, and performing some basic commands.

Step 1: Update the Package List

First, update the Ubuntu package repository list to ensure that you’re downloading the latest version:

sudo apt update

Step 2: Install PostgreSQL

After updating, install PostgreSQL and the necessary PostgreSQL client packages:

sudo apt install postgresql postgresql-contrib

This command installs PostgreSQL along with additional utilities that are commonly used for managing the database.

Step 3: Start and Enable PostgreSQL Service

Once installed, enable the PostgreSQL service to start on boot and start it immediately:

# Start PostgreSQL service
sudo systemctl start postgresql

# Enable PostgreSQL to start at boot
sudo systemctl enable postgresql

Step 4: Verify the Installation

To confirm PostgreSQL is running, use the following command:

sudo systemctl status postgresql

This command shows the status of PostgreSQL, including whether it is active.

Basic Configuration of PostgreSQL on Ubuntu

Once PostgreSQL is running, you can configure basic settings and set up your first database and user.

Switch to the PostgreSQL User

The default PostgreSQL installation creates a postgres user with superuser privileges. Switch to this user to manage databases:

sudo -i -u postgres

Create a New Database and User

Create a Database:

createdb mydatabase

Create a New User:

createuser --interactive

You'll be prompted to set options like superuser status for the new user.

Grant Privileges:

After creating the user, grant privileges on your new database:

psql -c "GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;"

Replace mydatabase and myuser with your actual database name and username.

Example: Connecting to PostgreSQL

To connect to PostgreSQL as a specific user, use the psql command:

psql -d mydatabase -U myuser

Once inside the psql shell, you can execute SQL commands directly:

Code:

-- Create a sample table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- Insert sample data
INSERT INTO customers (name, email) VALUES (‘Jeff Tresha’, '[email protected]');

Securing PostgreSQL on Ubuntu

To secure your PostgreSQL instance, configure the following:

1. Edit the pg_hba.conf File: This file controls client authentication. Open it:

Code:

sudo nano /etc/postgresql/<version>/main/pg_hba.conf

2. Enable Password Authentication: Change peer to md5 for local connections.

3. Restart PostgreSQL:

Code:

sudo systemctl restart postgresql

This setup enhances security by requiring passwords for user connections.

Summary:

With PostgreSQL installed on Ubuntu, you’re ready to start managing databases, adding users, and securing your database environment. PostgreSQL on Ubuntu is a flexible, reliable option for developing robust database-driven applications.

All PostgreSQL Questions, Answers, and Code Snippets Collection.