フィードアグリゲーター

Regina Obe: Compiling http extension on ubuntu 18.04

planet postgresql - 2019-02-11(月) 17:31:00

We recently installed PostgreSQL 11 on an Ubuntu 18.04 using apt.postgresql.org. Many of our favorite extensions were already available via apt (postgis, ogr_fdw to name a few), but it didn't have the http extension we use a lot. The http extension is pretty handy for querying things like Salesforce and other web api based systems. We'll outline the basic compile and install steps. While it's specific to the http extension, the process is similar for any other extension you may need to compile.

Continue reading "Compiling http extension on ubuntu 18.04"
カテゴリー: postgresql

Alexey Lesovsky: pgCenter 0.6.0 released.

planet postgresql - 2019-02-10(日) 04:34:00
Great news for all pgCenter users - a new version 0.6.0 has been released with new features and few minor improvements.

Here are some major changes:
  • new wait events profiler - a new sub-command which allows to inspect long-running queries and understand what query spends its time on.
  • goreleaser support - goreleaser helps to build binary packages for you, so you can find .rpm and .deb packages on the releases page.
  • Goreport card A+ status - A+ status is the little step to make code better and align it to Golang code style
This release also includes following minor improvements and fixes:
  • report tool now has full help list of supported stats, you can, at any time, get a descriptive explanation of stats provided by pgCenter. Check out the “--describe” flag of “pgcenter report”;
  • “pgcenter top” now has been fixed and includes configurable aligning of columns, which make stats viewing more enjoyable (check out builtin help for new hotkeys);
  • wrong handling of group mask has been fixed. It is used for canceling group of queries, or for termination of backends’ groups;
  • also fixed the issue when pgCenter is failed to connect to Postgres with disabled SSL;
  • and done some other minor internal refactoring.
New release is available here. Check it out and have a nice day.
カテゴリー: postgresql

Christophe Pettus: Do not change autovacuum age settings

planet postgresql - 2019-02-09(土) 10:52:05

PostgreSQL has two autovacuum-age related settings, autovacuum_freeze_max_age, and vacuum_freeze_table_age.

Both of them are in terms of the transaction “age” of a table: That is, how long it has been since the table has been scanned completely for “old” tuples that can be marked as “frozen” (a “frozen” tuple is one that no open transaction can cause to disappear by a rollback). In short, the “oldest” a table can become in PostgreSQL is 2^31-1 transactions; if a table were ever to reach that, data loss would occur. PostgreSQL takes great pains to prevent you from eaching that point.

The “vacuum freeze” process is the process that scans the table and marks these tuples as frozen.

vacuum_freeze_table_age causes a regular autovacuum run to be an “autovacuum (to prevent xid wraparound)” run, that is, an (auto)vacuum freeze, if the age of the table is higher than vacuum_freeze_table_age.

autovacuum_freeze_max_age will cause PostgreSQL to start an “autovacuum (to prevent xid wraparound)” run even if it has no other reason to vacuum the table, should a table age exceed that setting.

By default, vacuum_freeze_table_age = 100000000 (one hundred million), and autovacuum_freeze_max_age = 200000000 (two hundred million).

Do not change them.

In the past, I made a recommendation I now deeply regret. Because, before 9.6, each autovacuum freeze run scanned the entire table, and (on its first pass) potentially rewrote the entire table, it could be very high I/O, and when it woke up suddenly, it could cause performance issues. I thus recommended two things:

  1. Increase autovacuum_freeze_max_age and vacuum_freeze_table_age, and,
  2. Do manual VACUUM FREEZE operations on the “oldest” tables during low-traffic periods.

Unfortunately, far too many installations adopted recommendation #1, but didn’t do #2. The result was that they cranked up autovacuum_freeze_max_age so high that by the time the mandatory autovacuum freeze operation began, they were so close to transaction XID wraparound point, they had no choice but to take the system offl

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

Craig Kerstiens: The most useful Postgres extension: pg_stat_statements

