Sling Academy
Home/PostgreSQL/How to set up and configure PostgreSQL on Ubuntu

How to set up and configure PostgreSQL on Ubuntu

Last updated: January 04, 2024

Introduction

PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development. This tutorial will guide you through the process of installing and configuring PostgreSQL on an Ubuntu system, with step-by-step instructions and code examples.

Prerequisites

Before you begin, ensure that you have a machine running Ubuntu 20.04 or later with sudo privileges. It’s also a good idea to update your package list:

sudo apt-get update

Installation

Step 1: Installing PostgreSQL

To install PostgreSQL on your Ubuntu machine, run the following command:

sudo apt-get install postgresql postgresql-contrib

This command installs PostgreSQL along with a collection of additional utilities and functionalities that enhance the PostgreSQL experience.

Step 2: Verifying PostgreSQL Installation

After the installation is complete, PostgreSQL should automatically start. To verify that PostgreSQL is running, use the following command:

sudo systemctl status postgresql.service

Basic Configuration

The default PostgreSQL setup on Ubuntu is good for development, but for production environments, you’ll need to fine-tune your configuration.

Changing the PostgreSQL default user password

It’s important to secure your database with a password:

sudo -u postgres psql
\password

When prompted, enter a secure password for the default PostgreSQL user ‘postgres’.

Creating a New Role

Create a new role using the PostgreSQL’s interactive shell:

createuser --interactive --pwprompt

Follow the instructions to create a new role with login privileges and a password.

Advanced Configuration

After initial setup, consider these advanced configurations to optimize your PostgreSQL installation.

Configuring the pg_hba.conf File

This file controls which hosts are allowed to connect, who can connect, and which authentication method is used. Open the file with your preferred text editor:

sudo nano /etc/postgresql/12/main/pg_hba.conf

Make necessary changes to the file according to your connection policy and save it.

Tuning PostgreSQL Performance

Edit the main PostgreSQL configuration file for performance tuning:

sudo nano /etc/postgresql/12/main/postgresql.conf

Adjust memory-related parameters like ‘shared_buffers’ and ‘work_mem’ based on your server’s resources. Consult the PostgreSQL documentation for more details.

Setting Up a Remote Connection

To allow remote connections to your database, you might need to edit ‘postgresql.conf’ and change ‘listen_addresses’ from ‘localhost’ to ‘*’ or the specific IP addresses you want to allow.

listen_addresses = '*'

Remember to adjust your firewall settings to allow traffic on the default PostgreSQL port, 5432.

Creating and Managing Databases

Creating a New Database

Use the createdb command:

createdb mydb

This creates a new database ‘mydb’.

Accessing Your Database

To start using your new database, connect to it using psql:

psql -d mydb

Security Considerations

Always ensure that you secure your database:

  • Regularly update your PostgreSQL installation.
  • Use strong passwords.
  • Configure SSL for encrypted connections.
  • Restrict access to trusted hosts.

Backup and Restore

Creating Backups

Use the pg_dump utility to backup your database:

pg_dump mydb > mydb_backup.sql

Restoring a Database

To restore a database from a backup, use pg_restore or psql:

psql mydb < mydb_backup.sql

Maintenance and Monitoring

Regularly monitor your database’s performance using the built-in stats and logs. Automate vacuum and analyze operations to maintain your database efficiency.

Conclusion

Setting up and configuring PostgreSQL on Ubuntu is a straightforward process when followed step by step. Remember to secure your installation, make regular backups, and perform routine maintenance for optimal database performance. With these practices, your PostgreSQL server will be ready to handle production workloads on an Ubuntu system.

Next Article: PostgreSQL vs MySQL: Which is Better for Your Database Needs?

Previous Article: How to set up PostgreSQL and pgAdmin on Mac

Series: PostgreSQL Tutorials: From Basic to Advanced

PostgreSQL

You May Also Like

  • PostgreSQL with TimescaleDB: Querying Time-Series Data with SQL
  • PostgreSQL Full-Text Search with Boolean Operators
  • Filtering Stop Words in PostgreSQL Full-Text Search
  • PostgreSQL command-line cheat sheet
  • How to Perform Efficient Rolling Aggregations with TimescaleDB
  • PostgreSQL with TimescaleDB: Migrating from Traditional Relational Models
  • Best Practices for Maintaining PostgreSQL and TimescaleDB Databases
  • PostgreSQL with TimescaleDB: Building a High-Performance Analytics Engine
  • Integrating PostgreSQL and TimescaleDB with Machine Learning Models
  • PostgreSQL with TimescaleDB: Implementing Temporal Data Analysis
  • Combining PostgreSQL, TimescaleDB, and Airflow for Data Workflows
  • PostgreSQL with TimescaleDB: Visualizing Real-Time Data with Superset
  • Using PostgreSQL with TimescaleDB for Energy Consumption Analysis
  • PostgreSQL with TimescaleDB: How to Query Massive Datasets Efficiently
  • Best Practices for Writing Time-Series Queries in PostgreSQL with TimescaleDB
  • PostgreSQL with TimescaleDB: Implementing Batch Data Processing
  • Using PostgreSQL with TimescaleDB for Network Traffic Analysis
  • PostgreSQL with TimescaleDB: Troubleshooting Common Performance Issues
  • Building an IoT Data Pipeline with PostgreSQL and TimescaleDB