A MySQL DBA looks at PostgreSQL part2: MySQL To PostgreSQL

So I recently posted: A MySQL DBA looks at PostgreSQL

This post will explore the migration from MySQL to PostgreSQL. I will soon follow it up with a PostgreSQL migration back to MySQL. The longterm goal with these posts is to show how the data works within the different databases as well as how to solve similar issues in each database when it should arise.

For the migrations I will use the often used example: World database available on dev.mysql.com.

I will also acknowledge this, I am more experienced with MySQL than PostgreSQL. PostgreSQL  DBAs might write and recommend different solutions to such situations. This is also a very simple example.

First to ensure this process is from start to finish:


mysql> create database world;
Query OK, 1 row affected (0.00 sec

# mysql world < world_innodb.sql
mysql> show create table  City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB

mysql> select count(ID) from City\G
*************************** 1. row ***************************
count(ID): 4079


So now let me get the PostgreSQL database set up and ready.


# su postgres
$ psql
psql (9.3.2)
Type "help" for help.

postgres=# CREATE DATABASE world;
CREATE DATABASE

# GRANT ALL ON DATABASE world TO testuser;
GRANT





postgres=# \q


This simple perl script (mysql2pgsql.perl) helps the migration process from MySQL to PostgreSQL.


# su testuser
$ cd
$ pwd
/home/testuser
$ wget http://pgfoundry.org/frs/download.php/1535/mysql2pgsql.perl


Gather the MySQL data and get it ready.


 mysqldump  -u root -p world > mysql2postgresql.sql
$ ./mysql2pgsql.perl mysql2postgresql.sql mysql2postgresql.pgsql
table "city" will be dropped CASCADE
"city_id_seq"--
table "country" will be dropped CASCADE
table "countrylanguage" will be dropped CASCADE

$ psql world < mysql2postgresql.pgsql | more
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
CREATE TABLE
INSERT 0 1


..

INSERT 0 1

NOTICE:  drop cascades to 2 other objects

DETAIL:  drop cascades to constraint countrylanguage_countrycode_fkey on table countrylanguage

drop cascades to constraint city_countrycode_fkey on table city

..

INSERT 0 1

INSERT 0 1

DROP TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

..

INSERT 0 1

CREATE INDEX

ALTER TABLE

So let us see what we have.


$ psql -d world
psql (9.3.2)
Type "help" for help.

world=> \dt
              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | city            | table | testuser
 public | country         | table | testuser
 public | countrylanguage | table | testuser
(3 rows)





world=> select count(ID) from City;

 count 

-------

  4079

(1 row)

world=> select * from City limit 10;

 id |                name                 | countrycode |       district       | population 

----+-------------------------------------+-------------+----------------------+------------

  1 | Kabul                               | AFG         | Kabol                |    1780000

  2 | Qandahar                            | AFG         | Qandahar             |     237500

  3 | Herat                               | AFG         | Herat                |     186800

  4 | Mazar-e-Sharif                      | AFG         | Balkh                |     127800

  5 | Amsterdam                           | NLD         | Noord-Holland        |     731200

  6 | Rotterdam                           | NLD         | Zuid-Holland         |     593321

  7 | Haag                                | NLD         | Zuid-Holland         |     440900

  8 | Utrecht                             | NLD         | Utrecht              |     234323

  9 | Eindhoven                           | NLD         | Noord-Brabant        |     201843

 10 | Tilburg                             | NLD         | Noord-Brabant        |     193238

(10 rows)

world=> \dt+ City
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description
--------+------+-------+----------+--------+-------------
 public | city | table | testuser | 432 kB |
(1 row)

Well the counts match and data is available. But now I want to see the MySQL version of a "SHOW CREATE TABLE";  Keep in mind that in MySQL CREATE DATABASE and CREATE SCHEMA  are basically the same thing.


$ pg_dump -t city -s world
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: city; Type: TABLE; Schema: public; Owner: testuser; Tablespace:
--

CREATE TABLE city (
    id integer DEFAULT nextval('city_id_seq'::regclass) NOT NULL,
    name character(35) DEFAULT ''::bpchar NOT NULL,
    countrycode character(3) DEFAULT ''::bpchar NOT NULL,
    district character(20) DEFAULT ''::bpchar NOT NULL,
    population integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.city OWNER TO testuser;

--
-- Name: city_pkey; Type: CONSTRAINT; Schema: public; Owner: testuser; Tablespace:
--

ALTER TABLE ONLY city
    ADD CONSTRAINT city_pkey PRIMARY KEY (id);


--
-- Name: city_countrycode_idx; Type: INDEX; Schema: public; Owner: testuser; Tablespace:
--

CREATE INDEX city_countrycode_idx ON city USING btree (countrycode);


--
-- PostgreSQL database dump complete
--


As you can see to see the table is the same as a mysqldump command
$ mysqldump -u root -p --no_data --database world --tables City
More work than a typical MySQLis used to having to do just to see the table structure.

But our data and schema is moved over into PostgreSQL from MySQL.

Another post soon to come... Moving it back. 

A MySQL DBA looks at PostgreSQL

So this is a journey of the/a MySQL DBA looking into PostgreSQL. It is not an attack just observations and examples. 

Using CentOS 6.5 64Bit:

rpm -ivh  http://yum.postgresql.org/9.3/redhat/rhel-6.5-x86_64/pgdg-centos93-9.3-1.noarch.rpm

yum groupinstall "PostgreSQL Database Server 9.3 PGDG"
---> Package postgresql93.x86_64 0:9.3.2-1PGDG.rhel6 will be installed
---> Package postgresql93-contrib.x86_64 0:9.3.2-1PGDG.rhel6 will be installed
---> Package postgresql93-libs.x86_64 0:9.3.2-1PGDG.rhel6 will be installed

---> Package postgresql93-server.x86_64 0:9.3.2-1PGDG.rhel6 will be installed

yum install postgresql93-server

service postgresql-9.3 initdb
Initializing database:                                     [  OK  ]
service postgresql-9.3 start
Starting postgresql-9.3 service:                           [  OK  ]
chkconfig postgresql-9.3 on


All of the following examples are based on the PostgreSQL Wiki 
# su - postgres
-bash-4.1$ psql

psql (9.3.2)

postgres=# CREATE USER testuser PASSWORD '1234';
CREATE ROLE
postgres=# GRANT ALL ON SCHEMA test TO testuser;
GRANT

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO testuser;
GRANT

postgres=# \q

-bash-4.1$ exit

logout

# su testuser

$ pwd
/home/testuser
$  psql -d postgres
psql (9.3.2)

Type "help" for help.

postgres=> CREATE TABLE test.test (coltest varchar(20));

CREATE TABLE

postgres=> insert into test.test (coltest) values ('It works!');

INSERT 0 1

postgres=> SELECT * from test.test;

  coltest  

-----------

 It works!

(1 row)

postgres=> DROP TABLE test.test;

DROP TABLE

postgres=> 

I did notice that these grant statements worked well with 9.3 but the distro originally installed an 8.* version and some of the commands failed. 

Btw...You do of course still have features you would expect from a stable RDBM system. Granted these are simple examples.  

postgres=> EXPLAIN SELECT * from test.test;
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on test  (cost=0.00..19.20 rows=920 width=58)

Show databases == \l
postgres-> \l
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

USE is standard SQL: 
postgres-> use postgres  

List all schemas:

postgres-> \dn
 public | postgres

 test   | postgres

Show create table and etc are a little more work. Granted a MySQL DBA will see it as more work because of what we are used to doing. PostgreSQL uses tablespaces more than MySQL users are used to doing. 

postgres=> CREATE TABLE sometable (somefield varchar(255), anotherfield varchar(150), datefield date );
CREATE TABLE
postgres=> INSERT INTO sometable (somefield, anotherfield, datefield) VALUES ('fubar', 'rabuf', '2013-12-30');
INSERT 0 1
postgres=> INSERT INTO sometable (somefield, anotherfield, datefield) VALUES ('fubar', 'rabuf', NOW());
INSERT 0 1
postgres=> INSERT INTO sometable (somefield, anotherfield, datefield) VALUES ('fubar2', 'rabuf2', NOW());
INSERT 0 1
postgres=> select * from sometable;
 somefield | anotherfield | datefield  
-----------+--------------+------------
 fubar     | rabuf        | 2013-12-30
 fubar     | rabuf        | 2013-12-30
 fubar2    | rabuf2       | 2013-12-30
(3 rows)

postgres=> select * from sometable WHERE somefield='fubar'; somefield | anotherfield | datefield -----------+--------------+------------ fubar | rabuf | 2013-12-30 fubar | rabuf | 2013-12-30 (2 rows)

postgres=> \? <-- The help will show you how to navigate around.

Show tables == \dt

postgres=> \dt

           List of relations

 Schema |   Name    | Type  |  Owner   

--------+-----------+-------+----------

 public | sometable | table | testuser

postgres=> ALTER TABLE sometable ADD COLUMN intfield int[11]; ALTER TABLE

postgres=> select * from sometable; somefield | anotherfield | datefield | intfield -----------+--------------+------------+---------- fubar | rabuf | 2013-12-30 | fubar | rabuf | 2013-12-30 | fubar2 | rabuf2 | 2013-12-30 | (3 rows)

Show create table == \d+ tablename

postgres=> \d+ sometable

                                 Table "public.sometable"

    Column    |          Type          | Modifiers | Storage  | Stats target | Description 

--------------+------------------------+-----------+----------+--------------+-------------

 somefield    | character varying(255) |           | extended |              | 

 anotherfield | character varying(150) |           | extended |              | 

 datefield    | date                   |           | plain    |              | 

 intfield     | integer[]              |           | extended |              | 

Has OIDs: no

To help more you can install pgadmin 
yum install pgadmin3_93

Getting that to work and how it works could be another entire blog post. 

So far.... I will stick with MySQL but this is just a simplistic example of using PostgreSQL. This can get you started to you can evaluate for yourself. 

Helpful links below. Please refer to these as they have more experience with PostgreSQL than I do. 

• http://people.planetpostgresql.org/devrim/index.php?/archives/70-How-to-install-PostgreSQL-9.2-on-RHELCentOSScientific-Linux-5-and-6.html ( Modified for 9.3 below)
• http://yum.postgresql.org/9.3/redhat/rhel-6.5-x86_64/
• http://www.postgresql.org/docs/9.3/static/app-initdb.html
• https://wiki.postgresql.org/wiki/Detailed_installation_guides
• http://wiki.postgresql.org/wiki/First_steps
• http://www.postgresql.org/docs/9.3/static/sql-grant.html
• http://wiki.postgresql.org/wiki/How_to_make_a_proper_migration_from_MySQL_to_PostgreSQL
• http://blog.endpoint.com/2009/12/mysql-and-postgres-command-equivalents.html
• http://www.openlogic.com/wazi/bid/188016/PostgreSQL-Administration-for-MySQL-Admins
• http://www.postgresql.org/docs/9.3/static/sql-altertable.html
• http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL

ERROR 1356 (HY000)

The most important thing to understand when coming upon an error is to take a moment and relax. 
Sometimes people run across an error and become flustered and frustrated.  Take a deep breath and relax. Sometimes "Madness is like gravity. All that needs is a little push" and “Sometimes the questions are complicated and the answers are simple.”

What is all this about? Well for example I recently was asked to help solve a problem that was troubling another DBA. Nothing meant in disrespect at all to that DBA but being in a rush people just overlook the answer that is in front of them and instead spiral down into madness. 

Here is the general concept of the situation. 

After doing a mysqldump of a database the user moved it to another database for others to being testing. The import worked just fine. However, the users began to see errors when they tried to query from a prebuilt view. 

mysql> select * from <View_name> limit 1; 

ERROR 1356 (HY000): View '<DB_NAME>.<ViewName>' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 

The first reaction was to assume that the something went wrong with the import. Did the view or actually the related tables become corrupt? 

No. 

To solve this error the first thing todo was to:    mysql> show create view <View_Name> \G 

You likely will see something that begins like this:
*************************** 1. row *************************** 
View: Visitors 

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`<Some_user>`@`<Some_host_or_IP>` SQL SECURITY DEFINER VIEW

After you see who  the definer is : `<Some_user>`@`<Some_host_or_IP>`   you can view the grants for that user. 

mysql> show grants for `<Some_user>`@`<Some_host_or_IP>`; 

It turns out the the solution was just what the error said in the first place:  "definer/invoker of view lack rights to use them"

To make it easy you can do a show grants in the database where it already works and copy the grant statement, or you can review what type of permissions you need to implement in the new database to enable the view to gather the required data. 

A simple update of permissions for the user and the error was gone.  

“Sometimes the answers are simple.”

A strategy from the community

We have seen the news about MariaDB replacing MySQL in Fedora , SUSE and Red Hat.

While Oracle would not be happy about such news, the open source community supports the focus on a "more" open source solution to be implemented into Linux.

The interesting thing that we all might over look is that, the decision or strategy to move to MariaDB from MySQL was likely not just made by the upper management in Red Hat. This is much more likely to be a movement from the open source community that Red Hat evaluated and they listened.

Consider this, take a look back at Jackie Yeaney's (@jackieyeaney) post about the "Democratizing the Corporate Strategy Process at Red Hat" (posted Nov 10th, 2011) and learn how Red Hat works. "We utilized existing networks in the open source community to "keep our minds open" and socialize ideas outside of Red Hat."  The community wanted openness and a result was a move to MariaDB by Red Hat, is is related to the open strategy at Red Hat, in my humble opinion it is likely yes.

 Jim Whitehurst (@JWhitehurst) appears to embrace the open source community not just because of the financial gains it rewards the company but also because how it revolutionized how they work, make strategic decisions and take input from others: "Given enough eyeballs, all bugs are shallow." 

Take a moment to relate that last statement to MySQL.  If you follow MySQL then you are well aware that Oracle closed (or have a less open version now) the bugs.mysql.com site. While Oracle has their own corporate reasoning for that, the open source community follows "Given enough eyeballs, all bugs are shallow."  

As MariaDB grows and become more imbedded into the Linux distros as the default DB the related bug tracking will be open and it will be interesting to see how the bugs split across Maria and MySQL in the long run.

So now.. the interesting aspect is while MySQL had enterprise and community versions owned by Oracle the move still happened because of MariaDB.  Oracle also owns Java and the related OpenJDK. While I am just asking this as an outsider looking in... If a Java/OpenJDK  was branched out into another software package that was not owned by Oracle, would we soon see an replacement for Java/OpenJDK in Red Hat as well ?  Again I am an outsider to the Java world, so I just ask that because of the similarities it represents with MySQL.

 

MariaDB & the Linux Distributions

So by now many of you have seen the news about Google, SUSE and  Red Hat / Fedora moving to MariaDB as the default database instead of MySQL.

MariaDB and SkySQL have made very productive business moves this year. What does this really mean for MySQL community and the general open source community ?

For starters think back to what made MySQL so popular? It was readily available in all the top Linux distributions.

OpenSuse and Fedora already are moving to MariaDB so the push for an open source focused movement has begun. Following a RHEL migration that has MariaDB as the default database would then also mean that CentOS would soon have MariaDB as the default database. 

Likely soon to follow will be a move by Ubuntu and Debian. I could have missed the news on a move already but I don't think so.

All of this is big news and moves for MariaDB and the open source community. MariaDB will naturally begin to see more user acceptance and use. While Oracle's MySQL is still an open source software package the big issue has been the bugs.mysql.com site and tracking of mysql user bugs. Will people soon start to track more bugs at MariaDB?

MariaDB also has open source features that mimic the enterprise only solutions available in Oracle's MySQL. So many users are going to naturally pick up on those features. 

While Oracle is building great features and code, but how many in the community are taking it? MySQL 5.1 is heavily used in the community used Linux distributions and many users might only know MySQL 5.1 and so soon MariaDB 5.5.   

What will happen next?
Well what Oracle decides to do is still yet to be seen. Oracle already takes Red Hat Linux to build their Oracle Linux (OEL). So ironically now MariaDB, which is forked from Oracle's MySQL, will be in Red Hat Linux for Oracle to remove for their OEL. Will Oracle fight back with Java in some way?

What will Percona do? Percona is also a player in this and has had a respectful relationship with MariaDB and Oracle over the years. Naturally, Percona does lean towards the open-source side of things so it will be curious to see if any move occurs on Percona's part.  Will more tools focused on MariaDB features becoming soon?

What will MariaDB do?  Well, monitoring the growth rate of bugs in the MariaDB Bugs database will help show how many people are starting to take on MariaDB.
Will Maria at some point break away from the MySQL source code updates and continue builds with just the community and their engineers? 

So what happens next?  We do not know. It is up to the open source community. A lot of angry voices had been directed at Oracle over the years. So how do those voices change or show support to MariaDB will soon to be seen. While many of them likely already support MariaDB, it is the adoption of the masses that will show how well MariaDB does in regards to MySQL. Many companies know the name MySQL and will be hesitant to move to MariaDB (This happened to me just the other day.) . So MariaDB's work is not yet done.

• http://www.zdnet.com/red-hat-will-switch-from-oracle-mysql-to-mariadb-reports-7000017907/
• https://www.openshift.com/blogs/why-mariadb-matters-the-openshift-interview-with-monty
• http://www.bytebot.net/blog/archives/2013/07/11/some-mariadb-related-news-from-the-red-hat-front
• http://www.zdnet.com/google-quietly-dumps-oracle-mysql-for-mariadb-7000020670/
• http://www.zdnet.com/opensuse-also-considers-switching-from-mysql-to-mariadb-7000010223/
• https://lists.launchpad.net/maria-discuss/msg00514.html
• https://mariadb.com/kb/en/reporting-bugs/

And yet after all of this... Oracle,  Red Hat and Google still come together to help the us government.