Easy guide to install and configure PostgreSQL with Apache AGE on Windows

What is PostgreSQL

PostgreSQL is an open-source relational database system, created in 1986. The system uses SQL language and has several features for data storage. Its main characteristics are robustness, reliability, and scalability. Another important feature is its support for ACID properties (Atomicity, Consistency, Isolation, and Durability). This makes it one of the most popular databases in the world!

What is Apache AGE

Apache AGE is an extension for PostgreSQL that provides data querying in graphs. This is important because it enables the use of optimization and graph search algorithms for data analysis. The extension uses OpenCypher technology, which allows the use of Cypher language along with SQL to query the relational database using graphs.

How to configure PostgreSQL with Apache AGE on Windows

Unfortunately, Apache AGE does not have a Windows installation yet. However, it is possible to use it by using Windows Subsystem for Linux (WSL). If you have not set it up on your machine yet, follow Step 1 to set it up. If you have already done it, you can skip to Step 2.

1. Setting up WSL

To enable WSL, go to Control Panel > Programs and Features > Turn Windows features on or off and check the option "Windows Subsystem for Linux". Then, restart your computer to apply the changes.

If you are using Windows 10 version 2004 and higher (Build 19041 and higher) or Windows 11, installing WSL will only require a single command. Open the command prompt or PowerShell as an administrator and enter the following command:

wsl --install

This will install Ubuntu by default. This will be the Linux distribution used throughout the rest of the tutorial. If you want to install another distribution or have a different version of Windows, consult the complete documentation here.

You can use the Linux terminal through the apps available in the Windows Store. Just search for the desired distribution and click Install. In this tutorial, I'm using Ubuntu 20.04.5 LTS. When you open the app, it may take a few minutes to complete the installation. Then, a username and password will be requested. Keep the password in a safe place. It will be requested every time a command is run as an administrator with sudo.

With WSL installed, it becomes easy to configure PostgreSQL and Apache AGE. Basically, just open the Ubuntu terminal and follow the same steps as the Linux configuration. The next steps teach how to do this configuration.

2. Setting up PostgreSQL

Currently, Apache AGE is only compatible with versions 11 and 12 of PostgreSQL. If you already have one of these versions installed on your machine, you can skip to Step 3, where I teach how to configure Apache AGE. If you don't have it, an easy way to install PostgreSQL 12 in a customized way is through the source code. To follow this tutorial, you need to have git and some PostgreSQL dependencies installed. You can install them through the following command:

sudo apt install git libreadline-dev zlib1g-dev bison flex build-essential    

1. Create a directory in your workspace and clone the repository.
   

   mkdir age_project
   cd age_project
   git clone https://git.postgresql.org/git/postgresql.git
   

2. Access the postgresql directory and switch to version 12.
   

   cd postgresql
   git checkout REL_12_STABLE
   

3. Then, compile the code by passing the directory where the binaries will be installed.
   

   ./configure --prefix=/usr/local/pgsql-12
   

4. Compile the PostgreSQL code. The first time, all files will be compiled. This may take a little while.
   

   make
   

5. After compilation, create the installation directory, add user permission to install by replacing [user] with your username and install PostgreSQL.
   

   sudo mkdir /usr/local/pgsql-12
   sudo chown [user] /usr/local/pgsql-12
   make install 
   

6. Add the environment variables.
   

   export PATH=/usr/local/pgsql-12/bin/:$PATH
   export PGDATA=/usr/local/pgsql-12/bin/data
   

7. Great! PostgreSQL is now installed. To test it, initialize the cluster with the following command:
   

   initdb
   

8. Start the server.
   

   pg_ctl start -l log
   

9. Finally, start PostgreSQL.
   

   psql postgres
   

10. To exit and stop the server, type \q and run the following command:
    

    pg_ctl stop -l log
    

For more details about the installation, you can check the official PostgreSQL documentation.

3. Configuring Apache AGE

You can also configure AGE through the source code using the following steps:

1. Clone the AGE repository inside the age-project directory created in Step 2:
   

   git clone https://github.com/apache/age.git
   

2. Switch to the latest release. Currently, the latest stable release is 1.1.0.
   

   cd age
   git checkout release/PG12/1.1.0
   

3. Set the PG_CONFIG environment variable.
   

   export PG_CONFIG=/usr/local/pgsql-12/bin/pg_config
   

4. Finally, install the extension.
   

   make install
   

All set! To test the installation, create a database instance using steps 7-9 in Step 2 and use the following command to install AGE on the server:

CREATE EXTENSION age;    

For each AGE connection, you must load the extension.

LOAD 'age';    

If everything is correct, the system will return something similar to the following messages:

psql (12.14)
Type "help" for help.

postgres=# CREATE EXTENSION age;
CREATE EXTENSION
postgres=# LOAD 'age';
LOAD
postgres=#

For more details, you can consult the official Apache AGE documentation.

Related Articles

Here are some articles from AGEDB community that may be useful for your first projects with Apache AGE:

What is APACHE AGE?

Exploring the Functions of Apache AGE: A Comprehensive Guide (To Be Continued)

Apache AGE Tutorial - Creating Graphs, Nodes and Edges

References

PostgreSQL
Apache AGE
Setup — Apache AGE master documentation

Contribute to Apache AGE

Apache AGE website: https://age.apache.org/
Apache AGE Github: https://github.com/apache/age