Installing PostgreSQL 10 on Arch Linux: An In-Depth Guide

Introduction

PostgreSQL is a powerful, open source relational database management system. With over 30 years of development, it has earned a strong reputation for reliability, robustness, and performace.

Version 10, released in October 2017, adds significant new SQL commands, transaction grouping for greater consistency, improved partitioning for large databases, and better parallelization for enhanced scalability. In this comprehensive guide, we will install and configure PostgreSQL 10 from source on Arch Linux, optimize it for production use, create test databases and users, and highlight some of the key new v10 features.

Step 1 – Install Build Tools & Dependencies

As PostgreSQL 10 is not yet available in the Arch Linux repositories, we will build it from source. This requires installing the necessary compiler tools and libraries:

$ sudo pacman -S base-devel linux-headers gcc pkgconf libxml2 openssl zlib systemd

This installs:

• base-devel – A group that includes make, gcc and other build tools
• linux-headers – Headers for the running kernel, needed to compile some modules
• gcc – The GNU Compiler Collection for C and C++
• pkgconf – Tools for managing library compile/link flags
• libxml2 – XML parsing libraries
• openssl – Encryption libraries
• zlib – Compression libraries
• systemd – For managing PostgreSQL server process

Step 2 – Create Dedicated PostgreSQL User & Group

For security and permissions, we will run the PostgreSQL service as a separate postgres user account.

To start, create a new postgres group:

$ sudo groupadd postgres

Then add a matching postgres user. The -d /var/lib/postgres option sets postgres user‘s home directory to the PostgreSQL data path:

$ sudo useradd -d /var/lib/postgres -g postgres postgres

Check these were created successfully:

$ id postgres
uid=1001(postgres) gid=1001(postgres) groups=1001(postgres)

Step 3 – Download & Extract PostgreSQL Source Code

We will build PostgreSQL from version 10.1 source files. Download the latest 10.1 code as a gzipped tar archive:

$ cd /tmp
$ wget https://ftp.postgresql.org/pub/source/v10.1/postgresql-10.1.tar.gz

Then extract the archive contents to a new folder:

$ tar xzf postgresql-10.1.tar.gz
$ rm postgresql-10.1.tar.gz
$ mv postgresql-10.1 postgresql

This avoids cluttering tmp and places the PG code neatly into /tmp/postgresql ready for compilation.

Step 4 – Compile & Install PostgreSQL 10

To build with optimal performance for our server, first check available CPU instructions and enable appropriate flags:

$ grep -q sse2 /proc/cpuinfo && export CFLAGS="-march=native -O2"

Now compile with these performance optimizations:

$ cd postgresql
$ ./configure --prefix=/usr           \
              --bindir=/usr/bin       \  
              --sysconfdir=/etc       \
              --datadir=/var/lib/postgres \
              --with-systemd

Note the non-default --datadir path, to match the postgres user‘s home directory defined earlier. The --with-systemd flag enables system startup integration.

Compile everything with make:

$ make world -j "$(nproc)"

Finally, install the newly built PostgreSQL binaries system-wide:

$ sudo make install-world

That completes the compilation and installation from source.

Step 5 – Initialize Database Storage Area

The data directory path is set to /var/lib/postgres, matching the postgres user‘s home. Initialize this storage area with postgresql-setup:

$ sudo -i -u postgres
$ /usr/bin/postgresql-setup initdb

This creates the proper tablespaces, database templates, admin roles and connections required to start the server.

Step 6 – Configure postgresql.conf

Several parameters in postgresql.conf can be tuned for increased performance in production environments.

As postgres user, edit /var/lib/postgres/data/postgresql.conf:

$ vi /var/lib/postgres/data/postgresql.conf

Important settings to adjust include:

• max_connections – Sets maximum concurrent connections from clients, recommend setting to at least 200.
• shared_buffers – Sets memory allocated to cache data, start at 1/4 total RAM.
• effective_cache_size – Estimate of memory available for disk caching, start at 1/2 total RAM.
• maintenance_work_mem – Affects large objects like indexes, start at 1GB.
• checkpoint_completion_target – Helps spread out write IO over time, set to 0.9.

There are many more parameters that can be tuned as needed to optimize PostgreSQL configuration.

Step 7 – Set Up Authentication & Users

For access control, we will set up password authentication with a custom pg_hba.conf.

As postgres, edit the configuration file:

$ vi /var/lib/postgres/data/pg_hba.conf  

Then update the local access rules:

# Allow password login access from localhost
local   all             all                                     md5

# Allow password access from private LAN IPs 
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.0/24          md5

This enables password-based login for local sockets and connections from LAN addresses.

Now create an admin role able to create databases and roles:

CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE;

And assign a password:

ALTER ROLE admin WITH ENCRYPTED PASSWORD ‘securepassword‘;

Adding test users is equally simple with SQL statements.

Step 8 – Configure systemd Startup Service

To manage PostgreSQL server process with systemd, a custom unit file must be registered.

As root, create a config file:

# vi /etc/systemd/system/postgresql.service

With these contents to start on boot and run as postgres user:

[Unit]
Description = PostgreSQL database server
Documentation = man:postgres(1)
After = network.target

[Service] 
Type = notify
User = postgres
ExecStart = /usr/bin/postgres -D /var/lib/postgres/data
ExecReload = /bin/kill -HUP $MAINPID
KillMode = mixed
KillSignal = SIGINT 

[Install]
WantedBy = multi-user.target

Enable the service to start automatically:

$ systemctl enable postgresql

That completes configuration for system startup.

Step 9 – Start PostgreSQL Server

We are now ready to start up the PostgreSQL database engine:

$ sudo systemctl start postgresql  

And check it is running with systemd:

$ sudo systemctl status postgresql

The server log output can be tailed in realtime:

$ sudo journalctl -f -u postgresql

If any issues arise, check status and logs for details.

Otherwise PostgreSQL 10 is now running successfully!

Step 10 – Testing & Creating Sample Databases

With PostgreSQL 10 installation complete, test connectivity and SQL functionality.

First change to the postgres admin user:

$ sudo su - postgres

Then connect to the default postgres database template with psql:

$ psql -d template1 

This will open an interactive SQL prompt allowing commands like:

CREATE DATABASE testdb;
\c testdb
CREATE TABLE films (title text, release date);
INSERT INTO films VALUES (‘Blade Runner‘, ‘1982-09-09‘);
SELECT * FROM films; 

Multiple test databases can be created this way, with users assigned permissions on each via GRANT/REVOKE statements.

New Features in PostgreSQL 10

Some of the major improvements in PostgreSQL 10 include:

• Logical replication – Allows replicating specific tables between servers rather than entire instances.
• Declarative table partitioning – Split very large tables into inheritable child tables by rules and constraints.
• Improved query parallelization – Speed up queries by running portions concurrently across multiple CPUs.
• Stronger password authentication – New SCRAM-SHA-256 mechanism better secures user passwords.
• Stored procedures – Functionality to create and call server-side procedures using multiple languages.

There are many other useful additions – check the release notes for full details.

Conclusion

That completes our in-depth guide to compiling, configuring and setting up a high performance PostgreSQL 10.0 database server on Arch Linux.

The flexible postgresql.conf options enable tuning the engine for efficiency with production workloads. Authentication mechanisms keep data secure while ease of user and database creation allows simple testing.

With powerful new features for high volume environments, PostgreSQL 10 represents a major advance on previous stable versions. It‘s capability, reliability and speed make it a wise choice over basic SQL databases for mission-critical applications.