Tag Archives: ubuntu

Install PostgreSQL on Ubuntu

Installing the postgresql package

You would think that installing PostgreSQL on Ubuntu (actually as I write I’m using Mint 14, but it is still Ubuntu based, so bear with me on that one) is as simple as opening a terminal and typing:

$ sudo apt-get install postgresql

While the above command will install the latest PostgreSQL database server on your machine, you still need to configure some parts for it to become fully functional.

Disclaimer: The steps described below are intended to be used on a local development machine, production servers should be configured with a more tight security setup in mind. Please refer to the PostgreSQL Server Administration Documentation for further information.

Allowing TCP/IP connections

There’s not much use for a database server if nobody can connect to it, is there? And most clients will want to connect via TCP/IP, which is disabled by default. So let’s enable it.

Edit the /etc/postgresql/9.1/main/postgresl.conf file (please replace the 9.1 with the appropriate version). I really like vim, but it is not installed by default on most distros. If you’re not comfortable with it, use gedit (or pluma if you also use Mint wink wink) with gksudo.

$ gksudo gedit /etc/postgresql/9.1/main/postgresl.conf

Locate the line that starts with #listen_addresses – it should look like:

#listen_addresses = 'localhost'

And just remove the leading ‘#’ char, so that it will look like:

listen_addresses = 'localhost'

The file has a lot of comments that help understand what goes on there, but what we’re doing is that this server will accept TCP/IP connections originated on ‘localhost’, that is, the same machine running the PostgreSQL server.

Save the file and leave the editor.

Establishing user permissions

Now that we can connect to the PostgreSQL server, we need a user to do so. Since we’re on a development machine and want to keep it simple, we’re gonna use the already present postgres (yes, I wrote it right, the user does not have the ql suffix) user.

First, let’s connect to the template database (we’re impersonating the postgres user and using psql to connect to the template1 database):

$ sudo -u postgres psql template1

A prompt like this should show up:

psql (9.1.9)
Type "help" for help.


Now we’re gonna create a passwor for the postgres user:

template1=# \password postgres

Type and confirm the password and quit the psql program (\q at prompt).
Now we’re gonna edit the /etc/postgresql/9.1/main/pg_hba.conf file and add the following line:

local   all             postgres                                md5

Just go ahead and add it at the bottom of the file, save it and quit the editor.
Now, just one more step:

Restarting the Server

To apply the settings, you should restart the server by typing:

$ sudo service postgresql restart

You should see something like:

* Restarting PostgreSQL 9.1 database server                                [ OK ]

And you’re good to go!

Testing the connection

Just type (we’re using the psql program with the postgres user. If the -U postgres is ommited, the psql will try to use the current user as the database user, and therefore will fail) :

$ psql -U postgres

You should be prompted for a password – that is the one you’ve typed and confirmed for the postgres user.

If the following error is displayed:

psql: FATAL:  Peer authentication failed for user "postgres"

Just add the -W switch to the command:

$ psql -U postgres -W

and you will be prompted for a password. After you type it, you will be presented with the psql prompt, where you can issue commands and queries to your PostgreSQL database.

Share Button

MySQL Job failed to start on Ubuntu

That’s one reason (of many) for this blog title (grinding, ranting…)

Today, when I tried to access the blog I got a nice “Database connection error”. I really hadn’t done anything unusual (other than an apt-get upgrade, but I don’t really think it should fall at the “unusual” category), so I logged on my VPS provider and restarted the server, only to have the same error displayed.

(sigh here)

As you guessed by the post title, I use MySQL, so I logged on my machine (my VPS offers me full ssh access, which is both a bless and a curse) and found out (obviously) that the mysql daemon was not started. Tried to restart it with (I know, I’m both lazy and stubborn):

# service mysql start

and had the informative (not!) error displayed:

start: Job failed to start

That’s it! The only thing printed on the console after I tried to start the service… Since the console was not helping me, I decided to take a look a the logs at /var/log. Both the mysql.err and mysql.log files were empty. The /var/log/mysql/error.log file was also empty. Now, that’s helpful.

