Create pgSQL DBs on different drives in ms windows

Question

I have PostgreSQL 15 installed on a Windows Server 2019 Standard VM. I've installed pgSQL using the default configuration on the C:\ drive. I'm currently using the default data directory of C:...\data. I also have a D:\ drive that I would like to create pgSQL DBs on. I don't want to move the default ...\data directory but only want to create new DBs in the D:\PostgreSQLData directory, which is on the same VM. I didn't see any solution in my initial search on this site. If such solutions already exist here or elsewhere, please send me the link. Otherwise, what are the steps to do this?

Answer 1

I would recommend not to keep the data directory in C:\Program Files\PostgreSQL\13\data, because C:\Program Files is for program files, not for changing and growing application data. If I were you, I'd

• stop the server

• create D:\data and make sure that only administrators and the user that runs the PostgreSQL service has access to that directory

• move everything in C:\Program Files\PostgreSQL\13\data to D:\data except postgresql.conf, pg_hba.conf and pg_ident.conf

• edit postgresql.conf and set data_directory to D:\data

• start the server

If you don't want to do that, you'd have to create a tablespace:

CREATE TABLESPACE data LOCATION 'C:\data';

and create your new database there

CREATE DATABASE newdb TABLESPACE data;