Restoring the pg_dump from the postgres-s3-backups tool
We have a bunch of database dumps via this tool: https://github.com/railwayapp-templates/postgres-s3-backups
We're trying to figure out how to actually get the restore to work, does anyone have detailed directions?
GitHub
GitHub - railwayapp-templates/postgres-s3-backups: A simple Node ap...
A simple Node app to automatically backup PostgreSQL databases to an S3 bucket - railwayapp-templates/postgres-s3-backups
Solution:Jump to solution
this is what worked for me and my small backup -
- un-gzip the tarball to end up with a
backup_filename.tar file
- run pg_restore --dbname=${DATABASE_URL} --format=tar < backup_filename.tar
- ignore errors and wait...46 Replies
Project ID:
N/ASo the issue we're facing is that we're not sure which pg_restore tool to use. If it's a local postgres installed via homebrew, I'm having a hard time getting timescale and timescale-toolkit installed. Should I be doing this inside of a dockerized postgres/timescale container? Or is there a way to restore directly to a railway instance?
what version of postgres are your backups made from?
is your backup service running the latest commit?
What do you mean by backup service? We're trying to use pg_restore either on a local machine (postgres installed via homebrew) or via a docker container. In any case, it looks like it needs timescaledb and timescaledb-toolkit installed to restore properly.
btw, project id is
b08731c2-2a20-4ba4-b7b2-a97b42af589fthe backup service that made these backups, just trying to gather information so i can give you the best answer i can
Ah, I see. We made the backups via an instance of https://github.com/railwayapp-templates/postgres-s3-backups with the latest commit. The postgres instance is also really up to date. I ran Railway's migration tool earlier this year to upgrade the postgres version and data store.
okay thanks, what is the source image of the database you are trying to restore to?
Honestly, I've tried a whole bunch of options. I've tried another railway postgresql instance which presumably should be the same as the original data source. I've also tried restoring to a homebrew install of postresql@15 and postgresql@16 and a timescaledb docker image (https://hub.docker.com/layers/timescale/timescaledb/latest-pg16/images/sha256-d669a599ce935613c3e322fc56678b76762e53a9f8dac22a86a04c07cb0a4285?context=explore). In all cases, it seems I need both timescaledb and timescaledb-toolkit installed, and none of those destinations have it installed out of the box.
I also tried installing timescaledb and timescaledb-toolkit locally via homebrew, but there's version issues between postgres 15 & 16
what is your version of pg_restore?
Depends on which. 🙂 With homebrew 15, it's 15.x and it was giving me an error about incompatible headers. When I installed homebrew's postgresql@16, I think it was pg_restore 16.2, but it might've been different.
The timescaledb docker image has pg_restore 16.2.
does your app use anything timescale related?
To my knowledge, not at all. But it seems all of our pg_dumps have the timescale context baked into them.
they would, but since you dont need anything timescale related you can ignore any such errors
Hmm. But I end up with 0 records. The schema is restored, but no rows.
how large are you database backup tarballs?
tbh, I don't even know what timescale is. We use a pretty barebones postgres setup.
10gb 😬
what command have you ran to restore the backup?
pg_restore -d "postgresql://username:password@localhost:5432/postgres" -F t backup-2024-03-13T06-45-00-104Z.tarare you both pro?
Is that a Railway thing? @cstoneham is the account creator, and I'm pretty sure it's a pro account. I'm his cofounder and an Owner on the account.
Looks like.
just asking so i know that you arent trying to restore a 10gb dump to a 5gb database haha
Gotchya gotchya.
look like i have a small backup laying around, ill try some commands to see what works
Solution
this is what worked for me and my small backup -
- un-gzip the tarball to end up with a
backup_filename.tar file
- run pg_restore --dbname=${DATABASE_URL} --format=tar < backup_filename.tar
- ignore errors and waitdo you have a tar or tar.gz? otherwise you might need to extract it and put it into a folder and use that folder in the restore command
the backup template makes tar.gz files
you where able to restore a what 40gb backup? and theirs is only 10gb
This doesn't actually seem to work through the issues that I'm facing. What version of pg_restore are you using, and are you restoring to another database hosted on Railway?
What version of pg_restore are you using
pg_restore (PostgreSQL) 16.0
are you restoring to another database hosted on Railway?yes
I'm trying this right now. I'm getting pages and pages of this vomit, but maybe it'll work anyway?
does it stop itself, or have you been stopping it thinking its not doing anything?
I've been trying so many combinations of pg_restore versions and attempts to install timescale that I'm not 100% sure if I've done this test yet.
10gb is a big backup, it took several seconds to restore my 108kb backup, i think you should just let it run, whats the worst that can happen?
Yup. Running now.
Much appreciated!
Plausible that it's working.
thats very promising
Update here: it took quite a while on my current internet connection, but I was able to successfully restore the backup using your method!
For future readers:
I had to install pg_restore 16.x on my machine (I used homebrew's postgresql@16) and restore to another railway postgresql instance. I was never able to restore to a local db.
awesome! maybe next time you need to restore a backup you could temporarily rent a VPS? that's why I would do since I only have 9mbps up
Yeah, that's a great idea.
just curious what was the final in use size of the volume?
15.07gb
my backup tar was 10.69gb
that's checks out, glad you where able to get this sorted!