How to Install PostgreSQL from Source
Published: 2023-07-28 19:15:55

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**

```bash
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.

```bash
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.

```bash
adduser <username>
```

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

```bash
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.

```bash
su - <username>
```

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

```bash
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:

```bash
./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.

```bash
make install
```

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

```bash
mkdir -p /usr/local/pgsql/data
```

This command creates a directory for the database files.

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

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

```bash
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`):

```bash
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.

```bash
initdb -D $DATA
```

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

```bash
pg_ctl -D $DATA start
```

This command starts the PostgreSQL database.

```bash
createdb test
```

This command creates a new database named `test`.

```bash
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