Setting up PostgreSQL databases on Ubuntu 10.04.2 LTS

Classic Servoy
1

Hi all,

I’m setting up a new Servoy install on a headless Ubuntu 10.04.2 LTS server. Before I can “Add Database Server” in :8080/servoy-admin I need to create the new database in PostgreSQL. I am using the instance of PostgreSQL which comes bundled in the Servoy .jar installation. However, I am encountering problems because Ubuntu does not seem to recognise a PostgreSQL user. For example, if I type:

/usr/local/bin/application_server/postgres_db/bin/createdb <new_db_name>

I get the following error:

createdb: could not connect to database postgres: FATAL: role "servoy" does not exist

So using sudo, if I then type:

sudo /usr/local/bin/application_server/postgres_db/bin/createuser servoy

I get this error:

createuser: could not connect to database postgres: FATAL: role "root" does not exist

What is the default ‘role’ for the database postgres when using the bundled installation of PostgreSQL? Is there some config file I’ve overlooked somewhere?

Thanks,

Phil

2

Hi Phil, I suggest you to install Postgres on your Ubuntu server separately from servoy. It’s a lot easier to keep it up to date and start it and stop it at will.
You can do that the Ubuntu way but you first have to add an extra repository to your apt sources.list:

deb http://archive.canonical.com/ lucid partner
deb http://ppa.launchpad.net/pitti/ppa/ubuntu lucid main
deb-src http://ppa.launchpad.net/pitti/ppa/ubuntu lucid main

After that you can install postgres-9 with the usual commands:

sudo apt-get update
sudo apt-get install postgresql-9.0

Updating is as easy as:

sudo apt-get upgrade
3

Thanks ngervasi. I’ve now installed Postgresql separately (version 9.0 package could not be found so I ran sudo apt-get install postgresql which installed version 8.4). I then reinstalled Servoy.

When I try to run servoy_server.sh I get the following error in the log file:

0 [main] ERROR com.servoy.j2db.util.Debug - Configuration file is missing (you might have choosen to not install a database from installer), place a valid config file in the application_server directory (from your developer environment, for example)

I guess this is correct because I set the .xml install helper file not to install the database. What file do I now need to add into the application_server directory?

Best wishes,

Phil

4

I guess it’s the servoy.properties file but it should have been created by the installer, try to copy that file from your developer installation but it sounds strange to me.
How did you install exactly?

5

I installed at the command line using:

java -jar servoy_installer.jar install.xml

Here’s my install.xml:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<AutomatedInstallation langpack="eng">
<com.izforge.izpack.panels.HelloPanel id="UNKNOWN (com.izforge.izpack.panels.HelloPanel)"/>
<com.izforge.izpack.panels.LicencePanel id="UNKNOWN (com.izforge.izpack.panels.LicencePanel)"/>
<com.izforge.izpack.panels.TargetPanel id="UNKNOWN (com.izforge.izpack.panels.TargetPanel)">
<installpath>/usr/local/bin</installpath>
</com.izforge.izpack.panels.TargetPanel>
<com.izforge.izpack.panels.PacksPanel id="UNKNOWN (com.izforge.izpack.panels.PacksPanel)">
<pack index="0" name="Application Server" selected="true"/>
<pack index="1" name="Developer" selected="true"/>
<pack index="2" name="JDBC Driver Files" selected="true"/>
<pack index="3" name="Database" selected="false"/>
<pack index="4" name="Application Server Service" selected="true"/>
<pack index="5" name="Example Files" selected="true"/>
<pack index="6" name="Headless Client Sample" selected="true"/>
<pack index="7" name="Documentation Files" selected="true"/>
<pack index="8" name="Beans" selected="true"/>
<pack index="9" name="Plugin Files" selected="true"/>
<pack index="10" name="Look and Feel Files" selected="true"/>
<pack index="11" name="PDF support package" selected="true"/>
</com.izforge.izpack.panels.PacksPanel>
<com.izforge.izpack.panels.UserPathPanel id="UNKNOWN (com.izforge.izpack.panels.UserPathPanel)">
<UserPathPanelElement>/home/servoy/servoy_workspace</UserPathPanelElement>
</com.izforge.izpack.panels.UserPathPanel>
<com.izforge.izpack.panels.InstallPanel id="UNKNOWN (com.izforge.izpack.panels.InstallPanel)"/>
<com.izforge.izpack.panels.FinishPanel id="UNKNOWN (com.izforge.izpack.panels.FinishPanel)"/>
</AutomatedInstallation>

You were absolutely right, it was servoy.properties which was missing. I’ve now copied that into the /application_server directory and tried to restart. I am now seeing some output into servoy_log.txt:

Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "DBA"

I think these lines are now incorrect in servoy.properties as they refer to the bundled PostgreSQL instance:

nativeShutdownLauncher=%%user.dir%%/postgres_db/bin/pg_ctl|stop|-D|database|-l|postgres_db/postgres_log.txt
nativeStartupLauncher=%%user.dir%%/postgres_db/bin/pg_ctl|start|-D|database|-l|postgres_db/postgres_log.txt

Should I change these paths or remove them?

Thanks,

Phil

6

Hello

You have to create the user DBA on postgresql
From terminal :

sudo -u postgres psql template1

CREATE USER DBA WITH PASSWORD 'yourpassword'  CREATEDB CREATEROLE;

\q

Now probably you need to create the db on this serve :
always using terminal:

sudo -u postgres psql template1

CREATE DATABASE servoy_repository WITH ENCODING='UTF8'  OWNER="DBA"  CONNECTION LIMIT=-1;
/q

You can create also the other db.
Concerning servoy_properties you can prepare it on you developer an the copy to server.

Usually I install xfce on Ubuntu Server, is useful sometime to have a graphic environment

7

Thanks for you help albace :D

I managed to connect to PostgreSQL using pgAdmin III on my Mac in the end then set up the required databases. After that, I had to modify the settings in servoy.properties in order for Servoy to connect to the relevant databases.

Best wishes,

Phil