Postgresql installation and configuration - INRA Montpellier

pridefulauburnData Management

Dec 16, 2012 (4 years and 9 months ago)

144 views

Install
for
LINUX
(Ubuntu/Debian style)
You must have “sudo” rights for this installation. If this is not the case, ask your system

administrator.
Postgresql installation and configuration
Transfer the
.sql files on your server.
Open a shell console
on your server, or
connect by
a ssh session
.
Install postgresql (8.4
is
better,
but
8.3
also works
)
sudo apt-get install
postgresql
-
8.4
Run psql as postgres
user:
sudo -u postgres psql
T
hen, in psql, t
ype


(\q means “quit”)
:
CREATE ROLE seq454 WITH CREATEDB LOGIN ENCRYPTED PASSWORD 'seq454';
\q
With your own rights (no sudo),
run psql
and connect
to sesame
as
user
seq454:
psql -h localhost -U seq454 -W postgres
Type:
CREATE DATABASE sesame WITH OWNER = seq454 ENCODING = 'UTF8';
\q
Now connect to database sesame

:
psql -h localhost -U seq454 -W sesame
ONLY
if you use Postgresql
8.3
:
Parse the sql file (check path !) with
the following command

inside psql
:
\i sesame_addon_for8.3.sql
Build the structure and pre-fill it
by parsing sql files
with the following commands
inside psql
:
\i sesame_create.sql
\i sesame_base_data.sql
\q
Other softwares installation
You must install at least blast and muscle. In a console, type:
sudo apt-get install muscle blast2.
Check the complete path to these programs by typing:
which muscle blastall formatdb
Note their full paths !
Tomcat6
installation & configuration
In your console type:
sudo apt-get install tomcat6
tomcat6-admin
tomcat6-docs
tomcat6-user
Once installed

:
cd /etc/tomcat6
sudo nano tomcat-users.xml
Before the last line </tomcat-users>, insert this line

(replace XXXXX and YYYYY by a username

and a dedicated password in
plain
text):
<user username="XXXXX" password="YYYYY" roles="admin,manager" />
Save (Ctrl-o) and quit (Ctrl-x).
Then

, in the shell

:
cd /etc/tomcat6/policy.d
sudo nano 04webapps.policy
Append these lines
at the end of the file
:
grant codeBase "file:${catalina.base}/webapps/SESAME/-" {
permission java.security.AllPermission;
};
Save (Ctrl-o) and quit (Ctrl-x).
We will now increase the memory dedicated to the java machine of tomcat.
cd /etc/
init.d
sudo nano
tomcat6
Modify the java_opts area as following:
if [ -z "$JAVA_OPTS" ]; then
# JAVA_OPTS="-Djava.awt.headless=true -Xmx128M"
JAVA_OPTS="-Djava.awt.headless=true -Xmx1024M -XX:MaxPermSize=1024M"
fi
Note: You might have to increase the max sizes (eg. 2048 instead of 1024) if huge queries occurs in

the future.
Restart tomcat6

:
sudo /etc/init.d/tomcat6 restart
From a web browser (
eg.
firefox), open the url

:
http://localhost:8080/manager/html
If you don't open the browser from the same computer, replace localhost by the name or ip address

of your tomcat server.
Enter in the dialog your username and password (XXXXX and YYYYY
as above
).
In the lower part of the admin page, click on the button after «

Select WAR file to upload

».
Pick
the

SESAME.war file
from your computer
, then upload it.
SESAME configuration
Open SESAME in your browser

:
http://localhost:8080/S

ESAME

The initial login is “admin” and password is “admin” too.
Clic
k
on the “Administration” tab.
Create your first user (you) as administrator: In the “Users” area, clic
k
on the button “New” in the

form, then enter a username, a password and check “Administrator”. Save.
If the creation succeeded, you can now delete the “admin” account.
Click
on the row with “admin”

as username and then on the button “Delete”. Confirm. You are now the administrator !
Logout (upper-right corner) and login with your brand new account.
Create your first project:
In the column “Projects” click on “
New”, enter a project name, and save.
Allow you to use this project by creating an entry in the access control list:
Click on “
New”, get

your project in the “Project” drop-down, your name in the “User” drop-down and save.
Finally, configure SESAME
keys
: Enter the correct full paths for MUSCLE, BLASTALL and

FORMATDB by
clicking
the row, then “Update”, set the value
to the full path including the

executable itself
and then save. Set the TEMP dir the same way
(usually /tmp)
.
Check that “USE_MAFFT” and “USE_BLAST_+” are set to “no”.
If you wish to use blast+ and / or mafft, install them and set keys accordingly, and set USE_MAFFT

and /or/ USE_BLAST_+ to “yes”.
You are now ready for using SESAME, congratulations !
Security improvement
(optional)
Your database password is not safe. You might change it by connecting by psql and typing:
ALTER ROLE seq454 CHANGE ENCRYPTED PASSWORD 'zzzz';
You then must also change it in SESAME by editing the file

/var/lib/tomcat6/webapps/SESAME/WEB-INF/classes/check454.properties

and replacing the encrypted password by the new one in clear text. This last step should be repeated

if you upgrade or reinstall SESAME.war.
Once done,
stop and start
SESAME
from Tomcat manager.