آموزش تغییر و انتقال دیتابیس maddy از SQLite به PostgreSQL

apt update
apt install -y postgresql postgresql-client

ساخت دیتابیس PostgreSQL:

sudo -u postgres psql

CREATE DATABASE maddy;
CREATE USER maddy WITH ENCRYPTED PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE maddy TO maddy;
\q

اعطای دسترسی ها به کاربر maddy:
ویرایش فایل pg_hba.conf:
sudo nano /etc/postgresql/16/main/pg_hba.conf

احتمالاً شبیه این است:
local all all peer

تغییر peer به md5:
local all all md5

ریستارت PostgreSQL:
sudo systemctl restart postgresql

ورود به PostgreSQL:
sudo -u postgres psql

دادن کامل روی دیتابیس maddy به کاربر maddy:
ALTER SCHEMA public OWNER TO maddy;
\c maddy
GRANT ALL PRIVILEGES ON DATABASE maddy TO maddy;
ALTER SCHEMA public OWNER TO maddy;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO maddy;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO maddy;

نصب ابزار pgloader برای انتقال راحت‌تر دیتابیس:
sudo apt install pgloader

متوقف کردن ایمیل سرور:
systemctl stop maddy

بکاپ گرفتن از دیتابیس maddy:
rsync -aH /var/lib/maddy/credentials.db ~/maddy-backup/
rsync -aH /var/lib/maddy/imapsql.db* ~/maddy-backup/

بهینه سازی دیتابیس:
sudo apt install sqlite3

sqlite3 -cmd 'vacuum' ~/maddy-backup/imapsql.db
sqlite3 -cmd 'pragma wal_checkpoint(truncate)' ~/maddy-backup/imapsql.db

پاک کردن تیبل msgs (چون در روند انتقال دیتابیس ارور ایجاد میکنه و تیبل ضروری نیستش و بعد از اینکه maddy رو به postgre متصلش کردیم خودش این تیبل رو ایجاد میکنه):
sqlite3 imapsql.db
DROP TABLE msgs;

انتقال دیتابیس به PostgreSQL:
pgloader sqlite:///~/maddy-backup/credentials.db postgresql://maddy:password@localhost/maddy

pgloader sqlite:///~/maddy-backup/imapsql.db postgresql://maddy:password@localhost/maddy

فایل کانفیگش رو تغییر میدیم:
nano /etc/maddy/maddy.conf

قسمت auth.pass_table رو پیدا کنید و بصورت زیر تغییرش بدید:
auth.pass_table local_authdb {
auto_create yes
table sql_table {
driver postgres
dsn "host=127.0.0.1 port=5432 dbname=maddy user=maddy password=password sslmode=disable"
table_name passwords
}
}
دقت کنید که فقط مقدار driver و مقدار dsn رو تغییر دادیم و همچنین اگر ssl داره، sslmode رو enable بکنید

بعد قسمت storage.imapsql رو پیدا کنید:
storage.imapsql local_mailboxes {
driver postgres
dsn "host=127.0.0.1 port=5432 dbname=maddy user=maddy password=STRONG_PASSWORD sslmode=disable"
retention 24h
default_quota 1G
}
دقیقا مطابق با توضیحات قبلی تغییرش بدید و سیو کنید بیایید بیرون.

systemctl start maddy
journalctl -u maddy -f
در این مرحله maddy باید بدون خطا اجرا بشه. اگر تعداد کاربرانتون زیاده، یکم استارتش طول میکشه.
اگر مشکلی در اجرا شدن نداشت میتونید فایل های دیتابیس قبلی رو پاک کنید که حجم نگیره:
rm /var/lib/maddy/credentials.db
rm /var/lib/maddy/imapsql.db*
ارتباط با من