All the Perl that's Practical to Extract and Report

Moving from mysql to postgres

TeeJay on 2006-03-02T16:05:25

I haven't used postgres for several years, and in that time I've clearly managed to forget it's huge number of annoyances and quirks.

I think that using MySQL, and either mysql-query-browser or Tora has spoiled me a bit, as using Postgres 7.4 (as shipped with debian stable, ubuntu, fedora, etc) is like stepping back 10 years.

Setting up is arcane, admittedly the packagers for the linux distro's are partly to blame for not making it easy - but, crikey, why the heck do I need to create databases before I can even log in, and I have to su to postgres to do so as there is no standard user, and I have to muck about with several configuration files just to bloody access it on localhost

The syntax is archaic and inconsistent and the manuals are lacked worked examples - the mysql docs are much clearer and nicer

MySQL and SQL Server have left me expecting things to JustWork and not require hours of tinkering before being able to run queries.

Yes, I'm sure it gets easier once you've done it for a while, but porting from mysql, which I am familiar with - and never had these troubles with to postgres sucks eggs through a straw

update: attempting to allow a webapplication user multiple permissions to all tables, sequences, etc in a single database doesn't seem to be possible - in MySQL I just grant [list of privs] on dbname.* to user, etc - in postgres I have to list the dozens of tables and associated sequences - this is incredibly tedious. Hateful thing.

agreed, setup is a pain

perrin on 2006-03-02T18:58:51

I frequently have this conversation with Pg boosters who just won't recognize how annoying the initial setup for Pg is vs. the setup for MySQL. It's a fine database, and the auth system is powerful, but it's pretty obvious why newbies find MySQL easier to get going with.

Re:agreed, setup is a pain

Phred on 2006-03-02T19:15:53

I agree that installation is one of PG's weak areas, and I think part of the reason here is the way it's packaged on many distros. On Gentoo Linux, I can run 'emerge postgresql', followed by 'ebuild postgresql-8.1.3.ebuild config' and it takes care of creating a postgres user, creating a postgres database, and initializing the database. Doing this process manually (which is the case for many distributions) is tedious and probably turns off some users. If you have a reasonable sized database you'll also need to set the kernel shared memory max (kernel.shmmax).

Mysql is admittedly easier to get going with. On most rpm based distributions I just type 'rpm -ivh mysql...rpm', then 'mysql' and I'm off to the races.

I've had pretty good luck with the PG documentation though, and whenever I can't find something there I pop onto #postgresql on irc.freenode.net and I can get questions answered right away. Maybe that's because I'm more familiar with it though.

Re:agreed, setup is a pain

Dom2 on 2006-03-02T19:20:55

I've heard a lot of people say that Pg is hard to set up, and I just don't get it. I've used the Debian package install and the RPM package on RedHat and in both cases, it was just a question of "start server, create user, create database". Piece of cake.

-Dom

Re:agreed, setup is a pain

perrin on 2006-03-02T19:36:57

The details of "create user, create database" are more complex than they are for MySQL. As Phred pointed out, he has a script to do it on Gentoo. The last Red Hat RPM of it that I tried required me to dig up some RPM-specific docs and edit a text file.

Wow, the venom!

Dom2 on 2006-03-02T19:18:57

Your experiences I find astonishing. As a PostgreSQL user going back some 5 years, and recently asked to port my company's application to MySQL, I find MySQL to be unbelievably opaque. Funnily, I could mirror your complaints almost exactly:
  • Why is MySQL so hard to set up? Admittedly, I'm compiling from source in our environment, but PostgreSQL just compiles and installs. MySQL takes 3 times as long to compile, and installs a load of crap like tests that I'm not interested in, even when using the client only flag.
  • Why the heck am I allowed to connect without being attached to a database? What on earth purpose does it serve? It's just confusing.
  • Why is the SQL syntax so inconsistent? Why can't I have two timestamp columns with a default? Why the heck should I care about setting the storage engine? To enabled something so fundamental as transactions, fer chrissake!
  • Why is the command line interface so appalingly bad? The help is non-existent on every instance I've connected to. psql at least gives you \h.
  • Why is the documentation for MySQL so bad? It's full of random gibberish about previous versions that I don't use and don't care about. It rarely seems to get to the point. In contrast with the PostgreSQL documentation, which I find clear and to the point.

