メインコンテンツまでスキップ

Installing PostgreSQL on Ubuntu

Welcome to this detailed guide on installing PostgreSQL, one of the most advanced and robust open-source relational database systems, on Ubuntu. PostgreSQL, often simply called Postgres, is known for its reliability, feature robustness, and performance. It's a favorite among developers and enterprises alike for its compliance with SQL standards and its ability to handle complex queries.

Whether you're a seasoned database administrator or a newcomer to the world of databases, this guide will walk you through the process of installing PostgreSQL on your Ubuntu system step by step. We'll cover everything from the initial setup to verifying the installation, ensuring you have a solid foundation to start working with Postgres.

Prerequisites​

Before we begin, ensure that you have:

  • An Ubuntu system (Desktop or Server edition) with sudo privileges.
  • Access to a terminal window (Ctrl+Alt+T on Ubuntu Desktop).
  • An active internet connection to download the necessary packages.

Step 1: Update Package Lists​

The first step in any package installation is to update your system's package lists to ensure you have the latest information on available packages and their dependencies.

sudo apt update

Step 2: Install PostgreSQL​

With your package lists updated, you can now install PostgreSQL using the apt package management tool.

sudo apt install postgresql postgresql-contrib

The postgresql-contrib package contains additional utilities and functionality that extend PostgreSQL's capabilities.

You should see a series of messages as packages are downloaded and installed. Upon completion, you'll be notified that the installation is finished.

Step 3: Verify Installation​

After installation, PostgreSQL should be running. You can check its status with the following command:

sudo systemctl status postgresql

Output:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2023-04-05 15:00:00 UTC; 15min ago
    Process: 12345 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 12345 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4915)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

The active (exited) status might seem counterintuitive, but it's normal for PostgreSQL. The actual PostgreSQL server processes are managed by a separate service, postgresql@version-main, which is started automatically when needed.

Step 4: Access the PostgreSQL Database Server​

PostgreSQL creates a user named postgres during installation. You'll need to switch to this user to interact with the database server.

sudo -i -u postgres

Now, access the PostgreSQL command-line interface, psql:

psql

Output:

psql (13.1 (Ubuntu 13.1-1.pgdg20.04+1))
Type "help" for help.

postgres=#

The postgres=# prompt indicates that you are now interacting with the PostgreSQL database server.

Step 5: Create a New Database User and Database​

By default, PostgreSQL is set up with a postgres user and database. For security and organizational purposes, it's best practice to create a new user and database for your applications.

Let's create a new user and database:

CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydatabase OWNER myuser;

Note: Replace myuser and mypassword with your desired username and password. Also, use a strong password in a real-world scenario.

Step 6: Configure PostgreSQL for Remote Access (Optional)​

If you need to access your PostgreSQL server from a remote machine, you'll need to configure it to listen on all network interfaces and modify the pg_hba.conf file for authentication.

Edit the PostgreSQL configuration file:

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

Find the line that specifies listen_addresses and change it to:

listen_addresses = '*'

Next, edit the pg_hba.conf file:

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

Add a line to allow your remote IP address to connect:

host    all             myuser          192.168.1.2/32          md5

Replace 192.168.1.2/32 with your remote IP address.

After making these changes, restart PostgreSQL to apply them:

sudo systemctl restart postgresql

Step 7: Secure Your PostgreSQL Installation​

Security is paramount, especially for databases. Here are some tips to secure your PostgreSQL installation:

  • Regularly update your system and PostgreSQL to the latest versions.
  • Use strong passwords and consider additional authentication methods like peer or ident for local connections.
  • Limit network access to trusted IP addresses.
  • Use SSL connections for remote access.
  • Regularly back up your databases.

You have now successfully installed PostgreSQL on your Ubuntu system. You've learned how to access the database server, create new users and databases, and configure PostgreSQL for remote access.