フィードアグリゲーター

Bruce Momjian: Exploring Postgres Tips and Tricks

planet postgresql - 2019-06-07(金) 07:00:01

I did a webinar two weeks ago titled, "Exploring Postgres Tips and Tricks." The slides are now online, as well as a video recording. I wasn't happy with the transition I used from the PDF to the blog entries, but now know how to improve that next time.

I think I might do more of these by expanding on some of the topics I covered, like psql and monitoring. Also, a new video is available of the sharding presentation I mentioned previously.

カテゴリー: postgresql

Álvaro Hernández: PostgreSQL Ibiza: 2 weeks to go

planet postgresql - 2019-06-07(金) 00:37:11
Five-day PostgreSQL networking experience, embedding a 2-day conference Just 2 weeks to go for PostgreSQL Ibiza. The new, innovative PostgreSQL Conferences that happens 50m away from a beach. The conference for thinkers, for networking, for partnering. The conference to be at. But a conference is nothing without great content. And after receiving more than 71 talk submissions, and the hard work that the Committee has done to select the talks, PostgreSQL Ibiza will have top-notch talks from top-notch international speakers.
カテゴリー: postgresql

Robert Treat: The Lost Art of plpgsql

planet postgresql - 2019-06-06(木) 14:12:00

One of the big features talked about when PostgreSQL 11 was released was that of the new stored procedure implementation. This gave Postgres a more standard procedure interface compared to the previous use of functions. This is perticularly useful for folks who are doing database migrations where they may have been using the standards CALL syntax vs Postgres traditional use of SELECT function(); syntax. So it struck me as odd earlier this year when I noticed that, despite the hoopla, that a year later that there was almost zero in the way of presentations and blog posts on either the new stored procedure functionality or the use of plpgsql in general.

And so I got the idea that maybe I would write such a talk and present it at PGCon; a nod to the past and the many years I've spent working with plpgsql in a variety of roles. The commitee liked the idea (disclosure that I am on the pgcon committee, but didn't advocate for myself) and so this talk was born. For a first time talk I think it turned out well, though it could definitly use some polish; but I'm happy that it did help spark some conversation and actually has given me a few items worth following up on, hopefully in future blog posts.

Video should be available in a few weeks, but for now, I've gone ahead and uploaded the slides on slideshare.

カテゴリー: postgresql

Robert Haas: The Contributors Team

planet postgresql - 2019-06-06(木) 02:37:00
Recently, the PostgreSQL project spun up a "contributors" team, whose mission is to ensure that the PostgreSQL contributors list is up-to-date and fair. The contributors page has a note which says "To suggest additions to the list, please email contributors@postgresql.org."  The current members of the team are Dave Page, Stephen Frost, Vik Fearing, and me.

Read more »
カテゴリー: postgresql

Mark Wong: PDXPUG June Meetup: Accessing Postgres with Java

planet postgresql - 2019-06-06(木) 01:06:21

When: 6-8pm Thursday June 20, 2019
Where: PSU Business Accelerator (Parking is open after 5pm.)
Who: Will McLean

To follow the presentations on accessing Postgres from Python and Scala, I will lead a discussion on accessing Postgres with Java. I’ll start with a jdbc tutorial and finish with adding data access to a springboot webapp.

I have twenty years experience in e-commerce applications, the last eight here in Portland, mostly at Nike.For the last few years everything has been moving to Amazon RDS Postgres, that’s a trend pdxpug can get behind!  I am currently working for Navis on CRM applications for the hospitality industry.

カテゴリー: postgresql

Julien Rouhaud: PoWA 4: changes in powa-archivist!

planet postgresql - 2019-06-05(水) 23:26:17

This article is part of the PoWA 4 beta series, and describes the changes done in powa-archivist.

For more information about this v4, you can consult the general introduction article.

Quick overview

First of all, you have to know that there is not upgrade possible from v3 to v4, so a DROP EXTENSION powa is required if you were already using PoWA on any of your servers. This is because this v4 involved a lot of changes in the SQL part of the extension, making it the most significant change in the PoWA suite for this new version. Looking at the amount changes at the time I’m writing this article, I get:

CHANGELOG.md | 14 + powa--4.0.0dev.sql | 2075 +++++++++++++++++++++------- powa.c | 44 +- 3 files changed, 1629 insertions(+), 504 deletions(-)

The lack of upgrade shouldn’t be a problem in practice though. PoWA is a performance tool, so it’s intended to have data with high precision but with a very limited history. If you’re looking for a general monitoring solution keeping months of counters, PoWA is definitely not the tool you need.

Configuring the list of remote servers

Concerning the features themselves, the first small change is that powa-archivist does not require the background worker to be active anymore, as it won’t be used for remote setup. That means that a PostgreSQL restart is not needed needed anymore to install PoWA. Obviously, a restart is still required if you want to use the local setup, using the background worker, or if you want to install additional extensions that themselves require a restart.

