Step-by-Step Guide to Install PostgreSQL via Package Management for Apache AGE on Ubuntu
Tags: apacheage, postgres, tutorial
Published: 2023-04-26 00:56:12

PostgreSQL is a robust and reliable open-source relational database that supports ACID and is highly scalable, making it one of the most popular choices for data storage. With Apache AGE, it's possible to query and analyze graph data in PostgreSQL using the OpenCypher technology in conjunction with SQL. For this tutorial, I am using Ubuntu 22.04 LTS and will provide a step-by-step guide to install the new version of Apache AGE 1.3.0 for PostgreSQL 13. If you're curious to know what the main features of this version are, you can check out [this article](https://dev.to/wendellana/apache-age-13-release-for-postgresql-13-4081).

## Requirements

To install Apache AGE on Ubuntu, you need the following libraries:

```bash
sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison
```

## PostgreSQL Installation

To create your graph database with Apache AGE, you need PostgreSQL. Currently, the versions supported are 11, 12, and 13. You can obtain any of these versions from the Ubuntu package manager. You need to install both the `postgresql` library and the server:

```bash
sudo apt install postgresql-13 postgresql-server-dev-13
```

After installation, export the environment variable `PATH` to the executable and PostgreSQL configuration directories and `PGDATA` environment variable to the database data storage directory. When we install through the package manager, the files are installed by default in `/usr/lib/postgresql/{version}`, where `{version}` is the installed PostgreSQL version. In this case, it is version 13.

```bash
export PATH=/usr/lib/postgresql/13/bin:$PATH
export PGDATA=/usr/lib/postgresql/13/bin/data
```

## Apache AGE

To install Apache AGE, [download a compatible release](https://github.com/apache/age/releases) with your version of PostgreSQL or clone the repository and switch to the branch compatible with your version of PostgreSQL. In this case, we will install version 1.3.0 for PostgreSQL 13:

```bash
git clone https://github.com/apache/age.git
git checkout release/PG13/1.3.0
```

To proceed with the installation, export the `PG_CONFIG` variable to `pg_config` directory or run the `make` command passing the variable as an argument:

```bash
sudo make PG_CONFIG=/usr/lib/postgresql/13/bin/pg_config install
```

You can also export the variable to the same directory and then proceed with the installation:

```bash
export PG_CONFIG=/usr/lib/postgresql/13/bin/pg_config
sudo make install
```

The `pg_config` is a utility that contains installation information about PostgreSQL, such as the version installed. When running `pg_config` for the current installation, the terminal returns the following information:

```bash
BINDIR = /usr/lib/postgresql/13/bin
DOCDIR = /usr/share/doc/postgresql-doc-13
HTMLDIR = /usr/share/doc/postgresql-doc-13
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/13/server
LIBDIR = /usr/lib/x86_64-linux-gnu
PKGLIBDIR = /usr/lib/postgresql/13/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/13/man
SHAREDIR = /usr/share/postgresql/13
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/13/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE =  '--build=x86_64-linux-gnu' '--prefix=/usr' '--includedir=${prefix}/include' '--mandir=${prefix}/share/man' '--infodir=${prefix}/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--disable-option-checking' '--disable-silent-rules' '--libdir=${prefix}/lib/x86_64-linux-gnu' '--runstatedir=/run' '--disable-maintainer-mode' '--disable-dependency-tracking' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--mandir=/usr/share/postgresql/13/man' '--docdir=/usr/share/doc/postgresql-doc-13' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/13' '--bindir=/usr/lib/postgresql/13/bin' '--libdir=/usr/lib/x86_64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--with-extra-version= (Ubuntu 13.10-1.pgdg22.04+1)' '--enable-nls' '--enable-thread-safety' '--enable-debug' '--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-gssapi' '--with-ldap' '--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 'AWK=mawk' 'MKDIR_P=/bin/mkdir -p' 'PROVE=/usr/bin/prove' 'PYTHON=/usr/bin/python3' 'TAR=/bin/tar' 'XSLTPROC=xsltproc --nonet' 'CFLAGS=-g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer' 'LDFLAGS=-Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects -flto=auto -Wl,-z,relro -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-14' 'CLANG=/usr/bin/clang-14' '--with-systemd' '--with-selinux' 'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security'
CC = gcc
CPPFLAGS = -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects -flto=auto -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-14/lib -Wl,--as-needed
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm 
VERSION = PostgreSQL 13.10 (Ubuntu 13.10-1.pgdg22.04+1)
```

To verify if the installation is correct, run the regression tests:

```bash
make PG_CONFIG=/src/lib/postgresql/13/bin/pg_config installcheck
```

Se todos passarem, o console vai retornar a seguinte frase: `All 24 tests passed`.

## Testing the Installation

Let's test our installation by creating a graph in a database with the default user `postgres`. Start the PostgreSQL service and check the status:

```bash
sudo service postgresql start
service postgresql status
```

If everything is correct, a similar message will appear in the terminal indicating that the service is active:

```bash
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pr>
     Active: active (exited) since Tue 2023-04-25 19:34:59 -03; 41min ago
   Main PID: 9761 (code=exited, status=0/SUCCESS)
        CPU: 1ms

abr 25 19:34:59 carla-linux systemd[1]: Starting PostgreSQL RDBMS...
abr 25 19:34:59 carla-linux systemd[1]: Finished PostgreSQL RDBMS.
```

Now start the command-line tool:

```bash
sudo -u postgres psql
```

Use the following command to install the extension on the server:

```bash
CREATE EXTENSION age;
```

Load the extension for each AGE connection:

```bash
LOAD 'age';
```

Use the following command to create a graph:

```bash
SELECT * FROM ag_catalog.create_graph('new_graph');
```

To simplify queries, add `ag_catalog` to your `search_path` with `SET search_path = ag_catalog, "$user", public;`. In this case, you could use the following command to create the graph:

```bash
SELECT * FROM create_graph('new_graph');
```

If everything is correct, the console will return:

```bash
NOTICE:  graph "new_graph" has been created
 create_graph 
--------------
 
(1 row)
```

##Conclusion

In this tutorial, we have seen how to install PostgreSQL via package manager and use it with Apache AGE. If you want to learn more about this project, visit [https://age.apache.org/](https://age.apache.org/). If you want to know how to contribute, visit [https://github.com/apache/age](https://github.com/apache/age). All kinds of contributions are very welcome ❤️.

## Errata

My intention is to provide access to technology information through reliable sources. If you have found any incorrect information, please let your contribution in the comments below 😊.

## Related Articles
[Easy guide to install and configure PostgreSQL with Apache AGE on Windows](https://dev.to/carlasanches/easy-guide-to-install-and-configure-postgresql-with-apache-age-on-windows-n6p)
[Apache AGE 1.3 Release for PostgreSQL 13](https://dev.to/wendellana/apache-age-13-release-for-postgresql-13-4081)
[Easy Installation of PostgreSQL, AGE & Age-viewer on Mac](https://dev.to/maruf13/installation-of-postgresql-age-on-mac-4gc9)
[Installing apache age-viewer](https://dev.to/talhahahae/installing-apache-age-viewer-5dm3)

## References
Apache AGE. Apache AGE Documentation - Setup. 2023. Available at [https://age.apache.org/age-manual/master/intro/setup.html](https://age.apache.org/age-manual/master/intro/setup.html). Accessed on 04/25/2023.