Railway•12mo ago

Postgres Migration failed at "Migrating Data"

Hi! I have a legacy Postgres DB, and I've gotten the notice about needing to migrate. I finally went ahead and hit the "Migrate" button, but it failed at the "Migrating Data" step. I've tried hitting "Migrate" a couple more times, and it keeps failing at that step. I now have a total of 5 different entries (services?) in the project: - "Postgres Legacy" (status: "Migration errored") - "Postgres Migration" (status: "Crashed") - 2x "Postgres Legacy Migration" (status: "Crashed") - "Postgres" (status: "deployed") I think the relevant error logs section is:

==== Restoring database to NEW_URL ====
DO
psql:plugin_dump.sql:105009298: ERROR:  could not extend file "base/16384/23404.3": No space left on device
@replica:
View in Context
HINT:  Check free disk space.
CONTEXT:  COPY session, line 39074170
psql:plugin_dump.sql:105009298: STATEMENT:  COPY "public"."session" ("session_id", "website_id", "hostname", "browser", "os", "device", "screen", "language", "country", "subdivision1", "subdivision2", "city", "created_at") FROM stdin;
[ ERROR ] Failed to restore database to postgresql://postgres:A12CEEffaAeB5GbGg5FDcb4d3FBG

==== Restoring database to NEW_URL ====
DO
psql:plugin_dump.sql:105009298: ERROR:  could not extend file "base/16384/23404.3": No space left on device
@replica:
View in Context
HINT:  Check free disk space.
CONTEXT:  COPY session, line 39074170
psql:plugin_dump.sql:105009298: STATEMENT:  COPY "public"."session" ("session_id", "website_id", "hostname", "browser", "os", "device", "screen", "language", "country", "subdivision1", "subdivision2", "city", "created_at") FROM stdin;
[ ERROR ] Failed to restore database to postgresql://postgres:A12CEEffaAeB5GbGg5FDcb4d3FBG

Project ID is 9a511af8-a819-4274-a1a3-c64a6f7ff883 . Any suggestions on next steps here? (Also please @ me so I see the ping)

100 Replies

Percy•12mo ago

Project ID: 9a511af8-a819-4274-a1a3-c64a6f7ff883

Brody•12mo ago

how much data did you have in your old database?

acemarkeOP•12mo ago

not immediately sure. I'm running it as the backend for a Umami analytics instance, and I've had it up since about April (but also semi-manually exported a bunch of data from my old Google Analytics setup and inserted it into this for Umami migration)

Brody•12mo ago

do you think its anywhere near 5gb?

acemarkeOP•12mo ago

certainly possible. I haven't looked at the DB since I got the initial setup + GA migration step done, so I have no idea how much it's taking up

Brody•12mo ago

than thats likely why, the new database services get a 5gb volume

acemarkeOP•12mo ago

lovely. was there a size limit on the old plugin?

Brody•12mo ago

you may need to upgrade to pro for a 50gb volume not really

acemarkeOP•12mo ago

soooooo, the migration involves a bit of a downgrade / limitation, then

Brody•12mo ago

in a way, yes

acemarkeOP•12mo ago

