So 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-a97b42af589f

the 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.tar

are 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

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

• wait

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?

pg_restore: error: could not execute query: ERROR:  schema "_timescaledb_cache" does not exist
Command was: COPY _timescaledb_cache.cache_inval_bgw_job  FROM stdin;
pg_restore: error: could not execute query: ERROR:  schema "_timescaledb_cache" does not exist
Command was: COPY _timescaledb_cache.cache_inval_extension  FROM stdin;
pg_restore: error: could not execute query: ERROR:  schema "_timescaledb_cache" does not exist
Command was: COPY _timescaledb_cache.cache_inval_hypertable  FROM stdin;

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 to get this sorted!