Build and Use PostgreSQL 18 (Beta) from Git on Ubuntu Linux 24.04 LTS

Overview

If you're not familiar with PostgreSQL, it is a powerful, stable, and highly configurable open source relational database. Since we're developers & our database is a core, networked system function, we want to work with the latest release and its source to facilitate optimization, customization, and experimentation. Below is a basic guide on how we built and configured PostgreSQL 18 (master branch) from Git on Ubuntu Linux.

Install and Build from Git

Before we begin, let's see if we have PostgreSQL packages already installed on our Ubuntu system. We show various ways below to detect whether remants of postgres exist on our system.

$ which psql
$ 

$ ps -e | grep post
$

$ dpkg -l | grep postgres
$ 

$ dpkg -l | grep libpq
$

$ ldconfig -p | grep libpq
$

If it is installed, you can perform a remove or purge. Note that a purge will remove the configuration files along with the packages.

$ sudo apt purge postgresql-client-common postgresql-common postgresql

Next let's clone the postgresql git repo. We'll follow the official current postgreSQL documentation, and there's also a wiki page for Working with Git

$ mkdir -p /build; cd /build	

$ git clone git://git.postgresql.org/git/postgresql.git

$ cd postgresql/

We're going to work with the master branch, but we could have also chosen to checkout REL_17_STABLE:

$ git branch -r
  origin/HEAD -> origin/master
  ...
  origin/REL_17_STABLE
  ...
  origin/master


$ ls
aclocal.m4  config  configure  configure.ac  contrib  COPYRIGHT  doc  GNUmakefile.in  HISTORY  Makefile  meson.build  meson_options.txt  README.md  src

We can see from the directory list above, that we're dealing with an autotools project (e.g., configure script).

Also, we always prefer to build outside the source directory whenever possible, so let's create a new build directory and run configure from there. Note that you may need to install some packages before configure will succeed (e.g., libreadline6-dev, systemtap-sdt-dev, zlib1g-dev, libssl-dev, libpam0g-dev, and python-dev). Also, make sure you have flex and bison installed. If you get stuck on finding a package missing during configure, then take a look at the Ubuntu Packages Search page

$ sudo apt install pkgconf  libicu-dev libreadline-dev systemtap-sdt-dev zlib1g-dev libssl-dev libpam0g-dev python3-dev bison flex

$ openssl version 	
OpenSSL 3.0.13 30 Jan 2024 (Library: OpenSSL 3.0.13 30 Jan 2024)

$ mkdir build; cd build  	# /build/postgresql/build

$ ../configure --prefix=/opt/pgsql --with-python --with-openssl --with-pam --enable-debug --enable-depend

$ make
...
make[1]: Leaving directory '/build/postgresql/build/src'
make -C config all
make[1]: Entering directory '/build/postgresql/build/config'
make[1]: Nothing to be done for 'all'.
make[1]: Leaving directory '/build/postgresql/build/config'

Note that configure has many build and install options. Run ../configure --help for more information.

Next we need to install postgreSQL. Keep in mind that we chose to customize the install path using --prefix=/opt/pgsql/

$ make install  # no need for sudo since we took ownership of /opt
...
make -C config install
make[1]: Entering directory '/build/postgresql/build/config'
/usr/bin/mkdir -p '/opt/pgsql/lib/pgxs/config'
/usr/bin/install -c -m 755 /build/postgresql/build/../config/install-sh '/opt/pgsql/lib/pgxs/config/install-sh'
/usr/bin/install -c -m 755 /build/postgresql/build/../config/missing '/opt/pgsql/lib/pgxs/config/missing'
make[1]: Leaving directory '/build/postgresql/build/config'

Let's now do some checking and general housekeeping:

$ cd /opt/pgsql/
$ tree -d -L 2
.
├── bin
├── include
│   ├── informix
│   ├── internal
│   ├── libpq
│   └── server
├── lib
│   ├── pgxs
│   └── pkgconfig
└── share
    ├── extension
    ├── timezone
    ├── timezonesets
    └── tsearch_data

$ find . -name '*.conf.sample'
./share/pg_hba.conf.sample
./share/pg_service.conf.sample
./share/pg_ident.conf.sample
./share/postgresql.conf.sample