ugh. I'm primarily an FE dev, and I'd opted for this setup to keep it as minimal and hands-off as possible. I really did not want to spend part of my Christmas break figuring out how to deal with backend DB migrations :(

Brody•12mo ago

delete the failed stuff, upgrade to pro, run the migration again

acemarkeOP•12mo ago

as it is, there's also been a very annoying quirk: the DB's memory just keeps rising indefinitely over time, and I've had to work around that by manually restarting the DB service every few days

Brody•12mo ago

postgres does tend to do that, not much railway can do about that

acemarkeOP•12mo ago

okay. so, try deleting everything except "Postgres Legacy"?

Brody•12mo ago

correct, please be careful though

acemarkeOP•12mo ago

heh. anything specific to be "careful" about beyond selecting which item I'm deleting?

Brody•12mo ago

thats about it

acemarkeOP•12mo ago

okay, I'll give it a shot @Brody fwiw, I did upgrade to Pro and retried. This time the "Migrate Data" stepped seemed to go on much longer, but finally errored, and the "Legacy Migration" service actually says it "crashed an hour ago". Same error:

psql:plugin_dump.sql:221220849: ERROR:  could not extend file "base/16384/23500": No space left on device

psql:plugin_dump.sql:221220849: ERROR:  could not extend file "base/16384/23500": No space left on device

Brody•12mo ago

is the volume 50gb?

acemarkeOP•12mo ago

ah, no it's not, it's 5 I can hit a button to increase it, but is that going to get used if I hit "Migrate" again?

Brody•12mo ago

when you upgraded, where you asked to move some projects over?

acemarkeOP•12mo ago

yes, both the PG project and the Umami app project

Brody•12mo ago

and did you move them over?

acemarkeOP•12mo ago

yep

Brody•12mo ago

then yes grow the volume and restart the migration service actually you might want to wipe the volume first, who knows what's on it so best to start fresh

acemarkeOP•12mo ago

yep, it just errored due to not being empty same error:

==== Checking if NEW_URL is empty ====
table count: 11
[ ERROR ] The new database is not empty. Aborting migration.
Set the OVERWRITE_DATABASE environment variable to overwrite the new database.

==== Checking if NEW_URL is empty ====
table count: 11
[ ERROR ] The new database is not empty. Aborting migration.
Set the OVERWRITE_DATABASE environment variable to overwrite the new database.

Brody•12mo ago

did you wipe the volume on the new database?

acemarkeOP•12mo ago

tried to wipe the "Postgres Data" volume

Brody•12mo ago

tried?

acemarkeOP•12mo ago

I hit the button, said it was wiping it and that it completed

acemarkeOP•12mo ago

fwiw, the UI currently looks like:

acemarkeOP•12mo ago

pretty sure the top "Postgres Legacy" is my original plugin

Brody•12mo ago

delete all the failed stuff that isn't your original database and then re run the migration

acemarkeOP•12mo ago

including the "Postgres data" volume?

Brody•12mo ago

yes

acemarkeOP•12mo ago

stupid question, won't that leave me back where I was with an auto-created new DB that's only 5GB?

Brody•12mo ago

nope because now this project is on the pro plan, when the migration creates a new database for you it will automatically be 50gb

acemarkeOP•12mo ago

why didn't that happen with this last attempt, then? because this latest attempt was after I'd upgraded to Pro

Brody•12mo ago

I'm not sure, I don't have the ability to look into that

acemarkeOP•12mo ago

ok, I'll give it another shot @Brody the last attempt last evening failed too. I just opened up my existing DB with pgadmin and ran this query:

select pg_size_pretty(pg_database_size('railway'));

select pg_size_pretty(pg_database_size('railway'));

result? 73 GB oops. that explains it. honestly not sure what to do next, if the size is way over even what the Pro plan gives me

Brody•12mo ago

haha you sure where making all you could of that no database size limit with the legacy databases @Christian - pro user needs their volume size limit increased in order to migrate their legacy database

christian•12mo ago

Thank you for the flag, Brody! @acemarke I've increased the maximum size of the volume to 100GB. You'll only ever be charged for the amount of data actually stored. Please go ahead and try the migration again

acemarkeOP•12mo ago

thanks! if it helps for context: I maintain the Redux JS libraries. I migrated our docs away from Google Analytics this year to a Umami instance, and I'm hosting both the PG DB and Umami app instances on Railway. As part of that, I also did a lot of work to export several years worth of session hits from GA into Umami, because I wanted to have the historical traffic available just in case I ever wanted to compare. I frankly hadn't looked at how much data was getting used by the DB until just now. I think the vast majority of the data is actually the historical values prior to 2023-04. I'll see if I can complete the migration now that you've bumped it, but I'll also look at doing a DB dump (so I have that around), and then deleting rows prior to 2023 or so to save space thank you! just to check, which of the current project contents if any should I delete before re-running the migration? (or truncate, etc)

acemarkeOP•12mo ago

christian•12mo ago

Please try to rerun as-is from the migration status panel in the original Postgres Legacy service

Brody•12mo ago

is umami in a different project?

acemarkeOP•12mo ago

I'm a bit hazy on what "project" means for Railway, specifically, tbh :) The team consists of two pieces, which I assume are "projects": the Postgres DB, and the Umami app server. FWIW, last night I made a backup of the full DB locally (8GB zipped, 36GB unzipped), then deleted all historical data prior to 2023 and ran VACUUM FULL on the tables. The size report inside PG now says it's only 19GB instead of 73GB.
I'll try running the migration again shortly.

Brody•12mo ago

dashboard > projects > services projects show up on the dashboard, then within the projects there are services, like umami and postgres so with that said, is your postgres service in a different project then your umami service?

acemarkeOP•12mo ago

apparently, yes

Brody•12mo ago

you are subjecting yourself to unnecessary egress fees by having your database and umami services in separate projects, if you have both in the same project you would be able to use the private url to connect to the database thus eliminating any database to service egress fees. I say this because you have a fairly large database so I imagine you could cut costs by having both in the same project and using private networking

acemarkeOP•12mo ago

oh, that's good to know can I move the Umami service over?

Brody•12mo ago

I imagine it might be easier to move the umami service into the project that contains postgres does your umami service have a volume

acemarkeOP•12mo ago

don't think so, no

Brody•12mo ago

deploy my umami template into your project that already has the database, delete the database that my template will add, then hook your database up to the new umami service

acemarkeOP•12mo ago

which template is that?

Brody•12mo ago

the only umami template there is wait do you know railway has templates for things like umami already made?

acemarkeOP•12mo ago

only vaguely. I set this up back in April and was following bits and pieces from a couple different blog posts. Don't know if these templates existed back then

Brody•12mo ago

they did, and there was an umami template back then too

acemarkeOP•12mo ago

awright, got that template deployed. I'll try deleting the fresh DB, stopping the old project's Umami service, and pointing the new service at the legacy DB (which I haven't tried to re-migrate yet) also need to switch over the custom semi-domain, I guess: redux-docs-umami.up.railway.app

Brody•12mo ago

as a sanity check, can you show me a screenshot of the project you just deployed the umami template into?

acemarkeOP•12mo ago

I deleted the fresh Postgres created by the template, and configured Umami to point to DATABASE_URL from "Postgres Legacy"

acemarkeOP•12mo ago

and if I visit the URL from that fresh Umami instance, I do see real data that matches my sites:

Brody•12mo ago

awesome, so now to prepare yourself for a retry on the migration, delete all the failed stuff and the dangling volumes, and that postgres legacy service with a volume

acemarkeOP•12mo ago

yeah. also just switched the redux-docs-umami.up.railway.app name over to the new instance

Brody•12mo ago

sounds good

acemarkeOP•12mo ago

okay, we're down to just this:

Brody•12mo ago

rename that database to just Postgres your volume limit is 100gb so you shouldn't have any problems running the migration now

acemarkeOP•12mo ago

okay, that migration finally succeeded. now let's see what we've got... fiddled with the DB URL settings a bit, and it looks like I've got real data showing up in my Umami dashboard again (it does look like the new Postgres service "only" has 50GB instead of the 100GB that was applied yesterday, but not surprising given that I deleted that instance)

acemarkeOP•12mo ago

I guess it's safe to delete the old DB plugin now?

Brody•12mo ago

have you swapped umami's database url over to the new databases private url?

acemarkeOP•12mo ago

yeah: ${{Postgres (new).DATABASE_URL}}

Brody•12mo ago

use the private url, otherwise you will still pay egrees fees on database to service communication

acemarkeOP•12mo ago

where do I find that? nm, settings panel so after tweaking the name, just postgres-new.railway.internal as the database URL?

Brody•12mo ago

nope, remove the current reference and use the auto complete

acemarkeOP•12mo ago

ah... sorry, got me confused here. clarify that one? oh wait, ${{"Postgres (new)".DATABASE_PRIVATE_URL}} ?

Brody•12mo ago

that's it!

acemarkeOP•12mo ago

ok, giving that a shot

Brody•12mo ago

there's auto complete on all service variables in the same project so you should never have a need to type out these kinds of single variable references

acemarkeOP•12mo ago

hmm. build logs show it acknowledging that DATABASE_URL exists, but unable to connect

Brody•12mo ago

umami is a docker image, it doesn't have build logs, do you perhaps mean deployment logs?

acemarkeOP•12mo ago

sorry, yeah

Brody•12mo ago

show me the logs please?

acemarkeOP•12mo ago

✓ DATABASE_URL is defined.
✗ Unable to connect to the database.
error Command failed with exit code 1.

✓ DATABASE_URL is defined.
✗ Unable to connect to the database.
error Command failed with exit code 1.

Brody•12mo ago

gotcha, can you click the eye icon on the database url and make sure it renders properly?

acemarkeOP•12mo ago

Brody•12mo ago

screenshot of your project please

acemarkeOP•12mo ago

Brody•12mo ago

remove the (new) from the name of the database, the brackets are causing issues

acemarkeOP•12mo ago

of course they are :)

Brody•12mo ago

and update your variable reference accordingly

acemarkeOP•12mo ago

okay, that seems to be working

Brody•12mo ago

awesome then I think you're all set?

acemarkeOP•12mo ago

I think so. safe to delete the old PG plugin now?

Brody•12mo ago

if you say all your data made it to the new database in tact, then yes

acemarkeOP•12mo ago

ok, cool. thank you very much for all your help. like I said, I've got some experience with backend / full-stack stuff, but these days most of my work is frontend, and I generally avoid messing with backend services as much as possible :) so, much appreciated. (heh. any chance I could get this volume bumped up to 100GB, since I had to delete the earlier one? :) )

Brody•12mo ago

it's not 100gb?? ah must have only been bumped on a single volume and not account wide I'm not about to do that, I don't work for railway, I'd recommend emailing [email protected] instead, it's not an urgent request so they'd get back to you after the new years

acemarkeOP•12mo ago

sure. thanks again!

Brody•12mo ago

no problem!

Gaming

Programming

Postgres Migration failed at "Migrating Data"