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.

Note: This is a repost of my tutorial with some minor corrections (typos).

Start by installing postgres:
```
 # apt install postgresql
```
We create an 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
```

We create an user on postgres:

 # sudo -u postgres sh -c 'createuser -d etherpad-lite && createdb -O etherpad-lite etherpad-lite'

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.

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", // replace by your actual password
       "database": "etherpad-lite",
       "charset" : "utf8mb4"
   }

 ...
 }

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

We will run a perl script. To be able to run the script, install libdbd-pg-perl:
```
 # apt install libdbd-pg-perl
```

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.