Postgres End User Tools

It is possible to interact with databases via a number of tools. Some like DBeaver and DbVisualiser are multi-platform and can connect to lots of different databases (SQL Server, Oracle, MySQL, SQLite etc.) as well as PostgreSQL Others like pgAdmin are optimised for a particular database. It is also possible to connect to Postgres using popular desktop GIS and reporting tools. We will work with both pgAdmin and QGIS in this course.

pgAdmin

pgAdmin 4 is the most popular and feature-rich open source administration and development platform for PostgreSQL. It comes as part of the PostgreSQL package, but also can be downloaded and installed separately (from www.pgAdmin.org).

Now very stable, pgAdmin is a community project relying upon developers around the world with new features and bug fixes being delivered every few weeks. It is worth updating pgAdmin periodically to benefit from these constant improvements.

The pgAdmin interface shows all objects in a tree structure, and includes specific tools for each object, usually accessible from the right-click menu.

To start the exercises, you will connect to a server, then create a database to work on during the course, using a backup of a standard database.

Connect to a server in pgAdmin

Open pgAdmin to create a new connection
By default you will see a Servers node in the tree on the left of the interface
Right-click > Register > Server… on the Servers node to connect to a database server
The connection details you will need [in square brackets] are:
1. On the General tab:
  - Name: your name for the connection - for example Training
2. On the Connection tab:
  - Host: Name or IP address of the server [appstream_data.astuntechnology.com]
  - Port: The default is [5432]
  - Maintenance Database: the name of the database to connect to [postgres]
3. On the Basic tab
  - Username: [postgis]
  - Password: [postgis]
  - Save password?: [Yes]
When you have added these parameters, click Save to connect to the server and save the connection details
When this is done, you will be able to see the server in the tree, and browse to the Databases it contains

Create a database

Select the Databases object, then Right-click > Create > Database… to create a PostgreSQL database
1. give the database a name based on your name and your AppStream user - for example jane_guest3
2. make the owner postgis
3. on the Definition tab, set the Template to training_postgis_delegates from the dropdown - this will give you a copy of the training database
4. click Save to create the database
You should now have a database to work on during the course

QGIS

QGIS is a desktop GIS application which integrates tightly with Postgres. To download QGIS or find out more about its capabilities see https://qgis.org/en/site/ .

Connect to a database in QGIS

Open QGIS and find the PostgreSQL node in the Browser panel
Right-click > New connection to open the dialog
The connection details you will need [in square brackets] are:
1. Name: your name for the connection - e.g. [Training]
2. Host: Name or IP address of the server [appstream_data.astuntechnology.com]
3. Port: The default is [5432]
4. Database: the name you gave the database when you created it (e.g. jane_guest3)
5. Basic tab > Username: [postgis], check Store
6. Basic tab > Password: [postgis], check Store
When you have added these parameters, click Test Connection - you will get a confirmation message
Click OK to save the connection details
When this is done, you will see the database in the Browser, and will be able to navigate to the tables it contains

Feature Comparison

pgAdmin gives the user (subject to permissions) full control over database administration. QGIS provides much of the same functionality, again subject to permissions, but with the focus on higher level objects such as a schemas and tables.

Feature	pgAdmin	QGIS
Browse and manage schemas, tables, columns (create, delete, rename)	Yes	Yes
Load data	Yes	Yes
Browse and manage all database objects including indexes, constraints etc.	Yes	No
Manage access control	Yes	No
Write and run SQL	Yes	Yes
View raw data	Yes	Yes
Visualise spatial data	Yes (via Geometry Viewer)	Yes (in context with Base mapping and other data if required)

Run through the following exercise in both pgAdmin and QGIS, and be aware of the differences between them.

Database familiarisation: pgAdmin and QGIS

Run through the tasks in the table below in both pgAdmin and QGIS
Note that by default QGIS will only show tables which are spatial, i.e. which have a column of type geometry

	Task	pgAdmin	QGIS
1	Navigate to a table and view data	Go to [database name] > [schema name] > tables > [table name] Right-click > View/Edit Data > All Rows	Go to [database name] > [schema name] > [table name] Right-click > Layer Properties > Attributes See also Metadata and Preview
2	Create a table, add columns	Go to [database name] > [schema name] > tables Right-click > Create > Table Complete the dialog with suitable values, adding columns in the Columns tab (add two or three test columns) Use integer, double precision, or text data types for now	Go to [database name] > [schema name] > [table name] Right-click > New Table Complete the dialog with suitable values, adding columns using Add Field (add two or three test columns)
3	Query/filter data	Go to [database name] > crime > neighbourhood_crime Right-click > View/Edit Data > Filtered Rows Enter filter - e.g. `burglary > 10` and `click` OK	Edit the QGIS connection to the database so that Also list tables with no geometry is checked (otherwise you won’t be able to access the table below) Go to [database name] >> crime > neighbourhood_crime Double-click to load data In Layers panel, right-click > Filter Build filter from dialog Click OK
4	Run SQL	Go to Tools > Query Tool (or toolbar button) Type SQL, e.g. `SELECT * FROM crime.neighbourhood_crime` Click F5 to execute	Go to Database > DB Manager Navigate to database Click SQL Window button in toolbar Type SQL, e.g. `SELECT * FROM crime.neighbourhood_crime` Click Execute

PGIS-02 13: Postgres End User Tools