planet postgresql - 2019-02-09(土) 03:59:00

Extensions are capable of extending, changing, and advancing the behavior of Postgres. How? By hooking into low level Postgres API hooks. The open source Citus database that scales out Postgres horizontally is itself implemented as a PostgreSQL extension, which allows Citus to stay current with Postgres releases without lagging behind like other Postgres forks. I’ve previously written about the various types of extensions, today though I want to take a deeper look at the most useful Postgres extension: pg_stat_statements.

You see, I just got back from FOSDEM. FOSDEM is the annual free and open source software conference in Brussels, and at the event I gave a talk in the PostgreSQL devroom about Postgres extensions. By the end of the day, over half the talks that had been given in the Postgres devroom mentioned pg_stat_statements:

Most frequently dispensed #PostgreSQL tip-of-the-day here in the Postgres devroom at #FOSDEM? Use pg_stat_statements! @Xof’s talk on Breaking PostgreSQL at Scale is the 4th talk today to drive this point home HT @craig @net_snow @magnushagander pic.twitter.com/Tcwkhy8W8h

— Claire Giordano (@clairegiordano) February 3, 2019

If you use Postgres and you haven’t yet used pg_stat_statements, it is a must to add it to your toolbox. And even if you are familiar, it may be worth a revisit.

Getting started with pg_stat_statements

Pg_stat_statements is what is known as a contrib extension, found in the contrib directory of a PostgreSQL distribution. This means it already ships with Postgres and you don’t have to go and build it from source or install packages. You may have to enable it for your database if it is not already enabled. This is as simple as:

CREATE EXTENSION pg_stat_statements;

If you run on a major cloud provider there is a strong likelihood they have already installed and enabled it for you.

Once pg_stat_statements is installed, it begins silently going to work under the covers. Pg_stat_statements records queries that are run against your database, strips out a number of va

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

Bruce Momjian: PgLife For Familiarization

planet postgresql - 2019-02-09(土) 00:00:01

I worked with two companies this week to help them build open-source Postgres teams. Hopefully we will start seeing their activity in the community soon.

One tool I used to familiarize them with the Postgres community was PgLife. Written by me in 2013, PgLife presents a live dashboard of all current Postgres activity, including user, developer, and external topics. Not only a dashboard, you can drill down into details too. All the titles on the left are click-able, as are the detail items. The plus sign after each Postgres version shows the source code changes since its release. Twitter and Slack references have recently been added.

I last mentioned PgLife here six years ago, so I thought I would mention it again. FYI, this is my 542nd blog entry. If you missed any of them, see my category index at the top of this page.

カテゴリー: postgresql

Daniel Vérité: Postgres instances open to connections from the Internet

planet postgresql - 2019-02-08(金) 21:40:00

A PostgreSQL server may be accessible from the Internet, in the sense that it may listen on a public IP address and a TCP port accepting connections from any origin.

With the rising popularity of the DBaaS (“Database As A Service”) model, database servers can be legitimately accessible from the Internet, but it can also be the result of an unintentional misconfiguration.

As a data point, shodan.io, a scanner service that monitors such things, finds currently more than 650,000 listening Postgres instances on the Internet, without prejudging how they’re protected by host-based access rules, strong passwords, and database-level grants.

Such an open configuration at the network level is opposed to the more traditional, secure one where database servers are at least protected by a firewall, or don’t even have a network interface connected to the Internet, or don’t listen on it if they have one.

One consequence of having an instance listening to connections from the Internet is that intrusion attempts on the default port 5432 may happen anytime, just like it happens for other services such as ssh, the mail system or popular web applications like Drupal, Wordpress or phpMyAdmin.

If you have a server on the Internet, you may put its IP address in the search field of shodan.io to see what it knows about it.

The purpose of this post is to put together a few thoughts on this topic, for people who already manage PostgreSQL instances accepting public connections, or plan to do that in the future, or on the contrary, want to make sure that their instances don’t do that.

