Claire Giordano: 10 Most Popular Citus Data Blog Posts in 2018, ft. Postgres

planet postgresql - 2019-01-14(月) 00:42:00

Seasons each have a different feel, a different rhythm. Temperature, weather, sunlight, and traditions—they all vary by season. For me, summer usually includes a beach vacation. And winter brings the smell of hot apple cider on the stove, days in the mountains hoping for the next good snowstorm—and New Year’s resolutions. Somehow January is the time to pause and reflect on the accomplishments of the past year, to take stock in what worked, and what didn’t. And of course there are the TOP TEN LISTS.

Spoiler alert, yes, this is a Top 10 list. If you’re a regular on the Citus Data blog, you know our Citus database engineers love PostgreSQL. And one of the open source responsibilities we take seriously is the importance of sharing learnings, how-to’s, and expertise. One way we share learnings is by giving lots of conference talks (seems like I have to update our Events page every week with new events.) And another way we share our learnings is with our blog.

So just in case you missed any of our best posts from last year, here is the TOP TEN list of the most popular Citus Data blogs published in 2018. Enjoy.

The Postgres 10 feature you didn’t know about: CREATE STATISTICS

BY SAMAY SHARMA | Postgres stores a lot of statistics about your data in order to effectively retrieve results when you query your database. In this post, Samay dives deep into some of the statistics PostgreSQL stores and how you can leverage CREATE STATISTICS to improve query performance when different columns are related.

In Postgres, the EXPLAIN planner collects statistics to help it estimate how many rows will be returned after executing a certain part of the plan, which then influences which type of scan or join algorithm will be used. Before Postgres 10, there wasn’t an easy way to tell the planner to collect statistics which capture the relationship between columns. But since the release of Postgres 10, there’s a feature which is built to solve exactly this problem. And this feature has gotten even better in PostgreSQL 11, too. All about C

カテゴリー: postgresql

Abdul Yadi: pgqr: a QR Code Generator

planet postgresql - 2019-01-12(土) 21:23:03

Related with my post: https://abdulyadi.wordpress.com/2015/11/14/extension-for-qr-code-bitmap/. I have repackage the module and available on github: https://github.com/AbdulYadi/pgqr.

This project adds 2 functionality to QR code generator from repository https://github.com/swex/QR-Image-embedded:

  1. In-memory monochrome bitmap construction (1 bit per pixel).
  2. Wrap the whole package as PostgreSQL extension.

This project has been compiled successfully in Linux against PostgreSQL version 11.
$ make clean
$ make
$ make install

On successful compilation, install this extension in PostgreSQL environment
$ create extension pgqr

Function pgqr has 4 parameters:

  1. t text: text to be encoded.
  2. correction_level integer: 0 to 3.
  3. model_number integer: 0 to 2.
  4. scale integer: pixels for each dot.

Let us create a QR Code
$ select pgqr(‘QR Code with PostgreSQL’, 0, 0, 4);
The output is a monochrome bitmap ready for display.



カテゴリー: postgresql

Quinn Weaver:

planet postgresql - 2019-01-12(土) 07:28:00
In the Bay Area? This Tuesday, 2019-01-15, SFPUG features David Fetter's talk on ASSERTIONs. RSVPs close Monday at noon, so don't hesitate! Thanks to Heap for hosting at their FiDi office.
カテゴリー: postgresql

Shaun M. Thomas: PG Phriday: PgBouncer or Bust

planet postgresql - 2019-01-12(土) 02:00:27

What is the role of PgBouncer in a Postgres High Availability stack? What even is PgBouncer at the end of the day? Is it a glorified traffic cop, or an integral component critical to the long-term survival of a Postgres deployment?

When we talk about Postgres High Availability, a lot of terms might spring to mind. Replicas, streaming, disaster recovery, fail-over, automation; it’s a ceaseless litany of architectural concepts and methodologies. The real question is: how do we get from Here to There?

The Importance of Proxies

It’s no secret that the application stack must communicate with the database. Regardless of how many layers of decoupling, queues, and atomicity of our implementation, data must eventually be stored for reference. But where is that endpoint? Presuming that write target is Postgres, what ensures the data reaches that desired terminus?

Consider this diagram:

Managed Proxy Layer

In this case, it doesn’t matter what type of Standby we’re using. It could be a physical streaming replica, some kind of logical copy, or a fully configured BDR node. Likewise, the Failover Mechanism is equally irrelevant. Whether we rely on repmgr, Patroni, Stolon, Pacemaker, or a haphazard collection of ad-hoc scripts, the important part is that we separate the application from the database through some kind of proxy.

