Migrating Roundcube from MySQL to PostgreSQL

Migrating Roundcube from Mysql to PostgreSQL.md

A very much imperfect migration script for Roundcube.

Used successfully for a small deployment running roundcubemail:1.6.12.

To use, set database connections strings in roundcube.load, path in roundcube-schema-run.sql (\ir didn't work for me for some reason), and run pgloader roundcube.load.

roundcube-schema.sql was derived by running roundcube on a fresh database and dumping the contents. In hindsight, I should have taken SQL from roundcube repository. (note: comments need to be stripped)

pgloader still shows some type casting warnings… but seems to migrated correctly. It's possible that things would break if more of roundcube features were used on that instance.

Hopefully this is nevertheless helpful to the next person who has to spend a few hours doing this (and hopefully that person can share an improved recipe!)

roundcube-schema-run.sql

drop schema if exists public cascade;
create schema public;
\i /path/to/roundcube-schema.sql

roundcube-schema.sql

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;


CREATE TABLE public.cache (
    user_id integer NOT NULL,
    cache_key character varying(128) DEFAULT ''::character varying NOT NULL,
    expires timestamp with time zone,
    data text NOT NULL
);


ALTER TABLE public.cache OWNER TO roundcube;

CREATE TABLE public.cache_index (
    user_id integer NOT NULL,
    mailbox character varying(255) NOT NULL,
    expires timestamp with time zone,
    valid smallint DEFAULT 0 NOT NULL,
    data text NOT NULL
);


ALTER TABLE public.cache_index OWNER TO roundcube;

CREATE TABLE public.cache_messages (
    user_id integer NOT NULL,
    mailbox character varying(255) NOT NULL,
    uid integer NOT NULL,
    expires timestamp with time zone,
    data text NOT NULL,
    flags integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.cache_messages OWNER TO roundcube;

CREATE TABLE public.cache_shared (
    cache_key character varying(255) NOT NULL,
    expires timestamp with time zone,
    data text NOT NULL
);


ALTER TABLE public.cache_shared OWNER TO roundcube;

CREATE TABLE public.cache_thread (
    user_id integer NOT NULL,
    mailbox character varying(255) NOT NULL,
    expires timestamp with time zone,
    data text NOT NULL
);


ALTER TABLE public.cache_thread OWNER TO roundcube;

CREATE TABLE public.collected_addresses (
    address_id integer DEFAULT nextval(('collected_addresses_seq'::text)::regclass) NOT NULL,
    user_id integer NOT NULL,
    changed timestamp with time zone DEFAULT now() NOT NULL,
    name character varying(255) DEFAULT ''::character varying NOT NULL,
    email character varying(255) NOT NULL,
    type integer NOT NULL
);


ALTER TABLE public.collected_addresses OWNER TO roundcube;

CREATE SEQUENCE public.collected_addresses_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.collected_addresses_seq OWNER TO roundcube;

CREATE TABLE public.contactgroupmembers (
    contactgroup_id integer NOT NULL,
    contact_id integer NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.contactgroupmembers OWNER TO roundcube;

CREATE TABLE public.contactgroups (
    contactgroup_id integer DEFAULT nextval(('contactgroups_seq'::text)::regclass) NOT NULL,
    user_id integer NOT NULL,
    changed timestamp with time zone DEFAULT now() NOT NULL,
    del smallint DEFAULT 0 NOT NULL,
    name character varying(128) DEFAULT ''::character varying NOT NULL
);


ALTER TABLE public.contactgroups OWNER TO roundcube;

CREATE SEQUENCE public.contactgroups_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.contactgroups_seq OWNER TO roundcube;

CREATE TABLE public.contacts (
    contact_id integer DEFAULT nextval(('contacts_seq'::text)::regclass) NOT NULL,
    user_id integer NOT NULL,
    changed timestamp with time zone DEFAULT now() NOT NULL,
    del smallint DEFAULT 0 NOT NULL,
    name character varying(128) DEFAULT ''::character varying NOT NULL,
    email text DEFAULT ''::text NOT NULL,
    firstname character varying(128) DEFAULT ''::character varying NOT NULL,
    surname character varying(128) DEFAULT ''::character varying NOT NULL,
    vcard text,
    words text
);


ALTER TABLE public.contacts OWNER TO roundcube;

CREATE SEQUENCE public.contacts_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.contacts_seq OWNER TO roundcube;

CREATE TABLE public.dictionary (
    user_id integer,
    language character varying(16) NOT NULL,
    data text NOT NULL
);


ALTER TABLE public.dictionary OWNER TO roundcube;

CREATE TABLE public.filestore (
    file_id integer DEFAULT nextval(('filestore_seq'::text)::regclass) NOT NULL,
    user_id integer NOT NULL,
    context character varying(32) NOT NULL,
    filename character varying(128) NOT NULL,
    mtime integer NOT NULL,
    data text NOT NULL
);


ALTER TABLE public.filestore OWNER TO roundcube;

CREATE SEQUENCE public.filestore_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.filestore_seq OWNER TO roundcube;

CREATE TABLE public.identities (
    identity_id integer DEFAULT nextval(('identities_seq'::text)::regclass) NOT NULL,
    user_id integer NOT NULL,
    changed timestamp with time zone DEFAULT now() NOT NULL,
    del smallint DEFAULT 0 NOT NULL,
    standard smallint DEFAULT 0 NOT NULL,
    name character varying(128) NOT NULL,
    organization character varying(128),
    email character varying(128) NOT NULL,
    "reply-to" character varying(128),
    bcc character varying(128),
    signature text,
    html_signature smallint DEFAULT 0 NOT NULL
);


ALTER TABLE public.identities OWNER TO roundcube;

CREATE SEQUENCE public.identities_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.identities_seq OWNER TO roundcube;

CREATE TABLE public.responses (
    response_id integer DEFAULT nextval(('responses_seq'::text)::regclass) NOT NULL,
    user_id integer NOT NULL,
    changed timestamp with time zone DEFAULT now() NOT NULL,
    del smallint DEFAULT 0 NOT NULL,
    name character varying(255) NOT NULL,
    data text NOT NULL,
    is_html smallint DEFAULT 0 NOT NULL
);


ALTER TABLE public.responses OWNER TO roundcube;

CREATE SEQUENCE public.responses_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.responses_seq OWNER TO roundcube;

CREATE TABLE public.searches (
    search_id integer DEFAULT nextval(('searches_seq'::text)::regclass) NOT NULL,
    user_id integer NOT NULL,
    type smallint DEFAULT 0 NOT NULL,
    name character varying(128) NOT NULL,
    data text NOT NULL
);


ALTER TABLE public.searches OWNER TO roundcube;

CREATE SEQUENCE public.searches_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.searches_seq OWNER TO roundcube;

CREATE TABLE public.session (
    sess_id character varying(128) DEFAULT ''::character varying NOT NULL,
    changed timestamp with time zone DEFAULT now() NOT NULL,
    ip character varying(41) NOT NULL,
    vars text NOT NULL
);


ALTER TABLE public.session OWNER TO roundcube;

CREATE TABLE public.system (
    name character varying(64) NOT NULL,
    value text
);


ALTER TABLE public.system OWNER TO roundcube;

CREATE TABLE public.users (
    user_id integer DEFAULT nextval(('users_seq'::text)::regclass) NOT NULL,
    username character varying(128) DEFAULT ''::character varying NOT NULL,
    mail_host character varying(128) DEFAULT ''::character varying NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL,
    last_login timestamp with time zone,
    failed_login timestamp with time zone,
    failed_login_counter integer,
    language character varying(16),
    preferences text
);


ALTER TABLE public.users OWNER TO roundcube;

CREATE SEQUENCE public.users_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.users_seq OWNER TO roundcube;


SELECT pg_catalog.setval('public.collected_addresses_seq', 1, false);


SELECT pg_catalog.setval('public.contactgroups_seq', 1, false);


SELECT pg_catalog.setval('public.contacts_seq', 1, false);


SELECT pg_catalog.setval('public.filestore_seq', 1, false);


SELECT pg_catalog.setval('public.identities_seq', 1, false);


SELECT pg_catalog.setval('public.responses_seq', 1, false);


SELECT pg_catalog.setval('public.searches_seq', 1, false);


SELECT pg_catalog.setval('public.users_seq', 1, false);


ALTER TABLE ONLY public.cache_index
    ADD CONSTRAINT cache_index_pkey PRIMARY KEY (user_id, mailbox);


ALTER TABLE ONLY public.cache_messages
    ADD CONSTRAINT cache_messages_pkey PRIMARY KEY (user_id, mailbox, uid);


ALTER TABLE ONLY public.cache
    ADD CONSTRAINT cache_pkey PRIMARY KEY (user_id, cache_key);


ALTER TABLE ONLY public.cache_shared
    ADD CONSTRAINT cache_shared_pkey PRIMARY KEY (cache_key);


ALTER TABLE ONLY public.cache_thread
    ADD CONSTRAINT cache_thread_pkey PRIMARY KEY (user_id, mailbox);


ALTER TABLE ONLY public.collected_addresses
    ADD CONSTRAINT collected_addresses_pkey PRIMARY KEY (address_id);


ALTER TABLE ONLY public.contactgroupmembers
    ADD CONSTRAINT contactgroupmembers_pkey PRIMARY KEY (contactgroup_id, contact_id);


ALTER TABLE ONLY public.contactgroups
    ADD CONSTRAINT contactgroups_pkey PRIMARY KEY (contactgroup_id);


ALTER TABLE ONLY public.contacts
    ADD CONSTRAINT contacts_pkey PRIMARY KEY (contact_id);


ALTER TABLE ONLY public.dictionary
    ADD CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, language);


ALTER TABLE ONLY public.filestore
    ADD CONSTRAINT filestore_pkey PRIMARY KEY (file_id);


ALTER TABLE ONLY public.filestore
    ADD CONSTRAINT filestore_user_id_filename UNIQUE (user_id, context, filename);


ALTER TABLE ONLY public.identities
    ADD CONSTRAINT identities_pkey PRIMARY KEY (identity_id);


ALTER TABLE ONLY public.responses
    ADD CONSTRAINT responses_pkey PRIMARY KEY (response_id);


ALTER TABLE ONLY public.searches
    ADD CONSTRAINT searches_pkey PRIMARY KEY (search_id);


ALTER TABLE ONLY public.searches
    ADD CONSTRAINT searches_user_id_key UNIQUE (user_id, type, name);


ALTER TABLE ONLY public.session
    ADD CONSTRAINT session_pkey PRIMARY KEY (sess_id);


ALTER TABLE ONLY public.system
    ADD CONSTRAINT system_pkey PRIMARY KEY (name);


ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);


ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_username_key UNIQUE (username, mail_host);


CREATE INDEX cache_expires_idx ON public.cache USING btree (expires);


CREATE INDEX cache_index_expires_idx ON public.cache_index USING btree (expires);


CREATE INDEX cache_messages_expires_idx ON public.cache_messages USING btree (expires);


CREATE INDEX cache_shared_expires_idx ON public.cache_shared USING btree (expires);


CREATE INDEX cache_thread_expires_idx ON public.cache_thread USING btree (expires);


CREATE UNIQUE INDEX collected_addresses_user_id_idx ON public.collected_addresses USING btree (user_id, type, email);


CREATE INDEX contactgroupmembers_contact_id_idx ON public.contactgroupmembers USING btree (contact_id);


CREATE INDEX contactgroups_user_id_idx ON public.contactgroups USING btree (user_id, del);


CREATE INDEX contacts_user_id_idx ON public.contacts USING btree (user_id, del);


CREATE INDEX identities_email_idx ON public.identities USING btree (email, del);


CREATE INDEX identities_user_id_idx ON public.identities USING btree (user_id, del);


CREATE INDEX responses_user_id_idx ON public.responses USING btree (user_id, del);


CREATE INDEX session_changed_idx ON public.session USING btree (changed);


ALTER TABLE ONLY public.cache_index
    ADD CONSTRAINT cache_index_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.cache_messages
    ADD CONSTRAINT cache_messages_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.cache_thread
    ADD CONSTRAINT cache_thread_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.cache
    ADD CONSTRAINT cache_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.collected_addresses
    ADD CONSTRAINT collected_addresses_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.contactgroupmembers
    ADD CONSTRAINT contactgroupmembers_contact_id_fkey FOREIGN KEY (contact_id) REFERENCES public.contacts(contact_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.contactgroupmembers
    ADD CONSTRAINT contactgroupmembers_contactgroup_id_fkey FOREIGN KEY (contactgroup_id) REFERENCES public.contactgroups(contactgroup_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.contactgroups
    ADD CONSTRAINT contactgroups_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.contacts
    ADD CONSTRAINT contacts_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.dictionary
    ADD CONSTRAINT dictionary_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.filestore
    ADD CONSTRAINT filestore_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.identities
    ADD CONSTRAINT identities_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.responses
    ADD CONSTRAINT responses_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


ALTER TABLE ONLY public.searches
    ADD CONSTRAINT searches_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;

roundcube.load

LOAD DATABASE
   FROM mysql://foo:bar@baz/roundcube
   INTO postgresql://foo:bar@baz/roundcube

WITH data only, reset no sequences
CAST
      type date drop not null drop default using zero-dates-to-null,
      type int to integer,
      type int when (=10 precision) to integer,
      type tinyint to smallint

-- fixme
EXCLUDING TABLE NAMES MATCHING 'dictionary'

ALTER SCHEMA 'roundcube' RENAME TO 'public'

BEFORE LOAD EXECUTE roundcube-schema-run.sql
;