In summary, "Wah -- it's different to my comfy usualness!" from both of us.

Anyway, to address some of your points:

  1. You can log in from localhost by default. Just over a Unix socket, not tcp/ip. It's exactly the same as using /tmp/mysql.sock. Just leave the hostname off whatever connect string you're using and it will connect to localhost.
  2. You don't have to su to postgres. Or rather, you only have to do it once: sudo -u postgres createuser -ad $LOGNAME. That'll give you your own superuser account and you won't have to use su at all.
  3. The documentation does have examples -- for example, see CREATE TABLE AS.
  4. If you fancy a nicer interface, it's worth checking out pgAdmin or phppgadmin.
  5. Yes, having to explicitly list all the permissions sucks, royally. I utterly agree with you on that point. :-)

Re:Wow, the venom!

TeeJay on 2006-03-02T20:01:42

MySQL is WAY easier to install - configure, make, make install - no creating and su'ing to a special user to create the databases.

"Why is the command line interface so appalingly bad? The help is non-existent on every instance I've connected to. psql at least gives you \h."

myql allows you to switch, and show databases, list and describe tables and best of all quit is quit and help is help. Table names and fields are tab completed. Postgresql is grumpy and unhelpful compared. The source command allows you to run sql scripts without exiting and piping. The SQL Prompt is far easier and more powerful than psql.

"Why the heck am I allowed to connect without being attached to a database? What on earth purpose does it serve? It's just confusing."

It's not confusing - it's common sense, logging into 1 database to create another database or using command line scripts is confusing, as is describing users or add users that have nothing to do with the database you logged into.

"Why is the documentation for MySQL so bad? It's full of random gibberish about previous versions that I don't use and don't care about. It rarely seems to get to the point. In contrast with the PostgreSQL documentation, which I find clear and to the point."

In an alternative universe perhaps - the Manuals for 4.x and 5.x are seperate, and it's not uncommon to have to deal with databases on multiple versions, you can also access the manuals specific to a version. There isn't any gibberish in there and the examples and syntax are clearer than postgres. Of course thats subjective but hey.

Postgres documentation worked examples are a bit sparse, on the whole I find MySQL's better again subjective.

pgAdmin and pgAccess failed to compile and don't come anywhere near SQL Server, MySQL or Toad/Tora.

On the whole its arcane and unpolished - the fact we have to use a heath-robinson contraption like slony for a core job of replication is pretty worrying, ditto the lack of a built in full text index, not to mention pgdump not working between versions.

I'm sure that I'll get used to it once I've been bitten a few dozen times, but after using MySQL, Oracle and SQL Server it is truly a step back in time.

Re:Wow, the venom!

Phred on 2006-03-02T20:33:52

You can solve the pg_dump issue by grabbing the pg_dump from any 8.1.x version. It's been refactored to address the issue of not working between different versions.

Slony is actually a robust replication mechanism for what it's worth. It doesn't handle schema updates all that well, but I don't think many replication systems do handle that well.

There is at least one full text indexing system in the contrib tree with the source.

Re:Wow, the venom!

Dom2 on 2006-03-02T20:37:32

"myql allows you to switch, and show databases, list and describe tables and best of all quit is quit and help is help."

Just like psql, apart from quit/help.

"Table names and fields are tab completed."

Maybe in your version. For me, psql completes, mysql doesn't. And it gets ^W wrong to add insult to injury.

"The source command allows you to run sql scripts without exiting and piping."

In psql it's spelled \i. Try \? for some help on the psql commands.

"The SQL Prompt is far easier and more powerful than psql."

No, they're pretty similar, but you're more used to one than the other.

