I am attempting to migrate my heroku database to a database I have hosted on linode (linux vm).
I am able to get heroku to recognize its own database as well as the correct username/database name of the remote db. However it is attempting to pick my localhost pg db not the remote one.
This is Heroku's description of the pg:pull command:
$ heroku help pg:pull
See more details with DEBUG=*
pull Heroku database into local or remote database
USAGE
$ heroku pg:pull SOURCE TARGET
OPTIONS
-a, --app=app (required) app to run command against
-r, --remote=remote git remote of app to use
--exclude-table-data=exclude-table-data tables for which data should be excluded (use ';' to split multiple names)
DESCRIPTION
Pull from SOURCE into TARGET.
TARGET must be one of:
* a database name (i.e. on a local PostgreSQL server) => TARGET must not exist and will be created
* a fully qualified URL to a local PostgreSQL server => TARGET must not exist and will be created
* a fully qualified URL to a remote PostgreSQL server => TARGET must exist and be empty
To delete a local database run `dropdb TARGET`
To create an empty remote database, run `createdb` with connection command-line options (run `createdb --help` for details).
Examples:
# pull Heroku DB named postgresql-swimmingly-100 into local DB mylocaldb that must not exist
$ heroku pg:pull postgresql-swimmingly-100 mylocaldb --app sushi
# pull Heroku DB named postgresql-swimmingly-100 into empty remote DB at postgres://myhost/mydb
$ heroku pg:pull postgresql-swimmingly-100 postgres://myhost/mydb --app sushi
I thought alright not bad, so I tried:
heroku pg:credentials:url
Which gives you info like:
Connection information for default credential.
Connection info string:
"dbname=[db name here] host=[aws ip here] port=5432 user=[db username] password=[db password here] sslmode=require"
Connection URL:
postgres://[db username]:[db password here]@[db ip here]:5432/[db name here]
I thought wonderful I can use that connection url above for the heroku db and follow its syntax for my own linode pg db.
heroku pg:pull postgres://[heroku db username]:[heroku db password here]@[heroku db ip here]:5432/[heroku db name here] postgres://[linode db username here]:[linode db password here]@[linode ip here]/[linode db name here] --app [my-app-name-here]
Then I get the issue with it doesn't even accept its own db uri is valid:
Unknown database:
[Stuff I put in for their db url here, like the full string]
Valid options are: DATABASE_URL
Alright this is a little upsetting because I know that the credentials above work... I have connected to my heroku database from my rails app using those credentials. Whatever maybe they just want something else for it so I look at:
$ heroku pg:info
task: runHook init
plugin: heroku
root: /usr/lib/heroku
See more details with DEBUG=*
=== DATABASE_URL
Plan: Hobby-dev
Status: Available
Connections: 1/20
PG Version: 11.6
Created: 2019-06-03 16:06 UTC
Data Size: 9.8 MB
Tables: 15
Rows: 450/10000 (In compliance)
Fork/Follow: Unsupported
Rollback: Unsupported
Continuous Protection: Off
Add-on: postgresql-[random name here]
So I go alright maybe they want that "Add-on:" name instead:
$ heroku pg:pull postgresql-[random name here] postgres://[linode db username here]:[linode db password here]@[linode ip here]/[linode db name here] --app [my-app-name-here]
This gets me really close but I am now stumped on how to get it to look at the correct ip address/host, this is what I got:
heroku-cli: Pulling postgresql-[random name here] ---> postgres://[linode db username here]:[linode db password here]@[linode ip here]:5432/[linode db name here]
psql: FATAL: no pg_hba.conf entry for host [my **LOCAL** ip address], user [linode db username here], database [linode db name here], SSL on
▸ Remote database is not empty. Please create a new database or use heroku pg:reset
I have also tried:
$ heroku pg:pull postgresql-[random name here] postgres://[linode ip here]:5432/[linode db name here] --app [my-app-name-here]
Thinking it was somehow falling back to my local one when it couldn't figure out the remote. Same error but the username is my local computers username and still my ip not the remote's.
This is so close but why did it pick my local ip address? It got the linode username/db name correct but it is trying to connect it locally which is obviously not what I wanted.
Any suggestions would be very appreciated.... Feel like I followed their commands as well as I possibly could.
On linode I have my settings changed so it is open on 0.0.0.0:5432 not just localhost. So it should be able to accept outside connections.
EDIT: I have looked at these resources for help but found them woefully lacking in any specific details which are the crucial point to actually do this: https://devcenter.heroku.com/articles/heroku-postgresql#pg-push-and-pg-pull
https://blog.heroku.com/push_and_pull_databases_to_and_from_heroku
EDIT: I got the dump file..... but this means heroku's commands don't work. Yes I can do it with the dump file. However I'm upset saying to us this api command can work and it can't... anyone prove me wrong. I'll praise you if it can.