Pavel Stehule: New release of pspg pager

planet postgresql - 2018-08-27(月) 17:00:00
I redesigned some work with mouse - ncurses native implementation is simple, but slow by design.

A default layout of pspg is based on old Norton Commander layout. It is good for beginners, because almost all controls are visible. Probably, when you work with pspg longer time, then you prefer more visible content against auxiliary lines. The lines (bars) can be disabled now - you can run pspg with option --no-bars. The pspg is available from github
カテゴリー: postgresql

Jobin Augustine: PostgreSQL Accessing MySQL as a Data Source Using mysqsl_fdw

planet postgresql - 2018-08-25(土) 00:38:55

There are many organizations where front/web-facing applications use MySQL and back end processing uses PostgreSQL®. Any system integration between these applications generally involves the replication—or duplication—of data from system to system. We recently blogged about pg_chameleon which can be used replicate data from MySQL® to PostgreSQL. mysql_fdw can play a key role in eliminating the problem of replicating/duplicating data. In order to eliminate maintaining the same data physically in both postgres and MySQL, we can use mysql_fdw. This allows PostgreSQL to access MySQL tables and to use them as if they are local tables in PostgreSQL. mysql_fdw can be used, too, with Percona Server for MySQL, our drop-in replacement for MySQL.

This post is to showcase how easy it is to set that up and get them working together. We will address a few points that we skipped while discussing about FDWs in general in our previous post

Preparing MySQL for fdw connectivity

On the MySQL server side, we need to set up a user to allow for access to MySQL from the PostgreSQL server side. We recommend Percona Server for MySQL if you are setting it up for the first time.

mysql> create user 'fdw_user'@'%' identified by 'Secret!123';

This user needs to have privileges on the tables which are to be presented as foreign tables in PostgreSQL.

mysql> grant select,insert,update,delete on EMP to fdw_user@'%'; Query OK, 0 rows affected (0.00 sec) mysql> grant select,insert,update,delete on DEPT to fdw_user@'%'; Query OK, 0 rows affected (0.00 sec) Installing mysql_fdw on PostgreSQL server

Under the hood, MySQL FDW (mysql_fdw) facilitates the use of PostgreSQL server as a client for MySQL Server, which means it can then fetch data from the MySQL database as a client. Obviously, mysql_fdw uses MySQL client libraries. Nowadays, many Linux distributions are packaged with MariaDB® libraries. This works well enough for mysql_fdw to function. If we install mysql_fdw from the PGDG repo, then mariadb-devel.x86_64 packages will be installed alongsid

カテゴリー: postgresql

Devrim GÜNDÜZ: My picks for Postgres Open SV 2018 !

planet postgresql - 2018-08-24(金) 21:07:00
Postgres Open SV 2018 is less than two weeks away now, and I'll speak there as well.

Many people, including me, are flying overseas to attend this great conference, so please hurry up and register before the tickets run out!

Life is hard when there are a lot of talks in parallel, but still I picked up some interesting talks that I want to attend: Continue reading "My picks for Postgres Open SV 2018 !"
カテゴリー: postgresql

Dimitri Fontaine: Geolocation with PostgreSQL

planet postgresql - 2018-08-24(金) 19:11:33

We have loaded Open Street Map points of interests in the article The Most Popular Pub Names — which compares PostgreSQL with MongoDB for simple geographical queries, and is part of our PostgreSQL Extensions article series. In today’s article, look at how to geolocalize an IP address and locate the nearest pub, all within a single SQL query!

For that, we are going to use the awesome ip4r extension from RhodiumToad.

カテゴリー: postgresql

Hans-Juergen Schoenig: PostgreSQL: Improving sort performance

planet postgresql - 2018-08-23(木) 22:00:52

Sorting is a very important aspect of PostgreSQL performance tuning. However, tuning sorts is often misunderstood or simply overlooked by many people. So I decided to come up with a PostgreSQL blog showing, how sorts can be tuned in PostgreSQL.

Creating sample data

To show how sorting works, I created a couple of million rows first:

