Connecting Postgresql to an External Drive in Ubuntu

It finally happened. In looking for a new way to store and move data between computers and organizations securely and at low cost, I needed an external drive. That data, is a ton of business information stored in jsonb format in PostgreSQL. As it turns out mounting an ntfs drive in Linux with the proper read write tasks is not a simple task. However, create an external tablespace is.

Mounting a Drive

Mounting a drive with read and write permissions as it turns out takes a bit of work. First, format the drive to the ntfs standard if it is not using this type of file system. As stated, this is probably the most cross platform of file system standards. The drive should be mounted at this point. If it is not, you may have another issue. Use the blkid line to get the drives UUID and then edit the fstab folder.

sudo gedit /etc/fstab

Inside the file, add a line for a new folder under /media/[user]/[new_drive_name].

#my new drive with Read and Write Permissions
UUID=#######FROMABOVE##     /media/[user]/[new_drive_name] ntfs auto,users,permissions 0 0

After this make the folder at the directory. Then unplug and replug in your external drive. It should now appear under [new_drive_name] from above. Finally, grant the correct permissions (755 recommended). Change to the media/[user] directory first if you are not there.

sudo chmod 755 -R [new_drive_name]
chown postgres -R [new_drive_name]

That should do it for setting up your drive. It looks easy now but when you don’t know what to do it is the daunting part.

Create a Tablespace and Database

Finally, create the tablespace and database and you should be able to login to the database with the appropriate user. Done!

CREATE TABLESPACE [tablespace_name] OWNER [user] LOCATION 'media/[user]/[new_drive_name]';
CREATE DATABASE [name] OWNER [user] TABLESPACE [tablespace_name];

Don’t forget to update any tablespace with SET DEFAULT tablespace = [tablespace_name] to use the tablespace on the drive.

You should now be able to dump to the database, add/delete tables and more. Probably not a good idea to use anything other than HDD as I am not sure of the read write volume flash can handle. Also, dumps are better than direct writes for speed but you can now tote your data around air gapped, encrypted, and in jsonb/json/hstore format using what is a top tier database. PostgreSQL’s NoSQL is better than MongoDB according to EnterpriseDB.

Caveat

There may be a side effect where you need to attach the drive on Xenial to boot. I am not sure yet but it solved my startup issue. Use this with caution.

Advertisements