{"id":398,"date":"2015-03-05T13:34:58","date_gmt":"2015-03-05T13:34:58","guid":{"rendered":"https:\/\/solutionstreet.com\/blog\/?p=398"},"modified":"2015-03-05T13:34:58","modified_gmt":"2015-03-05T13:34:58","slug":"mysql-postgresql-migration","status":"publish","type":"post","link":"https:\/\/www.solutionstreet.com\/blog\/2015\/03\/05\/mysql-postgresql-migration\/","title":{"rendered":"MySQL to PostgreSQL Migration"},"content":{"rendered":"

\u00a0<\/strong><\/p>\n

By: Mike B.<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

<\/h1>\n

In a recent customer engagement we were tasked with migrating an application from MySQL to PostgreSQL. At first glance, this would appear to be a relatively simple and straightforward process. However, there are significant differences in how PostgreSQL and MySQL implement the ANSI-SQL standard which necessitated a review and refactoring of the data access objects, some interesting differences in the support of various data types, and a couple of production configuration issues we had not expected.<\/p>\n

\u00a0<\/strong><\/strong><\/p>\n

What follows is a brief outline of our approach, a few techniques and tools that proved useful in transitioning the application to a PostgreSQL backend, and a list of common sql query features we addressed. The project used python so several of the utilities mentioned in the outline are python-related.<\/p>\n

 <\/p>\n

 <\/p>\n

Installing & Using PostgreSQL<\/h1>\n

As with all modern db systems, there are on-line instructions and tools that make working with Postgres and MySQL easy. There are many GUI<\/a> tools available for working with PostgreSQL. Our team works primarily with Linux systems and we opted to use pgAdmin3<\/a>. In our work, the pgAdmin3 GUI did have a number of limitations and most of us chose to work primarily with the command line utility, psql.<\/p>\n

\u00a0<\/strong><\/strong><\/p>\n

PSQL is a terminal-based front-end to PostgreSQL that provides an interactive session as well as support for using sql\/data files. It provides a number of meta-commands (very linux like) and features that support a variety of tasks. For example in MySQL you use ‘show databases’ or ‘show tables’ to see lists of database objects. The corresponding commands in psql are ‘\\l’ and ‘\\d’. \u00a0PostgreSQL provides good in-line documentation for the commands and there are many helpful on-line resources, e.g., PSQL Cheat Sheet<\/a>.<\/p>\n

 <\/p>\n

 <\/p>\n

PostgreSQL Roles<\/b><\/h3>\n

PostgreSQL manages data access using roles and it is critical that they are properly defined in order to protect the data and data access. Roles that have log-in rights are called users.<\/p>\n

Roles can also be a group of users, or a group of roles, depending on how they are constructed.<\/p>\n

\u00a0<\/strong><\/strong><\/p>\n

By default, all roles have create and usage rights on the public schema. That is, all roles that can connect to a PostgreSQL database have the ability to create objects in the \u2018public\u2019 schema. And moreover, these privileges are also extended to any roles that might be created later. \u00a0To reiterate, any particular role will have the sum of privileges granted directly to it, privileges granted to any role (i.e., group) it is presently a member of, and those privileges explicitly granted to public.<\/p>\n

\u00a0<\/strong><\/strong><\/p>\n

In order to properly limit access to data objects in the database schema, the following script was used to create the appropriate access rights for developer use on local systems and on integration servers.<\/p>\n

 <\/p>\n

Create your userCREATE USER <user> WITH PASSWORD '<user_passwd>\u2019;\nCreate a new databaseCREATE DATABASE my_pg_database\n\nWITH OWNER = testdba\n\nENCODING = 'UTF8'\n\nTABLESPACE = pg_default\n\nLC_COLLATE = 'en_US.UTF-8'\n\nLC_CTYPE = 'en_US.UTF-8'\n\nCONNECTION LIMIT = -1;\nLimit access\nREVOKE ALL ON SCHEMA PUBLIC FROM PUBLIC;\n\nREVOKE ALL ON ALL TABLES IN SCHEMA PUBLIC FROM PUBLIC;\n\nREVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA PUBLIC FROM PUBLIC;\n\nREVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA PUBLIC FROM PUBLIC;\n\nREVOKE CREATE ON SCHEMA PUBLIC FROM PUBLIC;\n\nREVOKE CREATE ON SCHEMA PUBLIC FROM <user>;\nGRANT USAGE ON SCHEMA PUBLIC TO <user>;\n\nGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA PUBLIC TO \u00a0<user>;\n\nGRANT USAGE, \u00a0SELECT ON ALL SEQUENCES IN SCHEMA PUBLIC TO \u00a0<user>;\n\nGRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA PUBLIC TO \u00a0<user>;\nALTER DEFAULT PRIVILEGES IN SCHEMA PUBLIC GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO \u00a0<user>;\n\nALTER DEFAULT PRIVILEGES IN SCHEMA PUBLIC GRANT USAGE, SELECT ON SEQUENCES TO \u00a0<user>;\n\nALTER DEFAULT PRIVILEGES IN SCHEMA PUBLIC GRANT EXECUTE ON FUNCTIONS TO \u00a0<user>;\nREVOKE CONNECT ON DATABASE <database> FROM PUBLIC;\n\nGRANT CONNECT ON DATABASE <database> TO \u00a0<user>;\n<\/pre>\n
 <\/p>\n
Migration Process<\/h1>\n
While the basic plan for migration is simple:<\/p>\n
\u00a0<\/strong><\/strong><\/p>\n