Do not mistakenly open your instance to the Internet!

When asking “how to enable remote access to PostgreSQL?”, the typical answer is almost invariably to add some rules in pg_hba.conf and set in postgresql.conf:

listen_addresses = *

(replacing the default listen_addresses = localhost).

It does work indeed, by making all the network interfaces to listen, but not necessarily only those where these connections are expected. In the case that they should come onl

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

Michael Paquier: Postgres 12 highlight - Functions for partitions

planet postgresql - 2019-02-08(金) 12:50:47

Partitions in Postgres are a recent concept, being introduced as of version 10 and improved a lot over the last years. It is complicated, and doable, to gather information about them with specific queries working on the system catalogs, still these may not be straight-forward. For example, getting a full partition tree leads to the use of WITH RECURSIVE when working on partitions with multiple layers.

Postgres 12 is coming with improvements in this regard with two commits. The first one introduces a new system function to get easily information about a full partition tree:

commit: d5eec4eefde70414c9929b32c411cb4f0900a2a9 author: Michael Paquier <michael@paquier.xyz> date: Tue, 30 Oct 2018 10:25:06 +0900 Add pg_partition_tree to display information about partitions This new function is useful to display a full tree of partitions with a partitioned table given in output, and avoids the need of any complex WITH RECURSIVE query when looking at partition trees which are deep multiple levels. It returns a set of records, one for each partition, containing the partition's name, its immediate parent's name, a boolean value telling if the relation is a leaf in the tree and an integer telling its level in the partition tree with given table considered as root, beginning at zero for the root, and incrementing by one each time the scan goes one level down. Author: Amit Langote Reviewed-by: Jesper Pedersen, Michael Paquier, Robert Haas Discussion: https://postgr.es/m/8d00e51a-9a51-ad02-d53e-ba6bf50b2e52@lab.ntt.co.jp

The second function is able to find the top-most parent of a partition tree:

commit: 3677a0b26bb2f3f72d16dc7fa6f34c305badacce author: Michael Paquier <michael@paquier.xyz> date: Fri, 8 Feb 2019 08:56:14 +0900 Add pg_partition_root to display top-most parent of a partition tree This is useful when looking at partition trees with multiple layers, and combined with pg_partition_tree, it provides the possibility to show up an entire tree by just knowing one member at any level. Author: Michael Paquier Re[...]
カテゴリー: postgresql

PHP.Barcelona 2019

php.net - 2019-02-08(金) 00:22:17
カテゴリー: php

PHP 7.2.15 Released

php.net - 2019-02-07(木) 20:30:07
カテゴリー: php

PHP 7.3.2 Release Announcement

php.net - 2019-02-07(木) 20:00:19
カテゴリー: php

PHP 7.3.2 Release Announcement

planet PHP - 2019-02-07(木) 09:00:00
The PHP development team announces the immediate availability of PHP 7.3.2. This is a bugfix release, with several bug fixes included.All PHP 7.3 users are encouraged to upgrade to this version.For source downloads of PHP 7.3.2 please visit our downloads page, Windows source and binaries can be found on windows.php.net/download/. The list of changes is recorded in the ChangeLog.
カテゴリー: php

PHP 7.2.15 Released

planet PHP - 2019-02-07(木) 09:00:00
The PHP development team announces the immediate availability of PHP 7.2.15. This is a bugfix release.All PHP 7.2 users are encouraged to upgrade to this version.For source downloads of PHP 7.2.15 please visit our downloads page, Windows source and binaries can be found on windows.php.net/download/. The list of changes is recorded in the ChangeLog.
カテゴリー: php

Vasilis Ventirozos: Managing xid wraparound without looking like a (mail) chimp

planet postgresql - 2019-02-07(木) 05:07:00
My colleague Payal came across an outage that happened to mailchimp's mandrill app yesterday, link can be found HERE.
Since this was PostgreSQL related i wanted to post about the technical aspect of it.
According to the company :

