Compiling new Postgres (9.6.x) on newest LTS Ubuntu (16.04)

It is great to confirm that the latest version of Postgres cleanly builds on the latest Ubuntu without any errors or warnings.   Hmmm, I wonder now if GGC 5 makes anything measurably run faster??

Pre-Requisites:

$ sudo apt-get install build-essential  libreadline-dev  zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev

With this complete, you can then do an industrial strength build of PostgreSQL as per below:


$ tar -xvf postgresql-9.6.x.tar.bz2

$ cd postgresql-9.6.x

$ ./configure --with-libxml --with-libxslt --with-openssl

$ make

MySQL Foreign Data Wrapper : A quick tour

Data centers are no longer dominated by a single DBMS. Many companies have heterogeneous environments and may want their Postgres database to talk to other database systems. Foreign Data Wrappers can be the right solution for many scenarios. The BigSQL Project provides a well tested, ready to use MySQL FDW with Postgres. This makes life easy for a DevOps or DataCenter person.

Here is a quick tour on how to configure Foreign Data Wrappers for MySQL, so that Postgres can query a MySQL table. For this quick guide, I use a CentOS Linux machine. This, or a similar setup, should work fine on all other operating systems.

Setting up a test MySQL server for the test

In this demo I’m going to create a table in MySQL  which should be available to Postgres though the FDW.
The FDW can talk to any MySQL distribution including Oracle’s MySQL, Percona Server or MariaDB. I’m going to use MariaDB, which is more community friendly.

Install MariaDB Server and Start the service

$ sudo yum install mariadb-server.x86_64
$ sudo systemctl start mariadb

Connect as root user of mariadb and create a database

$ mysql -uroot
MariaDB [(none)]> create database postgres;

Connect to Database and create a table

MariaDB [(none)]> use postgres;
MariaDB [postgres]> create table t1m(id int,name varchar(30));

Insert some data in the table:

MariaDB [postgres]> insert into t1m values (1,'abc');
Query OK, 1 row affected (0.04 sec)

MariaDB [postgres]> insert into t1m values (2,'def');
Query OK, 1 row affected (0.00 sec)

MariaDB [postgres]> insert into t1m values (3,'hij');
Query OK, 1 row affected (0.03 sec)

Setting up Postgres Database

Install Postgres

For this test, I’m going to use the Postgres DevOps Sandbox from the BigSQL project.
Download the Sandbox from BigSQL
Since this is a sandbox, you just need to unpack it

$ tar -xvf bigsql-9.5.3-5-linux64.tar.bz2

Install MySQL FDW

Go to the unpacked directory and invoke the bigsql command line tool to install MySQL FDW

$ cd bigsql
$ ./pgc list
Category | Component | Version | Status | Port | Updates
PostgreSQL pg92 9.2.17-5 NotInstalled
PostgreSQL pg93 9.3.13-5 NotInstalled
PostgreSQL pg94 9.4.8-5 NotInstalled
PostgreSQL pg95 9.5.3-5 NotInitialized
Extensions cassandra_fdw3-pg95 3.0.0-1 NotInstalled
Extensions hadoop_fdw2-pg95 2.5.0-1 NotInstalled
Extensions mysql_fdw2-pg95 2.1.2-1 NotInstalled
Extensions oracle_fdw1-pg95 1.4.0-1 NotInstalled
Extensions orafce3-pg95 3.3.0-1 NotInstalled
Extensions pgtsql9-pg95 9.5-1 NotInstalled
Extensions pljava15-pg95 1.5.0-1 NotInstalled
Extensions plv814-pg95 1.4.8-1 NotInstalled
Extensions postgis22-pg95 2.2.2-2 NotInstalled
Extensions slony22-pg95 2.2.5-2 NotInstalled
Extensions tds_fdw1-pg95 1.0.7-1 NotInstalled
Servers bam2 1.5.0 NotInstalled
Servers cassandra30 3.0.6 NotInstalled
Servers hadoop26 2.6.4 NotInstalled
Servers hive2 2.0.1 NotInstalled
Servers pgbouncer17 1.7.2-1 NotInstalled
Servers pgha2 2.1b NotInstalled
Servers pgstudio2 2.0.1-2 NotInstalled
Servers spark16 1.6.1 NotInstalled
Servers tomcat8 8.0.35 NotInstalled
Servers zookeeper34 3.4.8 NotInstalled
Applications backrest 1.02 NotInstalled
Applications birt 4.5.0 NotInstalled
Applications ora2pg 17.4 NotInstalled
Applications pgbadger 8.1 NotInstalled
Frameworks java8 8u92 NotInstalled
$ ./pgc install mysql_fdw2-pg95
['mysql_fdw2-pg95']
Get:1 http://s3.amazonaws.com/pgcentral mysql_fdw2-pg95-2.1.2-1-linux64
Unpacking mysql_fdw2-pg95-2.1.2-1-linux64.tar.bz2

