RhodeCode sqlite to PostgreSQL migration

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 rc-setup-app command.

We must create a new .ini file that points to postgres instead of sqlite.
a) Make a copy of rhodecode.ini into rhodecode_pg.ini.
b) Edit the db1.url connection string to: sqlalchemy.db1.url = postgresql://postgres:new_db_password@localhost/rhodecode_migration
We use rhodecode_migration database name for this example.

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

The --password,–email, --user, --repos flag can be used as in the example as they will be later overwritten
during migration from old database.

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. The database is now migrated, we need to fix a 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

Ensure PG indexes are set properly in migrated db

  1. Create a new installation db for compare
    Edit the db1.url connection string to: sqlalchemy.db1.url = postgresql://postgres:new_db_password@localhost/rhodecode_migration_compare

  2. create a new DB

~/.rccontrol/enterprise-1/profile/bin/rc-setup-app ~/.rccontrol/enterprise-1/rhodecode_pg.ini --password=qweqwe --email=admin@rhodecode.com --user=admin --repos=/home/rc/repos
  1. Check indexes
select * from pg_indexes where schemaname = 'public';

There should be > 100 indexes present for rhodecode tables.
You can save those lines for later to re-create

  1. Now compare output of
select * from pg_indexes where schemaname = 'public';

With your migrated DB that has the data. If there’s no, or some missing indexes, create them
using the indexdef column data which has a sql statement to create such index

RESOURCES

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

1 Like

Executed this procedure just last week on our 3.22.0 community edition running on a Centos 7 system.
Worked like a charm. Used pgloader directly from the centos repositories. Using pg version 11 database.
I had to do the “Ensure PG indexes” step but other than that I only just followed the 4 steps.
So, this post might be 4 years old, still works on the current release.
Frits.