Welcome to this guide on installing PostgreSQL from source on your machine. PostgreSQL is a powerful, open-source object-relational database system. Today, we'll walk through the process of setting it up from scratch.

Prerequisites

apt update && apt upgrade --yes && apt install sudo locales --yes

This command will update the list of packages and their versions on your machine (apt update), upgrade your system's installed packages (apt upgrade --yes), and then install sudo and locales (apt install sudo locales --yes).

The sudo package provides ordinary users with root permissions for specific commands, and locales is a system software package that supports localization - the adaptation of a product to meet the language, cultural and other requirements of a specific locale.

dpkg-reconfigure tzdata

This command reconfigures the timezone data on your machine. It's important to set this correctly to ensure all time-related processes on your system follow your local timezone.

adduser <username>

This command creates a new user. Replace <username> with your preferred username.

echo "<username> ALL=PASSWD: ALL" > /etc/sudoers.d/<username>

This line adds the newly created user to the sudoers file, which controls which users can run what software on which machines and as which users.

su - <username>

This command switches the current user to the newly created user.

sudo apt install htop git build-essential cmake libreadline-dev zlib1g-dev flex bison libicu-dev pkgconf vim --yes

This command installs various software and libraries needed to compile and run PostgreSQL. These include a text editor (vim), version control system (git), essential compilation tools (build-essential, cmake), a couple of libraries for handling compressed data (zlib1g-dev), text parsing (flex, bison), international components for Unicode (libicu-dev), and others.

Installing PostgreSQL

Now that our system is prepared, we can download the PostgreSQL source code, configure it, and install it.

First, download the PostgreSQL source code from the official website and unpack it or clone from GitHub. Then navigate into the directory.

Next, configure the installation with debug information and additional checks:

./configure \
    --enable-debug --enable-cassert \
    CFLAGS="-ggdb -Og -fno-omit-frame-pointer" 

This command configures the source code to include debug information and enable assertion checks. The CFLAGS part sets compiler options to include debugging information in the executables (which helps when you need to debug something), disable optimizations (-Og), and not omit the frame pointer, which also helps with debugging.

make install

This command compiles the source code and installs the resulting binaries.

mkdir -p /usr/local/pgsql/data

This command creates a directory for the database files.

chown <username> /usr/local/pgsql/data

This command changes the ownership of the data directory to the new user we created earlier.

su - <username>

This command switches the current user to the new user.

Setting Environment Variables

Add these lines to your .profile file, typically located in your home directory (~/.profile):

LD_LIBRARY_PATH=/usr/local/pgsql/lib
export LD_LIBRARY_PATH
export PATH=/usr/local/pgsql/bin:$PATH
export DATA=/usr/local/pgsql/data

These commands set environment variables so that the system knows where to find the PostgreSQL executables and libraries, as well as where the data directory is.

Initializing the Database

Finally, let's initialize the database and start it up.

initdb -D $DATA

This command initializes the PostgreSQL database in the directory specified by the DATA environment variable.

pg_ctl -D $DATA start

This command starts the PostgreSQL database.

createdb test

This command creates a new database named test.

psql test

This command connects to the new test database with the psql client, and you're ready to start using PostgreSQL!

Conclusion:

Congratulations! You've successfully installed PostgreSQL from the source. This setup gives you full control over the configuration and enables debugging, which can be particularly helpful if you're developing an extension like Apache AGE or diving deeper into the PostgreSQL internals. Now you're ready to start creating and managing your databases!

Remember, when working directly with source installations, you have more control, but it also requires more responsibility for maintenance and updates. For production environments, it is generally recommended to use version-controlled packages provided by your operating system or trusted third-party repositories unless you have a good reason to compile from source.

I make these posts in order to guide people into the development of a new technology. If you find any misleading information, I urge you to comment below so I can fix it. Thanks!

Check Apache AGE: https://age.apache.org/.

Overview — Apache AGE master documentation: https://age.apache.org/age-manual/master/intro/overview.html.

GitHub - apache/age: https://github.com/apache/age