Note:- We can use the same command line tool to initalize a new postgres cluster

$ ./pgc init pg95

## Initializing pg95 #######################

Superuser Password [password]:
Confirm Password:
Giving current user permission to data dir

Initializing Postgres DB at:
-D "/home/vagrant/bigsql/data/pg95"

Using PostgreSQL Port 5432

Password securely remembered in the file: /home/vagrant/.pgpass

to load this postgres into your environment, source the env file:
/home/vagrant/bigsql/pg95/pg95.env

Create the extension in the postgres database

create extension mysql_fdw;

Create foreign server

postgres=# CREATE SERVER mysql_svr
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host 'localhost', port '3306');
CREATE SERVER
postgres=#

Create foreign table

postgres=# CREATE FOREIGN TABLE mysql_tab (
postgres(# id int,
postgres(# name varchar(30)
postgres(# )
postgres-# SERVER mysql_svr
postgres-# OPTIONS (dbname 'postgres', table_name 't1m');
CREATE FOREIGN TABLE
postgres=#

Create user mapping

postgres=# CREATE USER MAPPING FOR PUBLIC
postgres-# SERVER mysql_svr
postgres-# OPTIONS (username 'root');

(if your user is having password authentication to mysql, you have to pass that also in the format (username ‘username’, password ‘password’))

Now everything is set, You can test by querying the table.

postgres=# select * from mysql_tab;
id | name
----+-------
1 | abc
2 | def
3 | hij
(3 rows)


Note:- MySQL FDW for Postgres requires MySQL Client Libraries. Please make sure that libmysqlclient.so is there in the LD_LIBARY_PATH. if this file name is something different like “libmysqlclient.so.18.0.0″, you may have to create a softlink with name “libmysqlclient.so”

Welcome Affan

Affan Salman joins the team
East Brunswick, NJ, March 1, 2016

OpenSCG, a leading provider of subscriptions and services for PostgreSQL, announced today that Affan Salman has joined its team of top-tier PostgreSQL talent as Senior Database Architect. Affan is the primary original author of EnterpriseDB’s Oracle compatibility from 10 years ago and has spent much of the last six years rounding out his database internals credentials by becoming a Cassandra, Spark & Hadoop rock star.

“We are very excited that Affan has re-joined the family,” said Denis Lussier, CEO of OpenSCG. “Affan put EnterpriseDB on the map for Oracle compatibility back in the day. Having him as a key part of the OpenSCG team will enable us to move faster, and further enhance our strategic plans for the future.” As part of his new responsibilities, Salman is expected to contribute to BigSQL, a new PostgreSQL Distribution that OpenSCG is sponsoring.

“I was thrilled to join the OpenSCG/BigSQL team,” said Salman. “I have worked with the management team for over a decade, and was looking for a balance between new product development and working closely with customers on data-driven business problems. My new role offers the chance to do both.”

About OpenSCG
Open Source Consulting Group, Inc. (OpenSCG) is a leading provider of subscriptions and services for PostgreSQL, the world’s most advanced open source database. The OpenSCG PostgreSQL team is one of the largest and most experienced in the world, and the management team is respected and trusted across the global Postgres community. OpenSCG offers PostgreSQL consulting and migration services, as well as subscription services that provide 24×7 global support and remote DBA Services.

Jan Wieck joins the team!

East Brunswick, NJ, April 21, 2016

OpenSCG, a leading provider of subscriptions and services for PostgreSQL, announced today that Jan Wieck has joined its team of top-tier PostgreSQL talent as Senior Database Architect. Wieck is a long time contributor to the PostgreSQL Global Development Group, and has authored many key Postgres technologies over the years, including Stored Procedures (PL/pgSQL), Foreign Keys, TOAST (unlimited row size) and the Slony Replication System. He has also focused for years on stabilization and performance enhancements for PostgreSQL and related data technologies.

“We are very excited that Jan has joined us,” said Denis Lussier, CEO of OpenSCG. “My original vision for Oracle compatibility was based in large part on Jan’s elegant stored procedure design. Having him as a key part of the OpenSCG team will enable us to move faster, and further enhance our strategic plans for the future.” As part of his new responsibilities, Wieck is expected to contribute to BigSQL, a new PostgreSQL Distribution that OpenSCG is sponsoring.

“I was thrilled to join OpenSCG,” said Wieck. “I have known and respected the management team for over a decade, and was looking for a balance between new product development and working closely with customers on data-driven business problems. My new role offers the chance to do both.”

About OpenSCG

Open Source Consulting Group, Inc. (OpenSCG) is a leading provider of subscriptions and services for PostgreSQL, the world’s most advanced open source database. The OpenSCG PostgreSQL team is one of the largest and most experienced in the world, and the management team is respected and trusted across the global Postgres community. OpenSCG offers PostgreSQL consulting and migration services, as well as subscription services that provide 24×7 global support and remote DBA Services.

 

Win-task-manager

Building PostgreSQL on Windows for Performance

Before PostgreSQL 9.1, adding additional features like data types was non-standard and awkward, but 9.1 brought extensions. By adding extensions as well as the plumbing for things like foreign data wrappers, the use of extensions have exploded over the last few years. This is great for people who are using PostgreSQL on Linux or similar type environments, but the people in Windows have been pretty much out of luck. Most people need to hope the extension they want to use is popular enough that someone skilled with Windows build environments decided to release the extension on Windows. It just was not as simple as Linux where you just run a “make install”.

For years, there has been the ability to replicate the Linux build experience on Windows with the Mingw project, but historically the resulting binaries have been significantly slower than the binaries produced by the Microsoft compiler. Since I last played with compliers on Windows, I have been pleasantly surprised that the open source build tools have caught up with the commercial tools and in some ways surpassed them.

To check the performance of the resulting binaries from the compilers, I tried a couple of CPU intensive tests. The first one was just a SELECT only pgbench test. The second was running the square roots of random numbers and then sorting the results. For the Windows binaries built with Microsoft Visual Studio, I used the PostgreSQL Windows installer from the PostgreSQL community site at http://www.postgresql.org/download/windows/. The Mingw-w64 build came from the bigsql.org site at http://www.bigsql.org/se/postgresql/packages.jsp

The version strings of the 2 binaries are below.

test=# select version();
 version
-------------------------------------------------------------
 PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 64-bit
(1 row)

test=# select version();
 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.1 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit
(1 row)

For these tests, the only tuning of the postgresql.conf file was to set shared_buffers to 2GB. All other settings were left at the defaults.

The SELECT only pgbench test was conducted with the following command:

pgbench -n -S -c 10 -t 100000 -U postgres test
MSVC 34246 TPS
GCC 34706 TPS

That is a minor 1.3% performance improvement. That is not a ground breaking gain, but the gcc built binaries were consistently faster.

The second test was a bit more CPU intensive running the following query:

select sqrt((random() * g)::numeric) from generate_series(1, 1000) g order by 1;

This was put in a text file and run through pgbench with the following command:

pgbench.exe -n -f test.sql -c 8 -t 10000 -U postgres test
MSVC 610 TPS
GCC 634 TPS

This resulted in a larger performance gain of 3.8%.

I started this testing with trying to find an easier way to build extensions on Windows with hopes of not taking too much of a performance hit, but ended up finding that the easier way was actually a littler faster. I am thinking its time to ditch those proprietary build tools and stick with open source.

tPostgres Features Sybase & SQL Server Compatibility

We are pleased to announce version 9.3.2 of tPostgres. Find out more at http://www.tpostgres.org

The transformation of the tPostgres: Sybase & SQL Server Compatibility Project project is now
complete. We are fully based on the PGTSQL open source PostgreSQL Extension and are fully woking on
the very latest and greatest point release of PG (9.3.2).

Additionally, we have done away with the bulky and awkward 32-bit linux virtual machine. We now have safe and simple to use native command line sandbox installers for Windows, OSX & Linux.

We’ve still got documentation work to do, but there are excellent regression test transact-sql programming examples in the PGTSQL code. Please take a look for yourself and consider contributing to the project at https://bitbucket.org/openscg/pgtsql

–Luss

mondrian

Hadoop and Pentaho Tutorial Video

In this Video sample web log data is ingested into Hadoop HDFS, processed with a number of pig jobs and analyzed with Pentaho.  All completed in lightning time using BigSQL.  Interested in getting Big Data results?  Try our Hadoop Jumpstart!

[youtube url=”http://www.youtube.com/watch?v=eSEe_33pImA&feature=youtu.be” autohide=”1″ rel=”0″ showsearch=”0″]

Cygwin64 is Quite Nice

It’s a pretty complete POSIX/Bash scripting environment for Windows with good OpenSSH Client/Server capabilities. If you must use Windows for a project, and your a command line Unix &/or Linux person. This really is a must have.

US Fast Food Workers Deserve a Living Wage

I’m not generally a huge fan of Unions, but… enough is enough for goodness sake. Reliable, loyal and productive fast food workers certainly deserve a reasonable living wage. I’m not 100% sure we want to move in one fell swoop from $8/hr minimum wage to $15/hr, but, the industry doesn’t seem capable of doing the right thing on its own (raise prices a little and pay workers fairly).

I know that the wage level needs to be increased, my only concern is that unions may come in a charge large fees to workers without raising their pay very much (like in the grocery industry). Perhaps it’s time to just raise the federal minimum wage by $1/hr each year for the next few years.