How to move a PostgresSQL/ PostGIS to another drive

Question

Firstly I'd like to say I know little about databases (GIS background) and I might have bluffed it all so far. But we wanted to set up a database for use with QGIS. My understanding was that the PostgresSQL made the server and PostGIS add on made the databases on that server. I'm not operating with a network or dedicated servers I have a C drive and a Q drive. We installed PostgresSQL (servers?) and PostGIS (database?) on my Q drive where I created and manipulated all my data. But now we want to move it all to a portable drive (E) so that others can access the database by pluging into the drive is this possible?

and if so how does someone who does not understand databases nor SQL do it?

Cheers matthew

Answer 1

There are two things:

• the software directory containing the binaries like postgres and related files

• the data directory that you can find with the SQL statement

  SHOW data_directory;
  

On Debian Linux the configuration files are in a separate location, but since you are speaking about "drive letters", you are probably using Windows, where the configuration files are usually in the data directory.

You can simply copy the software directory and the data directory to your external disk after you have stopped PostgreSQL.

You can start the server with

<software directory>\bin\pg_ctl start -D <data directory>

---

If you want to start PostgreSQL through a Windows service, you may want to delete and re-create the service:

Delete the existing service:

<software directory>\bin\pg_ctl.exe unregister -N postgresql-11

The exact name to use for this, depends on your installation. You need to look it up in the "Services" management console (right click on the service, then choose "Properties" - make sure you close that dialog before proceeding). Once the old service is removed, create a new one pointing to the new data directory:

<software directory>\bin\pg_ctl.exe register -N postgresql-11 -D <data directory> -S auto

Make sure you enclose the data directory in double quotes if it contains spaces.