Updating Postgresql 8.4 to 9.1

[Lipplab]

Hallo everybody
I found some problem I cannot solve on my own that's why I turn to the forum. First of all I inherites this system so initial configuration is a bit fuzzy because most of it I needed to find out by trial and error.
I wanted to update our database server to version 9.1. Installing it with apt-get was not a problem. After that I tried to shut down the old postgres sever but OMERO than didn't work any longer. So I re-read the manual for updating on the postrgresql homepage and found I did not transfer the database correcty. So far so good. When I now tried to do the necessary steps I found out DBuser is just virtual. So i cannot login as the user to call the commands. Next step I created this user, new Problem is the user has no rights on the postgresql server files and omero folders, just root has. What i want to know is it really necessary to give the DBuser these rights.

System Ubuntu 12.04 LTS on IBM e-Server
OMERO 4.4.8
ice 3.4
postgresql 8.4/9.1

Thanks for any advice

[kennethgillen]

Hi,

Sorry to hear you're having problems. You should take a dump of the OMERO database from your 8.4 instance, and restore it into 9.1.

We have documentation that takes you through an OMERO server upgrade, and also a section on backing up an restoring and OMERO server.

Both of these will take you through the process you'll need to follow which will be roughly like

* Bring up Postgres 8
* Take a dump of the OMERO database
* Bring down Postgres 8
* Bring up Postgres 9
* Create the OMERO postgres user
* Restore the dump previously taken into Postgres 9
* Start OMERO

You mention database user permissions: the installation steps in the Linux OMERO installation documentation may help you here. Especially the following:

Code: Select all

Set up PostgreSQL:

$ sudo -u postgres createuser -P -D -R -S db_user
$ sudo -u postgres createdb -O db_user omero_database
$ sudo -u postgres createlang plpgsql omero_database

Please be in touch if you are still having problems.

Best,

Kenny

[Lipplab]

Thanks for your reply Kenny,
so to the root of my Problem. I cannot start 9.1 because it says data was created with 8.4 and if i try to run pg_update i fail beause i try to do it as root which is prohibited. If i try to use somehow the db super user with -u command it says i have no rights on the folder. Unfortunatly the hint you gave me is not helpfull at all because yes i konw we habe a DBuser and a DBpass that should have all the rights on the db but thats it. Cannot run server because old DB files cannot be upgraded. If i'd try to setup a new db cluster would that help??

Best regards Benjamin

[kennethgillen]

Hi Benjamin,

You shouldn't need to give any more access to the database files than Postgres 8 had before. You just need to execute the pgdump as the Postgres super-user account, against your Postgres 8 instance, provided it's still there. I read that apt-get won't by remove a Postgres 8 installation by default, but will install Postgres 9 along-side. [1] That link provides a command which should tell you what instances are running (pg_lsclusters).

The Postgres website discusses using pg_dump to migrate between version 8 and 9.

If you can bring your Postgres 8 instance back on-line, you should be able to run the pg_dump command from the root user via sudo, as follows:

(assuming 'postgres' is your database superuser account)

Code: Select all

sudo -u postgres pg_dump -Fc -f before_upgrade.db.dump omero_database

I hope this is of some use. If not, please stay in touch and update us.

[1] http://askubuntu.com/questions/285232/how-do-i-downgrade-postgresql

Best,

Kenny

[Lipplab]

Thanks again Kenny,
so what you say is I cannot do the upgrade via pg_upgrade, without the dump. This is one of the migration descriptions from 8 to 9 on the postgresql website. The dump was allready done by me before I tried the update so this is no major problem. I'll give it another go as soon as I made some progress with my thesis and than feed back!

Best
Benjamin

[Lipplab]

Hallo Kenny,
Again the user never existed for the os. So he has no rights on the folders etc.
First thing i cannot create a new db as described

$ sudo -u postgres createuser -P -D -R -S db_user
$ sudo -u postgres createdb -O db_user omero_database
$ sudo -u postgres createlang plpgsql omero_database

Because postgresql is not running since the db cluster does not exist. But if I try to use createdb I am either root for which it is prohibited, or i am the newly created user DBuser who has no rights on the data folder in our case
/var/lib/postgresql/9.1.
There the cat bites its tail how we say in Germany. If you tell me create the user give him rights to the folders, because there is no other way, i'll do it. Somehow I'm to stupid for linux.

Thanks i hope i made my problem i bit more clear or i fail to see your point.

Best

Benjamin

[Lipplab]

So some more info.
If i use pg_lsclusters i get

8.4 main 5432 down unknown /var/lib/postgresql/8.4/main And
9.1 main 5432 down unknown /var/lib/postgresql/8.4/main

