Storing PostgreSQL databases on mounted volumes

I am not sure whether you can confirm a data directory for PostgreSQL during Ubuntu installation, but anyway, I ended up with the data directory located in the default location.

For some database experiments that I expect to be quite harddisk-consuming, I want to PostgreSQL to store this database on a different volume.

Following the comments on this question on SO and this answer on DBA, I first created a directory for PostgreSQL:

me@ubuntu:/mnt/mydisk$ mkdir postgres  
me@ubuntu:/mnt/mydisk$ cd postgres  
me@ubuntu:/mnt/mydisk/postgres$ mkdir data 
me@ubuntu:/mnt/mydisk/postgres$ sudo chmod -R 700 /mnt/mydisk/postgres/data  
me@ubuntu:/mnt/mydisk/postgres$ chown -R postgres:postgres /mnt/mydisk/postgres/data

Then, I opened the PostgreSQL prompt to create a TABLESPACE

me@ubuntu $ sudo su postgres 
postgres@ubuntu$ psql 
postgres=# CREATE TABLESPACE mydisktablespace LOCATION '/mnt/mydisk/postgres/data'; 
CREATE TABLESPACE

Next, I created a database on the new tablespace

postgres=# CREATE DATABASE mydatabase WITH ENCODING 'UTF8' TABLESPACE mydisktablespace; 
CREATE DATABASE

and successfully connected to the database using DBeaver.

Apparently, you can also move an existing database to a new tablespace using the ALTER DATABASE SET TABLESPACE command, which I will do for the existing databases just to clean up some space.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.