How to Install PostgreSQL on Manjaro

How to install PostgreSQL on Manjaro (really any Arch distro) is not straight forward. Worse yet, most of the information you find online about how to do it is outdated fifteen minutes after it is published. You have to be sure the versions match before following the instructions.

My version

You really need to do this one from the terminal.

sudo pacman -Ss postgres

That will show you lots of stuff. If you just want to jump, run these two commands.

sudo pacman -Sy postgresql
sudo pacman -Sy libpqxx

The second one many will argue is not needed. Just install it. Technically it is for C/C++ users doing server development, but quite a few programs using PostgreSQL as their back end need it. Don’t rely on packagers being smart enough to include it as a dependency.

Oh, don’t get all happy and giddy. This is an Arch based distro, nothing is easy despite the “simplicity” motto.

It’s still not going to work

Most other distros would be really nice and simple. After you installed the server, they would enable and start it for you. Not Arch based distros. Being kind to the user isn’t in the development mantra.

Part of the reason is you cannot have “One package to rule them all!” This desktop thing is kind of an afterthought in the Arch world. A cute little thing to be passed along as a promotional item like a pen with a company name on it. The bulk of Arch based packaging and development appears to be focused on server markets. Here’s a good example.

sudo -u postgres -i
initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data/'
exit

You should see something like this:

Initializing PostgreSQL

Most desktop distros just do that (or something like it) for you as part of the POSTINST step. While some super desktop users will have multiple disk drives in their system and now how to automount NAS at boot, John and Jane Q. Public don’t.

Those of you who happen to have a spare 6TB Western Digital Black internal disk drive in your machine will want to change the directory path because you probably installed that drive just for database use . . . unless you really like funny cat videos.

A Debian Detour

On Debian and most other desktop friendly distros, they choose to make the expert users suffer.

sudo mkdir /media/db_data/postgres_data
sudo chown postgres:postgres /media/db_data/postgres_data
psql -d postgres -c " create tablespace bigspace location '/media/db_data/postgres_data' "

Then you have to explicitly create your tables in that tablespace.

createdb --tablespace bigspace tax_$tax_year 'Tax information for $tax_year'

Yes, parking my PostgreSQL data out on a second disk was painful. Most ordinary users only have one disk. Adding insult to injury, if I had to re-install my OS due to drive failure and no current backup, there was actually no way to recover the data in the tablespace. At least there wasn’t back then. I’m very religious about backing up, but that failure caught me at the right time.

OS/2 Still Lives

There is a method to the madness with the way Manjaro/Arch distros choose to do this. It just doesn’t work well for the average user. Server focused distros are pushing LVM.

I’m not really trusting this particular history of OS/2 and its statements about LVM. The reason for my distrust is I ran OS/2 as my primary desktop for years. The reason is that I never ran a server edition of OS/2, but I ran LVM for years.

Logical Volume management was way more important in the OS/2 days. You have to remember that an 80MEG hard drive was huge then. Depending on what flavor of DOS you were running, you couldn’t even partition that entire drive as a single partition. According to this post my memory is wrong, but I physically remember running into the partition size limit issue more than once. First time was with my first 40MEG drive and he second time was with my first 80MEG drive.

With LVM and a SCSI controller allowing for up to six drives you didn’t have these issues. OS/2 had a drive preparation that would let you dynamically add another drive to the LVM. Is that 80MEG drive getting full? Just add another. Once IBM ripped out most of the really bad Microsoft code, OS/2Warp 4.5 was a really stable and capable OS. So naturally they abandoned it.

Pieces of OS/2 keep showing up because it was ahead of its time. It had better memory management and networking than any Microsoft product of the day or many years to follow. In particular, LVM was something of a crown jewel. As a parting thumb in the eye of Microsoft, IBM OpenSourced LVM and it found its way into Linux.

The Method to the Madness

Some Linux distros are defaulting to LVM for the primary installation target, others are not. I will leave it up to the reader to click the links and do the research.

This long winded tale is explaining why opted to not be nice to the user. A sysadmin is going to want to point PostgreSQL to an LVM or network storage appliance providing hundreds, perhaps thousands of Terabytes of storage. An ordinary desktop user has one disks.