test=# CREATE TABLE t_test (x numeric); CREATE TABLE test=# INSERT INTO t_test SELECT random() FROM generate_series(1, 5000000); INSERT 0 5000000 test=# ANALYZE ; ANALYZE

What the code does is to create a table and load 5 million random values. As you will notice, data can be loaded within seconds.

Sorting data in PostgreSQL

Let us try to sort the data. For the sake of simplicity I am using the most simplistic statements possible. What you can see is that PostgreSQL has to sort on disk because the data we want to sort does not fit into memory. In this case a bit more than 100 MB of data is moved to disk:

test=# explain analyze SELECT * FROM t_test ORDER BY x; QUERY PLAN -------------------------------------------------------------------------- Sort (cost=804270.42..816770.42 rows=5000000 width=11) (actual time=4503.484..6475.222 rows=5000000 loops=1) Sort Key: x Sort Method: external merge Disk: 102896kB -> Seq Scan on t_test (cost=0.00..77028.00 rows=5000000 width=11) (actual time=0.035..282.427 rows=5000000 loops=1) Planning time: 0.139 ms Execution time: 6606.637 ms (6 rows)

Why does PostgreSQL not simply sort stuff in memory? The reason is the work_mem parameter, which is by default set to 4 MB:

test=# SHOW work_mem; work_mem ---------- 4MB (1 row)

work_mem tells the server that up to 4 MB can be used per operation (per sort, grouping operation, etc.). If you sort too much data, PostgreSQL has to move the excessive amount of data to disk, which is of course slow.

Fortunately changing work_mem is simple and can even be done at the session level.

Speeding up sorts in PostgreSQL – using more work_mem


カテゴリー: postgresql

第 152 回理事会議事録 (2018-08) news - 2018-08-23(木) 19:14:50
第 152 回理事会議事録 (2018-08) anzai 2018/08/23 (木) - 19:14
カテゴリー: postgresql

Joshua Otwell: PostgreSQL Streaming Replication vs Logical Replication

planet postgresql - 2018-08-23(木) 18:58:00

I consider myself a bit of an explorer. In certain things that is. I typically will always order the same food from a familiar restaurant for fear of disappointment outweighs my apprehension to try something new.

And of course, a hungry human tends to just eat right?

Yet, when it comes to technology, in particular SQL (PostgreSQL), I tend to stumble full speed (my definition of exploring) into oftentimes, unfamiliar areas, with the hope to learn. What better way to learn than experience?

So what on earth does this rambling have to do with Logical and Streaming replication in PostgreSQL?

I am a complete novice in these areas with zero knowledge. Yes, I have about as much understanding in this area of Postgres as I do in astrophysics.

Did I mention that I had zero knowledge?

Therefore, in this blog post, I will try to digest the differences in these types of replication. Without real-world hands on experience, I can't promise you the 'Be all end all' manuscript for replication.

Likely, others less-versed in this particular area (such as myself) would benefit from this blog post.

Experienced users and developers along for the ride, I hope to see you in the comments below.

A Couple of Base Definitions

In the broad sense of the term, what does Replication mean?

Replication as defined on Wiktionary has this to say:

"Process by which an object, person, place or idea may be copied mimicked or reproduced."

Yet, the 5th listed item there is more applicable to this blog post and I feel we should look at it as well:

"(computing) The process of frequent electronic data copying a one database in one computer or server to a database in another so that all users share the same level of information. Used to improve fault tolerance of the system."

Now there is something we can get into. The mention of copying data from one server or database to another? We are in familiar territory now...

So, adding in what we know from that definition, what are the definitions of Streaming Replication and Logical Replication?

Let's see what the

カテゴリー: postgresql

Federico Campoli: Happy birthday pg_chameleon

planet postgresql - 2018-08-23(木) 09:00:00
Today is two years since I started working on pg_chameleon. Back in 2016 this commit changed the project’s license from GPL v2 to the 2 clause BSD and the project’s scope, which became a MySQL to PostgreSQL replica system. Since then I learned a lot of lessons, made several mistakes and worked out solutions which resulted in a decent tool for bridging two different universes. Writing a replica system is a very complex task.
カテゴリー: postgresql