Skip to content

It's beer o'clock for PostgreSQL

A couple days ago Devrim forked PostgreSQL to get a beer.

That effort is of course laudable, but a bit extreme.

PostgreSQL is very extensible, and allows to implement a beer now clock with just a few easy steps.

Let's walk through this.

 

Continue reading "It's beer o'clock for PostgreSQL"
  • Twitter
  • Bookmark It's beer o'clock for PostgreSQL at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg It's beer o'clock for PostgreSQL
  • Bloglines It's beer o'clock for PostgreSQL
  • Technorati It's beer o'clock for PostgreSQL
  • Fark this: It's beer o'clock for PostgreSQL
  • Bookmark It's beer o'clock for PostgreSQL at YahooMyWeb
  • Bookmark It's beer o'clock for PostgreSQL at Furl.net
  • Bookmark It's beer o'clock for PostgreSQL at reddit.com
  • Bookmark It's beer o'clock for PostgreSQL at blinklist.com
  • Bookmark It's beer o'clock for PostgreSQL at Spurl.net
  • Bookmark It's beer o'clock for PostgreSQL at Simpy.com
  • Bookmark It's beer o'clock for PostgreSQL at blogmarks
  • Bookmark It's beer o'clock for PostgreSQL with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way

Christophe Pettus posted an interesting challenge here. Two strings which on the surface look equal, but if you ask PostgreSQL to compare them, they are not equal.

Now let me start with a note: Twitter totally screws this challenge up.

How so? Although the two strings are different in the original, when posting this to Twitter the strings are made equal. Where is the fun in that?

I asked Christophe for the original query:

INSERT INTO t VALUES (E'Zo\u0065\u0301', E'Zo\u00e9');

And you end up with the following texts in the table:

SELECT * FROM t;
  a  |  b  
-----+-----
 Zoé | Zoé
(1 row)

If you translate the UTF-8 strings into hex, you get "0x5a 0x6f 0x65 0xcc 0x81" and "0x5a 0x6f 0xc3 0xa9". Clearly they are different.

However if you convert the two strings from the Tweet, you get "0x5a 0x6f 0xc3 0xa9" and "0x5a 0x6f 0xc3 0xa9". Same string. Poor Twitter.

Checking the hex values was actually one of my first ideas when I spotted this challenge. But nevertheless based on my experience from my "Data Types in PostgreSQL" and "Advanced Data Types in PostgreSQL" talks, I figured it should be possible to "solve" this puzzle even if the strings are in fact equal.

Buckle up! We are about to dive deep into how extendible PostgreSQL really is!

 

Continue reading "Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way"
  • Twitter
  • Bookmark Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way
  • Bloglines Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way
  • Technorati Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way
  • Fark this: Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way
  • Bookmark Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way at YahooMyWeb
  • Bookmark Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way at Furl.net
  • Bookmark Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way at reddit.com
  • Bookmark Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way at blinklist.com
  • Bookmark Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way at Spurl.net
  • Bookmark Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way at Simpy.com
  • Bookmark Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way at blogmarks
  • Bookmark Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

GSoC 2021 completed

The Google Summer of Code 2021 for the PostgreSQL Project is wrapped up. The timeline this year was shortened to half, compared to previous years. That’s good, because smaller projects can be worked on, and students have a chance to cope with a changing environment at home and university. On the other hand, the shorter time doesn’t allow diving into more complex projects. Nevertheless, with the help of all mentors, six students successfully concluded their projects.

 

Continue reading "GSoC 2021 completed"
  • Twitter
  • Bookmark GSoC 2021 completed at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg GSoC 2021 completed
  • Bloglines GSoC 2021 completed
  • Technorati GSoC 2021 completed
  • Fark this: GSoC 2021 completed
  • Bookmark GSoC 2021 completed at YahooMyWeb
  • Bookmark GSoC 2021 completed at Furl.net
  • Bookmark GSoC 2021 completed at reddit.com
  • Bookmark GSoC 2021 completed at blinklist.com
  • Bookmark GSoC 2021 completed at Spurl.net
  • Bookmark GSoC 2021 completed at Simpy.com
  • Bookmark GSoC 2021 completed at blogmarks
  • Bookmark GSoC 2021 completed with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

PostgreSQL Project @ GSoC 2021

Wow! The PostgreSQL Project got all 7 proposals accepted into Google Summer of Code 2021!

This year Google changed the participation terms a bit, and cut the time for the students in half. This is supposed to help students who can’t work full-time from home, especially in light of the global pandemic situation. It also means smaller projects, which are easier to handle even for students new to the project.

The PostgreSQL project got a great number of initial applications (29), and we talked with many of the students about refining their proposals. 27 out of the 29 applications were finally submitted by the students. Some are duplicates, some are clearly just copied from somewhere, but many propose good ideas.

