Adding features to Postgres is often a frustrating experience for all involved. Some features are clear-cut, but many are subject to
interpretation and involve much email discussion. The email discussion is often draining, but this
comment from a Slashdot
thread about Linux desktop innovation is
illuminating. The comment is about human interface engineereers asking for tiny application changes to improve usability, and while the
application developer was originally “distracted trying to decide a fitting way to end the e-mail authors life”, he ultimately agreed that
the changes improved the user experience. I think Postgres developers often feel the same way.

I’ve been caught off-guard by the number of news stories that have been posted about Oracle turning off their Solaris boxes that participated in the PostgreSQL Buildfarm and the fact that I’ve been working to replace them. I think most of us in the community assumed it was going to happen sooner or later – Sun were contributors to PostgreSQL, but once they bought MySQL I think it was the beginning of the end of their contributions to the project. Being acquired by Oracle was the solid-gold nail in the coffin.

Anyway, just to clarify a few points in some of the articles I’ve seen:

1. My employer and corporate contributor to PostgreSQL, EnterpriseDB is providing the hardware for the new buildfarm members. We try to give back to the community wherever we can, and this is one thing that we can do quite easily.
    
2. So far I’ve enabled two Solaris SPARC buildfarm members, one running the Sun Studio compiler, and one using GCC. There are equivalent Solaris Intel members being setup at the moment, as well as a couple of Windows 7 ones using VC++ 2008 32 and 64 bit.
    
3. Contrary to reports and what might be inferred from the short bio on my blog, I actually work for EnterpriseDB on a full time basis. The “in my spare time I work for…” bit was my poor British attempt at humour. Sorry ’bout that – it won’t happen again!

In a recent blog post on Gigaom, Simeon Simeonov argues that virtualization is on the way out, and discusses VMware’s move toward platform-as-a-service computing. In a nutshell, his argument is that virtualization is inefficient, and is essentially a last resort when legacy applications can’t play nicely together in the same sandbox. In other words, the real goal for IT shops and service providers is not virtualization per se, but multi-tenancy, cost-effective use of hardware, and high availability. Find any two servers in the average corporate data center and ask why they’re not running on the same machine. It’s a good bet you’ll get one of the following four answers: (1) machine A is running a piece of software that misbehaves if run on the same machine as some piece of software running on machine B, (2) a single server couldn’t handle the load, (3) one of those servers provides redundancy for the other, or (4) no particular reason, but we haven’t gotten around to consolidating them yet. In my experience, the first answer is probably the most common. But as Simeonov points out, the ideal solution is not virtualization, but better software – specifically, platforms that can transparently service multiple customers.

PostgreSQL is very strong in this area. Hosting providers such as hub.org provision databases for multiple customers onto a single PostgreSQL instance; and here at EnterpriseDB, we support several customers who do much the same thing. Databases in PostgreSQL provide a high degree of isolation: many configuration parameters can be set on a per-database basis, extensions can be installed into a single database without affecting other databases that are part of the same instance, and each database can in turn contain multiple schemas. The ability to have multiple databases, each containing multiple schemas, makes the PostgreSQL model more flexible than Oracle or MySQL, which have only a single tier system. In the upcoming PostgreSQL 9.0 release, the new grant on all in schema and default privileges features will further simplify user administration in multi-user and multi-tenant environments. Behind the scenes, a PostgreSQL instance uses a single buffer pool which can be efficiently shared among any number of databases without excessive lock contention. This is critical. Fragmenting memory into many small buffer pools prevents databases from scaling up (using more memory) when under heavy load, and at the same time prevents databases from scaling down (using less memory) when not in use. By managing all databases out of a single pool, PostgreSQL can allow a single database to use every block in the buffer pool – if no other databases are in use – or no blocks at all – if the database is completely idle.

Simeonov seems to feel that virtualization has already nearly run its course, and predicts that the market will hit its peak within three years. That doesn’t seem likely to me. I think there is an awful lot of crufty hardware and software out there that could benefit from virtualization, but it’s working right now, so no one is eager to make changes that might break something. As the physical equipment starts to fail, IT administrators will think about virtualization, but hardware that isn’t touched can sometimes run for a surprisingly long time, so I don’t expect server consolidation projects to disappear any time soon. More importantly, Simeonov seems to assume that all new applications will be developed using platform-as-a-service architectures such as Google App Engine, Bungee, Engine Yard, and Heroku. While some certainly will be, it seems unlikely that the traditional model of application development, using a dedicated web server and a dedicated database running on a physical or virtual machine will disappear overnight. For one thing, choosing one of those vendors means being locked into that vendor’s API – and choice of programming language. Bungee and Heroku are Ruby environments, for example, while Google App Engine offers Java and Python. Good luck making the switch!

So, if plain old virtual machines are going to be around for a while, how does PostgreSQL stack up in that environment? Not too bad. Of course, write-intensive workloads will suffer from the generalized slowness of virtualized I/O. But PostgreSQL is designed to run well even in a very small memory footprint, to take good advantage of the OS buffer cache and process scheduler, and to be portable across a wide variety of platforms. If your database is small enough to fit in memory, performance should be good. And if your database isn’t small enough to fit in memory, there’s not much point in virtualizing it: you’re going to need a dedicated machine either way.

