I don't have answers for PostgreSQL specifically but servers usually don't require passphrases on their keys due to the need for human intervention. The alternative is running a key service - a server on a secure site that provides keys to servers as they come up - but that just moves the problem back by one step since you still have to have the authentication information for the key service on your server. An attacker could still steal that and obtain the key from the key service.
In the Unix/Linux world one approach is to put the key on an NFS server, or perhaps even a CD-ROM, and have the server startup script mount the drive, access the key on it, and then unmount the drive. That keeps the key off backup media(*) and invisible to the average attacker with illicit root access. A knowledgeable attacker would still know how to find the startup script and see how the key is retrieved but they're relatively uncommon. Needless to say the NFS server would be on an internal network and not visible to the outside world.
I don't know what's done in the Windows world.
(* the backup software should be smart enough to know that the keys shouldn't be backed up, e.g., by checking the extended attributes, but it's easy to misconfigure that.)