Archive for the ‘PostgreSQL’ Category

Install PostgreSQL 8.3 in Ubuntu Server 8.04. The simple way…

February 22nd, 2009

Lets say that we will install a fully functional PostgreSQL Server in 8 steps. This is how simple are things these days with Linux. Compared to the old times this is a peace of cake. Just bear in mind that in this article I am not including how to install Linux (Ubuntu 8.04). If you haven’t install Ubuntu Server before here is the link to a very acceptable tutorial, after it is done come back and continue.

1. Install Linux. Ubuntu 8.04 or greater…
You need to configure you static ip address and hostname. Check the tutorial.

To Connect to your Linux server you can use putty tray from windows or the terminal from your Linux workstation.

          ssh username@xxx.xxx.xxx.xxx

2. Login as root.
If you need to change the root password login as the user you already created when you installed Ubuntu and enter this command.

         sudo passwd

It will ask you for you password and then for the new UNIX password twice.

3. Install PostgreSQL and all packages needed.

          apt-get install vim-full postgresql-8.3 libpq-dev postgresql-8.3-slony1 pgadmin pgpool2 postgresql-contrib postgresql-dev-8.3

4. After installation is done go edit /etc/postgresql/8.3/main/pg_hba.conf in order to grant access to all users from any network address on your VLAN.*

For editing files from the terminal we will use vi the most common and pretty powerfull linux editor. If you are new with it use this command list to help you. And remember this Linux rule:

…” Always make backup of your configuration file before playing with it “…

   #Backup
   cp /etc/postgresql/8.3/main/pg_hba.conf /etc/postgresql/8.3/main/pg_hba.conf.backup
 
   #Restore
   cp /etc/postgresql/8.3/main/pg_hba.conf.backup /etc/postgresql/8.3/main/pg_hba.conf

this will save you from headaches.

   vi /etc/postgresql/8.3/main/pg_hba.conf

Add a line like this one on the connection section.

   host     ALL       ALL         xxx.xxx.xxx.0/24      md5

Note: – Replace the xxx.xxx.xxx by the first three octets of your vlan ip address.
– Do not forget to take into account what is your netmask. Depending on the netmask applied to the vlan or IP address you want to grant access to the server you would need to change the /24. Here is a link that can help you identifying yours.

Quit vi and Save.

5. Edit /etc/postgresql/8.3/main/postgresql.conf to make the server listen using the its static ip address. By default it will only listen to the loopback adapter 127.0.0.1.

Backup applies to this one too.

   vi /etc/postgresql/8.3/main/postgresql.conf

Look for a line with the Listen variable and set it to the server static ip address.

   LISTEN = xxx.xxx.xxx.xxx

Quit vi and Save.

6. Restart the PostgreSQL service in order for the changes to take effect.

   /etc/init.d/postgresql restart

7. Now you need to change the “postgres” account in order to access the server with administrative rights.

su postgres

Enter this command in the terminal.

   psql -d template1

After you are connected enter this statement and replace the ‘new password‘ by the password you want to use.

   ALTER USER postgres WITH password 'new password';

Quit the session.

   \q

8. It is ready. Enjoy it.

In order to connect to it using C# you can use NpgSQL and to manage it you can use pgAdminIII. They are all free and they are pretty stable too.

Tags: , , , ,
Posted in Linux, PostgreSQL, Ubuntu | Comments (0)