"It's not confusing - it's common sense, logging into 1 database to create another database or using command line scripts is confusing"

You're missing the point here. It's confusing because it's not what you're used to. I find MySQL confusing for exactly the same reason.

As regards the manuals, I would like to point out this example: TIMESTAMP Properties as of MySQL 4.1. It's laughable -- why is the 5.0 manual concerned with 4.1? And why are the properties so baroque anyway?

I agree -- the PostgreSQL manuals do have sparser examples. But that wasn't your original claim. Personally, I find them sufficient.

I'm sorry that you didn't manage to compile the packages. So how do you know that they are inferior? I'm curious to know...

As to pg_dump, that works fine between versions. I believe that the recommendation is to use the newest available version of pg_dump. What problems did you have?

As to full text search not being built in; well it's in contrib, which is easily installed if you're using a package. It's a shame it's not installed by default though.

I personally dislike Slony as well. OTOH, I've rarely needed replication, so I don't have enough experience of that. Again, it would be nice to get something easy to use installed by default.

"Arcane and unpolished" I find particularly amusing, because that describes my impressions of MySQL almost perfectly! Doubtless I will get used to it over time, but being introduced to it for the first time is really jarring.

Re:Wow, the venom!

vek on 2006-03-03T00:35:44

he last time I looked at PostgreSQL, I liked what I saw but the lack of reliable out-of-the-box replication was a showstopper. I was quite surprised that PostgreSQL was lagging behind MySQL & Oracle in that area. PostgreSQL for me therefore is fine for simple apps on a single database host, but once you move into something a little more advanced with multiple redundant database hosts sitting behind a load balancer, replication is a must have feature.

I just had a quick peek at the online documentation for Pg 8.1 and didn't see anything related to replication as a core functon so it would appear PostgreSQL hasn't caught up with MySQL & Oracle in that arena yet. Admittedly, I don't follow PostgreSQL development so have no idea if replication is in the works for a future version.

Re:Wow, the venom!

TeeJay on 2006-03-03T13:20:22

I have used pgAdmin on other people's machines, it's slower, ugly and contains less features than the equivilent tools for Oracle (Toad/Tora), MySQL (mysql-query-browser, mysq-admin) and SQL Server. PGaccess runs but lacks most of the features I'd want.

Trying Squirrel, I found that it can't find the jdbc classes for anything and is slow, ugly and clunky - but thats Java's fault rather than Postgres :)

Re:Wow, the venom!

Dom2 on 2006-03-02T20:49:16

I forgot my favourite bit of MySQL irritation: DBD::mysql doesn't support UTF-8. How long is it since Perl 5.6 came out now?

-Dom

Granting permissions on all objects

nmueller on 2006-03-02T21:01:03

In general, stuff like that is kind of dangerous. MySQL allows you to use wildcards in several places in grants, and (for some of them) instead of expanding the wildcards at grant time it does so at query time. New users can be created with default permissions that let them access _way_ more stuff then they should. If/as you get more into PostgreSQL you'll see that it likes implicit actions much less then MySQL. Not as good for a joe-user, but if I was running an important database (which is what PostgreSQL was intended for) I wouldn't have it any other way.

That said, these little functions might help you out. call "select grant_all('insert', 'nmueller')" to grant nmueller insert acess on all tables in your database.

create or replace function grant_all (text, text) returns integer as '
declare
tables record;
perms alias for $1;
user alias for $2;
begin

for tables in select ''"'' || nspname || ''"."'' || relname || ''"'' as table fr
om pg_class, pg_namespace where relname not like ''pg_%'' and relkind in (''r'',
  ''v'', ''S'') and nspname != ''information_schema'' and relnamespace = pg_names
pace.oid loop
execute ''grant '' || perms || '' on '' || tables.table || '' to '' || user;
end loop;

return 1;
end;
' language 'plpgsql';

create or replace function revoke_all (text) returns integer as '
declare
tables record;
user alias for $1;
begin

