フィードアグリゲーター

Dimitri Fontaine: PostgreSQL Concurrency: an Article Series

planet postgresql - 2018-08-14(火) 17:49:02

PostgreSQL is a relational database management system. It’s even the world’s most advanced open source one of them. As such, as its core, Postgres solves concurrent access to a set of data and maintains consistency while allowing concurrent operations.

In the PostgreSQL Concurrency series of articles here we did see several aspects of how to handle concurrent use cases of your application design with PostgreSQL. The main thing to remember is that a Database Management System first task is to handle concurrency access to the data for you.

カテゴリー: postgresql

pgCMH - Columbus, OH: Inside the Database Cluster

planet postgresql - 2018-08-14(火) 13:00:00

The Aug meeting will be held at 18:00 EST on Tues, the 28th. 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.

What

Our very own Arlette Garay (https://www.linkedin.com/in/arlette-garay-28075a2/) is going to share with us what she learned about the PostgreSQL database’s concept of a ‘cluster’. This will include discussing the object hierarchy and the on-disk locations of database objects.

We’re very excited for this talk, as Arlette has only recently made the jump from MySQL to PostgreSQL and this will be her very first public presentation! We hope everyone will come out and show their support for her.

Where

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

SunshinePHP 2019 CFP Started

php.net - 2018-08-14(火) 09:00:01
カテゴリー: php

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 12 – Allow multi-inserts during COPY into a partitioned table

planet postgresql - 2018-08-14(火) 03:36:32
On 1st of August 2018, Peter Eisentraut committed patch: Allow multi-inserts during COPY into a partitioned table CopyFrom allows multi-inserts to be used for non-partitioned tables, but this was disabled for partitioned tables. The reason for this appeared to be that the tuple may not belong to the same partition as the previous tuple did. […]
カテゴリー: postgresql

Luca Ferrari: An example of PostgreSQL rules: updating pg_settings

planet postgresql - 2018-08-13(月) 09:00:00

Rules are a powerful mechanism by which PostgreSQL allows a statement to be transformed into another. And PostgreSQL itself does use rules in order to make your life easier.

An example of PostgreSQL rules: updating pg_settings

When asked for a quick and sweet example about rules I often answer with the pg_settings example.

The special view pg_settings offers a tabular decodification of the current cluster settings, in other words allows you to see postgresql.conf (and friends) as a table to run queries against.

But there is more than that: you can also issue UPDATE commands against such table and get the configuration updated on the fly (this does not mean applied, it depends on the parameter context). Internally, PostgreSQL uses a very simple rule to cascade updates to pg_settings into the run-time configuration. The rule can be found in the system_views.sql files inside the backend source code and is implemented as:

CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE new.name = old.name DO SELECT set_config(old.name, new.setting, 'f');

It simply reads as: whenever there is an update keeping untouched the parameter name, invoke the special function set_config with the parameter name and its new value (the flag f means to keep changes not local to session). For more information about set_config see the function official documentation.

How cool!

カテゴリー: postgresql

Regina Obe: PostGIS 2.5.0beta2

planet postgresql - 2018-08-11(土) 09:00:00

The PostGIS development team is pleased to release PostGIS 2.5.0beta2.

This release is a work in progress. Remaining time will be focused on bug fixes and documentation until PostGIS 2.5.0 release. Although this release will work for PostgreSQL 9.4 and above, to take full advantage of what PostGIS 2.5 offers, you should be running PostgreSQL 11beta3+ and GEOS 3.7.0beta2 which were released recently.

Best served with PostgreSQL 11beta3 which was recently released.

Changes since PostGIS 2.5.0beta1 release are as follows:

  • 4115, Fix a bug that created MVTs with incorrect property values under parallel plans (Raúl Marín).
  • 4120, ST_AsMVTGeom: Clip using tile coordinates (Raúl Marín).
  • 4132, ST_Intersection on Raster now works without throwing TopologyException (Vinícius A.B. Schmidt, Darafei Praliaskouski)
  • 4109, Fix WKT parser accepting and interpreting numbers with multiple dots (Raúl Marín, Paul Ramsey)
  • 4140, Use user-provided CFLAGS in address standardizer and the topology module (Raúl Marín)
  • 4143, Fix backend crash when ST_OffsetCurve fails (Dan Baston)
  • 4145, Speedup MVT column parsing (Raúl Marín)

View all closed tickets for 2.5.0.

After installing the binaries or after running pg_upgrade, make sure to do:

ALTER EXTENSION postgis UPDATE;

— if you use the other extensions packaged with postgis — make sure to upgrade those as well

ALTER EXTENSION postgis_sfcgal UPDATE; ALTER EXTENSION postgis_topology UPDATE; ALTER EXTENSION postgis_tiger_geocoder UPDATE;

If you use legacy.sql or legacy_minimal.sql, make sure to rerun the version packaged with these releases.

2.5.0beta2

カテゴリー: postgresql

Avinash Kumar: Tuning Autovacuum in PostgreSQL and Autovacuum Internals

planet postgresql - 2018-08-11(土) 02:44:18

The performance of a PostgreSQL database can be compromised by dead tuples, since they continue to occupy space and can lead to bloat. We provided an introduction to VACUUM and bloat in an earlier blog post. Now, though, it’s time to look at autovacuum for postgres, and the internals you to know to maintain a high performance PostgreSQL database needed by demanding applications.

What is autovacuum ?

Autovacuum is one of the background utility processes that starts automatically when you start PostgreSQL. As you see in the following log, the postmaster (parent PostgreSQL process) with pid 2862 has started the autovacuum launcher process with pid 2868. To start autovacuum, you must have the parameter autovacuum set to ON. In fact, you should not set it to OFF in a production system unless you are 100% sure about what you are doing and its implications.

avi@percona:~$ps -eaf | egrep "/post|autovacuum" postgres 2862 1 0 Jun17 pts/0 00:00:11 /usr/pgsql-10/bin/postgres -D /var/lib/pgsql/10/data postgres 2868 2862 0 Jun17 ? 00:00:10 postgres: autovacuum launcher process postgres 15427 4398 0 18:35 pts/1 00:00:00 grep -E --color=auto /post|autovacuum Why is autovacuum needed ? 

We need VACUUM to remove dead tuples, so that the space occupied by dead tuples can be re-used by the table for future inserts/updates. To know more about dead tuples and bloat, please read our previous blog post. We also need ANALYZE on the table that updates the table statistics, so that the optimizer can choose optimal execution plans for an SQL statement. It is the autovacuum in postgres that is responsible for performing both vacuum and analyze on tables.

There exists another background process in postgres called Stats Collector that tracks the usage and activity information. The information collected by this process is used by autovacuum launcher to identify the list of candidate tables for autovacuum. PostgreSQL identifies the tables needing vacuum or analyze automatically, but only when autovacuum is enabled. Thi

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

Northeast PHP Boston 2018

php.net - 2018-08-10(金) 10:41:10
カテゴリー: php

10.5, 9.6.10, 9.5.14, 9.4.19, 9.3.24 リリース (2018-08-09)

www.postgresql.jp news - 2018-08-10(金) 09:40:44
10.5, 9.6.10, 9.5.14, 9.4.19, 9.3.24 リリース (2018-08-09) harukat 2018/08/10 (金) - 09:40
カテゴリー: postgresql

Dan Langille: PostgreSQL – convert a user to a role

planet postgresql - 2018-08-10(金) 07:03:46

Users and Roles in PostgreSQL are very similar. When I set up the FreshPorts database back in mid-2000, I was using PostgreSQL 7.0.3 (that’s my best guess based on my blog entry). I suspect roles were not available then and were introduced with PostgreSQL 8. I am positive someone will correct me if that’s wrong.

I now have a need to convert a user into a role, then add users to that role. Let’s see what happens.

I’m doing this on my development server, so there’s no concurrent access issue. I’ll just turn stuff off (disable the webserver, the scripts, etc).

Creating the new users begin; ALTER ROLE www NOLOGIN; CREATE USER www_dev WITH LOGIN PASSWORD '[redacted]' IN ROLE www; CREATE USER www_beta WITH LOGIN PASSWORD '[redacted]' IN ROLE www;

That went well, so I issued a COMMIT.

The two new users will have the same permission as the original user.

Changing the login

The login credentials will need to be changed. This is my update:

# $db = pg_connect("host=pg02.example.org dbname=freshports user=www password=oldpassword sslmode=require"); $db = pg_connect("host=pg02.example.org dbname=freshports user=www_beta password=newpassword sslmode=require"); Access rights

I also updated pg_hba.conf for this server.

#hostssl freshports www 10.0.0.1/32 md5 hostssl freshports www_beta 10.0.0.1/32 md5

After changing pg_hba.conf, you have to tell PostgreSQL about it. This is the FreeBSD command for that:

sudo service postgresql reload It just worked

I was impressed with how straight forward this was. https://beta.freshports.org/ came right up.

I have three other users to convert to roles but if it’s as easy as the above, I should be finished in time for dinner.

カテゴリー: postgresql

Álvaro Herrera: Talk slides: Partitioning Improvements in PostgreSQL 11

planet postgresql - 2018-08-10(金) 05:58:45

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 Brazilian gang. Next time, I promise I’ll make time for Sunday’s churrasco.

For our amusement, they posted a teaser video to share the community conference spirit.

カテゴリー: postgresql

Christophe Pettus: Three Steps to pg_rewind Happiness

planet postgresql - 2018-08-10(金) 02:06:52

pg_rewind is a utility included with PostgreSQL since 9.x. It’s used to “rewind” a server so that it can be attached as a secondary to a primary. The server being rewound could be the former primary of the new primary, or a secondary that was a peer of the new primary.

In pg_rewind terminology, and in this post, the “source” server is the new primary that the old server is going to be attached to, and the “target” is the server that will be attached to the source as a secondary.

Step One: Have a WAL Archive

While pg_rewind does not require that you have a WAL archive, you should have one. pg_rewind works by “backing up” the target server to a state before the last shared checkpoint of the two servers. Then, when the target starts up, it uses WAL information to replay itself to the appropriate point at which it can connect as a streaming replica to the source. To do that, it needs the WAL information from the rewind point onwards. Since the source had no reason to “know” that it would be used as a primary, it may not have enough WAL information in its pgxlog / pgwal directory to bring the target up to date. If it doesn’t, you are back to rebuilding the new secondary, the exact situation that pg_rewind is meant to avoid.

Thus, make sure you have a WAL archive that the target can consult as it is coming up.

Step Two: Properly Promote the Source Server

The source server, which will be the new primary, needs to be properly promoted. Use the pg_ctl promote option, or the trigger_file option in recovery.conf so that the source promotes itself, and starts a new timeline. Don’t just shut the source down, remove recovery.conf, and bring it back up! That doesn’t create a new timeline, and the source won’t have the appropriate divergence point from the target for pg_rewind to consult.

Step Three: Wait for the Forced Checkpoint to Complete

When a secondary is promoted to being a primary, it starts a forced checkpoint when it exits recovery mode. This checkpoint is a “fast” checkpoint, but it can still take a while, depending

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

Craig Kerstiens: Fun with SQL: Common Table Expressions for more readable queries

planet postgresql - 2018-08-09(木) 23:44:00

This week we’re continuing our fun with SQL series. In past posts we’ve looked at generate_series, window functions, and recursive CTEs. This week we’re going to take a step backward and look at standard CTEs (common table expressions) within Postgres.

Admittedly SQL isn’t always the most friendly language to read. It’s a little more friendly to write, but even still not as natuarlly readable as something like Python. Despite it’s shortcomings there it is the lingua franca when it comes to data, SQL is the language and API that began with relational databases and now even non traditional databases are aiming to immitate it with their own SQL like thing. With CTEs though our SQL, even queries hundreds of lines long, can become readable to someone without detailed knowledge of the application.

CTEs (common table expressions), often referred to as with clauses/queries, are essentially views that are valid during the course of a transaction. They can reference earlier CTEs within that same transaction or query essentially allowing you separate building blocks on which you compose your queries. It is of note that CTEs are an optimization boundary, so in cases they may have worse performance than their alternative non-CTE queries. Even still they’re incredible useful for readability and should be considered when constructing large complex queries. Let’s dig in with an example.

We’re going to assume a basic CRM schema where we have organizations, that have contacts which are tied to accounts, and those accounts have opportunities. In this CRM world we want to create a report that has all opportunities that were opened between 30 and 60 days ago, and have a contact that was contacted within the last 30 days, grouped by the sales rep owner. The goal is to see that our sales reps are actively chasing the deals that they said exist.

Query for opportunities created 1-2 months ago

First we’re going to construct our query that gives us all opportunities opened in that range:

WITH opp_list AS ( SELECT opportunities.id as o[...]
カテゴリー: postgresql

Viorel Tabara: An Overview of Amazon RDS & Aurora Offerings for PostgreSQL

planet postgresql - 2018-08-09(木) 20:15:49

AWS PostgreSQL services fall under the RDS umbrella, which is Amazon’s DaaS offering for all known database engines.

Managed database services offer certain advantages that are appealing to the customer seeking independence from infrastructure maintenance, and highly available configurations. As always, there isn’t a one size fits all solution. The currently available options are highlighted below:

Aurora PostgreSQL Related resources  ClusterControl for PostgreSQL  Comparing Cloud Database Options for PostgreSQL  Ten Tips for Going into Production with PostgreSQL

The Amazon Aurora FAQ page provides important details that need to be considered before diving into the product. For example, we learn that the storage layer is virtualized and sits on a proprietary virtualized storage system backed up by SSD.

Pricing

In term of pricing, it must be noted that Aurora PostgreSQL is not available in the AWS Free Tier.

Compatibility

The same FAQ page makes it clear that Amazon doesn’t claim 100% PostgreSQL compatibility. Most (my emphasis) of the applications will be fine, e.g. the AWS PostgreSQL flavor is wire-compatible with PostgreSQL 9.6. As a result, the Wireshark PostgreSQL Dissector will work just fine.

Performance

Performance is also linked to the instance type, for example the maximum number of connections is by default configured based on the instance size.

Also important when it comes to compatibility is the page size that has been kept at 8KiB which is the PostgreSQL default page size. Speaking of pages it’s worth quoting the FAQ: “Unlike traditional database engines Amazon Aurora never pushes modified database pages to the storage layer, resulting in further IO consumption savings.” This is made possible because Amazon changed the way the page cache is managed, allowing it to remain in memory in case of database failure. This feature also benefits the database restart following a crash, allowing the recovery to happen much faster than in the traditional method of replaying the logs.

According to the

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

10.5

postgresql.org - 2018-08-09(木) 09:00:00
10.5 is the latest release in the 10 series.
カテゴリー: postgresql

9.6.10

postgresql.org - 2018-08-09(木) 09:00:00
9.6.10 is the latest release in the 9.6 series.
カテゴリー: postgresql

9.5.14

postgresql.org - 2018-08-09(木) 09:00:00
9.5.14 is the latest release in the 9.5 series.
カテゴリー: postgresql

9.4.19

postgresql.org - 2018-08-09(木) 09:00:00
9.4.19 is the latest release in the 9.4 series.
カテゴリー: postgresql

9.3.24

postgresql.org - 2018-08-09(木) 09:00:00
9.3.24 is the latest release in the 9.3 series.
カテゴリー: postgresql

Christophe Pettus: Does anyone really know what time it is?

planet postgresql - 2018-08-08(水) 09:58:03

PostgreSQL has a variety of ways of telling time: now(), statement_timestamp(), and clock_timestamp(). Each has a different sense of when “now” is:

  • now() is the time at the start of the transaction; it never changes while the current transaction is open.
  • statement_timestamp() is the time that the current statement started running. It changes from statement to statement, but is constant within a statement (which means it is constant within a PL/pgSQL function).
  • clock_timestamp() changes each time it is called, regardless of context.

Each has its uses:

  • For a predicate in a WHERE clause, you want either now() or statement_timestamp(). These work properly with indexes, because they are constant within the execution of a statement.
  • If you need the time to update within a single transaction, use statement_timestamp(); otherwise, use now().
  • Generally, you only use clock_timestamp() inside of a programming language procedure so you can get the current timestamp.
カテゴリー: postgresql

ページ