I spent a couple of days in São Paulo, Brazil last week, for the top-notch PGConf.Brazil 2018 experience. This year I gave a talk about improvements in the declarative partitioning area in the upcoming PostgreSQL 11 release — a huge step forward from what PostgreSQL 10 offers. We have some new features, some DDL handling enhancements, and some performance improvements, all worth checking out.
I'm told that the organization is going to publish video recordings at some point; for the time being, here's my talk slides.
I'm very happy that they invited me to talk once again in Brazil. I had a great time there, even if they won't allow me to give my talk in Spanish! Like every time I go there, I regret it once it's time to come home, because it's so easy to feel at home with the (more…)
Here’s a step by step guide to install PostgreSQL on your machine using PGInstaller. PGInstaller supports three modes of installation; Graphical, Unattended and Text. We’re going to cover all three of them in this guide.
To Install PostgreSQL via Graphical Mode
Download PGInstaller here. PGInstaller is available for PostgreSQL 9.5, 9.6, 10, and 11(beta).
Click on the executable file to run the installer.
Select your preferred language.
Specify directory where you want to install PostgreSQL.
Specify PostgreSQL server port. You can leave this as default if you’re unsure what to enter.
Specify data directory to initialize PostgreSQL database.
Create a PostgreSQL user (more…)
The video of my presentation below walks you through the major features of the native JSON data type in PostgreSQL 9.3 and beyond.
This presentation covers the following topics:
What is JSON?
How is it available in PostgreSQL?
What's the difference between JSON and JSONB?
Accessing JSON values
Creating JSON from table data
Creating table data from JSON
Crosstabs with JSON
Indexing and JSON
When to use JSON, when to use JSONB, and when neither should be used (more…)
Installing the world’s top enterprise-class open source database - PostgreSQL - is now a whole lot easier with PGInstaller.
PGInstaller is a GUI based, user-friendly installer for PostgreSQL that is digitally signed and certified by 2ndQuadrant. The installer is currently available for PostgreSQL versions 9.5, 9.6, 10 and 11(beta) and has the ability to run in graphical, command line, or quiet installation modes.
In addition, PGInstaller provides the following features:
Built-in support for Python 3
Compression support using zlib
Integration with native service control managers such as systemd for Linux, Service Control Manager for Windows, and LaunchControl for OSX
Consistent interface across all supported platforms
How to get started with PGInstaller:
I've started to write about the tool (pglupgrade) that I developed to perform near-zero downtime automated upgrades of PostgreSQL clusters. In this post, I'll be talking about the tool and discuss its design details.
You can check the first part of the series here: Near-Zero Downtime Automated Upgrades of PostgreSQL Clusters in Cloud (Part I).
The tool is written in Ansible. I have prior experience of working with Ansible, and I currently work with it in 2ndQuadrant as well, which is why it was a comfortable option for me. That being said, you can implement the minimal downtime upgrade logic, which will be explained later in this post, with your favorite automation tool.
Further reading: Blog posts Ansible Loves PostgreSQL , PostgreSQL Planet in Ansible Galaxy and (more…)
The world of PostgreSQL continues to grow stronger by the year!
Last week we saw it all come together at PostgresConf US 2018 in Jersey City. 2ndQuadrant was proud to participate again this year as a platinum sponsor. The conference was held to promote awareness and usage of PostgreSQL through tutorials and case-studies, as well as providing the opportunity to listen first hand to some of the best minds in the community.
The 5-day event allocated two days to hands-on trainings on various PostgreSQL development and management topics. The remaining days were filled with over 80 breakout sessions covering everything related to PostgreSQL you could imagine.
At 2ndQuadrant, we take pride in supporting the continued development of the world’s most advanced open source database. (more…)
Last week, I was at Nordic PGDay 2018 and I had quite a few conversations about the tool that I wrote, namely pglupgrade, to automate PostgreSQL major version upgrades in a replication cluster setup. I was quite happy that it has been heard and some other people in different communities giving talks at meetups and other conferences about near-zero downtime upgrades using logical replication. Given that there is a talk that I gave at PGDAY'17 Russia, PGConf.EU 2017 in Warsaw and lastly at FOSDEM PGDay 2018 in Brussels, I thought it is better to create a blog post to keep this presentation available to the folks who could not make it to any of the conferences aforementioned. If you would like to directly go the talk and skip reading this blog post here is your link: Near-Zero Downtime (more…)
Last month, I was at 2ndQuadrant booth at P2D2, an annual conference organized in Prague by local user group called CSPUG (Czech and Slovak PostgreSQL User Group). The conference was certainly a huge success and more than 200 people showed up! Meeting a lot of PostgreSQL enthusiasts in town, I wondered why there are no regular meetups in Prague. At the conference after-party, I started to question folks and learned that given a chance some of them would give talks and many more would be happy to attend. The initial reactions verified my observation and I felt motivated enough to start organizing meetups myself!
Even though I've been living in Prague for some time now, it has been a little difficult to manage the myself all by myself. My colleague Tomas Vondra stepped forward to (more…)
Two serious security vulnerabilities (code named Meltdown and Spectre) were revealed a couple of weeks ago. Initial tests suggested the performance impact of mitigations (added in the kernel) might be up to ~30% for some workloads, depending on the syscall rate.
Those early estimates had to be done quickly, and so were based on limited amounts of testing. Furthermore, the in-kernel fixes evolved and improved over time, and we now also got retpoline which should address Spectre v2. This post presents data from more thorough tests, hopefully providing more reliable estimates for typical PostgreSQL workloads.
OmniDB is an open source web tool designed specifically for managing relational databases with focus on simplicity and user interaction, but at the same time be fast, powerful and light on memory. OmniDB, at the moment, supports several different relational database systems such as PostgreSQL.
The latest release of OmniDB, version 2.3.0, has introduced ‘Debugger for PL/pgSQL functions’ - a much requested feature for OmniDB users.
If you’re a PostgreSQL user and want to extract the power of its functionalities through a simple GUI tool, here is Simon Riggs from 2ndQuadrant demonstrating how to run OmniDB on top of PostgreSQL 10.
My name is Simon Riggs from 2ndQuadrant, and I’m going to give you a demo of OmniDB running on top of Postgres 10.
What is OmniDB (more…)