“Mandrill uses a sharded Postgres setup as one of our main datastores,”
the email explains.
“On Sunday, February 3, at 10:30pm EST, 1 of our 5 physical Postgres instances saw a significant spike in writes.” 
The email continues:
The spike in writes triggered a Transaction ID Wraparound issue. When this occurs, database activity is completely halted. The database sets itself in read-only mode until offline maintenance (known as vacuuming) can occur.”

So, lets see what that "transaction id wraparound issue" is and how someone could prevent similar outages from ever happening.

PostgreSQL uses MVCC to control transaction visibility, basically by comparing transaction IDs (XIDs). A row with an insert XID greater than the current transaction  XID shouldn't be visible to the current transaction. But since transaction IDs are not unlimited a cluster will eventually run out after
(2^32 transactions 4+ billion) causing transaction ID wraparound: transaction counter wraps around to zero, and all past transaction would appear to be in the future

This is being taken care of by vacuum that will mark rows as frozen, indicating that they were inserted by a transaction that committed far in the past that can be visible to all current and future transactions. To control this behavior, postgres has a configurable called autovacuum_freeze_max_age, which defaults at 200.000.000 transactions, a very conservative default that must be tuned in larger production systems.

It sounds complicated but its relatively easy not to get to that point,for most people just having autovacuum on will prevent this situation from ever happening. You can simply schedule manual vacuums by getting a list of the tables "closer" to autovacuum_freeze_max_age with a simple query like this:


SELECT 'vacuum analyze ' || c.oid::regcl[...]
カテゴリー: postgresql

Mark Wong: PDXPUG: February Meetup: Temporal Databases: Theory and Postgres

planet postgresql - 2019-02-07(木) 04:51:29

2019 February 21 Meeting (Note: Back to third Thursday this month!)

Location:

PSU Business Accelerator
2828 SW Corbett Ave · Portland, OR
Parking is open after 5pm.

Speaker: Paul Jungwirth

Temporal databases let you record history: either a history of the database (what the table used to say), a history of the thing itself (what it used to be), or both at once. The theory of temporal databases goes back to the 90s, but standardization has only just begun with some modest recommendations in SQL:2011, and database products (including Postgres) are still missing major functionality.

This talk will cover how temporal tables are structured, how they are queried and updated, what SQL:2011 offers (and doesn’t), what functionality Postgres has already, and what remains to be built.

Paul started programming on a Tandy 1000 at age 8 and hasn’t been able to stop since. He helped build one of the Mac’s first web servers in 1994 and has founded software companies in politics and technical hiring. He works as an independent consultant specializing in Rails, Postgres, and Chef.

カテゴリー: postgresql

Bruce Momjian: Expanding Permission Letters

planet postgresql - 2019-02-07(木) 03:30:01

Thanks to a comment on my previous blog post by Kaarel, the ability to simply display the Postgres permission letters is not quite as dire as I showed. There is a function, aclexplode(), which expands the access control list (ACL) syntax used by Postgres into a table with full text descriptions. This function exists in all supported versions of Postgres. However, it was only recently documented in this commit based on this email thread, and will appear in the Postgres 12 documentation.

Since aclexplode() exists (undocumented) in all supported versions of Postgres, it can be used to provide more verbose output of the pg_class.relacl permission letters. Here it is used with the test table created in the previous blog entry:

SELECT relacl FROM pg_class WHERE relname = 'test'; relacl -------------------------------------------------------- {postgres=arwdDxt/postgres,bob=r/postgres,=r/postgres} SELECT a.* FROM pg_class, aclexplode(relacl) AS a WHERE relname = 'test' ORDER BY 1, 2; grantor | grantee | privilege_type | is_grantable ---------+---------+----------------+-------------- 10 | 0 | SELECT | f 10 | 10 | SELECT | f 10 | 10 | UPDATE | f 10 | 10 | DELETE | f 10 | 10 | INSERT | f 10 | 10 | REFERENCES | f 10 | 10 | TRIGGER | f 10 | 10 | TRUNCATE | f 10 | 16388 | SELECT | f

