Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Jason O'Donnell
Jason O'Donnell
One of the biggest benefits of running PostgreSQL is running your cluster in primary-replica setup for the purposes of high-availability or load balancing your read-only queries. It is not necessarily simple to deploy a primary-replica setup out of the box, but by using modern containerization technology, we can greatly simplify the process. In this article, I will demonstrate how to easily setup and deploy a PostgreSQL primary-replica cluster using Docker and Docker Swarm . Streaming replica...
Read MoreJonathan S. Katz
Jonathan S. Katz
On March 1, 2018, the PostgreSQL community released version 10.3 and other supported versions of PostgreSQL. The release centered around a disclosed security vulnerability designated CVE-2018-1058, which is related to how a user can accidentally or maliciously "create like-named objects in different schemas that can change the behavior of other users' queries." The PostgreSQL community released a guide around what exactly CVE-2018-1058 is and how to protect your databases. However, we thoug...
Read MoreJonathan S. Katz
Jonathan S. Katz
One of the many reasons that PostgreSQL is fun to develop with is its robust collection of data types, such as the range type . Range types were introduced in PostgreSQL 9.2 with out-of-the-box support for numeric (integers, numerics) and temporal ranges (dates, timestamps), with infrastructure in place to create ranges of other data types (e.g. inet/cidr type ranges). Range data is found in many applications, from science to finance, and being able to efficiently compare ranges in PostgreS...
Read MoreJonathan S. Katz
Jonathan S. Katz
Row Level Security , aka " RLS ," allows a database administrator to define if a user should be able to view or manipulate specific rows of data within a table according to a policy . Introduced in PostgreSQL 9.5 , row level security added another layer of security for PostgreSQL users who have additional security and compliance considerations for their applications. At Crunchy Data, we care a lot about data security and supporting PostgreSQL. When we discovered an issue with creating a larg...
Read MoreJonathan S. Katz
Jonathan S. Katz
UPDATE : Want to learn how to use pgAdmin 4 with PostgreSQL 12 ? Then check out Easy PostgreSQL 12 and pgAdmin 4 Setup with Docker ! The open source software ecosystem around PostgreSQL is as robust as the database itself, but sometimes it can be hard for people new to PostgreSQL, and even some seasoned veterans, to get all of the software dependencies for their development environment setup on their computers. At Crunchy Data , we obsess on making things as easy as possible for people to g...
Read MoreKeith Fiske
Keith Fiske
I’ve been noticing that the query used in v1.x of my pg_bloat_check.py script (obtained from the check_postgres.pl module) was not always accurate and was often not reporting on bloat that I knew for a fact was there (Ex: I just deleted over 300 million rows, vacuumed & analyzed the table and still no bloat? Sure it could happen, but highly unlikely). So I continued looking around and discovered the pgstattuple contrib module that comes with PostgreSQL. After discussing it with several of...
Read MoreJoe Conway
Joe Conway
This is the third and final post of the series intended to introduce PostgreSQL users to PL/R, a loadable procedural language that enables a user to write user-defined SQL functions in the R programming language . The information below provides sample use of R Functions against the NDVI dataset. As introduced in the previous posts, the combination of PostgreSQL and R provides users with the ability to leverage the power and efficiency of PostgreSQL and the rich analytic functionality of R. When...
Read MoreJoe Conway
Joe Conway
This is the second in a series of posts intended to introduce PostgreSQL users to PL/R , a loadable procedural language that enables a user to write user-defined SQL functions in the R programming language . This post builds on the example introduced in the initial post by demonstrating the steps associated with preprocessing the Normalized Difference Vegetation Index ( NDVI ) satellite raster data in preparation for spatial analytics. The first post in this series provided users with an...
Read MoreJoe Conway
Joe Conway
This is the first in a series of posts intended to introduce PostgreSQL users to PL/R, a loadable procedural language that enables a user to write user-defined SQL functions in the R programming language . When further combined with PostGIS, the geospatial extender for PostgreSQL, users can perform powerful spatial analytics within the PostgreSQL database. This initial post introduces PL/R and R, provides set up instructions for following the Spatial Analytics example to be used in this series...
Read MoreJeff McCormick
Jeff McCormick
Crunchy Data is pleased to announce an initial implementation of a PostgreSQL Operator for Kubernetes to build on our work with PostgreSQL Containers. This initial implementation provides a user with the ability to perform certain PostgreSQL functions including creating PostgreSQL clusters, performing database backup and restores and viewing persistent volume claims. Last November the team at CoreOS introduced the concept of an “application-specific controller” for Kubernetes called software Op...
Read More