PostgreSQL’s general documentation is excellent, and very abundant, but
for QGis users (and perhaps ArcGis users) it’s hard to find a trivial document
explaining how to install Postgres and then Postgis , so as to have an operational
database in a few minutes. How to envisage the possibilities and functioning of the pair
Postgis / QGis without going through extensive training. Of course, once this
superficial trick is over, you will be able to tackle the reading of the
official documentation and learn how to manage or develop with Postgres.
This Article aims to:
- install Postgresql and Postgis on your computer (firstly we will discuss the less difficult installation in Windows)
The following article aims to:
- give you the keys to use the PgAdmin interface, the PostgreSQL management tool
- help you loading a shapefile layer into your new database
- load your Postgis layer in QGis
What is PostgreSQL?
PostgreSQL is a relational database engine.
It is an engine adapted to business bases, feature-rich and very
powerful. The PostgreSQL BSD license allows its unrestricted use even within
proprietary software.
From an architecture and functional point of view we are in the presence of an
equivalent of Oracle. This is no longer the level of Access or SQLite. In terms
of the volume of manageable data (Tos), robustness of the structure,
management of competing transactions, etc., you have all the tools and
functions needed to manage a production database.
Extensions and tools are available to complete the engine, in particular PostGis which allows the management of spatial data in a PostgreSQL database (management of geometries and coordinate systems).
PostgreSQL basics Cluster : (or group of servers) This is a virtual set of servers to which you have access.
Database: A database is a structured set of data (Usually, one database is used per application). A database is, necessarily, created within a cluster.
A database includes both the data and the applications that concern them (Triggers, extensions, …) Database schema: contains the entire structure of the database and allows you to create specific views of the data tables.
Login accounts and functions : Not to be confused. The login account is the system account used by PostgreSQL processes. The most commonly used is postgres . Functions, on the other hand, are the way to manage access rights to databases.
To finish with the basics, PostgreSQL is a client / server application . The server manages the files in the database, accepts client connections, and performs the operations requested by clients (requests …). The customer can take many forms. To administer PostgreSQL you have a graphical interface, pgAdmin3. To work on the data (display, formatting, update) you can do it with QGis.
How to install the latest version of PostgreSQL and Postgis.
You can download the latest version installation from the following link: Choose the version corresponding to your system:
Launch the installer:
You will get the various settings screens. You can change the default options, but if you do not have a really important reason for doing so, stay within the default option. The documentation, help and examples that you will find available on the internet are always conceived for the default options. The installation directory:
The directory for the data cluster:
You can later create other clusters. You will have to enter the password linked to the postgres administrator account:
Warning, in fact this covers two different notions:
• an operating system user, the one on whose account the server programs are running,
• the database super user.
They may have different names and passwords, but for this installer it was
chosen to give the same name and password. The name, which does not appear, is postgres.
Write down this password, you will need it systematically even to install Postgis. You will be asked to define the port of access to PostgreSQL:
Leave the default port (5432). If necessary, you can reconfigure this parameter when you study the security of your production base. You will have to define the language used by your installation. The default option is that of your operating system.
The setup of the installation is finished. Click Next to run. When the installation is complete, the next screen asks if you want to launch Stack Builder . This is the tool that will allow you to install the PostGis extension. Leave the box checked and click Finish
The Stack Builder installation screen is displayed. Scroll down the menu and select PostgreSQL 9.5 on port 5432
You will get the list of available extension types. Open the “Spatial Extensions” item and select the appropriate installation type for your system.
The installer asks you for a folder to download the module:
The setting is complete.
Click Next to run the installation. Accept the terms of the license. A screen asking you which components to install appears. Check the “Create Spatial database” box to have the installer create a blank Postgis database in your cluster.
Keep the default directory
Enter the postgres password , which you defined during Postgresql installation:
Give your blank Postgis database a name, or leave the default name.
You will get three questions regarding the rasters management. Answer YES to each of them.
The installation of Postgres and Postgis on your workstation is complete.