Then, as PoWA needs some configuration (frequency of snapshot, data retention and so on), some new tables are added to be able to configure all of that. The new powa_servers table stores the configuration for all the remote instances whose data should be stored on this instance. This local PoWA instance is call a repository server (that typically should be dedicated to storing PoWA data), in opposition to remote instances which are the instances you want to monitor. The conte

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

Hans-Juergen Schoenig: Tech preview: Improving COPY and bulkloading in PostgreSQL 12

planet postgresql - 2019-06-05(水) 22:03:47

If you are relying heavily on the PostgreSQL COPY command to load data into PostgreSQL quickly, PostgreSQL 12 might offer a feature, which is most likely very beneficial to you. Bulkloading is an important operation and every improvement in this area is certainly going to help many people out there, who want to import data into PostgreSQL as fast as possible.

COPY: Loading and unloading data as fast as possible

When taking a closer look at the syntax of the COPY command in PostgreSQL 12 you will quickly see two things:

• \h will now point to the correct page in the documentation
• COPY now supports a WHERE condition

Here is the complete syntax overview:

db12=# \h COPY Command: COPY Description: copy data between a file and a table Syntax: COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [, ...] ) ] [ WHERE condition ] COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | PROGRAM 'command' | STDOUT } [ [ WITH ] ( option [, ...] ) ] where option can be one of: FORMAT format_name FREEZE [ boolean ] DELIMITER 'delimiter_character' NULL 'null_string' HEADER [ boolean ] QUOTE 'quote_character' ESCAPE 'escape_character' FORCE_QUOTE { ( column_name [, ...] ) | * } FORCE_NOT_NULL ( column_name [, ...] ) FORCE_NULL ( column_name [, ...] ) ENCODING 'encoding_name' URL: https://www.postgresql.org/docs/12/sql-copy.html

While having a link to the documentation around is certainly beneficial, the WHERE condition added to PostgreSQL 12 might even be more important. What is the purpose of this new feature? So far it was possible to completely import a file. However, in some cases this has been a problem: More often than not people only wanted to load a subset of data and had to write a ton of code to filter data before the import or once data has been written into the database already.

COPY … WHERE: Applying filters while importing data

Im PostgreSQL data can be filtered

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

Venkata Nagothi: How to Optimize PostgreSQL Logical Replication

planet postgresql - 2019-06-05(水) 18:48:00

Logical Replication or Pglogical is a table level, WAL based replication mechanism which replicates the data of specific Tables between two PostgreSQL instances. There seems to be a confusion between “pglogical” and “Logical Replication”. Both of them provide the same kind of replication mechanism with some differences in features and capabilities. Logical Replication is introduced in PostgreSQL-10 as an in-built feature unlike pglogical which is an extension. “Pglogical” with ongoing continuous developments, remains as the only option for implementing Logical Replication for those environments using PostgreSQL versions prior to 10. Eventually, all the features part of pglogical will be part of Logical Replication. In other words, pglogical (extension) became Logical Replication (in-built feature). The basic advantage of Logical Replication is that it does not need any extensions to be installed / created which is in turn beneficial to those environments where-in installing extensions is restricted.

Related resources  ClusterControl for PostgreSQL  An Overview of Logical Replication in PostgreSQL  PostgreSQL Streaming Replication vs Logical Replication

This blog will focus on optimizing Logical Replication. That means, the optimization tips and techniques highlighted in this blog will apply for both pglogical and Logical Replication.

Logical Replication is a WAL based replication which is first of its kind. As a DBA, this would be much more reliable and performant replication mechanism when compared to other trigger based replication solutions. The changes made to the tables part of pglogical replication are replicated in real-time via WAL records which makes it highly efficient and non complex. All of the other replication mechanisms in the market are trigger based which can pose performance and maintenance challenges. With Logical Replication coming in, dependency on trigger based replication is almost gone.

There are other blogs which explain how to configure Logical Replication in quite a detail.

In

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

429 Too Many Requests

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

If an service wants to limit the amount of requests clients make, they can use the 429 Too Many Requests status code to inform the client that they’ve exceeded it.

For example, perhaps an API wants to limit users to 100 HTTP requests per hour.

It’s possible to tell a client when they can make requests again with the Retry-After header, but this is optional.

Example HTTP/1.1 429 Too Many Requests Content-Type text/plain Retry-After: 3600 You exceeded the limit. Try again in an hour References

Also see:

カテゴリー: php

Interview with Matthew Setter

planet PHP - 2019-06-04(火) 21:37:00

@settermjd Show Notes Audio This episode is sponsored by
Day Camp 4 Developers:Data

The post Interview with Matthew Setter appeared first on Voices of the ElePHPant.

カテゴリー: php

Michael Paquier: Postgres 12 highlight - Table Access Methods and blackholes

planet postgresql - 2019-06-04(火) 14:16:34