Patroni relies on HAProxy and Stolon has its own proxy implementation, but what about the others? Traditionally PgBouncer fills this role. Without this important component, applications must connect directly to either the Primary or post-promotion Standby. If we’re being brutally honest, the application layer can’t be trusted with that kind of responsibility.

But why is that? Simply stated, we don’t know what the application layer is. In reality, an application is anything capable of connecting to the database. That could be the official software, or it could be a report, or a maintenance script, or a query tool, or any number of other access vectors. Which database node are they connecting to, and doe

カテゴリー: postgresql

Bulgaria PHP Conference 2016

php.net - 2019-01-11(金) 15:03:23
カテゴリー: php

PHP 5.6.40 Released

php.net - 2019-01-11(金) 08:01:12
カテゴリー: php

PHP 7.1.26 Release Announcement

php.net - 2019-01-11(金) 03:52:00
カテゴリー: php

PHP 7.3.1 Released

php.net - 2019-01-10(木) 19:51:06
カテゴリー: php

Laurenz Albe: pgbouncer authentication made easy

planet postgresql - 2019-01-10(木) 18:00:45

pgbouncer is the most widely used connection pooler for PostgreSQL.
This blog will provide a simple cookbook how to configure user authentication with pgbouncer.

I wrote this cookbook using Fedora Linux and installed pgbouncer using the PGDG Linux RPM packages available from the download site.
But it should work pretty similarly anywhere.


What is a connection pooler?

Setting max_connections to a high value can impact performace and can even bring your database to its knees if all these connections become active at the same time.

Also, if database connections are short-lived, a substantial amount of your database resources can be wasted just opening database connections.

To mitigate these two problems, we need a connection pooler. A connection pooler is a proxy between the client and the database: clients connect to the connection pooler, which handles the SQL requests via a relatively stable set of persistent database connections (the “connection pool”).

Since clients connect to pgbouncer, it will have to be able to authenticate them, so we have to configure it accordingly.


The very simple method (authentication file)

This method is useful if the number of database users is small and passwords don’t change frequently.

For that, we create a configuration file userlist.txt in the pgbouncer configuration directory (on my system /etc/pgbouncer).
The file contains the database users and their passwords, so that pgbouncer can authenticate the client without resorting to the database server.
It looks like this:

"laurenz" "md565b6fad0e85688f3f101065bc39552df" "postgres" "md553f48b7c4b76a86ce72276c5755f217d"

You can write the file by hand using the information from the pg_shadow catalog table, or you can create it automatically.
For that to work, you need

    • the PostgreSQL command line client psql
      If it is not on your PATH, you’ll have to use the absolute path (something like /usr/pgsql-11/bin/psql or "C:\Program Files\PostgreSQL\11\bin\psql").
    • write access to the pgbouncer configuration file, which may require th
カテゴリー: postgresql

Magnus Hagander: Nordic PGDay 2019 - Schedule announced and registration open

planet postgresql - 2019-01-10(木) 17:41:25

Nordic PGDay 2019 is happy to announce our schedule has now been published.

Our registration has also been opened. Seats are limited so we recommend that you register early if you are interested! Early Bird discount is available until February 17th or for the first 40 tickets.

We look forward to seeing you in Copenhagen!

カテゴリー: postgresql

PHP 7.2.14 Released

php.net - 2019-01-10(木) 17:30:27
カテゴリー: php

Joshua Drake: PostgresConf 2019: CFP and Early Bird closing soon

planet postgresql - 2019-01-10(木) 03:04:00
PostgresConf hopes everyone had a great holiday season and we hope everyone has a fantastic 2019.

With January upon us, we start conference season. This year, PostgresConf 2019 will be in NYC at the Sheraton New York Times Square March 18-22, 2019.

If you have a story about lessons learned with Postgres, a cool feature that you've found a creative way of using, or just some practical knowledge you'd like to share with your fellow Postgres users, submit a talk. Remember, there are many people who are new to Postgres, so introduction levels talks are welcome. The CFP ends this Friday so get your talks submitted soon at:

Also, the Early Bird window for PostgresConf 2019 will be ending next Friday, January 18. This tickets are pretty substantially discounted if you purchase them early. On top of the Early Bird discount, for being a member of the NYC Postgres User Group, there is a promo code for an additional 15% off. Just use the code 2019_PUG_NYC when buying your tickets.
https://postgresconf.org/conferences/2019/buytickets Lastly, if you are already in the NYC area we look forward to seeing the community at the Winter Party on the 10th! https://www.meetup.com/postgres-nyc/events/257567648/ We hope to see you at PostgresConf 2019 and remember:

カテゴリー: postgresql

