Преди да получа отрицателното мнение, знам, че има много подобни публикации, но ги прочетох и ги използвах, за да стигна до тази точка, но не мисля, че отговарят на въпроса ми.
Преглед
Опитвам се да свържа моя тестер heroku сайт [РЕДАКТИРАНЕ: премахнато] към моята база данни PostgreSQL. Преди това опитах MS Server, но съм на Windows и Heroku не хареса pyodbc.
Съкратена версия
Почти съм сигурен, че съм настроил своя PostgreSQL правилно, но се опитвам да определя дали имам правилното име на хост за URL адреса на PostgreSQL и дали трябва да направя нещо повече по отношение на разрешаването на достъп през защитната стена на Windows.
Дълга версия
PostgreSQL
Следвайки приетия отговор Как да разрешим отдалечен достъп до база данни на PostgreSQL моят postgresql.conf
има listen_addresses = '*'
и порт 5432. Сложих допълнителен ред
host all all 0.0.0.0/0 md5
на pg_hba.conf
, за да получите следното
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
host all all 0.0.0.0/0 md5
Сега мога да направя това от командния ред
C:\etc> psql -U postgres -h 192.XXX.XX.XXX -d ProductionData
Където 192.XXX.XX.XXX
е моят IPv4 адрес (намерен от Start→change Ethernet settings→MyCompany.local) и всичко работи. Знам, защото ако отменя реда на pg_hba.conf
, получавам това
(venv) C:\etc> psql -U postgres -h 192.XXX.XX.XXX -d ProductionData
psql: FATAL: no pg_hba.conf entry for host "192.XXX.XX.XXX", user "postgres", database "ProductionData", SSL off
Хероку
Разбирам, че postgres се нуждае от URL адрес на формуляра
postgresql://username:password@hostname/database
or
postgresql://username:password@hostname:port/database
Наистина не разбирам името на хоста, освен това е IP мрежовият адрес. Опитах localhost
и същия IPv4 адрес като по-горе 192.XXX.XX.XXX
и 192.XXX.XX.XXX:5432
.
Секцията за регистрационни файлове по-долу съдържа netstat, настройката за конфигурация на heroku, рестартиране на postgresql за всеки от тези опити, направени в git bash, но получавам следното с двата 192.XXX.XX.XXX
като изчакване.
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?
Is the server running on host "192.XXX.XX.XXX" and accepting
TCP/IP connections on port 5432?
Защитна стена на Windows
Създадох входящо правило за разрешаване на достъп...
Тип протокол - TCP
Местно пристанище - 5432
Отдалечен порт - Всички портове
Програми - Тази програма - %ProgramFiles%\PostgreSQL\11\bin\postgres.exe
Дневници на Heroku
Опит #1 - localhost, резултат грешка
$ pg_ctl -D "C:\Program Files\PostgreSQL\11\data" restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-02-19 10:09:04.423 GMT [11952] LOG: listening on IPv6 address "::", port 5432
2019-02-19 10:09:04.423 GMT [11952] LOG: listening on IPv4 address "0.0.0.0", port 5432
2019-02-19 10:09:04.460 GMT [11952] LOG: redirecting log output to logging collector process
2019-02-19 10:09:04.460 GMT [11952] HINT: Future log output will appear in directory "log".
done
server started
$ netstat -ant | findstr 5432
TCP 0.0.0.0:5432 0.0.0.0:0 LISTENING InHost
TCP [::]:5432 [::]:0 LISTENING InHost
$ heroku restart -a pgtester
Restarting dynos on ? pgtester... done
$ heroku logs -t -a pgtester
...lots of stack errors...
2019-02-19T09:52:25.731597+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 437, in connect
2019-02-19T09:52:25.731599+00:00 app[web.1]: return self.dbapi.connect(*cargs, **cparams)
2019-02-19T09:52:25.731600+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/psycopg2/__init__.py", line 130, in connect
2019-02-19T09:52:25.731602+00:00 app[web.1]: conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
2019-02-19T09:52:25.731604+00:00 app[web.1]: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
2019-02-19T09:52:25.731606+00:00 app[web.1]: Is the server running on host "localhost" (127.0.0.1) and accepting
2019-02-19T09:52:25.731607+00:00 app[web.1]: TCP/IP connections on port 5432?
2019-02-19T09:52:25.731646+00:00 app[web.1]: (Background on this error at: http://sqlalche.me/e/e3q8)
Опит #2 - 192.XXX.XX.XXX, изчакване на резултата
$ heroku config:set DEV_DATABASE_URL=postgresql://postgres:[email protected]/ProductionData
Setting DEV_DATABASE_URL and restarting ? pgtester... done, v47
DEV_DATABASE_URL: postgresql://postgres:[email protected]/ProductionData
$ heroku restart -a pgtester
Restarting dynos on ? pgtester... done
$ netstat -ant | findstr 5432
TCP 0.0.0.0:5432 0.0.0.0:0 LISTENING InHost
TCP [::]:5432 [::]:0 LISTENING InHost
$ heroku logs -t -a pgtester
...lots of stack errors...
2019-02-19T10:00:57.273592+00:00 app[api]: Set DEV_DATABASE_URL config vars by user [email protected]
2019-02-19T10:00:57.273592+00:00 app[api]: Release v47 created by user [email protected]
2019-02-19T10:01:05.401378+00:00 heroku[web.1]: Starting process with command `waitress-serve --port=44666 flasky:app`
2019-02-19T10:01:05.672789+00:00 heroku[web.1]: Restarting
2019-02-19T10:01:08.193167+00:00 app[web.1]: [heroku-exec] Starting
2019-02-19T10:01:10.388293+00:00 app[web.1]: postgresql://postgres:[email protected]/ProductionData
2019-02-19T10:01:10.897113+00:00 app[web.1]: Serving on http://0.0.0.0:44666
2019-02-19T10:01:12.089984+00:00 heroku[web.1]: Stopping all processes with SIGTERM
2019-02-19T10:01:12.196052+00:00 heroku[web.1]: Process exited with status 143
2019-02-19T10:01:12.358184+00:00 heroku[web.1]: Starting process with command `waitress-serve --port=18540 flasky:app`
2019-02-19T10:01:16.133191+00:00 app[web.1]: [heroku-exec] Starting
2019-02-19T10:01:18.520615+00:00 app[web.1]: postgresql://postgres:[email protected]/ProductionData
2019-02-19T10:01:19.161158+00:00 app[web.1]: Serving on http://0.0.0.0:18540
2019-02-19T10:01:19.687131+00:00 heroku[web.1]: State changed from starting to up
2019-02-19T10:01:33.125392+00:00 heroku[router]: at=info method=GET path="/" host=pgtester.herokuapp.com request_id=7e65dc99-b99f-4b81-8bbe-a4b98adebd91 fwd="185.16.227.58" dyno=web.1 connect=1ms service=231ms status=200 bytes=2077 protocol=https
2019-02-19T10:02:11.389923+00:00 heroku[router]: at=error code=H12 desc="Request timeout" method=POST path="/" host=pgtester.herokuapp.com request_id=6837dbb0-b2e4-44ee-a67c-2f79a1c7c1b6 fwd="185.16.227.58" dyno=web.1 connect=1ms service=30000ms status=503 bytes=0 protocol=https
Опит #3 - 192.XXX.XX.XXX:5432, изчакване на резултата
$ pg_ctl -D "C:\Program Files\PostgreSQL\11\data" restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-02-19 10:09:04.423 GMT [11952] LOG: listening on IPv6 address "::", port 5432
2019-02-19 10:09:04.423 GMT [11952] LOG: listening on IPv4 address "0.0.0.0", port 5432
2019-02-19 10:09:04.460 GMT [11952] LOG: redirecting log output to logging collector process
2019-02-19 10:09:04.460 GMT [11952] HINT: Future log output will appear in directory "log".
done
server started
$ netstat -ant | findstr 5432
TCP 0.0.0.0:5432 0.0.0.0:0 LISTENING InHost
TCP [::]:5432 [::]:0 LISTENING InHost
$ heroku config:set DEV_DATABASE_URL=postgresql://postgres:[email protected]:5432/ProductionData
Setting DEV_DATABASE_URL and restarting ? pgtester... done, v48
DEV_DATABASE_URL: postgresql://postgres:[email protected]:5432/ProductionData
$ netstat -ant | findstr 5432
TCP 0.0.0.0:5432 0.0.0.0:0 LISTENING InHost
TCP [::]:5432 [::]:0 LISTENING InHost
$ heroku restart -a pgtester
Restarting dynos on ? pgtester... done
$ heroku logs -t -a pgtester
...lots of stack errors...
2019-02-19T10:11:10.352241+00:00 app[api]: Release v48 created by user [email protected]
2019-02-19T10:11:10.352241+00:00 app[api]: Set DEV_DATABASE_URL config vars by user [email protected]
2019-02-19T10:11:17.316331+00:00 heroku[web.1]: Starting process with command `waitress-serve --port=56616 flasky:app`
2019-02-19T10:11:20.998316+00:00 app[web.1]: [heroku-exec] Starting
2019-02-19T10:11:21.724624+00:00 heroku[web.1]: Restarting
2019-02-19T10:11:25.043993+00:00 app[web.1]: postgresql://postgres:[email protected]:5432/ProductionData
2019-02-19T10:11:25.797589+00:00 app[web.1]: Serving on http://0.0.0.0:56616
2019-02-19T10:11:26.872124+00:00 heroku[web.1]: Starting process with command `waitress-serve --port=51247 flasky:app`
2019-02-19T10:11:27.101119+00:00 heroku[web.1]: Stopping all processes with SIGTERM
2019-02-19T10:11:27.211928+00:00 heroku[web.1]: Process exited with status 143
2019-02-19T10:11:29.033774+00:00 app[web.1]: [heroku-exec] Starting
2019-02-19T10:11:30.261042+00:00 app[web.1]: postgresql://postgres:[email protected]:5432/ProductionData
2019-02-19T10:11:30.570976+00:00 app[web.1]: Serving on http://0.0.0.0:51247
2019-02-19T10:11:31.054334+00:00 heroku[web.1]: State changed from starting to up
2019-02-19T10:11:34.448659+00:00 heroku[router]: at=info method=GET path="/" host=pgtester.herokuapp.com request_id=7498e3a1-5a09-419b-8038-fc4bf3ddd642 fwd="185.16.227.58" dyno=web.1 connect=1ms service=170ms status=200 bytes=2077 protocol=https
...lots of stack errors...
2019-02-19T10:16:14.079101+00:00 app[web.1]: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection timed out
2019-02-19T10:16:14.079102+00:00 app[web.1]: Is the server running on host "192.168.10.162" and accepting
2019-02-19T10:16:14.079104+00:00 app[web.1]: TCP/IP connections on port 5432?
2019-02-19T10:16:14.079144+00:00 app[web.1]: (Background on this error at: http://sqlalche.me/e/e3q8)
192.x.y.z
IP адрес. Това е нетно-локално. Ако наистина имате основателна причина да направите това, ще ви е необходим статичен IP адрес или динамичен DNS домейн и пренасочване на портове или подобни. - person Chris   schedule 19.02.2019