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