Tomas Vondra: Sequential UUID Generators on SSD

planet postgresql - 2019-01-09(水) 22:00:01

After I shared the sequential UUID benchmarks a couple of weeks ago, one of the points raised in feedback was the choice of the storage space. I’ve intentionally used a fairly weak storage system (RAID10 on three 7.2k SATA drives) because I wanted to demonstrate the benefits. But a couple of readers suggested using SSDs might significantly reduce the difference between regular and sequential UUIDs due to SSDs handling random I/O much better than rotational storage. My hypothesis was that while using SSDs may reduce the gap, it certainly won’t eliminate it entirely because the amplification (both in terms of number of I/O requests and WAL volume) is independent of the storage system. But the only way to verify this it is to repeat the tests, this time on SSDs. So here we go …

I’m not going to repeat the explanation of how sequential UUIDs work – you can find that in the previous post. I’ll just show results on SSDs, with a short discussion.

The tests were done on two different systems I always use for benchmarking. Both systems use SSDs, but quite different types / generations. This adds a bit more variability, which seems like a good thing in this sort of tests anyway.

i5-2500K with 6 x S3700 SSD

This first system is a fairly small (and somewhat old-ish), with a relatively old Intel CPU i5-2500K with just 4 cores, 8GB of RAM and 6 Intel S3700 SSD disks in a RAID0.

The following chart illustrates the throughput for various test scales:

The degradation is clearly not as bad as with rotational storage – on the largest scale it drops “only” to ~50% (instead of ~20%). And for the medium scale the drop is even smaller.

But how is that possible? The initial hypothesis was that the I/O amplification (increase in number of random I/O requests) is the same, independently of the storage system – if each INSERT means you need to do a 3 random I/O writes, that does not depend on storage system type. Of SSDs do not behave simply as “faster” rotational device – that’s an oversimplification. For example most SSDs are interna

カテゴリー: postgresql

Michael Paquier: Postgres 12 highlight - pg_promote

planet postgresql - 2019-01-09(水) 14:31:42

The following commit has been merged into Postgres 12 a couple of months ago, easing failover control flow:

commit: 10074651e3355e2405015f6253602be8344bc829 author: Michael Paquier <michael@paquier.xyz> date: Thu, 25 Oct 2018 09:46:00 +0900 Add pg_promote function This function is able to promote a standby with this new SQL-callable function. Execution access can be granted to non-superusers so that failover tools can observe the principle of least privilege. Catalog version is bumped. Author: Laurenz Albe Reviewed-by: Michael Paquier, Masahiko Sawada Discussion: https://postgr.es/m/6e7c79b3ec916cf49742fb8849ed17cd87aed620.camel@cybertec.at

Promotion is a process which can be used on a standby server to end recovery and allow it to begin read-write operations, bumping this standby server to a new timeline on the way. This operation can be done using a couple of options:

  • pg_ctl promote, which waits for the standby to finish the promotion before exiting by default.
  • Define promote_trigger_file in postgresql.conf and create the file which would be detected by the startup process and translated so as recovery finishes (or trigger_file in recovery.conf up to v11, recovery parameters being merged to postgresql.conf in v12 and newer versions).

The commit mentioned above offers a third way to trigger a promotion with a SQL-callable function, which presents a huge advantage compared to the two other methods: there is no need to connect to the standby physical host to trigger the promotion as everything can be done with a backend session. Note however that this needs a standby server able to accept read-only operations and connections.

By default pg_promote() waits for the promotion to complete before returning back its result to its caller, waiting for a maximum of 60 seconds, which is the same default as the promote mode of pg_ctl. However it is possible to enforce both the wait mode and the timeout value by specifying the wait mode as a boolean for the first argument, and the timeout as an integer in seconds

カテゴリー: postgresql

pgCMH - Columbus, OH: Monitoring your DB with VividCortex

planet postgresql - 2019-01-09(水) 14:00:00

The Jan meeting will be held at 18:00 EST on Tues, the 22nd. Once again, we will be holding the meeting in the community space at CoverMyMeds. Please RSVP on MeetUp so we have an idea on the amount of food needed.


CoverMyMeds’ very own CJ will be presenting this month. He’s going to tell us all about how CoverMyMeds uses VividCortex to assist with monitoring PostgreSQL and maintaining the high performance of the database.


CoverMyMeds has graciously agreed to validate your parking if you use their garage so please park there:

You can safely ignore any sign saying to not park in the garage as long as it’s after 17:30 when you arrive.

Park in any space that is not marked ‘24 hour reserved’.

