UPDATE 08/2021
There is a claim (second answer) that using tablespaces on an external drive could invite database corruption if access to the tablespace is suddenly removed during operation (ie the drive is unplugged). I don’t know how true this is as no such fact was made known in the official postgres docs. It sounds like it could be true as tablespaces become an integral component of the database cluster once created. You should be cautious / do your own research if you decide to use tablespaces with external drives that won’t be permanent fixtures.
UPDATE 07/2021
I recently found out that there is a postgres built-in feature to store database objects (not the whole cluster) in other directories, called tablespaces. This page in the docs explains succintly what tablespaces are and how to use them. For many databases, using the tablespaces feature will be preferrable to moving the cluster to a different drive. To make it convenient, you can also set a default tablespace so that it doesn’t need to be explicitly named for every table creation.
Orignal Post
I pulled this beginner guide together to show you how to move a cleanly installed postgres “database cluster” (where postgres stores your database) to an external drive. On my machine postgres puts the “database cluster” at /var/lib/postgres/data by default; this can be an issue if you don’t want to clutter your root filesystem with a large amount of non-system data. These steps were tested on arch linux but it should largely transfer to other systemd distros.
Guide
- Install postgresql, on arch:
pacman -S postgresql
- Mount the root of the external drive to
<mount_location>.
mount /dev/<drive> <mount_location>
- Create the alternate data directory
<mount_location>/postgres/data.
mkdir -p <mount_location>/postgres/data;
chown -R postgres:postgres <mount_location>/postgres;
- Become the postgres user and initialize the database cluster at the desired location. Note: the postgres config file will now be at
<mount_location>/postgres/data/postgresql.conf.
sudo -iu postgres;
initdb -D <mount_location>/postgres/data;
- Edit the systemd service file to change the PGROOT and PIDFILE variables. On my system the file is at
/usr/lib/systemd/system/postgresql.service.
[Service]
Environment=PGROOT=<mount_location>/postgres
PIDFile=<mount_location>/postgres/data/postmaster.pid
- Start the postgresql service.
sudo systemctl start postgresql.service
(Optional) Run this to auto start the service on every subsequent boot:
sudo systemctl enable postgresql.service
- Add the first db user (make sure you are the postgres user when running this).
createuser --interactive
- Create a test db. If the user you created has the same name as your Linux username and has db creation privleges you’ll be able to do this from your standard user login shell, otherwise you can do this as the postgres user.
createdb <testdb>
- You can now enter the psql shell for that database.
psql -d <testdb>
From now on accessing the database cluster will work as long as the postgresql service is running and the external drive is mounted to the correct location