So, I Googled the problem and found some hints pointing to permission errors regarding the place where the mysqld.lock file is stored. I looked at /etc/mysql/my.cnf and at the directory /var/run/mysqld (that’s where the /etc/mysql/my.cnf file told me it should be), but the permissions were ok, mysql was the owner of the directory and it had 755 permission.

So I came back to /var/log and noticed that /var/log/mysql directory and that both the /var/log/mysql.err file were both root owned. Hm… Weird… That’s what ‘ls -l’ returned:

drwxr-s--- 2 root root 4096 Mar 20 07:35 mysql
-rw-r----- 1 root root    0 Mar 6  19:53 mysql.err

I then changed the owner of both the file and directory:

# chown -R mysql:adm /var/log/mysql
# chown mysql:adm /var/log/mysql.err

Maybe now some error would be written to the logs… And it really did after I tried to start the service once more; at least now the /var/log/mysql/error.log file had some content, and upon inspecting it I saw a really suspect line:

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
130320 18:40:13 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

Ok, so either the ./mysql/plugin.frm file really did not exist or its permissions were wrong. Now I had to find out where this file should be. Upon inspecting once again the /etc/mysql/my.cnf file, I saw some directories worth looking at:

datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql

And that was it, the /var/lib/mysql directory had root as owner and 700 permissions:

drwx------ 6 root root 4096 Mar 20 18:46 mysql

One more chown:

# sudo chown -R mysql:root /var/lib/mysql

And I was able to start mysql again!


Share Button

MySQL Server on Ubuntu 12.04

Install mysql-server via apt-get:

$ sudo apt-get install mysql-server

Set the root password

Activate MySQL:

$ sudo mysql_install_db

Finish the setup by running:

$ sudo /usr/bin/mysql_secure_installation

If you have setup the root password on the first step, confirm it and answer “No” to the change root password question, and accept the default answer for the rest (just press Enter).

To access the mysql console:

$ mysql -u root -p

Here you can issue any other command you want to…

That’s it!

Share Button

Tomcat 6 on Ubuntu 12.04

Supposing you don’t want to install the Tomcat version that is packaged on Ubuntu (if you need a differente version), this is what you should do:

Download Tomcat here to your home dir.

Add the Tomcat user and group

$ sudo groupadd tomcat
$ sudo useradd -g tomcat -s /usr/sbin/nologin -M tomcat

Go to /usr/local dir and unzip (beware of the file name, you may have downloaded a different/newer version), give appropriate permissions and create a simlink to ‘tomcat’

$ cd /usr/local
$ sudo tar -zxvf ~/apache-tomcat-6.0.36.tar.gz
$ sudo chown -R tomcat. /usr/local/apache-tomcat-6.0.36
$ sudo ln -s apache-tomcat-6.0.36 tomcat

Check if you can start it:

$ sudo /usr/local/tomcat/bin/startup.sh

There should be something like this on the screen:

Using CATALINA_BASE: /usr/local/tomcat
Using CATALINA_HOME: /usr/local/tomcat
Using CATALINA_TMPDIR: /usr/local/tomcat/temp
Using JRE_HOME: /usr
Using CLASSPATH: /usr/local/tomcat/bin/bootstrap.jar

Open the following addres on your web browser: http://localhost:8080 (or replace localhost with your server name/ip address) and verify that Tomcat started properly (a pretty page should be displayed telling you that you’ve installed Tomcat succesfully, or something like that).

Now shut it down:

$ sudo /usr/local/tomcat/bin/shutdown.sh

The same messages that appeared when you started it up should appear again (I know, I know, pretty weird).

Now you can use the following script to start and stop Tomcat. Download it to your home directory and place it at /etc/init.d

$ sudo mv tomcat /etc/init.d

To start/stop as the tomcat user, run the following command (it’s already on the script):

$ sudo su -s /bin/bash tomcat -c /usr/local/tomcat/bin/startup.sh
Share Button