How to upgrade Cloudera Manager Postgres database

Question

I have Cloudera Manager 5.9 installed on Ubuntu 12.04 with embedded postgres database. I upgraded Ubuntu to 14.04 using do-release-upgrade. In the process, Postgres also got upgraded from 8.4 to 9.3. Now when I try to start the CM database via:

# sudo service cloudera-scm-server-db start

I get the following error in CM db.log:

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.3.15.

How do I get past this? I have looked at a lot documentation which talks about dumping the postgres database via pg_dump and restoring via psql, but I don't know how this applies in the context of cloudera manager, especially when the database is not coming up.

On Ubuntu 12.04 when everything is working, I believe the dump can be taken like this:

#pg_dump -h localhost -p 7432 -U scm > /tmp/scm_server_db_backup.$(date +%Y%m%d)

I can try to create an empty database and restore the dump to this one using psql. But how do I configure cdh to point to this database?

Answer 1

I am able to resolve this problem using the following process:

Step 1: Take a dump of the running postgres database on Ubuntu 14.02

# sudo su

# su - postgres

# pg_dump -h localhost -p 7432 -U scm scm > scm.sql

Step 2: Upgrade Ubuntu to 16.04

# sudo do-release-upgrade

...

Step 3: Rename the old data directory

# mv /var/lib/cloudera-scm-server-db/data/ /var/lib/cloudera-scm-server-db/data9-3

Step 4: Restart cloudera-scm-server-db service. This will create an empty database which we will populate using the backup taken in step 1

# sudo service cloudera-scm-server-db restart

Step 5: Now restore the database

# sudo su

# su - postgres

# psql -h localhost -p 7432 -U scm

(password can be obtained like this: grep password /etc/cloudera-scm-server/db.properties)

scm> \i scm.sql

Step 6: Now restart cloudera-scm-server service:

# sudo service cloudera-scm-service restart

Answer 2

As you suggest, you need to find a way to "convert" 8.4 data files to 9.3 data files. Using pg_dump will need a working PostgreSQL 8.4 instance. So, basically you need a working Postgresql 8.4 (think VM or Docker), then copy your existing 8.4 fiels to that VM/Docker, have that VM/Docker provide a plain-text dump [plain SQL, so compatible with any version), restore that plain-text dump to your 9.3 instance).

You can try :

• Create a VirtualMachine or a Docker instance with Postgresql 8.4
  deployed.
• Locate the main data directory (usually /var/lib/postgresql/8.4/main on Ubuntu, but this might differ) of
  your upgraded Cloudera machine. Backup this directory and keep in
  safe.
• Stop PostgreSQL on your VM/Docker if necessary. Locate the main data directory (usually /var/lib/postgresql/8.4/main on Ubuntu, but
  this might differ).
• Replace the previous found directory by a copy of your existing 8.4/main content (the one on your upgraded machine having now PG 9.3) to your VM/Docker.
• Restart PostgreSQL 8.4 on the VM/Docker

• Use pg_dumpall to create a full backup :

  pg_dumpall > dump.sql

• Transfer dump.sql to your Cloudera machine, and restore it. You might need to drop previous schemas/databases :

  psql -f dump.sql postgres