Moving to PostgreSQL's Object-Relational DBMS
As more companies dive into open-source business systems, many are building web-to-back-end stacks that typically include Linux, PHP, Apache web server and an open-source database, usually either MySQL or PostgreSQL. PostgreSQL is gaining favor of late in many quarters, having reached, some say, a critical mass of functionality and stability. Several high-profile companies now provide 24/7 support, including Red Hat. This article shows what to expect when switching from Microsoft Access to an open-source database (in this case, BSD-style licensed PostgreSQL).
Michael Calabrese, manager of information systems for Bike Friday, recently undertook the challenge. Bike Friday is a rapidly expanding touring and mountain bike company based in Eugene, Oregon. It uses PostgreSQL to handle all of its sales, manufacturing and customer support data. Calabrese is also in the midst of changing all of the company's e-commerce systems from proprietary to open-source—Linux and Apache, with PostgreSQL at the core. For now, however, he has retained Microsoft Access 97 as the front end in order to minimize downtime while replacing the back end with PostgreSQL and adding new features. Calabrese says:
If you're not dealing with preserving an existing front end, life is easy. Just run the conversion scripts [detailed below] and start writing a new front end. If you have an Access front end that you can continue to use with a PostgreSQL back end, you've provided clear pathways for things to grow, without trying to convert the whole system at once. In the first scenario, you'd be looking at a year for the conversion after freezing the whole front end. Attacking the change incrementally allows you to start designing new things with the choice of whether to do it in Access or PostgreSQL.
Calabrese decided to move to PostgreSQL because it is the more enterprise-capable system. It has a mature transaction-management system with a sophisticated data-locking mechanism called multiversion concurrency control (MVCC), which allows read-only access to data even if it's in use.
Loading the Microsoft open database connectivity (ODBC) drivers onto the PostgreSQL template database lets Access and PostgreSQL set up house together. Besides basic conversion tools (see Resources), additional ODBC server-side functions that Access sometimes needs to run psql <database name> <odbc.sql> are located in the directory src/interfaces/odbc/odbc.sql. PostgreSQL also provides a platform-independent Type 4 Java database connectivity interface (JDBC) driver. An embedded interface for C (ECPG) is also part of PostgreSQL. Once the installation was finished, Calabrese chose data migration tools like pgAccess, available in Windows and UNIX versions, and exSQL public version 3.1.
After backing up existing databases using the included tools (either the pg_dumpall utility or a combination of the pg_dump and pg_dumpaccounts utilities) and running the Installer, the first step in the data conversion is to hunt down illegal file names in Access. Access is quite liberal in its allowance of illegal characters that other databases—Oracle, Sybase and PostgreSQL included—will not understand. Therefore, scores of illegal terms for Bike Friday's shipping and ordering data that Access thought were fine had to be converted for PostgreSQL. For example, tables like Order Detail needed to become Order_Detail or OrderDetail, and field names such as Shipped? had to become Shipped or ShippedYN.
The basic conversion tools will remove all illegal characters automatically. This can be problematic for those working with an existing front end, because the front and back ends can cease communicating without an apparent reason. Calabrese recommends that anyone planning to preserve an existing front end should not change the names containing illegal characters in the front-end data or, alternatively, make parallel changes manually. In his situation, Calabrese found himself manually changing characters one by one on Bike Friday's front and back ends, which was okay since he was going to have to change the front end anyway. Either way, it's at this point that one should perform the first of many tests to be sure everything works. With illegal character issues resolved, the data is ready for conversion.
Practical Task Scheduling Deployment
July 20, 2016 12:00 pm CDT
One of the best things about the UNIX environment (aside from being stable and efficient) is the vast array of software tools available to help you do your job. Traditionally, a UNIX tool does only one thing, but does that one thing very well. For example, grep is very easy to use and can search vast amounts of data quickly. The find tool can find a particular file or files based on all kinds of criteria. It's pretty easy to string these tools together to build even more powerful tools, such as a tool that finds all of the .log files in the /home directory and searches each one for a particular entry. This erector-set mentality allows UNIX system administrators to seem to always have the right tool for the job.
Cron traditionally has been considered another such a tool for job scheduling, but is it enough? This webinar considers that very question. The first part builds on a previous Geek Guide, Beyond Cron, and briefly describes how to know when it might be time to consider upgrading your job scheduling infrastructure. The second part presents an actual planning and implementation framework.
Join Linux Journal's Mike Diehl and Pat Cameron of Help Systems.
Free to Linux Journal readers.Register Now!
- SUSE LLC's SUSE Manager
- My +1 Sword of Productivity
- Murat Yener and Onur Dundar's Expert Android Studio (Wrox)
- Managing Linux Using Puppet
- Non-Linux FOSS: Caffeine!
- SuperTuxKart 0.9.2 Released
- Doing for User Space What We Did for Kernel Space
- Parsing an RSS News Feed with a Bash Script
- Google's SwiftShader Released
- Rogue Wave Software's Zend Server
With all the industry talk about the benefits of Linux on Power and all the performance advantages offered by its open architecture, you may be considering a move in that direction. If you are thinking about analytics, big data and cloud computing, you would be right to evaluate Power. The idea of using commodity x86 hardware and replacing it every three years is an outdated cost model. It doesn’t consider the total cost of ownership, and it doesn’t consider the advantage of real processing power, high-availability and multithreading like a demon.
This ebook takes a look at some of the practical applications of the Linux on Power platform and ways you might bring all the performance power of this open architecture to bear for your organization. There are no smoke and mirrors here—just hard, cold, empirical evidence provided by independent sources. I also consider some innovative ways Linux on Power will be used in the future.Get the Guide