As you can see the owner is unknown. Which is i think a problem no?

Best Benjamin

[rleigh]

Your immediate problem here is your cluster configuration. Here's what you should be getting (for a different version, but it should be the same; just replace 9.3 for 9.1):

Code: Select all

Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log


Note that in your output, you show that the 9.1 cluster data directory is /var/lib/postgresql/8.4/main. This is wrong. PostgreSQL 9.1 can not use the 8.4 data, ever.

The owner is, as far as I can tell, the owner of the cluster data on the system:

Code: Select all

% ls -ld /var/lib/postgresql/9.3 /var/lib/postgresql/9.3/main
drwxr-xr-x  3 postgres postgres 4096 Jun 30 21:33 /var/lib/postgresql/9.3
drwx------ 15 postgres postgres 4096 Oct  8 08:40 /var/lib/postgresql/9.3/main


So my "main" cluster for version 9.3 is owned by the postgres system user and group; this is unrelated to the users inside the database itself. You might want to do a "sudo chown -R postgres:postgres /var/lib/postgresql" if the ownership is incorrect here.

Note that both of your existing clusters are using the same port number; this is also wrong--you can't have two database servers using the same port since this is not possible.

Suggested actions:

• Edit your old cluster configuration (/etc/postgresql/8.4/main/postgresql.conf) and set port = 5431 (or any value other than 5432).
• Edit your new cluster configuration (/etc/postgresql/9.1/main/postgresql.conf) and set data_directory = '/var/lib/postgresql/9.1/main'.

This should get you back to a sensible starting point, and you should at this point be able to start up the 9.1 server, create an omero database, any needed users, and then restore the 8.4 backup into the running 9.1 server.


Regards,
Roger

[rleigh]

Also, please check that the postgresql system user exists, and that the ownership of the files and directories owned by postgres is correct. On my system, for example:

Code: Select all

% getent passwd postgres
postgres:x:120:129:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
% getent group postgres
postgres:x:129:
% ls -ld /var/lib/postgresql /var/lib/postgresql/* /var/log/postgresql /var/log/postgresql/* /etc/postgresql /etc/postgresql/* /etc/postgresql/*/*
drwxr-xr-x 1 root     root       12 Jun 30 21:33 /etc/postgresql
drwxr-xr-x 1 postgres postgres    8 Jun 30 21:33 /etc/postgresql/9.3
drwxr-xr-x 1 postgres postgres  142 Aug 16 01:48 /etc/postgresql/9.3/main
drwxr-xr-x 4 postgres postgres 4096 Jun 30 21:33 /var/lib/postgresql
drwxr-xr-x 3 postgres postgres 4096 Jun 30 21:33 /var/lib/postgresql/9.3
drwxrwxr-t 2 root     postgres 4096 Oct  6 22:52 /var/log/postgresql
-rw-r----- 1 postgres adm      1707 Oct  8 08:40 /var/log/postgresql/postgresql-9.3-main.log
-rw-r----- 1 postgres adm       569 Oct  6 22:52 /var/log/postgresql/postgresql-9.3-main.log.1
-rw-r----- 1 postgres adm       770 Aug  5 08:36 /var/log/postgresql/postgresql-9.3-main.log.10.gz
-rw-r----- 1 postgres adm      1158 Oct  4 20:54 /var/log/postgresql/postgresql-9.3-main.log.2.gz
-rw-r----- 1 postgres adm       344 Sep 23 08:18 /var/log/postgresql/postgresql-9.3-main.log.3.gz
-rw-r----- 1 postgres adm       798 Sep 15 12:51 /var/log/postgresql/postgresql-9.3-main.log.4.gz
-rw-r----- 1 postgres adm       681 Sep  8 09:39 /var/log/postgresql/postgresql-9.3-main.log.5.gz
-rw-r----- 1 postgres adm       753 Sep  1 08:17 /var/log/postgresql/postgresql-9.3-main.log.6.gz
-rw-r----- 1 postgres adm       434 Aug 25 07:32 /var/log/postgresql/postgresql-9.3-main.log.7.gz
-rw-r----- 1 postgres adm      1471 Aug 19 21:47 /var/log/postgresql/postgresql-9.3-main.log.8.gz
-rw-r----- 1 postgres adm       458 Aug 11 17:29 /var/log/postgresql/postgresql-9.3-main.log.9.gz


If the postgres user and/or group do not exist, or the directories are not owned by them, then that would explain where the "unknown" in the pg_lsclusters command output came from. Before you make any more changes, please let me know the output of the above three commands on your system, and we can then advise you how to correct things accordingly.

Regards,
Roger Leigh