Creating PostgreSQL cluster in portable hard drive

I'm working as a part-time software developer for EasyUni. Its database is so huge that I cannot install on my laptop's internal HDD. I have to look for a way to install it in my portable HDD.

My solution is to create a second PostgreSQL cluster with data directory residing in my portable HDD. This cluster must not be started up automatically, because the portable HDD is not always attached. So my command to create this second cluster is:

sudo pg_createcluster 9.5 second -d /media/quan/Quan-Backup/Postgres/second --start-conf=manual

given that:

  • 9.5 is the version of PostgreSQL
  • second is the name of this cluster
  • /media/quan/Quan-Backup/ is the mount point of my portable HDD. This mount point is created automatically by GNOME after I plug the HDD in and authenticated for decryption (my HDD is encrypted with LUKS).
  • --start-conf=manual: This cluster has to be started manually.

The first time doing, the command failed:

Creating new cluster 9.5/second ...
  config /etc/postgresql/9.5/second
  data   /media/quan/Quan-Backup/Postgres/second
  locale en_US.UTF-8
initdb: could not access directory "/media/quan/Quan-Backup/Postgres/second": Permission denied
Error: initdb failed

It is mythical because the folder /media/quan/Quan-Backup/Postgres/second was actually created and set with proper owner & permission!

Following one guy in StackExchange, I tested if user postgres is able to access the folder:

$ sudo -u postgres ls /media/quan/Quan-Backup/Postgres/second 
ls: cannot access '/media/quan/Quan-Backup/Postgres/second': Permission denied

Funny, because:

$ ll /media/quan/Quan-Backup/Postgres 
total 4.0K
drwxr-xr-x 2 postgres postgres 4.0K Oct 19 10:29 second

It had the x flag as suggested by another guy.

But, combining the two hints, I suspected that the x flags of parents folder also affect. So I checked:

$ ll /media                                                          
total 4.0K
drwxr-x---+ 3 root root 4.0K Oct 19 09:29 quan

/media/quan didn't have x flag for other user. I fixed it and tried creating Postgres cluster again:

$ sudo chmod a+x /media/quan
$ sudo pg_createcluster 9.5 second -d /media/quan/Quan-Backup/Postgres/second --start-conf=manual
Creating new cluster 9.5/second ...
  config /etc/postgresql/9.5/second
  data   /media/quan/Quan-Backup/Postgres/second
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433

Hooray! It works.

From that on, everytime I want to use that cluster, I need to run it with:

sudo pg_ctlcluster 9.5 second start