Public
Authored by Louis Royer

How to migrate etherpad-lite dirty.db to postgres on debian 10 (buster)

The first part of this tutorial comes from etherpad-lite wiki but the wiki content is outdated. Here is a recent version of the procedure, with some additions.

  1. Start by installing postgres:
# apt install postgresql
  1. We create a user etherpad-lite because postgres needs a pam user to be able to use unix sockets (we don't want to use tcp sockets):
# adduser etherpad-lite --no-create-home
  1. We create an user on postgres:
# sudo -u postgres sh -c 'createuser -d etherpad-lite && createdb -O etherpad-lite etherpad-lite'
  1. Add a password (here, yourpassword) to our new postgres user:
# sudo -u postgres psql
psql=# alter user etherpad-lite with encrypted password 'yourpassword';

You can type \q to exit psql prompt.

  1. Assuming you have installed your postgres on the same machine running etherpad-lite, add this to your etherpad-lite/settings.json (replace yourpassword with the password you created)
"dbType" : "postgres",
"dbSettings" : {
    "user"    : "etherpad-lite",
    "host"    : "localhost",
    "password": "yourpassword",
    "database": "etherpad-lite",
    "charset" : "utf8mb4"
}

At this point, you shourd be able to run etherpad-lite using postgres without any error. In nexts steps, we will see how to migrate your old dirty.db to postgres.

  1. We will run a perl script. To be able to run the script, install libdbd-pg-perl:
# apt install libdbd-pg-perl
  1. dirty.db is a json file. You can run the following perl script based on a script from etherpad-lite wiki. We made some modifications in order to be usable with postgres. You have to change yourpassword and /path/to/your/dirty.db to real values. Make sure your etherpad-lite is stopped before running the script.
#!/usr/bin/env perl
use strict;
use DBI;

my $dbh = DBI->connect("DBI:Pg:database=etherpad-lite;host=localhost", "etherpad-lite", "yourpassword",) or die;
$dbh->prepare("TRUNCATE TABLE store")->execute();

open(F,"/path/to/your/dirty.db") or die;

while (<F>) {
    if (m|\{\"key\":\"(.*)\",\"val\":(.*)\}|) {
    my ($k,$v) = ($1,$2);
    my $sth = $dbh->prepare("SELECT key FROM store WHERE key = ?") or die;
    $sth->execute($k) or die;
    my @a = $sth->fetchrow();
    if ($a[0]) {
        $sth = $dbh->prepare("UPDATE store set value = ? WHERE key = ?") or die;
        $sth->execute($v,$k) or die;
    } else {
        $sth = $dbh->prepare("INSERT INTO store (key,value) VALUES (?,?)") or die;
        $sth->execute($k,$v) or die;
    }
    } else {
    die "Err!\n";
    }
}
close F;

Now, your postgres is populated with your old dirty.db content. You can start your etherpad-lite again and check your previously created pads are online.

Edited
dirty_to_postgres.pl 818 Bytes
Markdown is supported
0% or
You are about to add 0 people to the discussion. Proceed with caution.
Finish editing this message first!
Please register or to comment