Search:


Main.InstallingPostgreSQLOnUbuntu

Recent Changes
Printable View
Page History
Edit Page

PostgreSQL installation on Ubuntu

PostgreSQL is an "enterprise-class" open-source relational database system. PostgreSQL can be installed in at least two ways on Ubuntu: 

	* From Official Repositories
	* From source

From Official Repositories

First, make sure Ubuntu is installed and your internet connection is working. Then, follow these steps:

  • Start the package manager

We will use the Synaptic package management software to install a copy of the PostgreSQL database server and tools. Synaptic is a graphical front-end for the "apt" system - the "advanced packaging tool".

From the panel, choose System -> Administration -> Synaptic Package Manager. You will be asked for your password, and after entering it you might be shown a box with some introductory text explaining the program. Read through this and close the box to continue.

The Synaptic tool provides a package browser and search features to allow you to discover and install the software you require. You should see large lists of software packages as well as a toolbar with buttons for Reloading the package list and searching for packages.

Click the Search button and enter "postgres". Then press <Enter> to begin the search for all postgres-related software. This might take a few moments. When the search completes, you should be able to scroll through a list of related packages. The ones we are interested in are: 

	* postgresql-8.2
	* postgresql-8.2-postgis
	* postgresql-contrib-8.2
	* postgresql-doc-8.2
	* pgadmin3

... and if you want to program your own add-ons for the database, you need: 

	* libpq-dev

We want to install the main postgreSQL server first (the latest stable version at the time of writing is 8.2) so search through the results to find "postgresql-8.2" and right-click on it to bring up a option menu. Choose the "mark for installation" option. You can select a bunch of different packages and mark them for installation, and then actually go ahead with the procedure by clicking the big "Apply" button on the toolbar.

When you mark the database server for installation, Synaptic will show you a list of packages that the selected one depends on. These will be downloaded as well, to make sure the software runs. This is one of the major advantages to using a repository system like apt - all "dependancies" are automatically found and installed. In the case of postgreSQL, the packages "libpq5", "postgresql-client-8.2" "postgresql-client-common" and "postgresql-common" are required. Click the "Mark" option to mark all dependancies for installation also. Then do the same for the other packages listed above, marking their dependancies as well.

Once you've marked all required packages for installation, click the "Apply" button. Synaptic will download, install and configure everything you need automatically from here.

Note, it would also have been possible to do all of the above from the command line, using commands such as: 

	sudo apt-get install postgresql-8.2 postgresql-client-8.2
	sudo apt-get install pgadmin3 pgadmin3-data
	etc.

Once the installation of postgresql is complete (the progress box should say "Changes Applied") you should be able to close the Synaptic package manager and create your first databases and perform administrative tasks with either the "psql" client program or "pgadmin3".

The installation procedure should have gone all the way and started a database service already. Check this by entering "ps -Al" at the command console and looking through the list for any processes named "postgres". The "ps" command provides a snapshot of currently running processes. The "postgres" processes handle requests for data by external programs and clients. Generally they run quietly in the background the entire time your computer is on.

Database Administration

In order to start using our new database software we need to do one or two things. First of all, we must set the password for the primary database login. Postgres actually runs as a Linux user with a name and password - the name is usually "postgres". You need to set the password before you can log in to perform any administration tasks. Enter the following command into a console: 

	sudo su postgres -c psql template1

The "sudo" part of the command tells Ubuntu we want to perform a task as a super-user (one who has great privilege and responsibility). The "su" part commands Ubuntu to "switch user" to another login - in this case "postgres". The "-c" part is an option for the "su" command that informs Ubuntu that a further command follows that must be performed as the user we switched to - the command in this case is "psql template1", which will launch the "psql" database client program and connect it to the "template1" database stored by PostgreSQL itself.

When you run this command, you will be asked for your password (your normal Ubuntu/Linux password, not the database password because you have not set it yet...), and after entering that you will find yourself at the psql command prompt. It should look like this: 

	postgres=#

