Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

postgresql - Heroku database restore issue

Have gone through different solutions available on stackoverflowand also on different forums. But none addresses the precise problem.

As per the documentation: https://devcenter.heroku.com/articles/heroku-postgres-import-export

I have the dump file created from my local database, with this command:

pg_dump -Fc --no-acl --no-owner -h localhost -U postgres dss_iaya>dss_iaya_db_dump1.dump

Then as per documentation, uploaded to a server with public access URL: https://firebasestorage.googleapis.com/v0/b/iaya-664f3.appspot.com/o/dss_iaya_db_dump1.dump?alt=media&token=06167d04-1e98-4e4b-b0e0-9d83a86dd167

Now when I try to restore on Heroku as per its documentation syntax heroku pg:backups:restore [BACKUP] [DATABASE] --app APP using following command, it returns error message when restoring.

heroku pg:backups:restore  --app heroku-postgres-*** 'https://firebasestorage.***/dss_iaya_db_dump1.dump?alt=media&token=***' 'postgres://quesu***:I***@ec2-54-***.eu-west-1.compute.amazonaws.com:5432/d3n***k0' 

I have used *** for security purpose only, as can not mention full credentials. But I believe one can understand the whole syntax.

When I restore same .dump file on a newly created local database it works without any issues and creates/restores the whole database with tables and data.

Command console screen

Info log

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Just found the solution, actually two things were wrong in my case.

One, the uploaded .dump file was not well readable/usable by the heroku.

Two, the heroku postgresql DB complete URL was not required to be provided.

So, the right way that worked for me was that the uploaded file should be accessible without any token and also without any virtual/indirect path, etc. The URL to the file should point to the file directly. In my questioned problem, I was using firebase to host my DB file temporarily to do the heroku operation. And firebase was not giving direct URL to the uploaded physical file.

heroku pg:backups:restore  --app heroku-postgres-f3*** 'https://www.h***.com/dss_iaya_db_dump2.dump' DATABASE_URL

After typing this command, I was asked to retype the heroku app name just to confirm the operation. Once done, everything worked like a charm.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...