This article can be enhanced by adding instructions for letting just any user create a database.
During the time of this article, the instructions on the Ubuntu wiki were pretty light and from what I can see not quite complete. These instructions are currently written for PostgreSQL 8.4.
Installing PostgreSQL
Ensure UTF-8 Encoding is Set
Make sure that your operating system has the right encoding set as explained in the "Select Locale" part of my Ubuntu Linux Base Server article. In the case of my Ubuntu server it was installed with the minimal configuration and was missing the utf-8 locale.
Install
The Ubuntu install is simply,
sudo apt-get install postgresql # installs postgresql
Similar to how Apache is installed, Ubuntu has it's own standard. Here are the keynotes.
You will now have a postgresql user on your system. This account is reserved for running Postgresql in a secure manner. This account neither has a password or a home directory. I will show you how to use it as we go forward.
The postgresql service is also added to the system to start automatically.
Finally here are some directories to be aware of,
- /var/lib/postgresql/8.4/main - location of the main database that can only be viewed and modified by the user postgresql.
- /etc/postgresql/8.4/main - location of all configuration files.
- /var/log/postgresql/ - location where Ubuntu keeps all the logs and automatically is set to archive and roll over.
That's all there is for the installation. Ubuntu apt-get makes it easy and does all the hard work for you.
Further Details
You might also be interested to know that Ubuntu's apt-get installer does a number of other things you would have had to manually configure if you installed manually,
- Initialize a database storage area on disk called the database cluster.
- ...
In order to complete this section I need to figure out how to do a configured portable setup of PostgreSQL.
Initial Setup
Change the Default postgres Database User Account
If you want to manage postgresql outside of the command line, you must change the default password in postgresql.
The first step, is to step into the postgresql shell launching the program psql as the user postgres.