Moving from mysql to postgres

Featured

Saving session data in a database.

Mystery of the dead speaker.

Directory browsing with PHP

By now you probably have the Rad User Manager up and running so let's convert it's schema to postgres using our online converter. All you have to do is copy and paste the SQL from the database.sql file into the form and the postgresql equivalent will be generated for you. Go on try it now. (don't forget to come back here when you are done).

I hope you remembered to save your the generated sql, we are going to need it, but first ask your administrator to create a postgresql database for you. If he looks at you funny, use the term catalog instead of database. Ask him to generate a username and password for you while he is at it. If you have been burdened with the task of looking after your postgres installation now is the time to take a peek at the administration guide of the manual.

Right, now it's time to create the set of tables. The command that is given below will do it for you. Take care to replace localhost with the name or IP of your server, pguser is the username that we are using to connect to the server while usermanager is the name that has been assigned to our database. The file database.pg.sql has to be replaced with the name you have chosen for the sql generated by the online converter.

psql -h localhost usermanager pguser database.pg.sql

Don't be put off if all you get is an error message. That is because the sql file has a call to the password() function, a function that is not available in postgres. Using the password() function to encrypt the password was a bad idea but what's done is done. In the process of conversion we will change all these calls to md5() instead; a function that is available on postgres.

INSERT INTO users VALUES (1, 2, 'admin', password('radmin'));
INSERT INTO users VALUES (2, 1, 'user', password('radmin'));

Should now become

INSERT INTO users VALUES (1, 2, 'admin', md5('radmin'));
INSERT INTO users VALUES (2, 1, 'user', md5('radmin'));

You can now continue with the table creation, but clean out the tables that have been created in the aborted attempt first or you will run into another error. When you are done let's move on to the next step

 

  Part 1:   Getting Started   ,   The Schema   ,   Queries
  Part 2:   Passwords   ,   Times Up   ,   Errors   ,   Download