In our last blog post about pg_featureserv, we showed how it can publish spatial datasets and access them via simple web requests. In this post, we’re going to discuss how publishing PostgreSQL

In our previous posting on tile serving, we showed off how pg_tileserv can use database functions to generate tiles by querying existing tables with user parameters.

We can also use functions to build geometry on the fly without input from tables. For example, hexagons!

Hexagons are a favourite input for visualizations, because they have a nice symmetric shape and provide equal areas for summarization.

Hexagonal Grid

In my previous posting on tile serving, I demonstrated how pg_tileserv can publish spatial tables as dynamic vector tiles.

Dynamic tiles

In addition to viewing PostGIS spatial data as vector tiles using pg_tileserv, it is often necessary to access data features directly. This supports use cases such as:

• display features at a point or in an area of interest
• query features using spatial and/or attribute filters
• retrieve features for use in a web application (for tabular or map display)
• download spatial data for use in applications

Beautiful, responsive maps are best built using vector tiles, and PostgreSQL with PostGIS can produce vector tiles on-the-fly.

However, to use vector tiles in a beautiful, responsive map, you need to be able to access those tiles over the HTTP web protocol, and you need to be able to request them using a standard XYZ tiled map URL

Let's put that on a map!

PostGIS has so many cool features that it is possible to do full GIS analyses without ever leaving the SQL language, but... at the end of the day, you want to get those results, show that data, on the map.

The Crunchy Data geospatial team has been thinking about how to bring PostGIS to the web, and we established some basic principles:

• Use the database, as much as possible.

• The database already has a user model and security model.

• The database can already generate output formats, like MVT

PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature. However, many people set up partition sets before native partitioning was available and would greatly benefit from migrating to it. This article will cover how to migrate a partition set using the old method of triggers/inheritance/constraints to a partition set using the native features found in PostgreSQL 11+. Note these instructions do not cover migrating to PG10 since some key features that make this migration easier were not yet implemented. It is highly recommended to move to PG11 or higher if you want to migrate existing partition sets.

Also note that while this migration article is specific to pg_partman

Crunchy Data recently released its latest version of the open source PostgreSQL Operator for Kubernetes, version 4.2. Among the various enhancements included within this release is support for Synchronous Replication within deployed PostgreSQL clusters.

As discussed in our prior post

It is important (understatement) that you take regularly scheduled backups of your PostgreSQL system as well as manage how many backups you have, which is known as "backup retention." These best practices ensure that you always have a recent backup of your database system to recover from in the event of a disaster (or use to clone a new copy of your database

Migrating to PostgreSQL from Oracle is a topic that often comes up in discussions around PostgreSQL. At Crunchy Data, we are of course not surprised that there is broad interest in moving to the world's most advanced database.

There are a lot of great reasons to migrate to PostgreSQL, and if you haven't looked at PostgreSQL lately, it would be a good time to do so again given the various improvements in PostgreSQL 12.

That said, migrating from one database to another is inherently new technology and can raise a multitude of questions. To help ease the transition, we are providing a few frequently asked questions and answers from users transitioning from Oracle to PostgreSQL, based on real life scenarios, to serve as a helpful resource.

Common Questions, Differences and Solutions