Your Final Step

sudo systemctl enable --now postgresql.service

You should be good to go!

Add yourself as a super user

There are those who offer up the argument for adding yourself to the postgres group. I’ve never done it. If you are really bothered by the createuser command not being able to change directory to your home then I guess you can. Just don’t forget to also change the group on your directory.

groups and permissions

If you want to understand more about Linux groups and file permissions, start here.

The Arch Bug

When you install PostgreSQL on Manjaro you run into the Arch bug. Manjaro is Arch Linux based. When you hear about Linux being an “expert friendly” operating system with an uninviting community, you are most likely hearing a story about someone’s adventure into Arch. Just signing up for the user forum should tell you what you are in for.

Arch forum registration greeting

Basically, the bug is Arch deciding to change a default from false to true. So, if you’ve been using my xpnsqt application to keep track of your expenses for filing taxes; (Tax season is just around the corner people!) you will see something like this when you try to load your CSV backup files.

'/home/roland/postgres_tax_backups/tax_2019_payees.csv' -> '/tmp/tax_2019_payees.csv'
 '/home/roland/postgres_tax_backups/tax_2020_categories.csv' -> '/tmp/tax_2020_categories.csv'
 '/home/roland/postgres_tax_backups/tax_2020_expenses.csv' -> '/tmp/tax_2020_expenses.csv'
 '/home/roland/postgres_tax_backups/tax_2020_payees.csv' -> '/tmp/tax_2020_payees.csv'
 ;;;;;
 ;;;;;
 ;;;;;   Loading 1992
 ;;;;;
 ;;;;;
 creating tax tables
    Importing …  1992
 psql:import_it.sql:1: ERROR:  could not open file "/tmp/tax_1992_payees.csv" for reading: No such file or directory
 HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
 psql:import_it.sql:2: ERROR:  could not open file "/tmp/tax_1992_categories.csv" for reading: No such file or directory
 HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
 psql:import_it.sql:3: ERROR:  could not open file "/tmp/tax_1992_expenses.csv" for reading: No such file or directory
 HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
  setval 

Since roughly the 1970s /tmp has been that unprotected no-mans land where you didn’t leave stuff sit for long. Most distros point temp environment variables for each user to /tmp/user-name and make that a directory owned by the user. This gives you some protection.

You know, the average schmoe using Linux at home or work doesn’t know much about Linux really. They’ve always used a desktop and only open a terminal when it is absolutely necessary.

Sysadmins on the other hand, should know all about systemctl overrides. They also would have been the only one who wanted this turned on. Seriously, there is no account information with my xpnsqt thing. I suspect there is no account information in your recipe database. How about the database you created to keep track of your music collection? DVDs?

When you install PostgreSQL on Manjaro – The Schmoe Tactic

At some point everyone is a schmoe because he universe likes to laugh at people. The schmoe approach will do just enough research to find out what value they need to change.

cd /usr/lib/systemd/system
sudo cp postgresql.service postgresql.service_original
sudo jed postgresql.service

You may not have jed installed. You should, but you probably didn’t install it. Use nano or whatever terminal emulator you have installed.

PrivateTmp needs to be false

Save an exit. The dutiful schmoe will then do the following:

sudo cp postgresql.service postgresql.service_fixed

Why? Because every update will probably walk on this file. The dutiful schmoe can use a diff utility like Meld to compare the before and after so they can remember what needed to be fixed.

When you install PostgreSQL on Manjaro – Shoulda Done

sudo systemctl edit postgresql.service
Override file

The dark lore of the override file won’t be immediately obvious. You cannot just override the value. You have to override the value under a tag/section whatever the name is this week.

Yeah, Arch did this to you. Every other distro I’ve used realizes your typical desktop user won’t know how to fix this. Far too many will be a schmoe, constantly fixing that file.

No Matter Which Approach

No matter which approach you take, your changes will not take effect until you either reboot or restart PostgreSQL.

sudo systemctl restart postgresql

Related posts:

How to install JED from AUR on Manjaro

How to make Manjaro KDE work with NAS