[opencms-dev] 6.2 Beta - Successfully Installed On PostreSQL 8.1.3

[Andreas Haumer]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

Ivan Codarin schrieb:
> Dear All,
> today I managed sucessfully the installation of Opencms 6.2 beta on
> PostgreSQL 8.1.3.
> 
I did install OpenCMS 6.2beta on PostgreSQL 8.1.3 yesterday for
the first time (I only used OpenCMS 6.0.x before) and also had
some troubles initially.

> I installed it without any modification to drivers/scripts... the only
> thing that could be a misunderstanding is the step in the Setup Wizard
> where you are asked for:
> 
I had some problems with the original OpenCMS 6.2beta setup script
at the "database_creation" step, mostly caused by a problem I see
quite often with PostgreSQL: As long as someone uses the "template1"
database, no one can create a new database, because PostgreSQL reports:

"template1 is being accessed by other users"

Is that the same problem you had when you first tried to install
OpenCMS 6.2beta? (I could not find your previous post)

>
> -----------------
> *Setup Connection* : I put my "global administrator" of the postgresql
> node. This user MUST EXIST on the DATABASE
> *TemplateDB*: for Postgresql 8.1.x is "postgres" for earlier versions is
> "template1" (=the default)
> -----------------
> *OpenCms Connection*: This is the postgres' user for Opencms
> connections. THIS USER MUST BE A NEW USER (the user have not to be
> already present on the DATABASE), the wizard will create it....
> 
> -----------------
> *Connection String* : The connection string have not to include the
> database's name (i.e. :dbc:postgresql://localhost:5432)
> 
> *Database name*: THIS MUST BE THE SAME NAME OF THE "OPENCMS CONNECTION"
> USER. This is the misunderstanding (in my opinion). This happens also in
> the previous versions of Opencms 6.
> The database (as for the OpencmsUser) MUST NOT EXIST!
> -----------------
> 
> *So pay attention to the fact that the Opencms' Postgresql User HAVE TO
> BE THE SAME OF THE Database Name.*
> 
> Than check the two checkboxes "Create user" and  "Create tables".
> 
> Let me know if also for you this solves the setup problems.
> In the next days I will try this installation of opencms, if all the
> things will run smoothly I can try to change the setup dialogbox for
> postgresql.
> 

I tried your recipe on two different installations and it did work
for me, too. The most critical point seems to be to specify database
"postgres" as Template DB and not to use database "template1"

If you use template database "template1", OpenCMS can not create the
required "opencms" database because the "template1" database is always
busy and PostgreSQL denies any attempt to create a new DB with the
following error:

ERROR:  source database "template1" is being accessed by other users

I think I found the reason for the problem, though, and IMHO it
does not depend on the PostgreSQL version, but rather on the
OpenCMS version: With OpenCMS 6.2beta there is now an additional
setup step "4a" called "database validation". This step did not
exist in OpenCMS 6.0.x (as far as I know) and it is responsible
for the "busy template1" problem.

In step 4a the setup-program tries to validate the database
connection parameters specified by the user in step 4.
It does so by connecting to the database as specified.

After step 4a is finished, the database connection is still
active (DB pool?). So, if you specified "template1" as
template DB, after step 4a the "template1" database is
"busy" and database creation in step 5 fails with the
error mentioned above.

If you specify any other database as "Template Db" (like
database "postgres" as you suggested), database validation
in step 4a works fine and it does not lock database "template1"
either, which then can be used for database creation in step 5

In that case, after step 5 is finished, I see the following
PostgreSQL server processes:

15175 pts/1    S      0:00 postgres: postgres postgres 127.0.0.1(46413) idle
15179 pts/1    S      0:00 postgres: opencms opencms 127.0.0.1(46417) idle

Here you can see the database connection "postgres", which was
used in step 4a for database validation, still hanging around,
and also database connection "opencms" which was used in step 5
and is used later on by OpenCMS

As an alternative, one could change the PostgreSQL specific
setup JSP file

webapps/opencms/setup/database/postgresql/step_4_database_setup.jsp

and change line

        String nextPage = "../../step_4a_database_validation.jsp";

to contain instead

        String nextPage = "../../step_5_database_creation.jsp";


This skips the "database validation" step and this is the way
OpenCMS version 6.0.x used to work. In this case you can
specify database "template1" as Template DB without problems,
at it is only used in the "database creation" step.

I'm not sure what is the best way to finally solve this problem.
One way would be to explicitely close the database connection
after step 4a (database validation) or before execution of step 5
(database creation) to avoid database "template1" being busy.
I currently do not know how to do this, though.

Another way would be to explicitely disallow the use of database
"template1" as "Template DB" for the database validation step.

Also the wording for input field "Template Db" in the database
setup screen for PostgreSQL should be changed. After all, this
information it isn't used as "template" database anyway, but
rather as "test database" for initial database connection
validation.

Comments?

- - andreas

- --
Andreas Haumer                     | mailto:andreas at xss.co.at
*x Software + Systeme              | http://www.xss.co.at/
Karmarschgasse 51/2/20             | Tel: +43-1-6060114-0
A-1100 Vienna, Austria             | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFEDWKLxJmyeGcXPhERAoi3AJ4ut11XxpNZZivEaSZUIhAt/gQqJACeLt0J
DEl7PaWVl9i4yCN98Z8UXxc=
=FlWS
-----END PGP SIGNATURE-----