Demonstration environment and programs
Windows 10
Postgres Version : 11.2
QGIS desktop version : 3.4.4
My working through of a process to export a single database (structure and data) from a Postgres Instance, the database has PostGIS and pgrouting extensions enabled, followed by importing into in this example the same instance but in principle could be a different instance.
Access the command prompt (RUN AS ADMINISTRATOR)
PLEASE NOTE run the command prompt as administrator or you will get frequently get an ACCESS DENIED message after using pg dump command.
Navigate to the directory of the PostgresVersion from which you wish to export the database. This will typically be the bin subdirectory of the version of your postgres ( here 11 ). You can ensure that pg_dump.exe is here if you do a dir on the directory to reveal alternatively you could reference the full path to pgdump and then pass the parameters to it subsequently.
eg
Next place in the parameters of the database what database you wish to export along with the name that you want to call the exported file and then hit return.
pg_dump -U postgres -p 5432 edinburghrouting > c:\dbexport.pgsql
Hitting return depending on the security of your instance you will be prompted for a password.
Enter the password hit return
When I do this on my home computer there is no return message but going into the C drive I can see that dbexport.pgsql now exists.
Next we want to create a blank database this is required to import the data and structure into.
This we do in psql signed in as a user with sufficient privelege.
Now back in the command line running as administrator we can run the following.
psql -U postgres importededinburghrouting < c:\dbexport.pgsql
Pressing return depending on your security you should be asked for your password.
Once this is done it goes through a process of recreating the structure of the database then importing all the data
For me the first lines look like this
and the last look like this
Now looking at the instance as a whole we can see the imported database
and here I am displaying geographical information through QGIS to get an idea of the data and ensure that it appears to be all correct.
- SUMMARY
There are quite a lot of tutorials online on how to do this but most seem to skip over some details - I've tried to be as accurate as possible but depending on you setup there may be differences. Nonetheless this is an extremely important task to perform so worth practicing to get right.