Converting MS Access to Linux (Part One)

A recent question here on LQ reminded me that a real frustration for many people who would like to convert from Windows to Linux is the lack of a satisfactory replacement for MS Access. I can't provide that replacement, but, if a person is willing to work a little bit, I think I can demonstrate how to get a reasonable facsimile. My goal is to explain step-by-step to people who are not professional DBAs how to convert a simple MS Access database to run in linux using POSTGRESQL and ReKall.

There is no magic bullet that I have seen, and I think this process will prove satisfactory to people willing to follow it through. I am actually documenting the process as I do it myself with my MP3 collection, currently managed in an MS Access database. I really think the hardest step for Access users is getting a database application working on Linux. That part is covered here. Part 2 will cover setting up ReKall and getting the data moved to a ReKall database. Feel free to move ahead on your own if I'm too slow. My distro is Debian, sorry about yours if it's not. The guide is somewhat Debian specific, but I don't think that should slow you down too much.

I will eventually move the completed document to an HTML file to facilitate screen prints. I don't think I could post that here anyway, and I am most interested, at this point, in getting feedback about needed clarifications (as well as commentary regarding the need for such a document).

***********************

You need root access, to do some of the setup, but the end user will not need any additional priviledges. In following the setup instructions, pay attention to the #'s and $'s preceding the commands.

This HOWTO is not about teaching you to efficiently use or maintain either ReKall or POSTGRESQL, just to get them up and functional enough to get data from an MS Access database. Once that's accomplished we (you) will evaluate how much more, and specifically what, you need to learn. I chose POSTGRESQL because its SQL language is very similar to Oracle's, as is AccessSQL. This should simplify some SQL specific issues as we go along. ReKall is a KDE application, so you'll need large portions of it installed even if (like me) you use Gnome.

Get the software.
Installation packages for both ReKall and POSTGRESQL are available from the Debian repos. (At least 'testing' has them. That's where I got mine.)

# apt-get install rekall
# apt-get install postgresql

Getting Postgresql Working
The default Debian installation of POSTGRESQL does not set up links to libraries so the 'initdb' command (coming up shortly) needs to be run using the complete path. Once you have it installed there will be a POSTGRESQL server running and a POSTGRESQL user named 'postgres' on your system. By Debian's default installation, POSTGRESQL will always be running from now on. It will come up automatically when you boot. For purposes of this project, we are going to assume you'll leave it that way.

You need to establish yourself as another POSTGRESQL user with the authority to create new databases, and you need to define a place where the data will be stored. The POSTGRESQL documentation recommends setting up the data storage area in /usr/local/pgsql/data. That way if you have several users they will all have access to that area.

Our example will assume only one user, joenormal, who wants the data in his home directory. (Understand that he already exists as a Linux user, and he will be added as a POSTGRESQL user.) Setting it up this way means that you would have to run 'initdb' once for each user who plans to develop and maintain databases. All right. Ready. Log in as root.

debian:~# su postgres

postgres@debian:/root$ createuser joenormal
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

postgres@debian:/root$ exit
exit

debian:~# exit
logout

joenormal@debian:~$ psql
Welcome to psql 7.4.9, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

joenormal=> \q

joenormal@debian:~$


There are a few indentations and carriage returns in there that you won't see on the screen ... artistic license. What we have done is is define 'joenormal' as a POSTGRESQL user, and tested to make sure he was recognized as such. So far, so good. Now we'll make a place to store the data.

joenormal@debian:~$ mkdir ./pgsql/

joenormal@debian:~$ mkdir ./pgsql/data

joenormal@debian:~$ su -

debian:~# chown postgres /home/joenormal/pgsql/data/

debian:~# su postgres

postgres@debian:/root$ /usr/lib/postgresql/7.4/bin/initdb -D /home/joenormal/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale ...
.
. (whole buncha other stuff here)
.
creating information schema... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:

/usr/lib/postgresql/7.4/bin/postmaster -D /home/joenormal/pgsql/data
or
/usr/lib/postgresql/7.4/bin/pg_ctl -D /home/joenormal/pgsql/data -l logfile start

postgres@debian:/root$ exit
exit

debian:~#
logout

joenormal@debian:~$

That's it. We are now ready for ReKall. Don't bother trying to use the start-up commands at the end since POSTGRESQL is already running.

Excellent post. Shouldn't this be a HOWTO? I hope that once the posting is completely finished this way, it will be copied to the other section.

One slight comment: for many databases it's a good idea to designate one user who "builds databases" and has the authority to construct and modify them; versus those who simply may "use" them. On my systems, the user-id that I use on a daily basis is non-privileged, and a separate non-root user is designated for system maintenance.

Here is Part Two.

nice, please submit as a tutorial( see link above) thanks