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
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 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
$ sudo systemctl stop postgres
$ sudo pg_dropcluster --stop 15 main
Add user to postgres group
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
-uflag specifies the user, in this case,
-gflag denotes the group, which is
-dflag designates the directory where Postgres would store its files.
- The number
15is the Postgres version we’re using, and
structis 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
email@example.com. 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 firstname.lastname@example.org
Create the DB and Set Password
$ createdb -O box -E UTF8 struct
You can now set the password for the user
$ psql -d struct
struct=# select 'world' as hello;
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
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!