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

Installing PostgreSQL on CentOS: A Comprehensive Guide

This guide will walk you through the process of setting up PostgreSQL on your CentOS system with ease.

Why PostgreSQL?​

Before we dive into the installation process, let's briefly discuss why PostgreSQL stands out in the crowded database market:

  • Extensibility: PostgreSQL allows users to define their own data types and functions.
  • MVCC (Multiversion Concurrency Control): This feature provides concurrent access to the database without read locks, which is crucial for high transactional systems.
  • Full ACID compliance: Ensures that database transactions are processed reliably.
  • Support for complex queries: PostgreSQL supports a wide range of query capabilities, including full SQL subselects and joins.
  • Open Source: It's free to use and has a vibrant community that contributes to its continuous improvement.

Prerequisites​

  • A CentOS system (CentOS 7 or 8) with root access or a user with sudo privileges.
  • A stable internet connection to download the necessary packages.

Step 1: Setting Up the Repository​

PostgreSQL is not included in the default CentOS repositories. Therefore, we need to add the PostgreSQL YUM repository to our system.

  1. Install the yum-utils package, which provides the yum-config-manager utility to manage YUM repositories:

    sudo yum install -y yum-utils

  2. Import the PostgreSQL repository RPM:

    sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

    For CentOS 8, replace EL-7-x86_64 with EL-8-x86_64 in the URL.

Step 2: Installing PostgreSQL​

With the repository in place, you can now install PostgreSQL:

  1. Update the YUM cache to include the new PostgreSQL packages:

    sudo yum makecache

  2. Install the PostgreSQL server and associated tools:

    sudo yum install -y postgresql13-server postgresql13-contrib

    Replace 13 with the version number you wish to install.

Step 3: Initializing the Database Cluster​

After installation, you need to initialize the database cluster:

  1. Initialize the database with the postgresql-setup command:

    sudo postgresql-setup initdb

Step 4: Starting and Enabling PostgreSQL​

Start the PostgreSQL service and ensure it starts automatically on boot:

  1. Start the PostgreSQL service:

    sudo systemctl start postgresql

  2. Enable PostgreSQL to start on boot:

    sudo systemctl enable postgresql

Step 5: Configuring PostgreSQL​

PostgreSQL is now running, but it's a good idea to make some basic configurations:

  1. Edit the postgresql.conf file to allow for connections to the database server:

    sudo nano /var/lib/pgsql/13/data/postgresql.conf

    Find the listen_addresses line and change it to:

    listen_addresses = '*'

  2. Update the pg_hba.conf file to allow password authentication:

    sudo nano /var/lib/pgsql/13/data/pg_hba.conf

    Add the following line to allow connections from localhost:

    host    all             all             127.0.0.1/32            md5

  3. Apply the changes by reloading the PostgreSQL service:

    sudo systemctl reload postgresql

Step 6: Creating a New Role and Database​

By default, PostgreSQL creates a user named postgres with the role of a superuser. For security reasons, it's best practice to create a new role for your applications:

  1. Switch to the postgres user:

    sudo -i -u postgres

  2. Access the PostgreSQL prompt:

    psql

  3. Create a new role and database:

    CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';
    CREATE DATABASE mydatabase OWNER myuser;

    Replace myuser and mypassword with your desired username and password.

  4. Grant privileges to the new role:

    GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

  5. Exit the PostgreSQL prompt:

    \q

Step 7: Testing the Connection​

Test the connection to the PostgreSQL server using the new role:

  1. Use the psql command to connect to the database:

    psql -U myuser -d mydatabase -h 127.0.0.1

    You should be prompted for the password for myuser.

What to Watch Out For​

  • Firewall Settings: Ensure that your firewall allows traffic on the PostgreSQL port (default is 5432).
  • SELinux: If SELinux is enforcing, you may need to adjust policies to allow PostgreSQL to listen on the network.
  • Updates: Keep an eye on updates for PostgreSQL and apply them regularly to maintain security and stability.

You have successfully installed and configured PostgreSQL on your CentOS system. You're now ready to develop and deploy applications using one of the most powerful and flexible database systems available.