Postgres is very nice when it comes to extending with custom plugins, with many set of facilities available, like:

After a heavy refactoring of the code, Postgres 12 ships with a basic infrastructure for table access methods which allow to customize how table data is stored and accessed. By default, all tables in PostgreSQL use the historical heap, which works on a page-based method of 8kB present in segment files of 1GB (default sizes), with full tuple versions stored. This means, in simple words, that even updating one attribute of a tuple requires storing a full new version. This makes the work related to vacuum and autovacuum more costly as well. Well, the goal of this post is not to discuss about that, and there is documentation on the matter. So please feel free to refer to it.

Table access methods are really cool, because they basically allow to plugin directly into Postgres a kind of equivalent to MySQL storage engines, making it possible to implement things like columnar storage, which is something where heap is weak at. It is possible to roughly classify what is possible to do into two categories:

  • Access method going through the storage manager of Postgres, which makes use of the existing shared buffer layer, with the exiting paging format. This has two advantages: backups and checksums are normally, and mostly, automatically supported.
  • Access method not going through Postgres, which has the advantage to not rely on Postgres shared buffers (page format can be a problem as well), making it possible to rely fully on the OS cache. Note that it is then up to you to add support for checksumming, backups, and such.

Access methods could make a comparison with foreign data wrappers, but the reliability is much different, one big point being that they are fully transactional with the backend they work with, which is usually a big deal for applications, and have transparent DDL and command su

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

Voices of the ElePHPant: Interview with Matt Trask

phpdeveloper.org - 2019-06-04(火) 09:00:01

@matthewtrask

Show Notes The League of Extraordinary Packages Fractal

Audio

This episode is sponsored by Day Camp 4 Developers

The post Interview with Matt Trask appeared first on Voices of the ElePHPant.

カテゴリー: php

Pine: Binding Relevant Models to Routes in Nested Laravel Resources

phpdeveloper.org - 2019-06-04(火) 09:00:01

Route model binding is a great and well-known feature of the Laravel framework. But, when we work with nested resources, we might write a custom rule explicitly that ensures the child models can be load with the correct parent model. Let’s see!

A Concrete Example Let’s say we have a ...

カテゴリー: php

PHP: Hypertext Preprocessor: PHP 7.2.19 Release Announcement

phpdeveloper.org - 2019-06-04(火) 09:00:01

The PHP development team announces the immediate availability of PHP 7.2.19. This is a security release which also contains several minor bug fixes.All PHP 7.2 users are encouraged to upgrade to this version.For source downloads of PHP 7.2.19 please visit our downloads page, Windows source and binar...

カテゴリー: php

PHP: Hypertext Preprocessor: PHP 7.3.6 Release Announcement

phpdeveloper.org - 2019-06-04(火) 09:00:01

The PHP development team announces the immediate availability of PHP 7.3.6. This is a security release which also contains several bug fixes.All PHP 7.3 users are encouraged to upgrade to this version.For source downloads of PHP 7.3.6 please visit our downloads page, Windows source and binaries can ...

カテゴリー: php

Exakat: Add Exakat To Your CI Pipeline

phpdeveloper.org - 2019-06-04(火) 09:00:01

Add Exakat To Your CI Pipeline

The Continuous Integration pipeline builds your code automatically, and runs a large number of checks. When those checks fail, the build of the application is cancelled, and the development team have a change to square everything again. 

With m...

カテゴリー: php

Matthias Noback - PHP & Symfony: Learning Laravel - Observations, part 1: The service container

phpdeveloper.org - 2019-06-04(火) 09:00:01

With excerpts from the documentation

I have worked with symfony 1, Symfony 2+, Zend Framework 1, Zend Expressive, but never with Laravel. Until now. My partner was looking for a way to learn PHP and building web applications with it. Most of my own framework knowledge is related to Symfony, so my...

カテゴリー: php

Community News: Latest PEAR Releases (06.03.2019)

phpdeveloper.org - 2019-06-03(月) 22:05:01

Latest PEAR Releases:

カテゴリー: php

Wim Bertels: PGConf.be 2019

planet postgresql - 2019-06-03(月) 21:47:00
A round up of the first PGConf.be

Stefan F. already wrote a nice blog about the conference.

Most of the presentations, a couple recordings and a few photos are available at the pgconf.be website

Using the well known and broadly spread technique of inductive reasoning we came to the conclusion that this first PGConf.be conference was a success, as well as the art work. No animals or elephants we’re hurt during this event.

The statistics are

  • 53 participants with a low standardeviation regarding attendance time

    • depending on the session, an extra 5 to 30 students attended as well

  • 11 speakers

  • 5 sponsors

This conference wouldn’t have been possible without the help of volunteers.
To conclude a big thank you to all the speakers, sponsors and attendants.
Without them a conference is just a like tee party.

カテゴリー: postgresql

ページ