RhodeCode sqlite to PostgreSQL migration


#1

Here’s a proven way how to migrate from sqlite database on RhodeCode to PostgreSQL. Sqlite is a good option to start, however if you would like to achieve good performance Postgres is the way to go.

1. Create new postgresql DATABASE using RhodeCode setup command.

We must create a new .ini file that points to postgres instead of sqlite. Make a copy of rhodecode.ini into rhodecode_pg.ini. Then you must set postgresql as a database engine in the rhodecode_pg.ini config before doing below command. We use rhodecode_migration database name for this example:

~/.rccontrol/enterprise-1/profile/bin/rc-setup-app ~/.rccontrol/enterprise-1/rhodecode.ini --password=qweqwe --email=admin@rhodecode.com --user=admin --repos=/home/rc/repos

2. Install, and use pgloader, and transfer the data (installation instructions are in the end of this document)

Create a script called command, and put the below content inside.
Be aware about the destination and target databases, adjust if needed.

load database
    from sqlite://rhodecode.db
    into postgresql://postgres:qweqwe@localhost/rhodecode_migration

WITH truncate,
     create no tables,
     create no indexes,
     reset no sequences

CAST type datetime to timestamp
;

3. use pgloader, to execute created script and migrate your data. Run this command:

  ../pgloader/build/bin/pgloader command

4. Database is now migrated, we need to fix few things still.

Open PSQL Prompt and, bump the Sequences, we need to bump them here so we don’t hit problems of duplicate
primary keys

DO
$$
DECLARE
    seqname text;
    nspname text;
    seqval bigint;
BEGIN
    FOR nspname, seqname IN select n.nspname, c.relname FROM
pg_class c INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE c.relkind = 'S' AND n.nspname = 'public' LOOP
        EXECUTE format('SELECT last_value FROM %I.%I', nspname, seqname) INTO seqval;
        PERFORM setval(quote_ident(nspname)||'.'||quote_ident(seqname), seqval + 10000);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

That’s it. !!

NOTES:

install PGLOADER

sudo apt-get install -y sbcl git curl patch unzip devscripts pandoc libsqlite3-dev freetds-dev make gawk libzip-dev
git clone https://github.com/dimitri/pgloader.git
$ cd pgloaded 
$ git checkout v3.2.2
$ make pgloader

Below are some extra help calls
that might help with other types of

sqlite

CAST type datetime to timestamp, is mostly required since there are differences between those types on sqlite vs Postgres

sqlite3 rhodecode.db .dump > rhodecode.db.sql

sed -i ‘/PRAGMA/d’ rhodecode.db.sql
sed -i ‘/sqlite_sequence/d’ rhodecode.db.sql
sed -i ‘/CREATE INDEX/d’ rhodecode.db.sql
sed -i ‘/INSERT INTO “cache_invalidation”/d’ rhodecode.db.sql

sed -i ‘s/PRIMARY KEY AUTOINCREMENT/PRIMARY KEY/ig’ rhodecode.db.sql
sed -i ‘s/CREATE TABLE/CREATE TABLE IF NOT EXISTS/ig’ rhodecode.db.sql

restore

PGPASSWORD=qweqwe psql -Upostgres -hlocalhost rhodecode_migration < rhodecode.db.sql

postgres

select * from repositories_repo_id_seq;
select pg_get_serial_sequence('<table_name>', '<column_name>');


DO
$$
DECLARE
    seqname text;
    nspname text;
    seqval bigint;
BEGIN
    FOR nspname, seqname IN select n.nspname, c.relname FROM
pg_class c INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE c.relkind = 'S' AND n.nspname = 'public' LOOP
        EXECUTE format('SELECT last_value FROM %I.%I', nspname, seqname) INTO seqval;
        PERFORM setval(quote_ident(nspname)||'.'||quote_ident(seqname), seqval + 0);
    END LOOP;
END;
$$ LANGUAGE plpgsql;
pg_dump -Upostgres -h localhost rhodecode_migration -s > schema.sql
PGPASSWORD=qweqwe psql -Upostgres -h localhost -c "drop database rhodecode_migration" \
&& PGPASSWORD=qweqwe psql -Upostgres -h localhost -c "create database rhodecode_migration" \
&& PGPASSWORD=qweqwe psql -Upostgres -h localhost rhodecode_migration < schema.sql

RESOURCES

http://tapoueh.org/pgloader/howto/pgloader.1.html