Lets take this from the beginning. The first thing you need to do is be on the physical machine where the database is running.
Next check that the database server is up and running
To do that
Go to the windows bar click on the search icon and type in
Services
Scroll down to P and look for the name postgresql this and look to the Status column in the above example there are two postgres 11 and postgres 14 of the two only 14 shows as Running.
There are several Utility executables that come with every postgres installation.
The standard installation set up puts these into a directory equivalent to the following;
We need to navigate at the command prompt to the location of the executable we are interested in. Technically you could qualify the path to, the executable when running a command, but I tend to navigate to the location.
So firstly in the search button go to the command prompt
cmd
cd C:\Program Files\PostgreSQL\14\bin
pg_dump -U postgres -W -F p -d dbspatialhub > c:\sql\dbspatialhub.sql
pg_dump -U postgres -W -F p -d dbspatialhub > c:\sql\dbspatialhub.sql
and here is a string that works if your database is on the local server with a non standard port / substitute 5433 for your port
pg_dump -U postgres -h localhost -p 5433 -d dbsource > c:\sql\dbsourceexport.sql
run this command – you will note that I qualify the output directory here. I prefer to do this as I don’t particularly like writing and deleting files into the program directory. I prefer this directory to be reserved for standard files and reduce the likelihood of me in anyway accidentally deleting an important file.
You should be presented with the Password: option you will need to input the password for specified User (-U) complete
Success will be shown as a return to the former prompt with no message.
and running a DIR on the directory look for the sql file…
Next from either psql prompt or from within PGAdmin4 create a database you are going to import the sql into and ensure that the postgis extension is installed. Remember this is the psql prompt and not DOS command line prompt.
CREATE DATABASE dbspatialhubnew CREATE EXTENSION postgis
Now you have to go back to the command prompt (and the correct directory).
If you are on a different port add the following string -p 5434
psql -U postgres -d dbspatialhubnew -f c:\sql\dbspatialhub.sql
After being asked for the password the import will start and continue showing a list of various commands and data being executed and imported.
Similar to below.
Now you can go back to a client such as QGIS , link to the database to start confirming a successful import.
Congratulations you have copied a database.
It should be noted that as it stands I am having problems importing the layers table which has the format of the boundaries for tables.
I will continue to work on this.