I switched over from mysql over a year ago, and have no plans to go back.
A couple of sites I've built with it:
www.bravekids.org (large resource directory, just about everything is dynamic)
www.citynews.com (searching and administrative functions)
I use it with PHP and/or Perl, and Apache. I've also used it on some
smaller site, under Free BSD and Linux. I've been pleased with
performance, ease of use, maintenance, scalability, and adherence to SQL
standards. I'm more of a backend developer than an SQL expert, but my SQL
guru buddies tell me the migration path to Oracle and other large databases
is much easier with Postgres than with other open source products.