Once parked, take the elevator/stairs to the 3rd floor to reach the Miranova lobby. Once in the lobby, the elevator bank is in the back (West side) of the building. Take a left and walk down the hall until you see the elevator bank on your right. Grab an elevator up to the 11th floor. (If the elevator won’t let you pick the 11th floor, contact Doug or CJ (info below)). Once you exit the elevator, look to your left and right; one side will have visible cubicles, the other won’t. Head to the side without cubicles. You’re now in the community space:

The kitchen is to your right (grab yourself a drink) and the meeting will be held to your left. Walk down the room towards the stage.

If you have any issues or questions with parking or the elevators, feel free to text/call Doug at +1.614.316.5079 or CJ at +1.740.407.7043

カテゴリー: postgresql

Richard Yen: Zombies!! Dealing with a Case of Stuck TransactionIDs

planet postgresql - 2019-01-09(水) 06:45:00
The Story

Postgres is up and running, and things are humming along. Replication is working, vacuums are running, and there are no idle transactions in sight. You poke around your logs and make sure things are clean, but you notice a little warning:

2019-01-03 07:51:38 GMT WARNING: oldest xmin is far in the past 2019-01-03 07:51:38 GMT HINT: Close open transactions soon to avoid wraparound problems.

Strange. There are no open transactions. You look in pg_stat_activity once, twice, three times–nothing. What’s up with that? How long has this been happening? You go back to you logs and you throw the whole thing into grep and find:

$ cat db1.log | grep "WARNING" 2019-01-03 07:51:38 GMT WARNING: oldest xmin is far in the past 2019-01-03 07:51:38 GMT WARNING: oldest xmin is far in the past 2019-01-03 07:51:38 GMT WARNING: oldest xmin is far in the past 2019-01-03 07:51:38 GMT WARNING: oldest xmin is far in the past 2019-01-03 07:51:38 GMT WARNING: oldest xmin is far in the past 2019-01-03 07:51:38 GMT WARNING: oldest xmin is far in the past 2019-01-03 07:51:38 GMT WARNING: oldest xmin is far in the past 2019-01-03 07:51:38 GMT WARNING: oldest xmin is far in the past 2019-01-03 07:51:55 GMT WARNING: oldest xmin is far in the past 2019-01-03 07:51:55 GMT WARNING: oldest xmin is far in the past ... $ cat db1.log | grep "HINT" 2019-01-03 07:51:38 GMT HINT: Close open transactions soon to avoid wraparound problems. 2019-01-03 07:51:38 GMT HINT: Close open transactions soon to avoid wraparound problems. 2019-01-03 07:51:38 GMT HINT: Close open transactions soon to avoid wraparound problems. 2019-01-03 07:51:38 GMT HINT: Close open transactions soon to avoid wraparound problems. ...

Uh oh. What’s going on? And the pressure is on–nobody wants to be stuck dealing with wraparound problems. The adrenaline rush hits, and you’re frantically looking for the silver bullet to kill off this problem. Kill. Maybe restart the database? No, you can’t do that–it’s a production server. You’ll get chewed out! You look everywh

カテゴリー: postgresql

Liaqat Andrabi: Webinar: pglogical and Postgres-BDR Update [Follow Up]

planet postgresql - 2019-01-09(水) 03:30:52

Since the release of v3, Postgres-BDR has evolved into the go-to clustering technology built specially for businesses that require geographically distributed databases with multiple masters.

To get an update on Postgres-BDR’s development, new features, and future roadmap, 2ndQuadrant held the pglogical and Postgres-BDR Update webinar as part of its PostgreSQL webinar series.

The webinar was presented by Simon Riggs, Founder & CEO of 2ndQuadrant, who is also a major contributor of the open source PostgreSQL project. Those who weren’t able to attend the live event can now view the recording here.

For any questions or comments regarding Postgres-BDR, please send an email to info@2ndQuadrant.com.

カテゴリー: postgresql

Sebastian Insausti: Deploying and Managing PostgreSQL 11: New in ClusterControl 1.7.1

planet postgresql - 2019-01-08(火) 21:16:44

A few days ago was the release of a new version of ClusterControl, the 1.7.1, where we can see several new features, one of the main ones being the support for PostgreSQL 11.

To install PostgreSQL 11 manually, we must first add the repositories or download the necessary packages for the installation, install them and configure them correctly, depending on our infrastructure. All these steps take time, so let’s see how we could avoid this.

In this blog, we will see how to deploy this new PostgreSQL version with a few clicks using ClusterControl and how to manage it. As pre-requisite, please install the 1.7.1 version of ClusterControl on a dedicated host or VM.

Deploy PostgreSQL 11

