PostgreSQL 9.5 has a new feature called Row Level Security (RLS). Using RLS, a PostgreSQL extension called sepgsql (somewhat modified), and Security Enhanced Linux (SELinux) configured properly, it is possible to create a Multi-level Security, PostGIS enabled, PostgreSQL database. This talk will describe how it can be done, including:
pgBackRest is open source software developed to perform efficient backup on PostgreSQL databases that measure in tens of terabytes and greater. It supports per file checksums, compression, partial/failed backup resume, high-performance parallel transfer, asynchronous archiving, tablespaces, expiration, full/differential/incremental, local/remote operation via SSH, hard-linking, restore, and more. pgBackRest is written in Perl and does not depend on rsync or tar but instead performs its own deltas which gives it maximum flexibility.
It's actually quite simple: If fields are updated or rows deleted, a trigger is called storing the changes in one audit table as JSONB. These snippets can be queried efficiently using GIN indexing and we can put them back together to create historical replicas of rows / tables / databases.
“Esri union” is a function that is often used by Arcgis users and in many cases an important reason why people still need Arcgis software, even when the data area available in Postgis. For NIBIO the Arcgis software was working ok with smaller datasets, but not bigger datasets. So we asked the users why not run this is in Postgis ? The answer was “The function is not available in Postgis, can you make one that works ?”
In this presentation we will go into how to manage
Street names carry local context and meaning and can vary locally, even within a relatively small geographic envelope, frequently appearing in languages other than English. The United States Federal Geographic Data Committee (FGDC) in 2011 released a data content standard for storing address data in a highly normalized way. This standard was intended for use within the United States of America and principally for parsing English language street names. This session will look at how to use this the FGDC address data standard for processing and storing non-English street names.
Many database administrators were first introduced to PostgreSQL years ago and have memories (good & bad) of using version numbers starting with 6, 7 & 8. Many people are still running these old versions as well, nervous about upgrading and unsure of the benefits. With the rise in popularity of MySQL, many open source advocates turned there since the majority of internet community support was centered around it. And the advanced features that commercial databases such as Oracle & SQL Server were advertising appealed to many enterprise administrators.
BRINs (Block Range INdexes) are one of the major new features introduced with PostgreSQL 9.5. They allow to directly select just blocks of table pages needed for queries execution, resulting on smaller indexes that can be easily contained in memory and that require less maintenance than the existing ones.
These features make BRIN particularly suitable for very large tables, and more in general for query with a low-selectivity that would be executed preferring a sequential scan of all tables blocks instead of using indexes.