[Documentation] [TitleIndex] [WordIndex

(!) Please ask about problems and questions regarding this tutorial on answers.ros.org. Don't forget to include in your question the link to this page, the versions of your OS & ROS, and also add appropriate tags.

Installation of a PostgreSQL server

Description: A step-by-step "cheat-sheet" for installing a PostgreSQL server on a Ubuntu machine. Does not cover any advanced installation procedures, just a very simple installation with no additional options. After installing the server, you can set up multiple databases, restore them from backup files, etc.

Tutorial Level: BEGINNER

Overview

This tutorial is a quick "cheat-sheet" for installing a PostgreSQL server on a Ubuntu machine. It is not meant to replace the detailed instructions that are provided along with PostgreSQL. However, we have found that some steps can be unclear in the instructions, and a step-by-step procedure can sometimes help. It also does not cover any advanced installation options.

This tutorial works for PostgreSQL 8.4. Here you can find the complete PostgreSQL 8.4 manual.

This tutorial only covers the installation of the database server itself. It does not cover the creation of individual databases once the server is up and running; an example of creating a database and populating it from a backup file can be found in the household objects database installation tutorial.

Install the PostgreSQL server

sudo apt-get install postgresql

In the config directory of PostgreSQL you will find two very important files which we'll be using later:

Create a PostgreSQL server account

General: PostgreSQL accounts are separate from OS accounts. During the installation process, both a PostgreSQL and an OS account will be created, both called postgres. The postgres account is a superuser, meaning it can add other PostgreSQL accounts.

What we need to do next is to login to PostgreSQL using the postgres account in order to create a new account for us to use.

Log in to the PostgreSQL server using root access

Become root, then su into the postgres OS account. That's it; if you are logged in to OS as the postgres account you can also log into PostgreSQL as the same account without a password.

Start the psql front end, then skip to the 'Add a new database user' section:

psql

Log in to the PostgreSQL server using sudo access

If you have sudo but not root access you should still be able to switch the the postgres user using sudo su and then open psql.

sudo su - postgres
psql

If that doesn't work see the next section, if it does then jump to the 'Add a new database user' section

Log in to the PostgreSQL server without root access

If you have sudo privileges, but not root access, and can not become the postgres OS account, you must edit pg_hba.conf to allow username posgres to be logged in by anybody without password

Find the lines:

# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser

Change ident sameuser to trust. Remember to change back when done with this tutorial!!!

After modifying pg_hba.conf, have the Postgresql server reload it by issuing a HUP signal:

ps auxw | grep postgresql
sudo kill -HUP xxxx

or

sudo service postgresql reload

You should now be able to start the Postgresql front-end (psql) as the superuser postgres:

psql --username postgres

Add a new database user

Once you are logged into psql as postgres, you can add a user. It is recommended to start by adding a user WITHOUT superuser privileges, but with CREATEROLE and CREATEDB privilages. This is achieved by the following command:

CREATE ROLE willow LOGIN CREATEDB CREATEROLE PASSWORD 'willow';

You can then quit psql (\q).

If you have modified pg_hba.conf to let the postgres user connect without a password, be sure to revert the change.

Allow connections through TCP/IP

We will now enable TCP/IP connections to the PostgreSQL server.

Edit pg_hba.conf. At the end, where the list of authentication methods is, add the following lines. This allows anybody to connect through TCP/IP and access all databases, as long as they supply a right username and password. The password is md5 encrypted

# Anybody through TCP/IP with password
host    all         all         0.0.0.0   0.0.0.0     md5

Now you must let the server know it's supposed to accept TCP/IP connections from anybody. Edit the postgresql.conf file and set

listen_addresses = '*'

You will then need to restart the server altogether. The simplest way of doing this is to restart the machine.

Localhost connections

If you do not want to enable TCP/IP connections, one alternative is to allow your brand new user to connect from the local machine, WITH a password. Note that this means only code running on the same machine as the server will be able to connect.

# willow user can connect locally with password
local   all         willow                            md5

Remember that after modifying pg_hba.conf you must have the Postgresql server reload it by issuing a HUP signal.

ps auxw | grep postgresql
sudo kill -HUP xxxx

or

sudo service postgresql reload

To check that it is working, try logging into psql on localhost as the new user:

psql --username willow --password --dbname postgres

PGAdmin3

PGAdmin is a graphical front-end to a database server. To install it use:

sudo apt-get install pgadmin3

The run it using:

pgadmin3

If you have set your server to accept TCP/IP connections, you can run pgadmin3 on any machine that can talk to your server machine over the network. If your server only accepts localhost connections, you'll have to run pgadmin3 on the same machine on which you just installed the server.

In pgadmin3, go through the following steps:

That's it, the server is up and running! You can now create your own databases on the server. Here is a tutorial for creating and restoring the household_objects database on your server, using a Willow Garage database backup file.


2019-12-07 13:10