PostgreSQL,
often simply called Postgres for
short, is a popular open source DBMS package. This topic is the
fourth 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 fourth topic in the above sequence of steps.
So far, we have installed PostgreSQL on our target machine and we have done a raw installation of PostGIS, but we have not yet enabled use of PostGIS within PostgreSQL. We must enable PostGIS use within PostgreSQL so our databases will be spatially-enabled and thus useful for GIS work.
Because we have already enabled use of PostgreSQL through a network, we could do this topic either on the same machine that hosts PostgreSQL (if we have a Manifold license on that machine) or we could launch Manifold on some other computer and connect to the PostgreSQL machine through a network.
This topic is illustrated assuming we have Manifold installed on the machine on which we have just installed PostgreSQL. That allows us to launch Manifold on that machine and to connect to PostgreSQL using localhost, that is, not connect through a network. 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.
Every machine running Manifold which
will be used to connect to PostgreSQL must
have the PostgreSQL Client .dll installed. The easiest way
to do that at the present time is to visit the Product
Downloads page on the Manifold website and, from the DLLs for Popular Open Source DBMS Packages
section, to download the extension-dlls
zip package for 64-bit operation, unzipping the package into the extras folder in the Manifold installation
folder.
We often use the word database casually, as a synonym for Database Management System (DBMS), or, in the case of file databases, in the sense of connecting to a particular database file, such as a particular GPKG database file or a particular Microsoft Access database .mdb file.
In this topic we use the special PostgreSQL meaning of database, to mean a collection of tables, queries and other infrastructure grouped together under a name within a particular PostgreSQL DBMS installation. When connecting to a PostgreSQL server, PostgreSQL allows us to specify the particular database within that server to which we want to connect. If we do not specify a particular database, we will simply connect to the overall master database by default.
We could, in theory, connect to and use the default master database without ever creating specific databases. But that would be disorderly, somewhat like keeping hundreds of files in a Windows system in the root of the C: drive without ever organizing those hundreds of files into sensible folders. It is much more orderly and efficient to create one or more databases within which we group tables and other components. For example, within our PostgreSQL server we might create a database called gisdb within which we will keep all of our GIS data.
We also create a database so we can enable PostGIS functionality for that database. So far, we have installed PostGIS but we have not yet enabled it. PostGIS must be enabled for each database within which we would like to have the geospatial functionality that PostGIS provides.
There are a variety of ways, for example, using PostgreSQL administration tools, to create a database within a PostgreSQL server and to enable PostGIS for that database. We can also use Manifold to issue the PostgreSQL commands which will create a database and then which will enable PostGIS for that database. That is what we will do in this topic.
In this topic, we launch Manifold on the same system on which the PostgreSQL server has been installed. The procedure below would be identical if we wanted to use Manifold from a remote machine, but instead of using localhost in the Create New Data Source dialog we would we would specify the IP address of the PostgreSQL server machine, as illustrated in the Connect to PostgreSQL topic.
Launch Manifold.
Choose File - Create - New Data Source.
In the dropdown menu we then choose More... to launch the New Data Source dialog.
Enter a descriptive name, Postgres, and choose Database: postgresql as the type. Click the browse [...] button to launch the Database Login dialog.
Enter localhost as the name of the server. If we are connecting from a remote machine, we would enter the IP address of the server, for example, 192.168.2.44.
Choose Use login and password and then enter postgres as the login. Enter the password we provided for the postgres superuser login in the Install PostgreSQL topic.
It is a good idea to check for errors by pressing the Test button. If that does not report a connection, we should fix any mistakes. If we are sure there are no typos or other errors, we press OK.
Back in the New Data Source dialog we review the connection string just built for obvious errors, find none, and press Create Data Source.
A new data source called Postgres appears in our Project pane. That data source is a connection to our PostgreSQL server. We did not name a database so this data source is a connection to the default master database. We can now use that connection to issue commands within that data source, which will take effect with the full power and privileges of our postgres login.
A risk element for security is the
plain text use of passwords in connection strings. We should not leave this project unattended
where somebody else can right-click
on the data source to see the Properties
of it, which would reveal in plain text the password for postgres in
the JSON string that gives the connection string. We
should only create such data sources for as long as we need to use the
mighty powers of the superuser. When our work is done, we should
delete the data source from the
project. When other people might have access to our computer and
our files, we should not save
the project with the data source in it, since someone else might open
that project to find the password string inside.
We can avoid such risks by using integrated security, where the Windows login through which a connection is made is mapped into user accounts within the DBMS, to which all the power and sophistication of roles and privileges can apply. To keep the presentation as simple as possible, except for the SQL Server example, integrated security is not used in these case studies.
In the Project pane we right-click on the Postgres data source and choose New Command Window.
That launches a Command Window that operates in the context of the Postgres data source, that is, within the PostgreSQL server. Whatever SQL and other commands we enter into that command window will be executed within the PostgreSQL server using the native SQL of PostgreSQL.
We enter the command
CREATE DATABASE gisdb;
And then we press the ! button to run the command.
That creates a new database within the PostgreSQL server. There is no visible change, since new databases do not appear as some sort of "database" component within PostgreSQL.
If we like, we can create other databases. For example, if we want to create a database for use by students we could run another command:
CREATE DATABASE studentdb;
For this example, we will settle for creating only one database, the gisdb database we have just created.
Although we have installed PostGIS, we must enable the PostGIS spatial extension for each database within our PostgreSQL database server. We will use Manifold to connect to the gisdb database and to issue the command that will enable PostGIS for the gisdb database.
We will now create a second data source that is a connection to the new gisdb database within our PostgreSQL server.
Choose File - Create - New Data Source.
In the dropdown menu we then choose More... to launch the New Data Source dialog.
Enter a descriptive name, Postgres gisdb, and choose Database: postgresql as the type. Click the browse [...] button to launch the Database Login dialog.
Enter localhost as the name of the server. If we are connecting from a remote machine, we would enter the IP address of the server, for example, 192.168.2.44.
Choose Use login and password and then enter postgres as the login. Enter the password we provided for the postgres superuser login in the Install PostgreSQL topic.
We enter gisdb as the database to which we will connect with this data source.
Press OK.
Back in the New Data Source dialog we review the connection string just built for obvious errors, find none, and press Create Data Source.
A new data source called Postgres gisdb appears in our Project pane. That data source is a connection to the database called gisdb within our PostgreSQL server. We now have two different data source connections to the same PostgreSQL server in our project. One connects by default to the master, overall database for the server, while the other connects only to the gisdb database within the server.
We now will issue a command in the context of the gisdb database, to enable PostGIS features for that database.
In the Project pane we right-click on the Postgres gisdb data source and choose New Command Window.
That launches a Command Window that operates in the context of the Postgres gisdb data source, that is, within the gisdb database in the PostgreSQL server.
In the illustration above, we have closed the tab for the previous command window that we earlier opened for the Postgres data source. We now just show the command window for the Postgres gisdb data source.
We enter the command
CREATE EXTENSION postgis;
And then we press the ! button to run the command.
That enables PostGIS functionality for the gisdb database. There is no visible change to the listings within either of the PostgreSQL data sources. To see the new geometry infrastructure added within the Postgres gisdb data source we can right-click on that data source and choose Refresh.
We have now created a new database, called gisdb, within the PostgreSQL server and we have enabled PostGIS functionality for that database. We can now close the project. In the next topic, we look at an example using the gisdb database we have created.
Continue this case study on installing PostgreSQL with the Connect to PostgreSQL topic.
See the preceding topic: Enable Network Access to PostgreSQL
Jump to the beginning of the case study: Install PostgreSQL
Deleting a database - If we make a typographical error when creating a database or if we simply want to delete a database we used for experimentation, we can open a command window on the Postgres data source and enter a command such as:
DROP DATABASE gisdb;
That will instantly delete the database and all it contains. PostgreSQL is designed for experts, so when logging in with the superuser postgres login and issuing commands like that there are no confirmation dialogs: give a command to drop a database and PostgreSQL immediately and irreversibly deletes that database. Handle with care, and never, ever give the superuser password to careless people.
File - Create - New Data Source
Open a Firewall Port for PostgreSQL
Enable Network Access to PostgreSQL
Big List of Formats and Data Sources
Example: Switching between Manifold and Native Query Engines