Yuan Yao, An Oracle ACE's Blog

In this guide, I’ll walk you through the process of creating a dedicated user for PostgreSQL, compiling, and installing PostgreSQL 17.2 from source on a Red Hat-based system. This approach allows you to customize your PostgreSQL installation for specific needs, such as enabling debugging information. The steps also cover configuring logging, verifying the installation, and ensuring the database server is up and running.

Prerequisites

Before we begin, ensure you have the necessary development packages installed. These are required to compile PostgreSQL from source.

Create the PostgreSQL User

First, create a dedicated system user for running PostgreSQL services. This ensures better security and separation of privileges:

sudo useradd -m postgres

• useradd -m postgres: Creates a new user named postgres with a home directory.

Switch to the PostgreSQL User

All subsequent commands should be run as the postgres user. Switch to this user:

sudo su - postgres

Install Required Packages

As the postgres user, execute the following command to install the required development libraries:

sudo yum install -y readline-devel libicu-devel

• readline-devel: Provides libraries for readline, which allows PostgreSQL to have command-line editing capabilities.
• libicu-devel: Provides support for Unicode and globalization features.

Download and Extract PostgreSQL Source Code

Next, download the PostgreSQL source code from the official site and extract it:

wget <https://ftp.postgresql.org/pub/source/v17.2/postgresql-17.2.tar.bz2>
tar -xvjf postgresql-17.2.tar.bz2
cd postgresql-17.2/

• wget: Downloads files from the web.
• tar -xvjf: Extracts the tar.bz2 archive. The options xvjf stand for extract, verbose, bzip2, and file, respectively.

Compile PostgreSQL

With the source code ready, configure the build environment and compile PostgreSQL:

./configure --prefix=/u02/postgres/server CFLAGS="-g"
make
make install

• ./configure --prefix=/u02/postgres/server: Prepares the build environment, specifying the installation directory.
• CFLAGS="-g": Adds debugging symbols to the compiled binaries, which is useful for troubleshooting.
• make: Compiles the source code.
• make install: Installs the compiled binaries to the specified directory.

Initialize the Database Cluster

Now, initialize the PostgreSQL data directory where the database files will reside:

/u02/postgres/server/bin/initdb -D /u02/postgres/data

This command sets up the directory structure and initial database configuration.

Configure Logging

To ensure PostgreSQL logs are recorded in a file, update the configuration as follows:

1. Edit the postgresql.conf File:
   • Locate and edit the postgresql.conf file to configure logging settings: nano /u02/postgres/data/postgresql.conf Adjust the following parameters: log_destination = 'stderr' logging_collector = on log_directory = 'log' # relative to data directory log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 100MB These settings enable logging, specify the log directory, define file naming patterns, and set rotation policies.
2. Create the Log Directory: If you’ve specified a relative directory for logs (e.g., log), make sure it exists: mkdir /u02/postgres/data/log

Start the PostgreSQL Server

With everything set up, start the PostgreSQL server:

/u02/postgres/server/bin/pg_ctl start -D /u02/postgres/data

You should see a confirmation message indicating the server has started.

Verify the Connection

Finally, verify your ability to connect to the PostgreSQL server using psql, the interactive terminal for PostgreSQL:

psql -h localhost

Once connected, you can interact with your database. Typing \\\\q exits the psql session.

Verify Logging

Check the log files in the specified directory to ensure logging is working correctly:

ls /u02/postgres/data/log

Conclusion

Compiling PostgreSQL from source gives you control over the build configuration and optimizations. This guide provides a straightforward approach to setting up PostgreSQL 17.2 on a Red Hat system, ensuring you can customize, debug, and log as needed. For further customization, you might explore additional configure options or adjust settings in postgresql.conf.