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