PUBLIC / CYHOEDDUS

Postgres, PostGIS, and Spatial Databases

Postgres/PostGIS installation
Database administration and management
 PUBLIC / CYHOEDDUS

Previously…
What is Postgres / PostGIS / pgAdmin / Dbeaver / QGIS / CES-GIS

Access to Postgres (your database, usw_teaching database)

on CES-GIS server via pgAdmin and other clients

Creating a Query Tool to issue SQL commands

 PUBLIC / CYHOEDDUS

This time

Review solutions/answers to questions posed in Exercise 1

Review use of " " and ' ' quotes in Postgres/pgAdmin

Database management skills, using DCL and DDL

Schemas, the search_path, creating a spatial database

Saving and loading SQL scripts; adding Comment lines

Backup & Restore to transfer/replicate data
 PUBLIC / CYHOEDDUS

Postgres database installation,

administration, and management
 PUBLIC / CYHOEDDUS

to install Postgres/PostGIS (& QGIS) on a laptop/PC…

 PUBLIC / CYHOEDDUS
Execute installer, follow prompts…
…best run as Administrator
 PUBLIC / CYHOEDDUS

superuser password **remember it**

 PUBLIC / CYHOEDDUS

add PostGIS
extension

www.qgis.org QGIS –
download and
run installer
 PUBLIC / CYHOEDDUS
Working on remote sever…

Two machines involved – your laptop & the remote server

postgres runs on remote server

pgAdmin client runs on your PC/laptop

pgAdmin must connect to a server – 81.87.34.57 or ces-gis

supply username & password – e.g. “12345678” / “eggs”
 PUBLIC / CYHOEDDUS
Working on personal PC/laptop…

One machine involved – your laptop/PC

both Postgres server (background service)

and pgAdmin client program (in browser)

pgAdmin must connect to a server – 127.0.0.1 or localhost

supply username & password for authentication
use superuser account (eg postgres/postgres) set during installation
PUBLIC / CYHOEDDUS
PUBLIC / CYHOEDDUS
 PUBLIC / CYHOEDDUS

Instructions for creating tables: example given in Oracle

– much the same in Postgres !

Instructions for inserting data vales into tables:

- much the same in Postgres !

Instructions for SELECTing data from tables:

- much the same in Postgres !
 PUBLIC / CYHOEDDUS

…same

…same

…same
 PUBLIC / CYHOEDDUS

Postgres database installation,

administration, and management
 PUBLIC / CYHOEDDUS

• Create and manage Users & Roles

• Create Databases & Extensions

• Set Privileges

• Create / Alter / Drop / Grant / Revoke

PUBLIC / CYHOEDDUS
 PUBLIC / CYHOEDDUS

create a new user/role (DDL)…

create role "12345678b" with password 'eggs' login;

all object names in Postgres are case sensitive

stick to using lower case if possible
use correct double quotes "newuser" not “newuser”
 PUBLIC / CYHOEDDUS

without login parameter, not a User, but a Group Role

can modify (add or remove login status)

later by using DDL command alter
 PUBLIC / CYHOEDDUS

alter a user/role using DDL…

alter role "12345678b" with nologin;

alter role "12345678b" with password 'eggs' ;

alter role "12345678b" with connection limit 10 ;

alter role "12345678b" with createrole;

 PUBLIC / CYHOEDDUS

drop a user/role using DDL…

provided you have sufficient privilege, use DDL command
drop to delete users (and other objects) from the system.
Something like….

drop role "12345678a";

only allowed if all objects owned by this user

are dropped first, or reallocated

drop owned by "12345678a" cascade;

 PUBLIC / CYHOEDDUS

create a database…

Postgres works on the concept of a database cluster

– a collection of databases managed by a single
instance of a server

Database postgres, is set up at installation, and can

can be used by all users, external applications, etc.

Further databases are created as required

By default, a user cannot create a database

unless granted this privilege with…

alter role "12345678b" with createdb;

 PUBLIC / CYHOEDDUS

create a database…

a new database can be created through SQL

or via pgAdmin’s GUI…

create database "12345678a";

 PUBLIC / CYHOEDDUS

create a database…

Database, and other objects ( schema,

table, etc), have an owner – normally set
as the user that created them

create a schema…
Same ideas apply to schemas,
except a user does not need special
privilege to create these..

create schema myschema;

 PUBLIC / CYHOEDDUS

create a spatial database…

When you create a new database it is a ‘normal’
(i.e. non-spatial) Postgres relational database

To make it spatial you must add the PostGIS extension –

only possible via a superuser account

Expand Extensions node

- if only one entry (plpgsql) database
is just a normal relational Db
 PUBLIC / CYHOEDDUS

create a spatial database…

Use pgAdmin’s GUI to add PostGIS

extension, or add via SQL…

create extension postgis;

 PUBLIC / CYHOEDDUS

Extension adds 1000+ functions,

several new data types, 500+ map
projections, and more !
 PUBLIC / CYHOEDDUS

on ces-gis you cannot create a

database or add PostGIS
– so already done for you

on localhost you must do

both for yourself
 PUBLIC / CYHOEDDUS

Working with privileges…

Control who can do what with which objects…

DCL command grant sets various privileges on Postgres objects

(database, schema, table, and others) – and can assign users to group roles

All members of a group role are granted any privileges

applied to the group – this is their main purpose

Some examples…

grant select on table_X to role_A;

revoke all on table_X to role_A;

revoke all on table_X to public;
 PUBLIC / CYHOEDDUS

Your account and database on ces-gis was created with…

create role "12345678" with password 'eggs' login;

grant "is565" to "12345678";

create database "12345678"

with encoding='utf8'
owner="12345678"
connection limit=20;

create extension postgis;

revoke connect on database "12345678" from public;
grant connect on database "12345678" to "12345678";

This includes the use of grant and revoke to control access to

the database – only named user (and superuser) can use it
 PUBLIC / CYHOEDDUS

You will want to let QGIS

as an external application
have access your spatial
tables, so that it can
display them

revoke all on schema ex4 from public;

grant usage on schema ex4 to public;
grant select on all tables in schema ex4 to public;
 PUBLIC / CYHOEDDUS

Dropping databases, roles, schemas, tables, …

drop the reverse of create

But intentions can become
complicated by ownership

drop role "12345678";

drop database "12345678";

drop schema myschema;

drop schema myschema cascade;

drop table mytable;

 PUBLIC / CYHOEDDUS

Cannot drop a database

unless you are the owner
(or the superuser)

Cannot drop a user if

they still own objects
within the cluster

drop owned by "12345678" cascade;

drop schema myschema cascade;

reassign owned by "12345678" to "another";

 PUBLIC / CYHOEDDUS

Using backup & restore for data security and transfer…

Several ways to backup a Postgres

database. Tools provided in
pgAdmin are useful for both data
security, and data transfer

Can backup an entire database,

just a schema, or an individual
table (see details on Bb)
 PUBLIC / CYHOEDDUS

By creating a backup on one server, and restoring the file to

another – you can in effect copy or replicate database contents

Use to replicate usw_teaching data on your laptop/PC

 PUBLIC / CYHOEDDUS

vacuum and analyze…

Postgres maintains metadata – to optimise queries and improve

system performance. Updated periodically

If you add or delete a lot of data in a table,

use these to recover disk space, and recalculate statistics