Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Steve Pousty
Steve Pousty
In today's blog post I am going to show you how to easily spin up PostGIS in your Kubernetes or OpenShift cluster using the PostgreSQL Operator . You may have already started to investigate how to take advantage of all the Kubernetes goodness, especially combining it with your favorite spatial database, PostGIS. Well have no fear, after a few introductory steps I will show you how one command can bring up PostGIS and then with another command, make it a replicated cluster. This replicated...
Read MorePaul Ramsey
Paul Ramsey
In our last installment , we covered the use of a constraint trigger to enforce data quality by looking at geometry spatial relationships. For this installment, we'll start with basic relationships and then look at more complex use cases: deferred constraints, and full table-level data structures. Linear Network Constraints Let's start with a simple road network. We've added a couple simple check constraints in the table definition: • we want our road segments to have a non-zero length; and, •...
Read MorePaul Ramsey
Paul Ramsey
If constraints in general have caught your interest, our interactive learning portal has a whole section on the use of non-spatial constraints , even a video walkthrough! In our last installment , we covered the use of CHECK constraints to enforce data quality at an object level. However, spatial data quality usually involves higher order relationships between geometries. Just as a strong non-spatial model will enforce foreign key relationships, spatial constraints can be used to enforce s...
Read MoreCraig Kerstiens
Craig Kerstiens
At a company where most all people have some Postgres expertise you can easily learn something new from your coworkers every day about Postgres. In my first week I saw a question in our internal slack that I could guess an answer to, but it wasn't definitive. It was "Why have composite types? Why would you use them?". I threw in an answer a few others did as well, but collectively we didn't have anything definitive but all these seemed like valid cases. Composite types ) are a custom type that i...
Read MoreKat Batuigas
Kat Batuigas
This post is the second in a two-part series -- read the first here: Going Back to Basics with PostgreSQL Data Types . In my last post, I shared some interesting (and at times surprising) things that I learned while digging into data types in PostgreSQL. Data types like numeric, integer, date, and char/varchar exist in every other relational database system since the need to work with such data is pretty much a given. The implementation may vary somewhat between systems, but generally there are...
Read MoreKat Batuigas
Kat Batuigas
When I first started to learn how to code, I was introduced to the concept of data types: a 6 is not the same as "6", because the former is numeric (typically an integer type, with some variations in terminology based on the language) and the latter a string; "true" is not necessarily the same as true, because true can be a Boolean value in some languages. Underneath the code, these pieces of data are really just a combination of ones and zeros, but declaring their types allows them to play a pa...
Read MoreSteve Pousty
Steve Pousty
Greetings friends of Crunchy Data, it is my pleasure to announce the initial release of our application developer portal . An awesome team has been working behind the scenes to bring together this nice little website to help application developers find all their Postgres needs in one place. Our goal is to become a single-stop resource for application developers looking to work with PostgreSQL. We have released three main parts to the site that form the foundation for future growth. Let’s go ove...
Read MoreMark Lane
Mark Lane
Let's say you needed to document all of the tables in your PostgreSQL database. You wanted the output of the psql meta-command for all of the tables so you could put it in a shared documentation area. However, there were a lot of tables and you did not want to have to type all the commands that you needed. Before I explain how I can help, we will need to set up a simple database and provide some background on the psql meta-command. Open a connection to a Postgres instance with the psql clien...
Read MoreJonathan S. Katz
Jonathan S. Katz
Advanced PostgreSQL high-availability and disaster recovery strategies designed to maximize uptime involve distributing database clusters across multiple data centers. While on the surface, this may seem intuitive (e.g. eliminate outages due to a single-point-of-failure), there are many nuances to consider, including avoiding the dreaded split-brain problem. To set up a PostgreSQL deployment that spans across data centers (or regions, or Kubernetes clusters), and to mitigate the risk of a...
Read MoreJonathan S. Katz
Jonathan S. Katz
When I first started working with PostgreSQL and containers , one of the first items presented to me was a recipe to get PostgreSQL 10 setup with pgAdmin 4 using Docker, which was over two years ago. Quite a bit has changed in the ecosystem since then, including PostgreSQL itself! As such, it felt like it was time to update the recipe and work through some new examples. Much like before, there's a " tl;dr " if you do not wish to read through my ramblings, but a more in depth explanation there...
Read More