Assumptions

• You started off from the basic storage config:
  • Regular data is in filesystem.
  • mam (xep0313) message archive is in SQLite3.
• You want to transition to using just PostgreSQL.
• PostgreSQL version is 9.4.

Installation

# # largest version of pgsql you have
~# apt install postgresql-9.4-client

# # get lua-dbi module for pgsql
~# apt install lua-dbi-postgresql

Creating PostgreSQL user

# # PostgreSQL trusts users connecting over unix domain socket to be the
# # same as their local account.
# # Therefore, become postgres -- the admin account.
user:~$ sudo su postgres

# # run user creation
postgres:~$ createuser --interactive
# # name: prosody
# # no other administrative options

# # run postgresql client
postgres:~$ psql

-- create database
CREATE DATABASE prosody;

-- give the prosody user all rights on it
GRANT ALL ON DATABASE prosody TO prosody;

-- in case of connecting over network and using md5 trust,
-- set prosody account password:
ALTER ROLE prosody WITH ENCRYPTED PASSWORD 'here_some_password';

Over network?

Assuming you want to connect over the network, edit /etc/postgresql/9.4/main/pg_hba.conf. Append:

# type, database, user, address, auth method
host prosody prosody 172.16.0.0/16 md5

• Try to minimize your permitted netmask.
• Can you configure a more secure auth method than md5? Do so.

Migrate data from filesystem

# # as prosody local user

prosody:$ cd prosody-hg/tools/migrator

# # overwrite the config.
prosody:$ cat > migrator.cfg.lua << _EOF

local data_path = "../../data";

input {
        type = "prosody_files";
        path = data_path;
}
output {
        type = "prosody_sql";
        driver = "PostgreSQL";
        database = "prosody";
        username = "prosody";
        password = "here_some_password";
        host = "database.host.here"; -- this assumes network connection; migration with local user credentials was not attempted.
}

_EOF

# # run the migrator in ~/prosody-hg/tools/migrator
prosody:$ lua prosody-migrator.lua input output

Migrate mam archive from SQLite3

# # as prosody user

prosody:~$ cd prosody-hg/data

# # Having first verified there is nothing in Prosody table...
prosody:$ sqlite3 prosody.sqlite 'SELECT COUNT(*) FROM prosody;'
# # ...drop prosody table. All its data (roster etc) was until now stored on the filesystem.
prosody:$ sqlite3 prosody.sqlite 'DROP TABLE prosody;'

# # dump and massage the sqlite3 output, piping it into psql.
# # psql authenticates as the 'prosody' user and does not require the password.
prosody:$ sqlite3 prosody.sqlite .dump | \
    grep -v 'BEGIN TRANSACTION;' | \
    sed 's/PRAGMA foreign_keys=OFF;/BEGIN TRANSACTION; SET CONSTRAINTS ALL DEFERRED;/' | \
    grep -v sqlite_sequence | \
    awk '/CREATE TABLE/{gsub("`","\"");} 1' | \
    awk '/CREATE UNIQUE INDEX/{gsub("`","\"");} 1' | \
    sed 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/' | \
    psql

# # manual step :(
# # fix the autoincrement.
prosody:$ sqlite3 prosody.sqlite 'SELECT COUNT(*) FROM prosodyarchive;'
# # use this number + 1 in:
prosody:$ echo 'ALTER SEQUENCE prosodyarchive_sort_id_seq RESTART WITH 123456;' | psql

Update Prosody config

In prosody.cfg.lua:

storage = {
        archive2 = "sql";
}
sql = { driver = "PostgreSQL", database = "prosody", username = "prosody", password = "here_some_password", host = "database.host.here" }

–
via blog.vucica.net