To perform a new installation from ClusterControl, simply select the option “Deploy” and follow the instructions that appear. Note that if you already have a PostgreSQL 11 instance running, then you need to select the ‘Import Existing Server/Database’ instead.

ClusterControl Deploy Option

When selecting PostgreSQL, we must specify User, Key or Password and port to connect by SSH to our PostgreSQL hosts. We also need the name for our new cluster and if we want ClusterControl to install the corresponding software and configurations for us.

ClusterControl Deploy Information 1

Please check the ClusterControl user requirement for this task here.

ClusterControl Deploy Information 2

After setting up the SSH access information, we must define the database user, version and datadir (optional). We can also specify which repository to use. In this case, we want to deploy PostgreSQL 11, so just select it and continue.

In the next step, we need to add our servers to the cluster we are going to create.

ClusterControl Deploy Information 3

When adding our servers, we can enter IP or hostname.

In the last step, we can choose if our replication will be Synchronous or Asynchronous.

ClusterControl Deploy Information 4

We can monitor the status of the creation of our new cluster from the ClusterControl activity monitor.

ClusterControl Activity[...]
カテゴリー: postgresql

Gregory Stark: Monitoring Postgres with Prometheus

planet postgresql - 2019-01-07(月) 23:05:00
I'm glad people found my presentation at Lisbon on monitoring Postgres using Prometheus last October interesting. The slides are now uploaded to the conference web site at https://www.postgresql.eu/events/pgconfeu2018/schedule/session/2166/. Sorry for the delay. Now it's a near year it's time to work on improving monitoring in Postgres. As an aside I experimented a bit with the build process for this slide deck. It's a Gitlab project and I set up a Gitlab CI pipeline to run Latex to build the beamer presentation and serve it from Gitlab pages. So you can see the most recent version of the slide deck from https://_stark.gitlab.io/monitoring-postgres-pgconf.eu-2018/monitoring.pdf any time and it's automatically rebuilt each time I do a git push. You can also see the source code at https://gitlab.com/_stark/monitoring-postgres-pgconf.eu-2018 and feel free to submit issues if you spot any errors in the slides or even just suggestions on things that were unclear. ** But now the real question. I want to improve the monitoring situation in Postgres. I have all kinds of grand plans but would be interested to hear what people's feelings about what's the top priority and most practical changes they want to see in Postgrs. Personally I think the most important first step is to implement native Prometheus support in Postgres -- probably a background worker that would start up and expose all of pg_stats directly from shared memory to Prometheus without having to start an SQL session with all its transaction overhead. That would make things more efficient but also more reliable during outages. It would also make it possible to export data for all databases instead of having the agent have to reconnect for each database! I have future thoughts about distributed tracing, structured logging, and pg_stats changes to support application profiling but they are subjects for further blog posts. I have started organizing my ideas as issues in https://gitlab.com/_stark/postgresql/issues feel free to comment on them or create new issues [...]
カテゴリー: postgresql

Douglas Hunley: pgBackRest 2.08 and macOS Mojava

planet postgresql - 2019-01-04(金) 22:30:44

The team has released pgBackRest 2.08 today. As part of a continuing effort, more bits have been moved from Perl to C. Sadly, this adds a new wrinkle for those of us on OSX, as when compiling, you now get:

gcc -I. -I../libc -std=c99 -D_POSIX_C_SOURCE=200112L -O2 -Wfatal-errors -Wall -Wextra -Wwrite-strings -Wswitch-enum -Wconversion -Wformat=2 -Wformat-nonliteral -Wno-clobbered -Wno-missing-field-initializers -Wstrict-prototypes -Wpointer-arith -Wvla `xml2-config --cflags` `perl -MExtUtils::Embed -e ccopts` -DWITH_PERL -DNDEBUG -c common/lock.c -o common/lock.o warning: unknown warning option '-Wno-clobbered'; did you mean '-Wno-consumed'? [-Wunknown-warning-option] common/lock.c:76:21: warning: implicit declaration of function 'flock' is invalid in C99 [-Wimplicit-function-declaration] if (flock(result, LOCK_EX | LOCK_NB) == -1) ^ common/lock.c:76:21: warning: this function declaration is not a prototype [-Wstrict-prototypes] common/lock.c:76:35: fatal error: use of undeclared identifier 'LOCK_EX' if (flock(result, LOCK_EX | LOCK_NB) == -1) ^ 3 warnings and 1 error generated. make: *** [common/lock.o] Error 1

To fix this, you will need to edit src/Makefile and change line 12 from:

CSTD = -std=c99 -D_POSIX_C_SOURCE=200112L



Then, you can follow the other steps on my previous post and everything should compile and function properly.


カテゴリー: postgresql