Slashdot Mirror
PostgreSQL Inc. Open Sources Replication Solution
Martin Marvinski writes "PostgreSQL Inc, the commercial company providing replication software and support for PostgreSQL, open sourced their eRServer replication product. This makes PostgreSQL one step closer to being able to replace Oracle as the de facto RDBMS standard. More information can be found on PostgreSQL's website."
I've been waiting anxiously for this. Postgres will definately be running my startup now... lack of replication was the only thing holding Postgres back. Wonder how long it will take for this to migrate to debs stable branch...
-1 Uncomfortable Truth
I can't say a bad thing about postgresql; this was really the only thing I felt the need for. For anyone who hasn't tried it you really should. Although I don't want to start a MySQL v postgresql flamewar, after trying both I think that postgres has the edge. Mysql was undisputably easier to work with and (at the time) was faster. PostgreSQL has moved on at a much faster rate though. In particular postgresql has solid support for transactions, large objects, subselects, object oriented tables. I'm convinced that if you use databases long enough you'll want every last one of these and won't be able to do without.
Carpe Daemon
They also have been working on a procedual language for PostgreSQL for server side triggers, and functions. Information can be found here, plPHP.
PostgreSQL has made some pretty nice advancements post version 7+, performance and feature wise. I worked on a intranet where the company spent lots of money trying to get an Oracle solution to work, but found it was way to slow. The suggestion of PostgreSQL, and MS SQL came up. We tested PostgreSQL, and it was acutually faster, and easier to maintain then our Oracle database. The best part was, it was free!
Every Super Villan uses Linux.
InnoDB Hot Backup is a tool which allows you to backup a running InnoDB database without setting any locks or disturbing normal database processing. You get a consistent copy of your database, as if the copy were taken at a precise point in time. InnoDB Hot Backup is also the ideal method of setting up new slaves if you use the MySQL replication on InnoDB tables.
For how many server computers you want to order an evaluation copy, or 1-year licenses (390 euros or 450 US dollars each), or perpetual licenses (990 euros or 1150 US dollars each); discounts are available for large volume orders.
This is from http://www.innodb.com/hotbackup.html
mysql> select count(*) from histticks;
+----------+
| count(*) |
+----------+
| 80081227 |
+----------+
No, it's not true. (This is just a quick example I pulled out, we have larger tables).
PenguiNet: the (shareware) Windows SSH client
> This makes PostgreSQL one step closer to being able to replace Oracle...
Please! While this may help win the hearts and minds of OOS geeks, it does little to improve their standing in the business world.
Oracle is as established in the database world as Microsoft is on the desktop. This alone would doom any OOS wannabe to quiet places like web server back ends where they already do well anyway ( e.g. mySql ).
Put aside the technical considerations, support, client base, etc and PostgreSQL still offers as much of a threat to Oracle as mySql or dBase. The only real threat I've seen to Oracle supremacy is Microsoft's SQL Server but, of course, that's only in MS shops.
Starting with the next release SAPDB will be rebranded as MaxDB by MySQL AB.
This will probably mean that PostgreSQL will have a very hard time competing with MySQL ! (also see the info on the SAPDB webpage)
At the same time the licencing will change to pure GPL (no more LGPL libraries !!!)
Yeah, but there are irritants to postgres as well.
:\
:)
No source code packages. You can't create a library, like you would in oracle.
When you have an sql error, it tells you the char it occured at, and not much more. Quite annoying if oyu miss a , in a multiline query and have to paste it back.
You can't network two instances so to speak. You can't say.. "select * from slashdot.messages, freshmeat.list where..." Bloody useful for running remote queries over a dedicated line, w/o dump-replicationg stuff.
Not easy to see, verbatim, what queries are running. Well, nothing i've seen so far.
But you know what, I use it, and I like it. 'cuz it doesn't require a java installer and it is simple. But it doesn't hold a light to oracle in some ways
-
ping -f 255.255.255.255 # if only
clearly the poster does not understand the intricacies of replication in a real-time environment.
you can not pull the data out of table and stuff it into another table under even a reasonable workload.
but i understand this is slashdot and technical relevance need not necessarily apply.
In most cases application level replication does not work. When you replicate a DB you need an exact copy, not only the data or table structures, but stored procedures, functions, foreign keys, triggers, synonyms, etc.
In my day job we use Oracle because
1.- Management is using the Powered by Oracle (or something like that) to sell the products to our clients
2.- Replication
But in my freelance company we use PostgreSQL exclusively and have experienced the burden of not having a reliable replication software. This is Good News for us small shops.
Life isn't like a box of chocolates. It's more like a jar of jalapenos. What you do today, might burn your ass tomorrow.
From the MySQL docs:
Version 4.0 of the MySQL server includes many enhancements and new features:
* The InnoDB table type is now included in the standard binaries, adding transactions, row-level locking, and foreign keys. See section 7.5 InnoDB Tables.
PenguiNet: the (shareware) Windows SSH client
As someone who has been messing with Oracle for more than 10 years, I must say that NOTHING (in commercial or OSS world) comes even close on the high-end hardware you are describing. Properly tuned, humming Oracle database is a work of art.
But it is also true that wast majority of Oracle installations are poorely implemented (due to enourmous and unjustyfiable complexity), Oracle's management software sucked (getting better recently), support far from stellar (telephone support hardly usable), yearly costs are sky-high.
I started looking at PostgreSQL and the more I look the more I like what I see - it is conceptually simple, seems to have adequate performance with large tables, JDBC seems to work well too, stored procedures language is very close to Oracle's (I wish for better exceptions handling), and the whole thing is more than adequate replacement for 80% of Oracle installations I have personally seen.
And I have to add that I tried very hard to like MySQL but it did not work for me.
Everything above is IMHO and the usual disclaimers apply.
I had a bit of a giggle when I read the parent but seriously guys (those replying to him/her). and this will seem as a troll but I don't care.
Have you actually used MySQL and tried to break it? The damn thing is hopeless in comparison to PostgreSQL, Oracle and even SQL Server:
InnoDB transactions don't include the DDL so your create table/index etc... WON'T roll back when you cancel a transaction - so really mysql transactions are for inserts, updates and deletes ONLY. Don't give me this crap about innodb being the be all and end all..
it will not perform validation checking on dates correctly, inserting 29/02/2003 works! It allows you to insert 00/00/0000 when that doesn't even EXIST!
it doesn't obey the datatypes you tell it to use and will happily insert 100.00 into a numeric(4,2) field but no -100.. why? cause the programmers use an extra bit for signing and instead you'll wind up with -99.99. This is correct (although your data is fucked) but whats with 100 being legal?? It will even allow you to insert a CHARACTER into a numeric field WITHOUT complaints - I want my database to tell me when something is wrong and enforce my business rules.
You should always try and build as much of your rules into the db app server - thats what it is - an application server, don't put all your logic in your client app. I'm not surprised that Slashdot is fine and all - all the logic is probably in the perl.
I'm sorry for the trollish tone but I could NEVER recommend someone use MySQL. Now MaxDB might be different and I'm all for it if it is but lets just hope it doesn't inherit to much from the MySQL codebase...
8. clients using JDBC would need some mechanism of finding out what the new master is when an update/insert/delete fails.
C-JDBC may take care of this
My largest table works fine at way over
4 billion rows.
And yes, I've got other tables with more than 1 billion rows.
Not easy to see, verbatim, what queries are running. Well, nothing i've seen so far. :\
Try adding
stats_command_string = true to your postgresql.conf
then, "select * from pg_stat_activity" for a list of users pids and queries
-Mark
pg_dump -f mydb.dump -U mydbuser mydb
Allows to backup a running PostgreSQL database.
No locks.
Does not disturb normal database processing.
Consistent copy.
Free.
Original Ingres product -> more features -> Postgres -> compliance with SQL standards -> PostgreSQL
It doesn't. However, Oracle _is_ evil, at least for their Enterprise Applications. Their sales reps are complete liars - the are trained to always say "yes" to "can it do X". Why? Because if it's not in the base install, you can code it yourself. Well, Golly Gee, don't I purchase an enterprise application so I don't have to code it myself?
In addition, you can't really take it for a test drive, because it takes MONTHS to set up, even for small installations (it was a year process for a 300-employee company I used to work for, but we also had customizations as well).
Also, they never feel a need to actually ship a _working_ product. The version we used would not let you enter an order quantity over 9. 9??? Was this tested by ANYONE? I've heard worse stories from their salesforce management software. They basically ship code straight from the developers to the end-users, and allow YOU to test it. And, when you discover a bug, it may take several months for them to get around to a fix, even if you have it escalated to SEV-1 (okay, maybe SEV-1s are fixed within a few weeks, but still...).
And their consulting staff... Ahhh!! We were paying hundreds of dollars and hour for someone who had only used Oracle for 8 months! This was from Oracle headquarters. The guy had only done COBOL programming before, and had never clued into the concept of local variables. We had to recode everything he did.
And, on top of that, we paid a HUGE amount of money for this privilege.
It's not really the money that's the problem. It's that you might expect such service from a two-bit company that charged you $10/hour, but to be charged hundreds an hour for a totally crappy product, you just feel screwed in the end.
Engineering and the Ultimate
"No source code packages. You can't create a library, like you would in oracle."
.so files to be included into Postgres for calling from SQL. You can build Oracle from source, and there is the libpq library for accessing it. What is missing?
I'm not sure what you mean here. You can certainly create
The main feature I see missing from Postgres is tablespaces.
Engineering and the Ultimate
Here's a pretty good article comparing MySQL and Postgres.
Replication
Point in Time Recovery
Two-Phased Commit
Win32
Nested Transactions
I'm the lead developer for a company that sells a Linux-based document management "appliance". We chose PostgreSQL to be the database backend during the initial spec. After we started rolling these out to customers we monitored the performace carefully for any hint of scalability issues.
We've recently sold appliances to some high volume customers and I must say that PostgreSQL has had no issues. It's fast for small, medium, and large installations serving up thousands to millions of documents.
We have our own system for redundancy since the database is only one piece, but we may switch over if their new offering turns out to be better for the database piece.
/. finds me to be 20% Troll, 80% Funny
> it is now the standard for companies needing a
:-). But PG handles RubyForge - 180,000 records, about 20K hits per day - just fine.
> low or mid range solution
Is it?
> it improves with each iteration.
As does Postgres.
> Our needs aren't high-end, but it handles
> our 300GB databases
That's a lot higher-end than where I'm at
The Army reading list
FYI:
eRServer has fail over of the master to the primary slave, which makes it just fine for HA applications
Replication in postgres is still quite alive. The website is dead, but the mailing list and development is pretty active.
They were trying to get replication code into the main line 7.4, but that got pushed back. However people are still hgacking away at it.
No need to paste it back, if you're using psql. Just type \e right after you get the error and you're in $EDITOR looking at the query as entered. Make your tweaks to it, :wq, and it runs again automatically.
It already has them. You have to write the rule yourself, so it's not nearly as convenient as in Oracle, but it's possible. It's something like:
Now, I could see why you'd really want these to be automatic, as updating the definition of these rules manually is a maintenance headache. But you can do it.
No source code packages. You can't create a library, like you would in oracle.
I'm not sure what this means. You can create everything from basic queries to languages that are used to implement stored procedures and ship them with your product.
When you have an sql error, it tells you the char it occured at, and not much more. Quite annoying if oyu miss a , in a multiline query and have to paste it back.
If you find this to be inappropriate, perhaps you should file a bug or offer a better error message handler. The source code is available, and they do amazing things with it.
You can't network two instances so to speak. You can't say.. "select * from slashdot.messages, freshmeat.list where..." Bloody useful for running remote queries over a dedicated line, w/o dump-replicationg stuff.
That sounds like dblink, which is included in the distribution (contrib).
Not easy to see, verbatim, what queries are running.
Check out all of the pg_stat views. In particular (slightly modified to avoid lameness filter):
cms_log=# select usename, current_query from pg_stat_activity;
usename | current_query
dustin | <IDLE>
cms13 | select count(*) from gateway_log;
cms37 | <IDLE>
-- The world is watching America, and America is watching TV.
I've got good news... PostgreSQL 7.4 beta 2 will reclaim index space and has a vacuum daemon in /contrib (preparation for a built in daemon).
Oracle runs vacuum as well -- it just has a different name. It's what the undo / redo logs are for, to allow MVCC while maintaining an overwriting storage manager.
Rod Taylor
Take 10k and contract out a PostgreSQL developers. Several features have been added in this way. I
believe cross transaction cursors is the most recent.
Rod Taylor
Just to clear up any confusion:
A transaction can provide the ACID properties throughout the duration of the transaction. Most importantly for read-only operations, you have Isolation. Isolation let you perform your queries without being affected by ongoing writes.
This is better than a lock, which another poster suggested, as a lock will block all writers, while a transaction need not block anybody.
PostgreSQL has:
* RMAN/hot backup (yes)
* Log Miner (no)
* rollback (yes)
* PL/SQL (plPgSQL similar but not same)
* triggers (yes)
* archive log mode (no)
* duplex control, redo and archive log files (no)
* buffer cache (yes)
* row-level locking (yes)
* automatic undo management (more info?)
* constraints (yes)
* inline views, WHERE EXISTS, subqueries, et. al. (yes)
Is this what you mean by archive log mode? If so, then PostgreSQL has it. :)
dtach - A tiny program that emulates the detach feat
It's not as scalable as oracle or db/2 but at the same time it costs 15K per processor which is a hell of a lot of money.
I don't want to defend MS too much, but last month we bought Small Business Server for about $1500. That included Win2k Server, SQL Server 2000 and a lot of other things.