By far the most common issues we see reported with the “one-click” PostgreSQL installers that we build here at EnterpriseDB are password related. In this post I’ll explain what the passwords are, why we need them, and how to reset them.

Superuser Password

The Superuser Password is used by Postgres to secure the built-in “postgres” superuser account in the database itself. This is the only account found in a fresh installation. The password is setup during the initial installation of the database server, and may be changed at any point in the future using pgAdmin, or by issuing an SQL query such as:

ALTER USER postgres WITH PASSWORD ‘VeryVerySecret’;


The superuser password is not required to upgrade Postgres to a newer version, however it will be required if you install certain StackBuilder modules, such as PostGIS or any of the PHP applications that are available. The password is required for these installers because they connect to the database server and create databases and other objects required to run the software.


Service Password

The service password is the one that tends to confuse people. Postgres runs under a special operating system user account for security reasons. This account is created on your machine when the installer runs, and unless overridden on the command line, it will be called “postgres”. On Unix-like operating systems such as Linux and Mac OS X, the account is setup without a password and users generally never need to worry about it again.

On Windows however, things are slightly different. In a normal installation of Postgres we’ll setup the service account on Windows to use the same password that we use for the superuser account (expert users can override this behaviour using command line options for the installer). We need to do this because Windows requires that service accounts have secured passwords.

Note: If you change the superuser password in the future, that does not change the service password as well.

When you upgrade Postgres on Windows, in order to re-install the service we need to know the service password which is why the installer will require that it be re-entered correctly.

If you have forgotten the password, you can reset it on the command line (there are GUI tools that can be used as well, but they are not available on all versions of Windows). Start a command prompt, and then enter a command like:

net user postgres *

You will be prompted to enter and confirm a new password for the user. If you do this, you must then also update the password in the service configuration for any Postgres or related services (such as pgAgent or pgBouncer) that may use the account, or expect them to fail to start at the next reboot. You can do this using the Services management console which can be found in the Administrative Tools folder. Just right-click each service, select Properties, and enter the new password and click OK.

We get a lot of people asking us to reset their service password, as they mistakenly think it’s related to either their Postgres Community Login, or their account on the EnterpriseDB website. It’s not – and we cannot change it for you!

Uninstallation


If you uninstall Postgres, the service account will not be removed from the operating system. This is because each individual uninstaller has no way of knowing if you are using the service account with other packages or installations. This isn’t a huge problem unless you come to reinstall Postgres at a later time and realise you’ve completely forgotten what the password was set to – in which case you can reset it as shown above. If you prefer to remove the account, you can use a command like:

net user postgres /delete

Summary

So to recap, we have Superuser passwords which are used to secure the database server’s superuser account, and on Windows a service password to secure the operating system service account used by Postgres. Accounts use the same username (postgres) and the same password in a normal installation, but either password can be changed independently of the other post-installation. Both passwords are specific to your own machine, and can be changed (or the service account deleted) as shown.

For a walkthrough of the installation process, please see this guide.

If you need any further assistance having read this post, please use the pgsql-general@postgresql.org mailing list, or the EnterpriseDB Forums.

I mentioned in March that user videos would be created at PG East.
Those videos have now been released. Hopefully they will be useful in promoting Postgres.

As I mentioned in a previous blog post, trying to find pages in the PostgreSQL documentation using Google doesn’t work very well: most often, one gets links to older versions.

A recent thread on pgsql-performance (somewhat off-topic for that mailing list, but that’s where it was) suggested that perhaps we could use Google’s canonical URL feature to work around this problem.

Another suggestion was that we ask people who link to our docs to link to http://postgresql.org/docs/current/ (or some sub-page) rather than linking to a specific version (e.g. the same URL with 8.4 in place of current). That way, as new versions come out, everyone’s links will still be pointing at the latest version of the docs, helping the new versions accumulate “Google karma” more quickly than they would otherwise. Or at least, that’s the idea: I have no idea whether it would actually work.

I had a great time again at OSCON, and feel more energized than ever about Postgres. Because I
attend OSCON every year, it is a great event to gauge changes in Postgres adoption and mindshare. Years ago, Postgres visibility at OSCON
was minimal, but now Postgres is a major player at the conference. For example, our booth this year was no longer in the open source
section (A.K.A. the open source ghetto), but in the section with commercial vendors. We had abundant booth staffing, thanks to Gabrielle,
and artistically designed staff t-shirts (image),
thanks to Josh Berkus. I think we were moved to the commercial section because our booth staff and activity often overwhelm smaller
booths.

The conference had major tutorials (1,
2) and sessions about Postgres, and Postgres as often mentioned in
non-database-specific sessions, including the closing session. I heard one non-database session polled the audience about how many
attendees had lost data on specific databases — obviously we did very well in that poll.

Continue Reading »

I have decide to attend the SURGE conference in Baltimore in September. It is being hosted by
Postgres-friendly OmniTI, who just did several Postgres-related presentations at
OSCON (Robert Treat, Theo Schlossnagle). As a database internals guy, my focus is so much on the
software that I often know little about how Postgres is deployed at high-volume sites; I hope to learn about that at the conference. With
so much Postgres activity in the United States now, I am doing little travel to international conferences
(schedule).

There is one week until the Postgres Pool Party at my home. No RSVP
necessary.