After talking with available mentors, and “recruiting” a few more, we settled on 7 final applications, and submitted them to Google.

As usual many of the proposals are not directly developing code for core PostgreSQL, but work on tools and applications from the PostgreSQL ecosystem. Expect some great output over the following months.
 

  • Twitter
  • Bookmark PostgreSQL Project @ GSoC 2021 at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg PostgreSQL Project @ GSoC 2021
  • Bloglines PostgreSQL Project @ GSoC 2021
  • Technorati PostgreSQL Project @ GSoC 2021
  • Fark this: PostgreSQL Project @ GSoC 2021
  • Bookmark PostgreSQL Project @ GSoC 2021 at YahooMyWeb
  • Bookmark PostgreSQL Project @ GSoC 2021 at Furl.net
  • Bookmark PostgreSQL Project @ GSoC 2021 at reddit.com
  • Bookmark PostgreSQL Project @ GSoC 2021 at blinklist.com
  • Bookmark PostgreSQL Project @ GSoC 2021 at Spurl.net
  • Bookmark PostgreSQL Project @ GSoC 2021 at Simpy.com
  • Bookmark PostgreSQL Project @ GSoC 2021 at blogmarks
  • Bookmark PostgreSQL Project @ GSoC 2021 with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Make Ansible "postgresql_ping" fail if the database does not exist

Ansible has a very useful module "postgresql_ping" which checks connectivity to the database server. I'm using it in quite a few Playbooks as first step just to ensure that the database server is present - this fails early if there is a problem which otherwise just prevents the rest of the Playbook to work properly.

TASK [Check if database is available]
[WARNING]: PostgreSQL server is unavailable: could not connect to server: No such file or directory         Is the server running locally and accepting         connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
fatal: [127.0.0.1]: FAILED! => {"changed": false, "failed_when_result": true, "is_available": false, "server_version": {}}

 

However this module does not check if the database exists, just if the server is reachable. Example Playbook:

- name: Check if database is available
  postgresql_ping:
    db: "testdb"
  become: yes
  become_user: postgres

When I run the Playbook:

TASK [Check if database is available]
[WARNING]: PostgreSQL server is unavailable: FATAL:  database "testdb" does not exist
ok: [127.0.0.1]

 

As you can see, the database "testdb" does not exist. Which for the module is a reason to raise a warning, but not  a reason to fail.

One possible solution is to let this module do it's work, and add a "postgresql_db" call next, which ensures that the database is created. But not every Playbook is supposed to create and populate a database, and has all the required parameters (owner, encoding, template ect) available. Therefore it would be nice if "postgresql_ping" fails early if the database in question doesn't exist. That's possible, with two more lines of code:

- name: Check if database is available
  postgresql_ping:
    db: "testdb"
  become: yes
  become_user: postgres
  register: ping_database
  failed_when: ping_database.warnings is defined

And the Playbook run:

TASK [Check if database is available]
[WARNING]: PostgreSQL server is unavailable: FATAL:  database "testdb" does not exist
fatal: [127.0.0.1]: FAILED! => {"changed": false, "failed_when_result": true, "is_available": false, "server_version": {}}

Together with "any_errors_fatal: True" this ends the entire Playbook early enough before I have to debug the problem later on.

  • Twitter
  • Bookmark Make Ansible "postgresql_ping" fail if the database does not exist at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Make Ansible "postgresql_ping" fail if the database does not exist
  • Bloglines Make Ansible "postgresql_ping" fail if the database does not exist
  • Technorati Make Ansible "postgresql_ping" fail if the database does not exist
  • Fark this: Make Ansible "postgresql_ping" fail if the database does not exist
  • Bookmark Make Ansible "postgresql_ping" fail if the database does not exist at YahooMyWeb
  • Bookmark Make Ansible "postgresql_ping" fail if the database does not exist at Furl.net
  • Bookmark Make Ansible "postgresql_ping" fail if the database does not exist at reddit.com
  • Bookmark Make Ansible "postgresql_ping" fail if the database does not exist at blinklist.com
  • Bookmark Make Ansible "postgresql_ping" fail if the database does not exist at Spurl.net
  • Bookmark Make Ansible "postgresql_ping" fail if the database does not exist at Simpy.com
  • Bookmark Make Ansible "postgresql_ping" fail if the database does not exist at blogmarks
  • Bookmark Make Ansible "postgresql_ping" fail if the database does not exist with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Google Summer of Code 2020 - Intermediate status update

The three PostgreSQL projects for this year’s Google Summer of Code are on track, and making good progress. All projects expect to finish on time.

Performance Farm

