Create new database in shared drive Postgresql

Question

I have my postgresql/ postgis installed in the c drive but I am running out of space. Is it possible to create a new database in the shared drive and access it from local pgadmin?

Answer 1

You can do this in one of two ways.

1. Move you entire data directory to a new drive. The data_directory is a config setting in postgresql.conf .

   • So, you would need to stop the server
   • move everything you already have in your current data directory to the new location
   • alter the data_directory setting in postgresql.conf
   • restart the server.

Here are the relevant postgresql.conf docs.

You will probably want to take a backup first, with pg_dump, although in my experience, this approach works well.

2. Create a new tablespace. With this approach you can have multiple storage locations and you can put whole databases or tables in different tablespaces. You need to be a superuser to create a tablespace, and users need the create privilege to create databases/tables. The examples in the docs are for linux style paths, but, I assume it will work the same in Windows (though you probably need admin rights for the OS, as well as superuser DB rights).

Option 2 is more flexible, as you can leave existing databases as they are, and add a new tablespace for new databases tables.

As a more general observation, it is generally considered bad practice to put everything in the C drive. A good approach is to only have OS related files in C and to create new partitions for data storage and other 3rd party programs. It makes backups easier, and is generally cleaner.