Unable to restore postgres database from back-up file
Anonymous
TRIALOP

2 years ago

I'm following the steps here: https://blog.railway.app/p/postgre-backup

It seems to create the back-up file OK.

But when attempting to restore I get a huge stream of what looks like the same error over and over (for each table in the schema). Here's an example of one such error:

pg_restore: error: could not execute query: ERROR:  type "PAYMENT_COLLECTION_STATUS_ENUM" already exists
Command was: CREATE TYPE public."PAYMENT_COLLECTION_STATUS_ENUM" AS ENUM (
    'not_paid',
    'awaiting',
    'authorized',
    'partially_authorized',
    'canceled'
);

I'm using the restore command as outlined in Railway's blog post: pg_restore -U -h -p -W -F t -d

Anyone else experienced this issue and know how I can solve it?

Thanks.

18 Replies

Anonymous
TRIALOP

2 years ago

76c6f0bb-2ca0-4c7c-803f-e1ae1fba90ca

Anonymous
TRIALOP

2 years ago

Found a reddit post related to this issue: https://www.reddit.com/r/PostgreSQL/comments/xe832f/restore_error/

On there someone I should do this:

drop database ;
create database ;

Then restore to the new database.

Is that right? Feels a bit scary to drop the DB without being sure it'll work…

brody
EMPLOYEE

2 years ago

does the database you are restoring to have data in it?

Anonymous
TRIALOP

2 years ago

Yeah it does.

brody
EMPLOYEE

2 years ago

do you want to overwrite that data?

Anonymous
TRIALOP

2 years ago

But it's OK if the data in it is overwritten.

Anonymous
TRIALOP

2 years ago

Snap — yes.

brody
EMPLOYEE

2 years ago

then i think you have all the information needed to decide if you want to drop your database

Anonymous
TRIALOP

2 years ago

I just fear the restore will still fail if I drop it which will leave me in an even worse position.

Anonymous
TRIALOP

2 years ago

But I'll give it a go.

brody
EMPLOYEE

2 years ago

is there a flag you can provide to pg_restore that will simply overwrite any existing tables?

Anonymous
TRIALOP

2 years ago

I'd read "--clean" should work but it doesn't appear to.

brody
EMPLOYEE

2 years ago

not sure, havent ever had to use pg_restore myself

Anonymous
TRIALOP

2 years ago

OK no worries, thanks for the help anyway.

Next question — how can I actually go about dropping my database? Doesn't seem it's possible via the Railway interface (I can delete the service but that doesn't seem the right action).

brody
EMPLOYEE

2 years ago

connect to the database with your favorite database gui tool and run your desired query

Anonymous
TRIALOP

2 years ago

Cool thanks. Will give it a try with pgadmin4

Anonymous
TRIALOP

2 years ago

Can't drop the database via pgadmin4 (assume due to user permissions).

jack
PRO

2 years ago

Using root database user? Maybe try Brody’s dbgate template?

https://railway.app/template/dbgate

Loading...