Is it possible to create a database not in the "base" directory, in Postgres 9.6 or later

Question

I am using Postgres 9.6 Standard. All databases keep their files in subdirs of the "base" dir of the data directory assigned to server when installing it. New and restored databases also keep their files in that dir only

Is it possible, if not in ver 9.6 then in later versions of Postgres, to create or restore a database so that its files be in any OTHER dir, the dir that I want? My disk where the "base" dir resides, is running out of space

I know that in Microsoft SQL Server and in Oracle, I can easily keep a database in any dir at all. Is the same available on Postgres?

• Bergi: You mean you want to have multiple databases of the same server (db cluster) in independent and arbitrary directories?
• Bergi: You'll probably want to look into tablespaces
• Frank Heikens: Version 9.6 is EOL for a long time
• Adrian Klaver: Read versioning policy, in particular the EOL for 9.6.

Answer 1

Yes, that feature is known as "tablespace".

First, you have to create a directory. Let me assume that you are using Windows. Then run something like the following on your command line:

mkdir D:\wherever\tbsp

Then, run an SQL statement as superuser create a tablespace:

CREATE TABLESPACE tbsp LOCATION 'D:\wherever\tbsp';

Now you can create a database in that tablespace:

CREATE DATABASE newdb TABLESPACE tbsp;

However, this procedure gives me a feeling of the 20th century. I am not going to ask how old your hardware and your operating system is. All I will recommend is that you update your setup to something current, where you can just extend a logical volume.

• SargeAnt: Yes, I am from the 20th century :) I bought my first IBM-compatible computer in 1991. And now I am still using a notebook bought in 2014, running Windows 7 on it, Oracle 11, and MSSQL 2014. And I am not going to upgrade to Windows 10 till the end :) The higher is version of Windows or Postgres, or of anything, - the slower that software operates. Thank you very much! your advice of using tablespaces in Postgres is perfect, just what I needed
• Laurenz Albe: I wrote punched cards in the early eighties, but I don't share your position concerning upgrades.