4

Is it possible to create a PostGIS database on an external hard drive? If so, is there anything to be mindful of and what would be the host address to connect to it with pgAdmin and QGIS ?

Background: I am part of a UK wide team, all remote workers. We have a PostGIS db on a cloud server for our shared project work which with the offline editing plugin works fine. We all use locally stored OS Mastermap. We could all have a local PostGIS db and use OS Translator II to populate it with the OSMM we want and styled. But all our machines are 'locked down', so only the IT department can install software or change files on the C drive. So as an option I thought we could each use an external HDD for our OSMM.

  • 6
    How would an application be able to distinguish between databases using internal and external devices for storage? Why would it care? PostGIS is just an extension within the PostgreSQL database, which is responsible for all block storage, so this isn't really a GIS issue anyway. – Vince Mar 5 '17 at 22:14
  • 6
    You need tablespaces. You can have different dbs in different locations. The host address doesn't change, though. – John Powell Mar 6 '17 at 14:01
  • 3
    @Vince, since you obviously know the answer to this question, why not just answer it? – dbaston Mar 6 '17 at 16:48
  • 2
    Portable GIS will install postgres/postgis on a USB Stick archaeogeek.com/portable-gis.html – Mapperz Mar 6 '17 at 21:22
  • 3
    PostgreSQL is as reliable as the storage used for the cluster. (postgresql.org/docs/9.3/static/wal-reliability.html). So I wouldn't use an external storage for a production environment. RAID1 or RAID10 is recommended. And the cluster should not be used by many server instance – Gob Tron Mar 6 '17 at 21:26
3

Yes, but you would invite trouble doing it. If the external drive is missing then PostgreSQL will fail to start, and be extremely difficult to repair.

http://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace/

  • Thanks, that really useful to read through. I was thinking of giving it a go next week when I have a bit of time. But I'll look at other solutions instead now. – Martin Hügi Mar 17 '17 at 10:13

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.