The data gathering for performance farm members is completed, as well as the new implementation for the JSON data transfer. The project iteratively updated it’s goals, and adjusted for newly identified UI issues.

Current work centers around making the website more pretty and useful, as well as reducing the number of used JavaScript libraries. The next step is presenting the work to the PostgreSQL Community for broader feedback.


PL/Java build system

The PL/Java project has just merged (PR #288) the first major pull request of new code from GSoC, creating a new plugin for the Maven build system that allows its actions to be guided by script snippets clearly exposed in the build files.

The same effect was formerly achieved by a workable but brittle combination of an existing Maven plugin that could handle most of the build requirements with another plugin that was able to run Ant, which was able to run scripts. That resulted in a non-ideal division of labor, where a good deal of build logic was hidden away inside plugins, while some parts were exposed in script out of necessity, rather than because they were interesting or likely to need adjustment.

This pull request proves the concept of a new plugin where the hardcoded Java portions are the uninteresting building blocks, and the overall logic of the build is clearly exposed in script.

For now, the new plugin is used to retire the maven-javadoc-plugin and remove the constraints it had imposed on the project's javadocs (such as the need for absolute URLs for intermodule references, making the resulting tree hard to preview or relocate).

Work continues to reimplement the C native build and retire the nar-maven-plugin and maven-antrun-plugin, to be delivered in a future PR.


WAL-G Performance

We’ve just completed the decoupling of the complex WAL-G internal class. Thanks to it, the new functionality developed in July for a more intelligent backup creation process can now be safely integrated. This feature involves major changes so it requires time to verify that everything is working as expected. We plan to finish the integration in parallel with working on other features.

Currently, we are working on merging the new series of commands for the WAL archives that have been uploaded to storage. These commands will allow end users to analyze the storage for any missing WAL segments that may prevent performing a PITR. Also, Dan now is in the process of implementing the last feature and he expects to finish it on time.


Thanks to all mentors for the status update!

  • Twitter
  • Bookmark Google Summer of Code 2020 - Intermediate status update at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Google Summer of Code 2020 - Intermediate status update
  • Bloglines Google Summer of Code 2020 - Intermediate status update
  • Technorati Google Summer of Code 2020 - Intermediate status update
  • Fark this: Google Summer of Code 2020 - Intermediate status update
  • Bookmark Google Summer of Code 2020 - Intermediate status update at YahooMyWeb
  • Bookmark Google Summer of Code 2020 - Intermediate status update at Furl.net
  • Bookmark Google Summer of Code 2020 - Intermediate status update at reddit.com
  • Bookmark Google Summer of Code 2020 - Intermediate status update at blinklist.com
  • Bookmark Google Summer of Code 2020 - Intermediate status update at Spurl.net
  • Bookmark Google Summer of Code 2020 - Intermediate status update at Simpy.com
  • Bookmark Google Summer of Code 2020 - Intermediate status update at blogmarks
  • Bookmark Google Summer of Code 2020 - Intermediate status update with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Grafana: select host for a dashboard

InfluxDB is running on a Raspberry Pi in my home network (with separate attached disk), and I installed a Grafana on top of it, to visualize crucial data.

In Grafana it is possible to define a variable for a dashboard and this variable can query the data source and use the returned list of values. Let's say the variable is $host, then the data query can use:

WHERE host =~ /^$host$/

and limit the current dashboard to the selected host. Also the variable will provide a select field at the top of the dashboard, which allows selecting the system one wants to see:

Now usually - according to the documentation - a "SHOW TAG VALUES" in the data source should be sufficient However as it is, this did not work for me, and the query came back empty:

> show tag values from system with key = host

Looks like I'm not the only one with this problem.

 

Luckily there is a way around with another query:

select distinct("host") from (select "host","load1" from system)

The result:

> select distinct("host") from (select "host","load1" from system)
name: system
time distinct
---- --------
0    host1
0    host2
0    host3
0    host4
0    host5
0    host6

Grafana ignores the "time" column and uses the second column for the host list. Voila.

  • Twitter
  • Bookmark Grafana: select host for a dashboard at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Grafana: select host for a dashboard
  • Bloglines Grafana: select host for a dashboard
  • Technorati Grafana: select host for a dashboard
  • Fark this: Grafana: select host for a dashboard
  • Bookmark Grafana: select host for a dashboard at YahooMyWeb
  • Bookmark Grafana: select host for a dashboard at Furl.net
  • Bookmark Grafana: select host for a dashboard at reddit.com
  • Bookmark Grafana: select host for a dashboard at blinklist.com
  • Bookmark Grafana: select host for a dashboard at Spurl.net
  • Bookmark Grafana: select host for a dashboard at Simpy.com
  • Bookmark Grafana: select host for a dashboard at blogmarks
  • Bookmark Grafana: select host for a dashboard with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Monitor additional details in Telegraf with the "Exec" input filter

After installing Telegraf and hooking up everything into InfluxDB, I was missing the status of my backups. Every system here creates encrypted backups every night, and stores them on a central NAS, and off-site. But I want to know statistics about the backups, and see if something is not working.

I'm using Restic for the backups (will blog about this another time). However Telegraf does not support Restic directly, I need a few workarounds. This blog post however is not directly about monitoring the backups, but about how to write your own plugin for Telegraf.

 

Continue reading "Monitor additional details in Telegraf with the "Exec" input filter"
  • Twitter
  • Bookmark Monitor additional details in Telegraf with the "Exec" input filter at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Monitor additional details in Telegraf with the "Exec" input filter
  • Bloglines Monitor additional details in Telegraf with the "Exec" input filter
  • Technorati Monitor additional details in Telegraf with the "Exec" input filter
  • Fark this: Monitor additional details in Telegraf with the "Exec" input filter
  • Bookmark Monitor additional details in Telegraf with the "Exec" input filter at YahooMyWeb
  • Bookmark Monitor additional details in Telegraf with the "Exec" input filter at Furl.net
  • Bookmark Monitor additional details in Telegraf with the "Exec" input filter at reddit.com
  • Bookmark Monitor additional details in Telegraf with the "Exec" input filter at blinklist.com
  • Bookmark Monitor additional details in Telegraf with the "Exec" input filter at Spurl.net
  • Bookmark Monitor additional details in Telegraf with the "Exec" input filter at Simpy.com
  • Bookmark Monitor additional details in Telegraf with the "Exec" input filter at blogmarks
  • Bookmark Monitor additional details in Telegraf with the "Exec" input filter with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Install Telegraf using Ansible

I have an InfluxDB up and running in my network, and decided to monitor all (well, all possible - the QNAP seems to be a problem) devices. That's quite easy to do by installing Telegraf as a server agent, and add some configuration. Everything is deployed using Ansible, so I can re-use the same Playbook for many devices.

 

Continue reading "Install Telegraf using Ansible"
  • Twitter
  • Bookmark Install Telegraf using Ansible at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Install Telegraf using Ansible
  • Bloglines Install Telegraf using Ansible
  • Technorati Install Telegraf using Ansible
  • Fark this: Install Telegraf using Ansible
  • Bookmark Install Telegraf using Ansible at YahooMyWeb
  • Bookmark Install Telegraf using Ansible at Furl.net
  • Bookmark Install Telegraf using Ansible at reddit.com
  • Bookmark Install Telegraf using Ansible at blinklist.com
  • Bookmark Install Telegraf using Ansible at Spurl.net
  • Bookmark Install Telegraf using Ansible at Simpy.com
  • Bookmark Install Telegraf using Ansible at blogmarks
  • Bookmark Install Telegraf using Ansible with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string)

