Running PostgreSQL on Ubuntu as non-Postgres User: The Definitive Guide

We’re running Postgres for the production deployment of Struct, a (not-yet-launched) chat platform we’re building to deal with the disorganized chaos of live communication. I’m using PostgreSQL to store the data — which I have started to enjoy because of its decades long improvements around useability, documentation and in particular, the command-line tool psql (though, some things around schema management are strangely explicit and feel old-school). We’ll see how it scales, but for now it’s working out great.

I’m firmly in the baremetal camp. And we run our own PostgreSQL server. One of the things we wanted to do is to run it as the linux user that’s running the application server and docker images, called box. Thus, avoiding having to switch between users, making it easier to deal with files and system management.

This article is a guide to setting up and running PostgreSQL without the postgres user it defaults to, and the main Postgres service it uses. Instead, we’d use box user and a struct service.

How to Purge Postgres

We’re going to use PostgreSQL 15. So, first we are going to delete any existing or older Postgres installations that might have come with the system. Note that this and the installation of Postgres below is an OPTIONAL step.

WARNING: Following these steps would result in a deletion of all Postgres data and configuration files. Only run these when you have either don’t have any data or configs of value, or have backed up your Postgres database. There’s a backup command at the end of this article that you can use.

$ sudo apt --purge remove postgresql\*
$ sudo rm -rf /run/postgresql
$ sudo rm -rf /etc/postgresql
$ sudo rm -rf /var/lib/postgresql

Install Postgres 15

For this setup, I’m using Postgres 15, but it should work with earlier versions of Postgres too.

If you have an earlier version installed and you don’t wish to update, you can adjust the commands accordingly. Just be aware that some steps or configurations may vary between versions.

# Create the file repository configuration
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null

# Update list of available packages
apt update
apt install postgresql-15 postgresql-client-15 -y

Remove the main Postgres Cluster

I prefer removing the main Postgres cluster to avoid any permission conflicts. This step is particularly important in ensuring that our desired configuration doesn’t get interrupted by the default Postgres cluster. If you’re curious about why we’re removing the main cluster, consider that it runs under the postgres user, which we’re trying to avoid in this setup.

The end goal is that we want only one Postgres cluster running, under box.

$ sudo systemctl stop postgres
$ sudo pg_dropcluster --stop 15 main

Add user to postgres group

Adding the box user to the postgres group gives it the necessary permissions to interact with Postgres-related files and directories, which saves you from needing to alter permissions for these resources manually and keeps things clean.

$ sudo usermod -a -G postgres box

Create a new Postgres Cluster

To create a new cluster, we’d run the pg_createcluster command. Let’s break down what each flag does in the pg_createcluster command:

The -u flag specifies the user, in this case, box.
The -g flag denotes the group, which is postgres here.
The -d flag designates the directory where Postgres would store its files.
The number 15 is the Postgres version we’re using, and
struct is the name of the cluster we’re creating.

$ sudo pg_createcluster -u box -g postgres -d /struct/pgdata/struct -e UTF8 --locale C 15 struct

# Creating new PostgreSQL cluster 15/struct ...
# /usr/lib/postgresql/15/bin/initdb -D /struct/pgdata/struct --auth-local peer --auth-host scram-sha-256 --no-instructions --encoding UTF8 --locale C
# The files belonging to this database system will be owned by user "box".
# This user must also own the server process.

# The database cluster will be initialized with locale "C".
# The default text search configuration will be set to "english".

# Data page checksums are disabled.

# fixing permissions on existing directory /struct/pgdata/struct ... ok
# creating subdirectories ... ok
# selecting dynamic shared memory implementation ... posix
# selecting default max_connections ... 100
# selecting default shared_buffers ... 128MB
# selecting default time zone ... Etc/UTC
# creating configuration files ... ok
# running bootstrap script ... ok
# performing post-bootstrap initialization ... ok
# syncing data to disk ... ok
# Ver Cluster Port Status Owner Data directory        Log file
# 15  struct  5432 down   box   /struct/pgdata/struct /var/log/postgresql/postgresql-15-struct.log

Postgres Settings for Server

Let’s figure out the settings for the specific server you’re running Postgres on. For optimal settings specific to your server, I recommend this tuning tool located at https://pgtune.leopard.in.ua/ . Modify the parameters they have to generate a recommended configuration.

This is the config that was generated for my server, along with the parameters I had set:

# DB Version: 15
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 64 GB
# CPUs num: 32
# Connections num: 64
# Data Storage: ssd

max_connections = 64
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 64MB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 32
max_parallel_workers_per_gather = 4
max_parallel_workers = 32
max_parallel_maintenance_workers = 4

I added that in the /etc/postgresql/15/struct/postgresql.conf towards the end.

If all the communication with Postgres is local or via a VPN, then you don’t need SSL on. In fact, this should by default be off. But, you can explicitly turn that off.

ssl = off

Note that all these settings may vary depending on the nature of your workload, hardware, setup and database size. Always test changes before applying them to production servers.

Start the Service

$ sudo systemctl restart postgresql

# Enable it to run automatically on a restart
$ sudo systemctl enable postgresql

If you look carefully, you’d find another service called postgresql@15-struct.service. This was automatically generated and runs via the postgresql.service. So, you don’t need to anything special to run it. But it’s good to see status of that specific service.

$ sudo systemctl status postgresql@15-struct.service

Create the DB and Set Password

$ createdb -O box -E UTF8 struct

You can now set the password for the user box.

$ psql -d struct

struct=# select 'world' as hello;
 hello
-------
 world
(1 row)

struct=# alter user box with password 'abracadabra';

In the steps above, we created a new database and set a password for the user box. Even though, abracadabra is quite a magical password which can open many doors for you, make sure to replace it with a strong, unique, less-memorable password. It’s a good practice to use a password manager to store such sensitive information, I recommend 1Password.

Backup and Restore

Now that you have a running database, it’s time to add data to it. If you have another database running elsewhere, you might want to bring it to this one. For that, you can run a backup and restore operation.

# On the source machine, do a backup
$ pg_dump -F t struct > backup.tar

# Copy over backup.tar to this machine's ~/backups

# On this machine, do a restore
$ pg_restore -c -d struct ~/backups/backup.tar

Conclusion

We’ve just gone through a detailed process of setting up PostgreSQL under a non-default user on Ubuntu. This process, although a bit involved, provides us with a more streamlined and efficient way to manage our server, avoiding the constant need to switch between user accounts.

Every step taken, every command executed, and every setting adjusted contributes to creating an optimized system. Remember, improvement comes from constant learning and adapting. So, stay curious and keep exploring. Your efforts today will result in a smoother, better-performing application tomorrow.

I’m on Twitter. So, feel free to message me, ask questions, or share your experiences. Happy data hoarding!

Date

May 19, 2023