Postgresql (PG) is a quite powerful open source database systems and widely used as DB for many application types.
By default, PG stores its data in one particular space, usually under /var/lib/pgsql/data.
Sometimes it is necessary to have data living on different file systems, i.e. some applications need access to a high-performing db and thus the data needs to be on fast, more expensive SSD drives. Other applications might not need that fast access and simply can be on cost effective slow storage.
So how can we use one PG instance make use of different file systems?
Well Linux or Unix as operating system is flexible on that matter and we can simply add diffent types of disks and mount them on OS-level to make it accessiblel to PG. Then all we need to do is providing a folder structure with proper permissions to PG, create a new table space and make use of that for databases or even simple tables within databases.

Disk layout

Since there are many ways of doing this, I am depicting here the setup I am using on Hetzner: a regular virtual host with an internal SSD drive and externally attached a storage box via SSHFS:

In order for this to happen, you need to have SSHFS drivers etc installed, this depends on your OS. On my preferred OS, Rocky 9, this would be the fuse-sshfs package. Once installed, you can place an entry in /etc/fstab:

:share /path/to/share fuse.sshfs comment=sshfs,defaults,transform_symlinks,identityfile=/path/to/file,users,exec,auto,allow_other,_netdev,uid=26,gid=26,reconnect 0 0

The /path/to/share/ needs to be created before you can mount it:

mkdir /path/to/share

Then you can mount the share:

sudo mount -a

If this is the first time you are connecting to that storage box from that host, you need to add it to the known hosts, the OS will ask you during the mount process. Once this is done, you should have that new mount available as local disk. When the host reboots, that mount is automatically recreated. The UID and GID (typically 1000) must be set to the postgres UID and GID, usually 26 This is a special thing with SSHFS, all the files in the mountpoint will be always with the UID / GID of that mount and cannot be changed. Another thing to remember with SSHFS is that you cannot change any of the mount options. SSHFS is an FUSE file system and does not suport mounting options for selinux, like NFS would do. Once mounted, so it is not possible to change the selinux context and you need to disable selinux unfortunaly.

Configuring postgresql to use the new space

In my case I choose /var/lib/postgres as path, which is very close ztto the default path of /var/lib&pgsql. That makes it easy enough to remember.

Doublecheck that the permissions and ownership is correct:

ls -alh /var/lib/postgres/
total 8.0K
drwx------.  1 postgres postgres    2 Oct 19 12:33 .
drwxr-xr-x. 34 root     root     4.0K Oct 19 13:37 ..

Connect to your PG instance as superuser:

psql -h <dbhost> -U <user>
Password for user <user>:
psql (13.22)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
<user>=# CREATE TABLESPACE colddata OWNER postgres LOCATION '/var/lib/postgres';
CREATE TABLESPACE
<user>=# \db
            List of tablespaces
    Name    |  Owner   |     Location
------------+----------+-------------------
 colddata   | postgres | /var/lib/postgres
 pg_default | postgres |
 pg_global  | postgres |
(3 rows)

So now we have a shiny new table space and can create a database within:

<user>=# CREATE DATABASE slow_db
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = colddata;
CREATE DATABASE
<user>=# \l
                                            List of databases
      Name      |     Owner     | Encoding |   Collate   |    Ctype    |        Access privileges
----------------+---------------+----------+-------------+-------------+---------------------------------
slow_db        | postgres      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Verify it:

<user>=# SELECT datname, spcname FROM pg_database d JOIN pg_tablespace t ON d.dattablespace = t.oid WHERE datname = 'slow_db';
 datname | spcname
---------+----------
 slow_db | colddata
(1 row)

ls -alh /var/lib/postgres/
total 12K
drwx------.  1 postgres postgres    3 Oct 19 14:09 .
drwxr-xr-x. 34 root     root     4.0K Oct 19 13:37 ..
drwx------.  1 postgres postgres    3 Oct 19 14:13 PG_13_202007201

New DB is created and lives in the right file system.

I also created a post on my wiki for that topic .

About The Author