Tuesday, October 16

PostgreSQL installer by 2ndQuadrant – now supports OmniDB!

2ndQuadrant, Marriya's PlanetPostgreSQL, OmniDB, PGInstaller
PostgreSQL installer or PGInstaller - is a user-friendly, graphical installation and configuration tool for PostgreSQL. With just a few clicks you can install PostgreSQL - version 9.5, 9.6, 10 and 11(beta) - on Windows, Linux and macOS. The latest PGInstaller release includes support for OmniDB - an interactive and user-friendly database management tool to manage multiple databases in a unified workspace. Using utilities bundled with PostgreSQL, the only means to connect to the database is via psql. Psql works via the command line, which can be fairly tricky for new users especially if they are migrating from another database and are not used to the interface. PGInstaller makes the connection process easier with OmniDB. PGInstaller comes bundled with OmniDB, as an optional (more…)

[Video] Data Integration with PostgreSQL

PostgreSQL, Shaun's PlanetPostgreSQL
Just in case you missed the live broadcast, the video of my presentation below covers various topics around integration of PostgreSQL with other data sources and database technologies. This presentation covers the following topics: What is a Foreign Data Wrapper? How to query MySQL, a Flat file, a Python script, a REST interface and a different Postgres Node Perform all of the above simultaneously Take snapshots of this data for fast access Tweak remote systems for better performance Package as an API for distribution Stream to Kafka Write data to... MongoDB!? What does all of this have in common? It's an exciting topic, and I hope more developers and admins begin to see Postgres as the global integration system it really is (more…)

PostgreSQL and IoT Data Localization, Integration, and Write Scalability

Haroon's PlanetPostgreSQL, Internet of Things
In my previous post we looked at various partitioning techniques in PostgreSQL for efficient IoT data management using IoT Solution. We do understand that the basic objective behind time based partitions is to achieve better performance, especially in IoT environments, where active data is usually the most recent data. New data is usually append only and it can grow pretty quickly depending on the frequency of the data points. Some might argue on why to have multiple write nodes (as would be inherently needed in a BDR cluster) when a single node can effectively handle incoming IoT data utilizing features such as time based partitioning. Gartner estimated 8.4 billion connected devices in 2017, and it expects that this number will grow to over 20 billion by 2020. The scale at which (more…)

Talk slides: Partitioning Improvements in PostgreSQL 11

Alvaro's PlanetPostgreSQL, PostgreSQL
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…)

PostgreSQL Time-based Partitioning for IoT Data using pg_partman

Haroon's PlanetPostgreSQL, Internet of Things
This blog continues the discussion from my previous post on IoT Solution's scalability for IoT workloads where I discussed how declarative partitioning in PostgreSQL 10 can help achieve scalability. While native declarative partitioning is a good start, the experience of creating and maintaining the same partitions I did in my last post becomes much more fun with pg_partman. pg_partman is an extension to create and manage both time-based and serial-based table partition sets. Native partitioning in PostgreSQL 10 is supported as of pg_partman v3.0.1. It is important to note that all the features of trigger-based partitioning are not yet supported in native, but performance in both reads and writes is significantly better. Since Postgres-BDR runs as an extension on PostgreSQL, we can (more…)

PGInstaller – A Step by Step Guide to install PostgreSQL

2ndQuadrant, Liaqat's PlanetPostgreSQL, PGInstaller, PostgreSQL
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 Step 1: Download PGInstaller here. PGInstaller is available for PostgreSQL 9.5, 9.6, 10, and 11(beta). Step 2: Click on the executable file to run the installer. Step 3: Select your preferred language. Step 4: Specify directory where you want to install PostgreSQL. Step 5: Specify PostgreSQL server port. You can leave this as default if you’re unsure what to enter. Step 6: Specify data directory to initialize PostgreSQL database. Step 7: Create a PostgreSQL user (more…)

Webinar : Ansible & PostgreSQL [Follow Up]

2ndQuadrant, Liaqat's PlanetPostgreSQL, Webinars
PostgreSQL administration, configuration, and deployment can be a tough ask while working in an agile environment with strict deadlines. The key to simplify these operational tasks for PostgreSQL is Ansible - an open source IT automation tool. To explain how these technologies work together, 2ndQuadrant hosted a Webinar on PostgreSQL deployments using Ansible. The webinar was presented by Tom Kincaid, GM North America at 2ndQuadrant, who gave an overview of Ansible and PostgreSQL, covered best strategies for deployments, and a variety of other topics. If you weren’t able to make it to the live session, you can now view the recording here. For any questions, comments, or feedback, please visit our website or send an email to [email protected] (more…)

[Video] Introduction to JSON data types in PostgreSQL

Andrew's PlanetPostgreSQL, PostgreSQL
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…)

Supporting the Elephant

Simon's PlanetPostgreSQL
The elephant has been the symbol of PostgreSQL for many years now, referring to the robustness and strength as well as its reputed wisdom. Long may that association continue. Even after many years of protection, the elephant is being killed by poachers at an incredible rate of 20,000 per year, or approximately 1 elephant will be killed while you read this. The things we care about can be destroyed if we do nothing. If the online trade in ivory can be reduced, we can reduce the killing. Please contribute in some way, and report traders if you see them. https://www.worldwildlife.org/pages/global-coalition-to-end-wildlife-trafficking-online

Scaling IoT Time Series Data with Postgres-BDR

2ndQuadrant, Haroon's PlanetPostgreSQL, Internet of Things
A couple of weeks back, I wrote about how to use Windows Functions for time series IoT analytics in Postgres-BDR. This post follows up on IoT Solution's time series data and covers the next challenge: Scalability. ‘Internet of Things’ is the new buzzword as we move to a smarter world equipped with more advanced technologies. From transport to building industry, smart homes to personal gadgets, it’s not just about gadgets and sensors anymore. In reality, it is all about data. Not just simple data, but data that grows at an enormous rate. Businesses and application developers in Internet of Things domain face some similar questions today in terms of finding the best combination of technologies to support them. Without a doubt, database remains at the core of any such decision (more…)