I'm in the process of updating my entire home setup, and integrate everything properly. Part of this process is to automate everything, and use Ansible Playbooks to deploy devices and configurations.

Today: install Telegraf and send data to InfluxDB

Along the way something broke, and Ansible doesn't really like me anymore. But let's start at the beginning.

In the Telegraf configuration in "/etc/telegraf/telegraf.conf" one can specify output plugins. One of them (probably the most used one) is "InfluxDB". The InfluxDB instance(s) are specified as a [...] list. In Ansible I somehow need to have this list as a string, and write it into the configuration file. This happens:

TASK [Update telegraf.conf InfluxDB settings] ***************************************************
changed: [localhost] => (item={'section': '[outputs.influxdb]', 'option': 'urls', 'value': ['http://192.168.xxx.xxx:8086'], 'state': 'present'})
[WARNING]: The value ['http://192.168.xxx.xxx:8086'] (type list) in a string field was converted to "['http://192.168.xxx.xxx:8086']" (type string). If this does not look like what you expect, quote the entire value to ensure it does not change.

Looks nasty ...

 

Continue reading "Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string)"
  • Twitter
  • Bookmark Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string) at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string)
  • Bloglines Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string)
  • Technorati Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string)
  • Fark this: Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string)
  • Bookmark Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string) at YahooMyWeb
  • Bookmark Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string) at Furl.net
  • Bookmark Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string) at reddit.com
  • Bookmark Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string) at blinklist.com
  • Bookmark Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string) at Spurl.net
  • Bookmark Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string) at Simpy.com
  • Bookmark Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string) at blogmarks
  • Bookmark Add InfluxDB settings in Telegraf using Ansible: [WARNING]: The value [...] (type list) in a string field was converted to "[...]" (type string) with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca