Railway•7mo ago

Unable to restore postgres database from back-up file

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'
);

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 <username> -h <host> -p <port> -W -F t -d <db_name> <dump_file_name>

pg_restore -U <username> -h <host> -p <port> -W -F t -d <db_name> <dump_file_name>

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

Railway Blog

How to Backup and Restore Your Postgres Database

In this post, we will go over the process of backing up and restoring your Postgres database hosted on Railway.

16 Replies

Percy•7mo ago

Project ID: 76c6f0bb-2ca0-4c7c-803f-e1ae1fba90ca

highandoverOP•7mo ago

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

highandoverOP•7mo 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 <db_name>;
create database <db_name>;

drop database <db_name>;
create database <db_name>;

Then restore to the new database. Is that right? Feels a bit scary to drop the DB without being sure it'll work...

From the PostgreSQL community on Reddit

Explore this post and more from the PostgreSQL community

Brody•7mo ago

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

highandoverOP•7mo ago

Yeah it does.

Brody•7mo ago

do you want to overwrite that data?

highandoverOP•7mo ago

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

Brody•7mo ago

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

highandoverOP•7mo ago

I just fear the restore will still fail if I drop it which will leave me in an even worse position. But I'll give it a go.

Brody•7mo ago

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

highandoverOP•7mo ago

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

Brody•7mo ago

not sure, havent ever had to use pg_restore myself

highandoverOP•7mo 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•7mo ago

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

highandoverOP•7mo ago

Cool thanks. Will give it a try with pgadmin4 Can't drop the database via pgadmin4 (assume due to user permissions).

Jack•7mo ago

Using root database user? Maybe try Brody’s dbgate template? https://railway.app/template/dbgate

Gaming

Programming

Unable to restore postgres database from back-up file