How to Set Up a PostgreSQL Database on Ubuntu

Introduction

PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development. It boasts a strong reputation for reliability, feature robustness, and performance. This tutorial aims to provide a comprehensive guide for setting up a PostgreSQL database on Ubuntu, targeting users who have some experience with Linux systems and are familiar with database concepts.

Prerequisites

Before we begin, ensure that you have the following:

An Ubuntu system (preferably the latest LTS version).
Root or sudo user privileges.
Basic understanding of Linux command line.

Step 1: Update the System

First, it’s crucial to update your package lists to ensure you have the latest information on the newest versions of packages and their dependencies.

sudo apt update
sudo apt upgrade -yCode language: Bash (bash)

Step 2: Install PostgreSQL

Ubuntu includes PostgreSQL by default in its repositories, which makes the installation straightforward. Run the following command to install PostgreSQL and the contrib package, which provides additional functionalities.

sudo apt install postgresql postgresql-contrib -yCode language: Bash (bash)

Step 3: Verify Installation

After the installation, you can verify the PostgreSQL service status to ensure it is running correctly.

sudo systemctl status postgresqlCode language: Bash (bash)

You should see output indicating that PostgreSQL is active and running.

Step 4: PostgreSQL Basic Configuration

PostgreSQL operates with a default user called postgres. To interact with the PostgreSQL database, you need to switch to this user.

sudo -i -u postgresCode language: Bash (bash)

Access PostgreSQL Prompt

While logged in as the postgres user, open the PostgreSQL prompt using the psql command.

psqlCode language: Bash (bash)

You should see the PostgreSQL prompt:

postgres=#Code language: Bash (bash)

Create a New Role

To create a new PostgreSQL role (user), use the following SQL command. Replace username with your desired username.

CREATE ROLE username WITH LOGIN PASSWORD 'password';Code language: SQL (Structured Query Language) (sql)

Grant Superuser Privileges

Optionally, you can grant this user superuser privileges:

ALTER ROLE username WITH SUPERUSER;Code language: SQL (Structured Query Language) (sql)

Exit PostgreSQL Prompt

To exit the PostgreSQL prompt, type:

\qCode language: SQL (Structured Query Language) (sql)

Step 5: Create a New Database

Still logged in as the postgres user, you can create a new database. Replace mydatabase with your desired database name.

createdb mydatabaseCode language: Bash (bash)

Step 6: Adjusting Authentication Methods

PostgreSQL uses a file called pg_hba.conf to control client authentication. By default, this file is located in the /etc/postgresql/12/main/ directory (version number may vary).

sudo nano /etc/postgresql/12/main/pg_hba.confCode language: Bash (bash)

In this file, you can adjust authentication methods for local connections. For example, to use MD5-encrypted passwords for all users, you might modify the following lines:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     md5Code language: plaintext (plaintext)

Save and close the file (Ctrl + X, Y, Enter).

Step 7: Restart PostgreSQL Service

After making changes to the configuration file, restart the PostgreSQL service to apply the changes.

sudo systemctl restart postgresqlCode language: Bash (bash)

Step 8: Connect to the PostgreSQL Database

You can now connect to the newly created database using the new role. Exit the postgres user if you haven’t already.

exitCode language: Bash (bash)

Connect to your database:

psql -U username -d mydatabase -h 127.0.0.1 -WCode language: Bash (bash)

You’ll be prompted to enter the password for the username role.

Step 9: Basic PostgreSQL Operations

Once connected to your database, you can perform basic operations. Here are a few examples:

Create a Table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary NUMERIC
);Code language: SQL (Structured Query Language) (sql)

Insert Data

INSERT INTO employees (name, position, salary) VALUES ('Alice', 'Engineer', 60000);
INSERT INTO employees (name, position, salary) VALUES ('Bob', 'Manager', 80000);Code language: SQL (Structured Query Language) (sql)

Query Data

SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

Update Data

UPDATE employees SET salary = 65000 WHERE name = 'Alice';Code language: SQL (Structured Query Language) (sql)

Delete Data

DELETE FROM employees WHERE name = 'Bob';Code language: SQL (Structured Query Language) (sql)

Step 10: Backup and Restore

Backing up and restoring your PostgreSQL database is crucial for data safety.

Backup a Database

You can use the pg_dump tool to back up a database. Run the following command, replacing mydatabase and backup.sql with your database name and desired backup file name.

pg_dump -U username -d mydatabase -F c -f backup.sqlCode language: Bash (bash)

Restore a Database

To restore a database, use the pg_restore tool. First, create a new database to restore the data into:

createdb newdatabaseCode language: Bash (bash)

Then, restore the backup:

pg_restore -U username -d newdatabase -1 backup.sqlCode language: Bash (bash)

Step 11: Automating Tasks with Cron

To automate backups, you can create a cron job. Edit the crontab file:

crontab -eCode language: Bash (bash)

Add a cron job to back up your database daily at 2 AM:

0 2 * * * pg_dump -U username -d mydatabase -F c -f /path/to/backup/backup_$(date +\%F).sqlCode language: Bash (bash)

Step 12: Securing PostgreSQL

Securing your PostgreSQL installation is vital. Here are a few recommendations:

Use Strong Passwords

Ensure all your PostgreSQL roles use strong passwords.

Restrict Remote Access

By default, PostgreSQL listens on localhost. To allow remote connections, modify the postgresql.conf file:

sudo nano /etc/postgresql/12/main/postgresql.confCode language: Bash (bash)

Uncomment and set the listen_addresses line to:

listen_addresses = 'localhost'Code language: plaintext (plaintext)

To allow specific IP addresses, adjust the pg_hba.conf file accordingly.

Enable SSL

Enabling SSL encrypts connections between clients and the database server. You can enable SSL in the postgresql.conf file:

ssl = onCode language: plaintext (plaintext)

You’ll need SSL certificates, which you can generate or obtain from a certificate authority.

Step 13: Performance Tuning

Optimizing PostgreSQL performance involves tuning various settings in the postgresql.conf file. Key settings to consider include:

Shared Buffers

shared_buffers = 25% of total RAMCode language: plaintext (plaintext)

Work Mem

work_mem = 4MB or more, depending on your workloadCode language: plaintext (plaintext)

Maintenance Work Mem

maintenance_work_mem = 64MB or moreCode language: plaintext (plaintext)

Effective Cache Size

effective_cache_size = 50% to 75% of total RAMCode language: plaintext (plaintext)

Conclusion

Setting up PostgreSQL on Ubuntu involves several steps, from installation and configuration to securing and optimizing your database. By following this comprehensive guide, you should have a robust PostgreSQL installation ready for development or production use. Remember, ongoing maintenance and regular backups are crucial to ensure your database remains secure and performant.