Continue Reading »

カテゴリー: postgresql

Peter Eisentraut: PostgreSQL with passphrase-protected SSL keys under systemd

planet postgresql - 2019-02-06(水) 21:34:56

PostgreSQL supports SSL, and SSL private keys can be protected by a passphrase. Many people choose not to use passphrases with their SSL keys, and that’s perhaps fine. This blog post is about what happens when you do have a passphrase.

If you have SSL enabled and a key with a passphrase and you start the server, the server will stop to ask for the passphrase. This happens automatically from within the OpenSSL library. Stopping to ask for a passphrase obviously prevents automatic starts, restarts, and reboots, but we’re assuming here that you have made that tradeoff consciously.

When you run PostgreSQL under systemd, which is very common nowadays, there is an additional problem. Under systemd, the server process does not have terminal access, and so it cannot ask for any passphrases. By default, the startup will fail in such setups.

As of PostgreSQL 11, it is possible to configure an external program to obtain the SSL passphrase, using the configuration setting ssl_passphrase_command. As a simple example, you can set

ssl_passphrase_command = 'echo "secret"'

and it will apply the passphrase that the program prints out. You can use this to fetch the passphrase from a file or other secret store, for example.

But what if you still want the security of having to manually enter the password? Systemd has a facility that lets services prompt for passwords. You can use that like this::

ssl_passphrase_command = '/bin/systemd-ask-password "%p"'

Except that that doesn’t actually work, because non-root processes are not permitted to use the systemd password system; see this bug. But there are workarounds.

One workaround is to use sudo. So you use

ssl_passphrase_command = 'sudo /bin/systemd-ask-password "%p"'

and then put something like this into /etc/sudoers:

postgres ALL=(root) NOPASSWD: /bin/systemd-ask-password

A more evil workaround (discussed in the above-mentioned bug report) is to override the permissions on the socket file underlying this mechanism. Add this to the postgresql service unit:

ExecStartPre=+/bin/setfa[...]
カテゴリー: postgresql

Midwest PHP 2019

php.net - 2019-02-06(水) 20:22:01
カテゴリー: php

Jignesh Shah: PGConf.RU 2019: Slides from my sessions

planet postgresql - 2019-02-06(水) 19:17:00
It was my first visit to Moscow for PGConf.RU 2019. Enjoyed meeting the strong community of PostgreSQL in Russia!


Slides from my sessions:

1. Deep Dive into the RDS PostgreSQL Universe


Deep Dive into the RDS PostgreSQL Universe from Jignesh Shah

2. Tips and Tricks for Amazon RDS for PostgreSQL

Tips and Tricks with Amazon RDS for PostgreSQL from Jignesh Shah

This blog represents my own view points and not of my employer, Amazon Web Services.


カテゴリー: postgresql

James Coleman: We scale both vertically and horizontally with dozens of PostgreSQL clusters across multiple…

planet postgresql - 2019-02-06(水) 00:03:47

We scale both vertically and horizontally with dozens of PostgreSQL clusters across multiple datacenters.

カテゴリー: postgresql

410 Gone

planet PHP - 2019-02-06(水) 00:00:00

410 Gone is a status code that can be used in cases where a resource is gone and never coming back. It’s a more specific version of 404 Not Found.

A good example for using 410 instead of 404 is when a resource was intentionally removed.

Using 410 can be helpful, because it signals to other people linking to you that the link is dead and should be removed. A 404 is the default for missing resources, and it can just mean that the owner of the site has moved the content and didn’t put the right redirects in place.

So to sum it up: 410 implies intent.

Example HTTP/1.1 410 Gone Content-Type: text/plain Server: curveball/0.6.0 I deleted it and it's never coming back! References
カテゴリー: php

ページ