Opened 4 years ago

Closed 4 years ago

#9889 closed Task/To do item (fixed)

Migrate roundcube database from postgres 8.4 -> 9.1

Reported by: https://id.mayfirst.org/srevilak Owned by: https://id.mayfirst.org/srevilak
Priority: Medium Component: Tech
Keywords: stallman.mayfirst.org roundcube postgres Cc:
Sensitive: no

Description

Stallman is getting upgraded from debian 6 to debian 7. As part of that upgrade, we're getting postgres 9.1. We should migrate roundcube's database to postgres 9, and remove the (now-obsolete) postgres 8.4 packages.

Change History (8)

comment:1 Changed 4 years ago by https://id.mayfirst.org/srevilak

  • Owner set to https://id.mayfirst.org/srevilak
  • Status changed from new to assigned

comment:2 Changed 4 years ago by https://id.mayfirst.org/srevilak

Unfortunately, I was a little too quick to agree with "apt-get autoremove"'s suggestions for package removal. Unfortunately apt-get removed postgres-8.4.

Luckily, the data directories were not removed.

I tgz'd the 8.4 data directories, copied to my local machine.

I compiled postgres 8.4.22 on my local machine, and pointed postgres to a copy of stallman's postgres database. From there, I was able to pg_dump the postgres and postgres-dev databases.

On stallman (under postgres 9.1), I did

create user "roundcube-dev" ...
create user "roundcube" ...

and used psql to restore the databases.

comment:3 Changed 4 years ago by https://id.mayfirst.org/srevilak

Quick sanity check, post-reload. The user and count contacts seem reasonable.

0 roundcube@stallman:~$ psql
psql (9.1.13)
Type "help" for help.

roundcube=> \dt
                List of relations
 Schema |        Name         | Type  |   Owner   
--------+---------------------+-------+-----------
 public | cache               | table | roundcube
 public | cache_index         | table | roundcube
 public | cache_messages      | table | roundcube
 public | cache_shared        | table | roundcube
 public | cache_thread        | table | roundcube
 public | contactgroupmembers | table | roundcube
 public | contactgroups       | table | roundcube
 public | contacts            | table | roundcube
 public | dictionary          | table | roundcube
 public | identities          | table | roundcube
 public | searches            | table | roundcube
 public | session             | table | roundcube
 public | system              | table | roundcube
 public | users               | table | roundcube
(14 rows)

roundcube=> select count(*) from users;
 count 
-------
  1033
(1 row)

roundcube=> select count(*) from contacts;
 count 
-------
 41565
(1 row)

---------------------------------------------------------------

0 roundcube-dev@stallman:~$ psql 
psql (9.1.13)
Type "help" for help.

roundcube-dev=> \dt
                  List of relations
 Schema |        Name         | Type  |     Owner     
--------+---------------------+-------+---------------
 public | cache               | table | roundcube-dev
 public | cache_index         | table | roundcube-dev
 public | cache_messages      | table | roundcube-dev
 public | cache_shared        | table | roundcube-dev
 public | cache_thread        | table | roundcube-dev
 public | contactgroupmembers | table | roundcube-dev
 public | contactgroups       | table | roundcube-dev
 public | contacts            | table | roundcube-dev
 public | dictionary          | table | roundcube-dev
 public | identities          | table | roundcube-dev
 public | searches            | table | roundcube-dev
 public | session             | table | roundcube-dev
 public | system              | table | roundcube-dev
 public | users               | table | roundcube-dev
(14 rows)

roundcube-dev=> select count(*) from users;
 count 
-------
    47
(1 row)

roundcube-dev=> select count(*) from contacts;
 count 
-------
   154
(1 row)

comment:4 Changed 4 years ago by https://id.mayfirst.org/srevilak

  • Summary changed from Migrade roundcube database from postgres 8.4 -> 9.1 to Migrate roundcube database from postgres 8.4 -> 9.1

comment:5 Changed 4 years ago by https://id.mayfirst.org/srevilak

From the command-line (psql), the roundcube and roundcube-dev databases seem to be okay. From the roundcube application, we're still not out of the woods

DATABASE ERROR: CONNECTION FAILED!

Unable to connect to the database!
Please contact your server-administrator.

When stallman rebooted, we had these couple of line logged to the console:

PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib/php5/20100525/suhosin.so' - /usr/lib/php5/20100525/suhosin.so: cannot open shared object file: No such file or directory in Unknown on line 0
PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib/php5/20100525/suhosin.so' - /usr/lib/php5/20100525/suhosin.so: cannot open shared object file: No such file or directory in Unknown on line 0

At present, not sure whether the auth problem is postgres, or php/suhosin.

I'm going to start by reconciling postgres configuration files (8.4 vs 9.1). We'll see where we get with that.

comment:6 Changed 4 years ago by https://id.mayfirst.org/srevilak

0 stallman:/etc/postgresql# diff 8.4/main/environment 9.1/main/environment 
0 stallman:/etc/postgresql#

0 stallman:/etc/postgresql# diff 8.4/main/pg_ctl.conf 9.1/main/pg_ctl.conf 
0 stallman:/etc/postgresql#

# migrated these deltas
0 stallman:/etc/postgresql# diff -w -I "^#" 8.4/main/pg_hba.conf 9.1/main/pg_hba.conf 
76,77c84,85
< # Database administrative login by UNIX sockets
< local   all         postgres                          ident
---
> # Database administrative login by Unix domain socket
> local   all             postgres                                peer
82c90
< local   all         all                               ident
---
> local   all             all                                     peer
1 stallman:/etc/postgresql# 

0 stallman:/etc/postgresql# diff -I "^#" 8.4/main/pg_ident.conf 9.1/main/pg_ident.conf 
0 stallman:/etc/postgresql#

0 stallman:/etc/postgresql# diff  8.4/main/start.conf 9.1/main/start.conf
0 stallman:/etc/postgresql#

0 stallman:/etc/postgresql# diff  -I "^#" 8.4/main/pg_ident.conf 9.1/main/pg_ident.conf 
0 stallman:/etc/postgresql# 

The only significant difference in postgres.conf is

-port = 5432                            # (change requires restart)
+port = 5433                            # (change requires restart)

Let's change the port to 5432, and restart.

Socket directory before restart:

0 stallman:/etc/postgresql# ls -la /var/run/postgresql/.s.PGSQL.5433
srwxrwxrwx 1 postgres postgres 0 Aug  9 18:32 /var/run/postgresql/.s.PGSQL.5433

Socket directory after restart

0 roundcube@stallman:~$ ls -l /var/run/postgresql/.s.PGSQL.5432
srwxrwxrwx 1 postgres postgres 0 Aug  9 18:45 /var/run/postgresql/.s.PGSQL.5432
0 roundcube@stallman:~$

With the port/socket-file change, roundcube.mayfirst.org (and roundcube.dev.mayfirst.org) are working again.

comment:7 Changed 4 years ago by https://id.mayfirst.org/srevilak

Regarding

PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib/php5/20100525/suhosin.so' - /usr/lib/php5/20100525/suhosin.so: cannot open shared object file: No such file or directory in Unknown on line 0
PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib/php5/20100525/suhosin.so' - /usr/lib/php5/20100525/suhosin.so: cannot open shared object file: No such file or directory in Unknown on line 0

Jamie suggested removing /etc/php5/conf.d/suhosin.ini, which I did.

comment:8 Changed 4 years ago by https://id.mayfirst.org/srevilak

  • Resolution set to fixed
  • Status changed from assigned to closed

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.