After a recent hard drive crash, I decided to try out Ubuntu, the most popular consumer Linux distribution. Ubuntu has become popular because it is more lenient than other distributions with regard to proprietary software – it can use Flash and play mp3s out of the box, without installing additional software. Ubuntu, like other Linux distros, is also a great platform for the exploration of open source GIS. This post is the first in a series of tutorials on how to get some of the basic GIS system tools up and running. For the demonstration, I’ll be using a default installation of Ubuntu 12.04 LTS 32-bit desktop.
The first step is to install PostgreSQL. PostgreSQL is a free, open source, object-oriented database that can be spatially extended.
1. Open the Ubuntu Software Center. On the dash bar (the toolbar running down the left side of the screen), its icon looks like an exploding shopping bag. In the Software Center window, enter ‘postgresql’ in the search box near the upper right corner. The first result should be “Object-relational SQL database (supported version)” – that’s the one. Click on it; an install button will appear. Click the install button, and wait a few minutes while the package is downloaded and installed. It will probably ask for your password, so be prepared to enter it.
2. Now do a search for ‘postgresql-contrib’. There should only be one result, “Additional facilities for PostgreSQL (supported version)” – go ahead and install it. Next do a search for “pgadmin” – this will also only yield one result, pgAdmin III, a graphical database management application. Install this as well. When you’re done, close the Software Center.
3. At this time, launch the Terminal application by clicking the Dash Home button at the top of the dash bar. The window that appears will have a search box at the top of it – if you type ‘terminal’, it will find the Terminal application for you. Enter the following command (press enter after typing all commands):
cd /etc/postgresql/9.1/main
cd = change directory; this will take you to the location where the PostgreSQL configuration files are stored. You’ll need to edit the one called postgresql.conf. Enter the command:
sudo gedit postgresql.conf
and enter your password when prompted. This will launch a graphical text editor. There are two lines in this file that need to be changed. The first is line 59, which says:
#listen_addresses = 'localhost'
Lines starting with # are comments – deleting the # forces the interpreter to pay attention to this line. So go ahead and delete it. Now find line 84 which says:
#password_encryption = on
and delete the # from this line as well. Very easy, now save the file, and then close the text editor window, which will return you to the Terminal.
4. Now it’s time to start the PostgreSQL server. Enter the following command in the Terminal:
sudo service postgresql start
and press enter. If prompted for your password, enter it (sudo invokes the superuser account, and you will have to enter your password if you haven’t done so in the past few minutes). You will see the following on the screen:
* Starting PostgreSQL 9.1 database server [ OK ]
Congratulations, you now have a database server running on your computer! Unfortunately, you’re not quite done yet – you still need to add a user who can work with the database.
5. Enter the following command in the Terminal:
sudo -u postgres createuser
You’ll see the following prompt:
Enter name of role to add:
Enter the name of the new user. For the purposes of this exercise, I’ll be using ‘newusername’, but you should use something more relevant, like ‘dbuser’ or your own username. Next you see:
Shall the new role be a superuser? (y/n)
Not every user should be a superuser, but you need at least one person who has total access to the system, and it might as well be yourself. So type ‘y’ here and press enter.
6. Now your new user needs a password. To assign one, you’ll have to log into the database:
sudo -u postgres psql
You will see the following prompt:
psql (9.1.4) Type "help" for help. postgres=#
You are now inside the database, giving commands directly. Let’s not spend too long in here right now. There’s just one command to give. In my example, the username is newusername, and the password is newpassword:
alter user newusername with encrypted password ‘newpassword’;
Don’t forget the semicolon at the end – all SQL statements are terminated with a semicolon. If you forget it, the interpreter will think you’re adding more lines of code. When the job is done, you’ll see this message:
ALTER ROLE
You’re all done in here, so type:
\q
to exit the database. Restart the server by typing:
sudo service postgresql restart
and then close the Terminal.
7. Now you can get inside your database and look around using a graphical management client. During the install process, pgAdmin III should have been added to your dash bar – its icon is a blue elephant. Open pgAdmin and expand its window. On the far left of the toolbar is the Add Connection button, which looks like an electrical plug. Click that button to bring up the New Server Registration dialogue. Here’s the information you need to enter:
name: pgtest
host: localhost
port: 5432
maintenance db: postgres
username: newusername
password: newpassword
store password: checked
Use the following diagram as a guide:
Click OK when you’re done. You will probably see a warning message about stored passwords – that’s okay, because we told it to encrypt the passwords (using an md5 hash) back in step 3. You may also see a warning about server instrumentation, that’s okay. If you see a warning telling you to install an admin-pack or postgresql-contrib, that’s fine – we actually did that already, back in step 2.
At this point, your database installation is complete. Right now, you only have access to one database, called ‘postgres’. Double-clicking on the icon will connect to it, allowing you to browse through its structure of tablespaces, schemas, and catalogs. At the bottom of the tree, you should see login roles, which includes the user we created in step 5. Feel free to browse around the postgres database, but don’t modify it in any way – it’s the maintenance database, not meant for user data. You could add a user database at this point, but this tutorial won’t be getting into that until part 3. At this time, you may wish to browse the help documentation and familiarize yourself with pgAdmin.
Thus concludes this installation of the PostgreSQL database. Coming up in Step 2, we will install GEOS and GDAL, some important dependencies of the spatial database extender PostGIS.
Comments are closed.