This means you are logged in as the user "postgres" and any SQL commands you enter here will affect the "template1" database mentioned earlier. We want to set the password for the "postgres" user, so enter the following SQL command: 

	ALTER USER postgres WITH PASSWORD 'mypassword';

Replace 'mypassword' with whatever password you want, and remember to include the ';' at the end. Then press <Enter>. You should see a little confirmation appear saying "ALTER ROLE". Done.

Now you can quit the psql tool and return to your linux command console. Do this by entering: 

	\q

At this point it should be possible to use the "pgadmin3" tool to browse and manage your PostgreSQL databases. Run this tool by using the Applications -> System Tools -> pgAdminIII menubar option, or enter "pgadmin3" at the command console. As the program loads you will see a logo screen and then some Tip for the day. You will also see some empty data areas. They are emty because you have not yet connected and logged in to a database. Using this tool you can (like with psql) enter and administer databases found on your local machine as well as ones over the network.

To connect to a database, you must create a connection using the File -> Add Server menu option, or by clicking the little button with a plug icon. A new dialog should appear with entry fields to fill in. You must enter the connection information for the required server at this point.

To connect to the default "postgres" database, owned by the "postgres" user, enter the following: 

	Address: localhost
	Description: Local Test Cluster
	Service:
	Port: 5432
	Maintainance DB: postgres
	Username: postgres
	Password: (whatever it is)
	Store Password: (choose no for more security)
	Connect Now: yes

Then click OK. You should now see new entries filling the left-hand panel. A hierarchy of server information should expand. Particularly, under the "Databases" section you should see an item called "postgres". This is the default admin database. If you click on this database, you can connect to it and browse its internals. You may recieve a large warning about the lack of server instrumentation - ignore these for now.

Everything you click on within the left-hand panel will trigger the display of SQL code used to create that item in the bottom panel. This can be helpful for learning various aspects of administering a database.

If you expand the postgres database item (using the little + sign) on the left-hand panel, you will see the various parts that make up all the functions and data within it. One of the most important sections is the "Schemas". These are the blueprints for the database - all it's tables, functions and sequences that maintain it's structure. Most databases come with a default "public" schema. It is best to create your own entirely new databases and schemas for storing your own data and leaving the "public" schema and "postgres" databases alone.

Database Cluster Creation

On systems other than Ubuntu or debian, your installation process for PostgreSQL will be slightly more complex - you might have to initialize a database cluster, which is done automatically on ubuntu. This is done with the "initdb" command, which will not be dealt with in this guide.

To create a fresh database within the default cluster that you can use for all your important task-specific work and data, you use the "createdb" command. The createdb command can be triggered with various options controlling the creation task, such as the new name of the database and who it's owner is. If no name given - createdb will use current user name as the dbName. Lets create a database called "projects" which will hold important information about any projects you are working on. Enter the following command to do so: 

	sudo su postgres -c "createdb -U postgres projects"

This will switch the Linux user to "postgres", and then run the command in quotes, which creates the database called "projects" as the database user "postgres". This might seem backwards, but it is made slightly over-complex by the Ubuntu "sudo" system. Without the sudo system, you would enter the command line this: 

	createdb -U postgres projects

This is much simpler, but presumes you are logged in as a user with access to the "createdb" command, and also presumes you have set up the database access permissions and authentication, which you have not so far. Read on for details....

Populating your Database

If you go back to pgAdminIII and refresh the "Local Test Cluster" item under "Servers" in the left-hand pane, you should see your new database called "projects". It will contain the default (empty) public schema. At this point you can use the pgAdmin graphical tools to create new data tables and populate them with data.

Restoring Backed Up Data

If you have existing database backup dumps that you want to restore, see the Postgres Backup and Restore? guide after reading about authentication and permissions.

Database authenication and permissions

A fresh install of PostgreSQL is very paranoid - it does not allow connections from other machines on your network and also might not let you in from your own machine if you attempt to connect using TCP/IP network protocols. You need to enable certain kinds of connection to allow them to happen. We bypassed some of these restrictions using the sudo command in the case of creating database above. For a more general ability to manipulate your own databases, you need to relax restrictions a little - but be careful - too much relaxation and you could render your database server vulnerable to attack by other network machines and even other users on your own computer. Read the official postgreSQL documentation for much more comprehensive information.