Now configure the dynamic linker to find our postgreSQL libraries:

$ cd /etc/ld.so.conf.d/

# edit libc.conf as root to append /opt/pgsql/lib
$ more /etc/ld.so.conf.d/libc.conf 
/usr/local/lib
/opt/pgsql/lib


$ sudo ldconfig

$ ldconfig -p | grep libpq
        libpqwalreceiver.so (libc6,x86-64) => /opt/pgsql/lib/libpqwalreceiver.so
        libpq.so.5 (libc6,x86-64) => /opt/pgsql/lib/libpq.so.5
        libpq.so (libc6,x86-64) => /opt/pgsql/lib/libpq.so

Basic Configure of PostgreSQL

We want to work with a separate user account, and we'll follow the convention of using the user account postgres.

$ sudo adduser --system --home /opt/pgsql/home  --shell /bin/bash --group postgres
info: Selecting UID from range 100 to 999 ...
info: Selecting GID from range 100 to 999 ...
info: Adding system user `postgres' (UID 123) ...
info: Adding new group `postgres' (GID 124) ...
info: Adding new user `postgres' (UID 123) with group `postgres' ...
info: Creating home directory `/opt/pgsql/home' ...


$ sudo adduser postgres ssl-cert
info: Adding user `postgres' to group `ssl-cert' ...

$ sudo adduser postgres sudo		# or optionally perform a visudo
info: Adding user `postgres' to group `sudo' ...

$ sudo passwd postgres			# set our user passwd

$ su postgres
$ cd
$ pwd
/opt/pgsql/home

$ sudo chown -R postgres:postgres /opt/pgsql

$ /opt/pgsql/bin/initdb -D /opt/pgsql/data --pwprompt

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password: 
Enter it again: 

creating directory /opt/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /opt/pgsql/bin/pg_ctl -D /opt/pgsql/data -l logfile start

Note the warning above about enabling trust authentication. Carefully review the documentation and setting in /opt/pgsql/data/pg_hba.conf

Starting the server and interacting with it

Before we start the server, let's modify the PATH environment variable to include /opt/pgsql/bin

$ cd /opt/pgsql/home

$ echo 'export PATH=$PATH:/opt/pgsql/bin' >> .bashrc	# use single quotes so not to expand $PATH

$ echo 'export PGDATA=/opt/pgsql/data' >> .bashrc

$ source ./.bashrc

$ pg_ctl start -l logfile
server starting

$ ps -e | grep postgres
20853 ?        00:00:00 postgres
20855 ?        00:00:00 postgres
...
 
$ psql
psql (18beta1)
Type "help" for help.

postgres=# 

Some basic interaction on the command line:

# select version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 18beta1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
(1 row)


# \list
                                                     List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
(3 rows)


# \d
Did not find any relations.

# select 2+2;
----------
        4
(1 row)

#\q

Create an example database

Below we create a minimal database, table, and table entry.

$ psql 

# create role dbowner with createdb login;
CREATE ROLE

# create database testdb owner dbowner;
CREATE DATABASE

# \l
                                    List of databases
    Name     |    Owner    | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------+-------------+----------+-------------+-------------+-----------------------
 testdb | dbowner | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
...

#\q

$ psql testdb -U dbowner

# CREATE TABLE users (
	uid integer PRIMARY KEY,
	name varchar(32)
);
CREATE TABLE

# INSERT INTO users VALUES (1, 'Tony');
INSERT 0 1

# select * from users;
 uid | name 
-----+------
   1 | Tony

Access our database using the C language

For this next step, we'll rely on some guidance provided by the following documentation on postgresql.org:

• Building libpq Programs
• Example Programs
• libpq - C Library: Command Execution Functions

Let's first build and run the testlibpq.c example provided in the above reference.

$ gcc  -O0 -g -I/opt/pgsql/include testlibpq.c -L/opt/pgsql/lib -lpq -o testlibpq

$ ./testlibpq

oid            datname        datdba         encoding       datcollate     datctype       datistemplate  datallowconn   datconnlimit   datlastsysoid  datfrozenxid   datminmxid     dattablespace  datacl         

