Mastodon migration

[DidierMalenfant]

Ok it seems like 'Enable Recovery Mode' is the way to do this on Cloudron. I should then be able to replace the posgresql database with the one that is dumped from my masto.host server.

On the masto.host side I can generate a new backup from live data after stopping the server from the control panel and that will give me an option to also download the remote media cache and restore that too.

I'm told that I can generate the REDIS database from the PostgreSQL database after importing it over.

I also have to set a short TTL on my domain a couple of days before doing this so that the new IP gets picked up rapidly.

Is the folder organisation for the Mastodon app on Cloudron pretty much standard (for things like the cache, emojis, etc...)?

[james]

Hello @DidierMalenfant

@DidierMalenfant said in Mastodon migration:

Is the folder organisation for the Mastodon app on Cloudron pretty much standard (for things like the cache, emojis, etc...)?

Everything that you can edit and is saved on reboot is in /app/data/ everything outside of that will be overwritten on disabling the recovery mode or restarting the app or server as a whole.

Or did I understood your question wrong?

[DidierMalenfant]

@james said in Mastodon migration:

Or did I understood your question wrong?

No that was part of my question. Thank you!

The other part is, the masto.host backup has a folder structure with things like media/accounts/avatars or media/custom_emojis and I want to make sure that those things are in a similar location on the Cloudron install so I know where to copy everything over.

I'm going to do a trial run on a test Cloudron app anyway before I attempt to do it for real.

[DidierMalenfant]

So I created a test masto instance on my Cloudron server so that I can look at how things are organized. It look likes app/data/system which will map perfectly with the data in the masto.host backup so I’m good there.

The database is a bit more puzzling. When I look at the DBs in there I find two that have generated names:

            Name          |           Owner          | 
--------------------------+--------------------------+ ...
 db65axxxxxx6             | user65axxxxxx6           | 
 dbd4xxxxxxx8             | userd4xxxxxxx8           |

(I cut out the actual random numbers on purpose but basically they are two different db and user names).

I have access to one of those two, the one that has the username and password set in env.configuration and it does contain some masto stuff.

What is the other DB? Is that a left over from a previous installation in Cloudron app image?

Thanks for the help!

[joseph]

@DidierMalenfant Where and how did you get that db output? In general, each app will only have a single database .

[DidierMalenfant]

Mmmm.... I put the Cloudron mastodon app in repair mode but I can't seem to drop the database because it's the only one I can connect to. I don't see any way to get the passwords for any other user and when I try to drop the database from the command line I get no pg_hba.conf entry for host errors which seems to indicate I'm not allowed to do that.

How can I replace the database with the one from my current masto.host install?

[DidierMalenfant]

@joseph said in Mastodon migration:

@DidierMalenfant Where and how did you get that db output? In general, each app will only have a single database .

I used the PGPASSWORD=${CLOUDRON_POSTGRESQL_PASSWORD} psql -h ${CLOUDRON_POSTGRESQL_HOST} -p ${CLOUDRON_POSTGRESQL_PORT} -U ${CLOUDRON_POSTGRESQL_USERNAME} -d ${CLOUDRON_POSTGRESQL_DATABASE} command line and then did \l to list the databases.

[joseph]

@DidierMalenfant if you do echo $CLOUDRON_POSTGRESQL_DATABASE , that is the only database the app has access to . Alternately, SELECT current_database(); in the psql prompt .

[DidierMalenfant]

@joseph said in Mastodon migration:

@DidierMalenfant if you do echo $CLOUDRON_POSTGRESQL_DATABASE , that is the only database the app has access to . Alternately, SELECT current_database(); in the psql prompt .

Yeah I can see that from the env.production too. It's the only one I have access to since I don't have the password for the other one.

I re-installed the app from scratch and I can confirm that the other db is indeed there from the start. Same username, same db name (that must have been randomly generated at some point) and then the new app db next to it that has a different db name and username each time the app is installed. It must be a leftover from something...

@DidierMalenfant said in Mastodon migration:

How can I replace the database with the one from my current masto.host install?

Since I don't seem to be able to use dropdb from the command line, I was able to use the regular psql command line (by pressing on the button at the top of the console screen) and then drop all tables from there:

DO $$ 
BEGIN
    EXECUTE (
        SELECT string_agg('DROP TABLE IF EXISTS ' || tablename || ' CASCADE;', ' ')
        FROM pg_tables
        WHERE schemaname = 'public'
    );
END $$;

I then had to delete some functions and sequences that otherwise error our when I import the dump because they already exists:

DROP FUNCTION timestamp_id CASCADE;
DROP SEQUENCE notification_requests_id_seq CASCADE;
DROP SEQUENCE encrypted_messages_id_seq CASCADE;
DROP SEQUENCE accounts_id_seq CASCADE;
DROP SEQUENCE media_attachments_id_seq CASCADE;
DROP SEQUENCE statuses_id_seq CASCADE;

and then finally import the sql dump with no errors:

PGPASSWORD=${CLOUDRON_POSTGRESQL_PASSWORD} pg_restore -Fc -j2 -h ${CLOUDRON_POSTGRESQL_HOST} -p ${CLOUDRON_POSTGRESQL_PORT} -U ${CLOUDRON_POSTGRESQL_USERNAME} -n public --no-owner --role=${CLOUDRON_POSTGRESQL_USERNAME} -d ${CLOUDRON_POSTGRESQL_DATABASE} pg_dump.custom

This all seems to work on my test app but now I have to test it with an app at the real url and then start it up again to see if it comes alive correctly (since I can't try that with my test app being at the wrong url compared to the sql dump).

Wish me luck!

[joseph]

@DidierMalenfant said in Mastodon migration:

It must be a leftover from something...

the other databases belong to other apps! you probably has some other app installed that uses postgres as well. The database name and the app id should closely resemble each other.

[DidierMalenfant]

@joseph said in Mastodon migration:

the other databases belong to other apps! you probably has some other app installed that uses postgres as well. The database name and the app id should closely resemble each other.

That makes sense. I didn't realize that they shared the same db server.

That's why it connect to the db via looping back into the server and a port number. I keep forgetting the apps are containers.

[joseph]

@DidierMalenfant that's correct. it's a single postgresql instance and all apps share that instance. they all have mutually exclusive databases though . one app cannot access the database of another .

[DidierMalenfant]

It looks like the migration went smoothly. I'll put together a blog post to details the steps I took.

Thank you again for all the help!