fr
en
PostgreSQL Administration and PostGIS Fundamentals
Your applications depend on well-administered databases. With PostgreSQL, connect configuration, operations and maintenance to work more methodically. Build your autonomy and your ability to monitor the environment against service requirements.
- Duration
- 5 days 35 hours
- Code
- SIG05FR Code
Presentation
This course explores PostgreSQL relational databases using graphical and command-line administration tools. Participants use these tools to configure the database, then learn to manage the server. The course covers best practices for building a production architecture, followed by access security and data protection. Database administration, monitoring and diagnostics, performance measurement, backup and recovery are also examined in depth.
A second module covers installation of the PostGIS spatial data extension. Participants learn how it manages spatial geometries through geometric functions for creating them and spatial relationships for analysis. They learn to manage spatial reference systems and use different methods to import GIS data into a PostGIS table.
Objectives
The course covers PostgreSQL database installation, configuration, optimisation and administration, enabling participants to operate a PostgreSQL database in production. It addresses database monitoring methods and tools, as well as approaches to detecting, diagnosing and resolving query execution issues. Participants learn to back up and restore their databases.
A second module covers installation of the PostGIS spatial data extension. Participants gain the knowledge needed to manage and process geometries, define coordinate reference systems, create functions (triggers) and use tools for importing data into a PostGIS database.
Program
PostgreSQL database administration
- Introduction
- Using pgAdmin 4.
- Using psql.
- Exploring the database.
- Configuration
- Default configuration.
- Custom configuration.
- Adding external modules.
- Server control
- Starting and stopping the server.
- Using multiple schemas.
- Tables and data
- Naming best practices.
- Loading data from tables.
- Loading data from files.
- Database security
- User management.
- LDAP integration.
- Data encryption.
- Database administration
- Editing table definitions.
- Managing schemas.
- Managing tablespaces.
- Views and data updates.
- Monitoring and diagnostics
- Monitoring database activity.
- Monitoring disk space.
- Performance analysis
- Identifying causes of slow queries.
- Using indexes.
- Optimising queries.
- Parallelism.
- Backup and recovery
- Backing up one, several or all databases.
- Restoring one, several or all databases.
Getting started with PostGIS
- Installing PostGIS.
- Geometry types.
- Organising spatial data
- Columns containing heterogeneous geometries.
- Homogeneous geometries.
- Table inheritance.
- Using rules and triggers.
- Geometric functions
- Constructors.
- Outputs.
Getters and setters.
Measurement functions.
Decomposition. - Boxes and envelopes.
- Coordinates.
- Boundaries.
Point markers for geometries: centroids.
Composition.
- Multi- and single-part geometries.
- Creating points, lines and polygons.
Simplifying geometries.
- Relationships between geometries
- Intersections.
- Interior.
- Exterior.
- Adjacent.
- Contains.
- Is contained.
- Covers.
- Is covered.
- Touches.
- Crosses.
- Disjoint.
- Reference system considerations
- Spatial reference systems.
- Projections.
- Geographical coordinates.
- Determining the source data reference system.
- Data import tools
- Tools included with PostGIS.
- Ogr2ogr.
- QGIS Shapefile to PostGIS.
- Tools included with PostGIS.
Audience
This course is designed both for PostgreSQL beginners with no prior knowledge and for people who have used PostgreSQL as a local or development database. It provides the knowledge needed to operate a production database.
Prerequisites
--
Teaching and assessment methods
- Initial skills assessment
- Training materials provided to participants
- Continuous assessment throughout the course
- End-of-course feedback questionnaire
- Combination of theory and practical application
- Attendance records
- Post-course follow-up evaluation
Dates and sessions
Choose the date and delivery format that suit you.
No upcoming sessions are currently available.