Configuration File: postgresql.conf

The primary configuration for the entire PostgreSQL database is called "postgresql.conf". This file controls all kinds of options for the overall database setup, and it contains one important line that controls whether or not remote machines can access the database.

You need to use sudo and enter your password to access and edit these protected files. On Ubuntu the postgresql.conf file is found at the following location in the computers' file-system: 

	/etc/postgresql/8.2/main/postgresql.conf

you can edit it using the standard ubuntu text editor (called "gedit") by entering the following command: 

	sudo gedit /etc/postgresql/8.2/main/postgresql.conf

The file should open into the gedit text editing program. You should see lots of different configuration options surrounded by comments explaining each section.

Now for editing that important line... This line is labelled "listen_addresses" and is usually found in the "Connections and Authentication" section. It looks like this by default: 

	#listen_addresses = 'localhost'     #some comments...

In order to allow other machines on the network (or the internet) to connect to the database server, you must change the line to read like so: 

	listen_addresses = '*'

Note that the '#' has been removed (this uncomments the line and makes it active) and the '*' symbol means that all remote hosts can connect. You may also want to uncomment (remove the # symbol) from the line that looks like this: 

	#password_encryption = on

You have to restart the database server in order for these options to take effect. Do this by entering the following: 

	sudo /etc/init.d/postgresql-8.2 restart

At this point the server should shut down, and restart with any new options in effect. To further customize and filter the range of possible connections and means of authentication, you must edit another configuration file...

Configuration file: pg_hba.conf

The database connection permissions are stored in a simple text file called "pg_hba.conf". This stands for "postgres host-based authentication configuration". It allows you to define who, how and from where connections can be made. The file is quite well commented, allowing you to read up on the various options.

You need to use sudo and enter your password to access and edit these protected files. By default Postgres only allows local connections using "ident" authentication. This method uses your Linux user name as your database user name, which may not be what you want. Network connections are only allowed from the local machine (over a Unix-domain socket), so if you want to connect over TCP/IP (either locally or from remote machines) you need to enable host-based authentication. If you try the following to log into the default database as the user "postgres", it will probably not work: 

	sudo psql -U postgres

It will fail with something like this: 

	Ident authentication failed for user "postgres"

To remedy this, add the following line to the "pg_hba.conf" file under the "IPv4" section to allow other machines on your own local network to connect in various ways. This is useful for internal office databases that need to be accessed by users and programs from their own workstations: 

	host    all     all     192.168.0.1/24      md5

The "/24" on the end of the IP address specifies that only the first 24 bits (3 bytes - containing the values "192", "168" and "0") of the full 32 bit address (192.168.0.1) matter for authentication - this means that the database server should allow any computer with an IP address that starts with "192.168.0" to connect - for example "192.168.0.1" or even "192.168.0.113" etc. Perhaps less confusingly, you could enter the line above like so: 

	host    all     all    192.168.0/24         md5

If you want to list individual explicit addresses one at a time for an even more specific security setup, enter lines like so: 

	host    all     all     192.168.0.3/32      md5
	host    all     all     192.168.0.4/32      md5
	host    all     all     192.168.0.5/32      md5

The "/32" means that all bits of the address are relevant. To test your server allows remote connections, try to connect from another machine on your network (one that has it's address listed as above in the hba file: 

	psql -h SERVERADDRESS -d DATABASENAME -U USERNAME

You will probably have to enter the password for user USERNAME. Hopefully the connection will go OK and you should be able to query tables from the logged in machine.

Installing PostgreSQl from Source Code

For another day...

Misc Notes

Installation/Setup: 

	* Rememeber initdb encoding
  • Install postgres and tools 
	* libwxbase2.6.0
	* libwxgtk2.6.0
	* pgadmin3-data
	* postgresql
	* phppgadmin

PSQL commandline: 

	su -u postgres psql dbname