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
-
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 -
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
- 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
- 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