12695          postgres       10             6              en_US.UTF-8    en_US.UTF-8    f              t              -1             12694          480            1              1663                          
16385          testdb         16384          6              en_US.UTF-8    en_US.UTF-8    f              t              -1             12694          480            1              1663                          
1              template1      10             6              en_US.UTF-8    en_US.UTF-8    t              t              -1             12694          480            1              1663           {=c/postgres,postgres=CTc/postgres}
12694          template0      10             6              en_US.UTF-8    en_US.UTF-8    t              f              -1             12694          480            1              1663           {=c/postgres,postgres=CTc/postgres}

Next let's create a simple C program that accesses our database and prints out the first row from our users table:

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>


int main() {

    PGconn *conn = PQconnectdb("dbname=testdb");
    if (PQstatus(conn) == CONNECTION_BAD) {
        fprintf(stderr, "Connection to database failed: %s\n", PQerrorMessage(conn));
        PQfinish(conn);
        exit(1);
    }

    PGresult *res = PQexec(conn, "SELECT * FROM users");
    printf("id: %s\n", PQgetvalue(res, 0, 0));
    printf("name: %s\n", PQgetvalue(res, 0, 1));

    PQclear(res);
    PQfinish(conn);
    return 0;
}

Build it and run it (as postgres):

$ gcc -O0 -g  -I/opt/pgsql/include testlibpq2.c -L/opt/pgsql/lib -lpq -o testlibpq2

$ $ ./testlibpq2 
id: 1
name: Tony

Access our database using Python

For Python access, we'll turn to the popular, third-party, open source library Psycopg. As stated on its web site: "Psycopg is the most popular PostgreSQL adapter for the Python programming language. At its core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL."

Psycopg 2/3 wraps around the libpq library, which we used in the previous C programming examples. Below we clone psycopg2 and take a brief look at the source:

Note that we're performing the following steps as a typical Ubuntu Linux user and not postgres or root.

$ cd /build
$ git clone https://github.com/psycopg/psycopg2.git
$ cd psycopg2/

$ ls
AUTHORS  INSTALL  LICENSE   MANIFEST.in  psycopg         README.rst  setup.cfg  tests
doc      lib      Makefile  NEWS         psycopg2.cproj  scripts     setup.py   tox.ini

$ find psycopg/ -name *.c | xargs grep PQ
sycopg/utils.c:            ql = PQescapeStringConn(conn->pgconn, to+eq+1, from, len, &err);
psycopg/utils.c:            ql = PQescapeString(to+eq+1, from, len);
psycopg/utils.c: * Return a string allocated by Postgres: free it using PQfreemem
psycopg/utils.c:    rv = PQescapeIdentifier(conn->pgconn, str, len);
psycopg/utils.c:        msg = PQerrorMessage(conn->pgconn);
psycopg/utils.c:/* Make a dict out of PQconninfoOption array */
psycopg/utils.c:psyco_dict_from_conninfo_options(PQconninfoOption *options, int include_password)
psycopg/utils.c:    PQconninfoOption *o;
psycopg/lobject_int.c:    conn_set_error(conn, PQerrorMessage(conn->pgconn));
psycopg/connection_int.c:    while ((pgn = PQnotifies(self->pgconn)) != NULL) {
...

$ python3 setup.py build_ext --pg-config /opt/pgsql/bin/pg_config  build

$ sudo python3 setup.py build_ext --pg-config /opt/pgsql/bin/pg_config  install

$ ls -lt /usr/local/lib/python3.6/dist-packages/
total 36
-rw-r--r-- 1 root root   42 May 17 00:30 easy-install.pth
drwxr-xr-x 4 root root 4096 May 17 00:30 psycopg2-2.9.10-py3.12-linux-x86_64.egg
...

$ python3 
> import psycopg2
> conn = psycopg2.connect("dbname='testdb' user='dbowner'")
> cur = conn.cursor()
> cur.execute("SELECT * FROM users")
> rows=cur.fetchall()
> rows[0]
(1, 'Tony')
> quit()

Additional References

• PostgreSQL Don't Do This
• Development Version: libpq - C Library