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.x.
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 launch the postgresql shell.
sudo -u postgres psql postgres
The command is confusing so here is a break down,
sudo -u postgres # Launch the following command as the user postgres. psql # Launch the postgres shell. postres # This is a reference to the name of the database to load (not the user).
Here is what it looks like once you are in,
psql (8.4.1) Type "help" for help. postgres=#
All commands are prefixed by the the \. So to change the postgres account password,
postgres=# \password postgres
Note that this changes the account inside of postgres, not the operating system account postgres. Finally exit the postgres shell,
postgres=# \quit
With this change you can now use a client software such as pgAdmin III. Note that be by default, postgres is very secure. As such with this change you will be able to use pgAdmin III in only the following 2 scenarios,
- Using the pgAdmin III installed on the same system as Postgresql.
- From another machine using an SSH Tunnel.
Read further for more details on connecting.
Connecting to the Database
GUI Client
Besides the command line interface, I like to be able to manage the database using a remote GUI client.
pgAdmin 3 from www.pgadmin.org
pgAdmin 3 is one of the most popular GUI clients. It is cross platform so I happen to run it on my Windows desktop.
Note: for the nit picky users like me, in the Windows environment I personally hate installers, so I install this in a test machine, copy out the directory and then just use on my other systems without having to install and have strange things in my Windows registry.
From my Windows desktop I SSH into the Ubuntu server and tunnel port of 5432 to connect. See, Secure TCP/IP Connections with SSH Tunnels for more details.
I find using SSH to be the most secure method. In some cases you may need to allow remote administration.
Server Instrumentation Warning
Note, the very first time I connected with pgAdmin 3 I got a warning on Server instrumentation. I am currently trying to understand if these messages should be addressed or ignored. My gut is saying that I do not need this in a stripped down system. It's most likely nice to have tools for DB admins to get more data.
Here is the warning,
Server instrumentation
The server lacks instrumentation functions.
pgAdmin III uses some support functions that are not available by default in all PostgreSQL versions. These enable some tasks that make life easier when dealing with log files and configuration files.
When you install PostgreSQL 8.0 or up using the Windows installer, you just need to select the "admin" or "adminpack" module.c
When compiling from source, the necessary files can be found in the xtra subdirectory of the pgAdmin source tree. For PostgreSQL 8.0, copy the admin directory under the postgresql contrib source directory, make and make install from there. For PostgreSQL 8.1, use the admin81 directory for that.
PostgreSQL 8.2 and above include the instrumentation functions in the "adminpack" contrib module. After the module is installed, you need to create the instrumentation functions in your maintenance database using the admin.sql script (admin81.sql for PostgreSQL 8.1) which are usually located in the pgsql share directory (e.g. /usr/local/pgsql/share)
Extended server instrumentation is not supported for PostgreSQL 7.3 and 7.4.
Backup of a Database
This section needs to be written and tested.
http://www.postgresql.org/docs/8.1/static/backup.html - not too clear, for example how do I specify a user?
http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/ - looks promising, syntax looks very different from official manual though.
Also, there is a backup interface via the gui... figure out how to get the command line equivalent. It worked well enough for my 52MB database. Just took a while as it went over the network (gui ran from a client machine).