0

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?

3

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.

  1. 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.

  • Does this not work in some way? Did I answer the wrong question? – John Powell Jul 23 '18 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.