PostgreSQL, often simply called Postgres for short, is a popular open source DBMS package. This topic is the third step in a five topic sequence showing a complete installation of PostgreSQL from the very beginning, including the PostGIS extension required for GIS use. Please see the Install PostgreSQL topic for basic info on PostgreSQL.
Disclaimer: These topics were accurate when written, using the versions of installation software indicated. Third party packages can and do change, so this topic may be out-of-date. It is provided as an example of how such installations can be approached.
Installing PostgreSQL and readying it for use in typical installations involves five steps:
Open a Firewall Port for PostgreSQL
Enable Network Access to PostgreSQL
Configure PostGIS in PostgreSQL
This is the third topic in the above sequence of steps.
In this topic, we are using a 64-bit Windows 10 system to which we have connected via Remote Desktop (RDP). This entire topic was conducted on that machine via RDP.
If we have Manifold installed on the same computer on which we install PostgreSQL, and if we will always use our PostgreSQL installation from the same computer, we can skip this step. For example, if we are an individual user with only one computer and we do everything on that one computer, we can always connect to PostgreSQL from Manifold using a localhost designation, that is, not going through any network.
We only need to do this procedure if we want to access this PostgreSQL installation through the network from other computers. For example, if we work in an organization with several computers and one of them hosts this PostgreSQL installation but we actually do our Manifold work on a different computer, to connect from our desktop computer to the machine hosting PostgreSQL we have to be able to connect to it through the web.
Microsoft's Windows Defender Firewall will prevent connections through the network to PostgreSQL, so we must first configure the firewall to allow connections (previous topic), and then (this topic) we must configure PostgreSQL to accept such connections.
The easiest way to enable PostgreSQL to accept connections from other machines is to list the IP addresses of machines we will allow to use this PostgreSQL installation. To do that, we first stop Postgres, we edit two text configuration files, and then we restart Postgres.
If we have connected to PostgreSQL locally, such as trying out a local connection with Manifold, close all such connections.
Launch Windows Control Pane, the full version. That is easiest to do by entering Windows Control Pane in the Windows task bar search box, and then launching the command.
Click Administrative Tools.
Double-click on Services.
In the Services dialog, scroll down to the entry for Postgresql, right-click on that entry and choose Stop. This stops PostgreSQL. Leave the Services dialog open: after editing text files we will re-start PostgreSQL by right-clicking the same entry and choosing Start.
We must tell PostgreSQL from which IP addresses we will connect, so PostgreSQL allows those addresses to connect. We can specify either individual IP addresses or ranges of addresses (sub nets). To specify IP addresses, we edit a text file called pg_hba.conf.
In Windows Explorer, navigate to the C:\Program Files\PostgreSQL\11\Data folder. This is the default installation folder used in the Install PostgreSQL topic.
Open the pg_hba.conf file in Notepad.
We add one or more lines below the line indicated. The easiest way to do that correctly is to Copy the indicated line and Paste it one line below, and then make changes in the IP address as desired.
If more than one machine on the same sub net will connect, it is easiest to add the entire sub net using a /24 designation. For example, if our internal network runs on addresses like 192.168.2.xxx with the IP address for each machine on our local network replacing the xxx with a number such as 41, 42, 43, 57, etc., we would add the line:
host all all 192.168.2.44/24 md5
We add that line:
The new line is indicated with a magenta arrow in the illustration above. The /24 designation allows everything on the subnet, matching IP addresses that end with .44, .233, .45, and so on.
We could have added individual machines instead of an entire subnet, using a /32 designation instead of /24:
host all all 192.168.2.41/32 md5
host all all 192.168.2.42/32 md5
host all all 192.168.2.43/32 md5
The /32 designation allows only the cited IP address.
Save the file and exit Notepad.
Next, in the same folder we open the postgresql.conf file in Notepad.
if necessary, change the line
#listen_addresses = 'localhost'
to
#listen_addresses = '*'
The line that might need to be changed is indicated below with a magenta arrow.
In our installation, when first opened the file already had
#listen_addresses = '*'
If that line is already in place, no edits need to be made.
Save the file and exit Notepad.
To apply the changes made to the configuration files, we must restart PostgreSQL.
Back in the Services dialog, scroll down to the entry for Postgresql, right-click on that entry and choose Start. This starts PostgreSQL. We can now close the Services dialog and the other administrative and control pane dialogs.
We have now enabled use of PostgreSQL through networks. Next, we must enable PostGIS use within PostgreSQL so our databases will be spatially-enabled and thus useful for GIS work.
Continue this case study on installing PostgreSQL with the Configure PostGIS in PostgreSQL topic.
See the preceding topic: Open a Firewall Port for PostgreSQL
Jump to the beginning of the case study: Install PostgreSQL
File - Create - New Data Source
Open a Firewall Port for PostgreSQL
Configure PostGIS in PostgreSQL
Big List of Formats and Data Sources
Example: Switching between Manifold and Native Query Engines