for tables in select nspname || ''.'' || relname as table from pg_class, pg_name
space where relname not like ''pg_%'' and relkind in (''r'', ''v'', ''S'') and r
elnamespace = pg_namespace.oid loop
execute ''revoke all on '' || tables.table || '' from '' || user;
end loop;

return 1;
end;
' language 'plpgsql';

        --Nate

PS: Try being more positive -- it's good for your health.

Yay! Relgious war!

Alias on 2006-03-03T00:29:37

But seriously...

While I suspect you missed a couple of simple things, you're mostly right (especially for pre 8.0)

I remember starting with MySQL back around 2000 and it was quite easy to set up.

Postgres still remains harder, especially WRT the whole pg_hba.conf or whatever that bloody file is.

I'm sure it's more secure or something, but it is still annoying.

The more important underlying issue for me though is I'm not and never wish to be a sysadmin. Installation of any database (dear god Oracle *sob*) I just assume will be painful and ugly.

But it only happens once, and then you are left with your experience AFTER you get connected. And that's where I find the difference much more clear.

Postgres hurts you when you install.

MySQL hurts you every second or third day when you uncover some gotcha or feature weirdity you didn't know existed.

Postgres (with maybe the exception of LOBs before they added BYTEA support) just works, it works consistently, and it works sanely.

And the only issue I ever had with Postgres was that it only implemented a subset of the language for modifying table structures (turning notnull on-off for example).

And I'm happy to say that with the 8.0 series, now it has a much bigger chunk of that language and life is good now.

As for MySQL, I'm afraid until they wake up and realise there's a point to sequences OTHER than AUTO_INCREMENT, I'm not even going to bother looking at it.

That said, kudos to them for _finally_ adding non-quirks mode though. Now they just need sequences and maybe it will be useful finally.

Re:Yay! Relgious war!

TeeJay on 2006-03-03T11:11:11

Actually I haven't found MySQL hurts you on a day to day basis and neither have the guys at google, who looked at porting to postgres and decided (wisely) IMHO to stick with mySQL.

I've found Postgres much more painful, and not just for setting up - I have to write my own bloody cast functions to convert boolean to int, I have dozens of small little queries utilising the ease of MySQL in the application I'm porting and find that each one requires a great deal of mucking about to replicate in postgres.

Postgres does have some strengths - user defined functions, user defined casts, and, importantly, robust and effecient transactions, constraints, subselects and stored procedures.

Of course the projects I'm currently using postgres for - utilise none of those features and would be much quicker and easier in MySQL.

Re:Yay! Relgious war!

TeeJay on 2006-03-03T11:19:10

And of course - just to make life interesting - I can't just use an int in postgres I have to use int2, int4, int8 etc.. how annoying - why not go the whole hog and have char8, char9, char10 gaah! I find this especially hateful when I have to write a function to build a cast from just to be able to treat boolean results as either 0 or 1.

Re:Yay! Relgious war!

Dom2 on 2006-03-03T12:52:21

Yes, you can just use an integer. It really does just work as expected:
% psql test
test=# create table foo (bar integer);
test=# \d foo
      Table "public.foo"
Column |  Type   | Modifiers
-------+---------+-----------
bar    | integer |

And why are you casting from boolean to integer anyway. Is this down to the fact that you've assumed integers instead of booleans everywhere in your code because you're used to MySQL (which AFAIK didn't have proper boolean columns for a long while)? And more to the point, why on earth are you having to do this? As far as I can see it only matters if you have set the pg_bool_tf flag inside your $dbh. Which seems surprising to me.

-Dom

Re:Yay! Relgious war!

TeeJay on 2006-03-03T13:15:29

I am using aggregation (MIN,COUNT) on the results of an IF, which always returns boolean. Postgres doesn't let you do that without writing a function and using it in a user-defined cast - MySQL DWIM, in fact that's the thing about MySQL - it may not always do STRICTLY the right thing, but 99.99% of the time it DWIM, and that is a whole lot more useful.