PostgreSQL 12 has recently been released. 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 12 (master branch) from Git on Ubuntu Linux 18.04.
Install and Build from Git
Before we begin, let's see if we have PostgreSQL packages already installed on our Ubuntu 18.04 system. We show various ways below to detect whether remants of postgres exist on our system. As you can, our system is clean of previous releases.
$ 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
$ 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 Release 12 Stable:
$ git branch -r origin/HEAD -> origin/master ... origin/REL_12_STABLE ... origin/master $ ls aclocal.m4 config configure configure.in contrib COPYRIGHT doc GNUmakefile.in HISTORY Makefile README README.git 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 libreadline6-dev systemtap-sdt-dev zlib1g-dev libssl-dev libpam0g-dev python-dev $ openssl version OpenSSL 1.1.1 11 Sep 2018 # we're using Ubuntu's package version $ mkdir build; cd build # /build/postgresql/build $ ../configure --prefix=/opt/pgsql --with-python --with-openssl --with-pam --enable-debug --enable-depend $ make ... All of PostgreSQL successfully made. Ready to install.
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 ... PostgreSQL installation complete.
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_ident.conf.sample ./share/pg_hba.conf.sample ./share/postgresql.conf.sample ./share/pg_service.conf.sample
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/data --shell /bin/bash --group postgres Adding system user `postgres' (UID 123) ... Adding new group `postgres' (GID 130) ... Adding new user `postgres' (UID 123) with group `postgres' ... Creating home directory `/opt/pgsql/data' ... $ sudo adduser postgres ssl-cert Adding user `postgres' to group `ssl-cert' ... Adding user postgres to group ssl-cert Done. $ sudo adduser postgres sudo # or optionally perform a visudo Adding user `postgres' to group `sudo' ... Adding user postgres to group sudo Done. $ sudo passwd postgres # set our user passwd $ su postgres $ cd $ pwd /opt/pgsql/data $ /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: fixing permissions on existing 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 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
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/ $ sudo ldconfig $ sudo 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
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/data $ 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 (13devel) Type "help" for help. postgres=#
Some basic interaction on the command line:
postgres=# select version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit (1 row) postgres=# \list List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows) postgres=# \d Did not find any relations. postgres=# select 2+2; ---------- 4 (1 row)
At this point, we have built a server from source that is fun to experiment with & test, but we haven't yet created any databases, roles, or configured necessary access permissions. We'll do this in a part 2, which we'll post in the coming weeks.