OperationalError: could not connect to server: Operation timed out - when trying to change database from sqlite to postresql
psql: could not connect to server: Connection timed out Is the server running on host "IP" and accepting TCP/IP connections on port 5432?
Are you really connecting to an IP Address rather to its DNS name? That could be the problem - when changing the RDS size the IP had changed. And in fact it can change from time to time unexpectedly.
Never use the IP directly (e.g. 172.31.x.x), always use the DNS name like myrds.xxx.us-west-2.rds.amazonaws.com.
Hope that helps :)
Comments
-
Murcielago over 1 year
I have upgraded my RDS amazon db instance (postgresql database ) from micro to small. Although it says that it is available on the amazon console, I keep getting the error
(psycopg2.OperationalError) could not connect to server: Connection timed out
. everytime I access the db from my app. when trying to access the server from my ubuntu machine with:psql -h Ip -d db -U myuser
I get the same error:
psql: could not connect to server: Connection timed out Is the server running on host "IP" and accepting TCP/IP connections on port 5432?
On the other hand, everything seems fine as well inside of PgAdmin4.
I am wondering if I just need to wait a bit longer to let things initialize. By the way, I am working on a django project, are there some settings that I need to take care off after such an upgrate? Has someone had the same problem?
here is postgres error log:
2020-08-07 10:00:16 UTC::@:[13371]:LOG: checkpoint starting: time 2020-08-07 10:00:16 UTC::@:[13371]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.101 s, sync=0.005 s, total=0.132 s; sync files=1, longest=0.005 s, average=0.005 s; distance=65536 kB, estimate=65537 kB 2020-08-07 10:05:16 UTC::@:[13371]:LOG: checkpoint starting: time 2020-08-07 10:05:16 UTC::@:[13371]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.101 s, sync=0.005 s, total=0.136 s; sync files=1, longest=0.005 s, average=0.005 s; distance=65536 kB, estimate=65537 kB 2020-08-07 10:10:16 UTC::@:[13371]:LOG: checkpoint starting: time 2020-08-07 10:10:16 UTC::@:[13371]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.101 s, sync=0.005 s, total=0.135 s; sync files=1, longest=0.005 s, average=0.005 s; distance=65534 kB, estimate=65537 kB 2020-08-07 10:12:03 UTC::@:[13368]:LOG: received fast shutdown request 2020-08-07 10:12:03 UTC::@:[13368]:LOG: aborting any active transactions 2020-08-07 10:12:03 UTC::@:[13368]:LOG: background worker "logical replication launcher" (PID 13377) exited with exit code 1 2020-08-07 10:12:03 UTC::@:[13371]:LOG: shutting down 2020-08-07 10:12:03 UTC::@:[13371]:LOG: checkpoint starting: shutdown immediate 2020-08-07 10:12:03 UTC::@:[13371]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.000 s, sync=0.007 s, total=0.037 s; sync files=1, longest=0.007 s, average=0.007 s; distance=65535 kB, estimate=65537 kB 2020-08-07 10:12:03 UTC::@:[13368]:LOG: database system is shut down 2020-08-07 10:12:06 UTC::@:[28963]:LOG: database system was shut down at 2020-08-07 10:12:03 UTC 2020-08-07 10:12:06 UTC::@:[28961]:LOG: database system is ready to accept connections 2020-08-07 10:17:06 UTC::@:[28964]:LOG: checkpoint starting: time 2020-08-07 10:17:07 UTC::@:[28964]:LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.881 s, sync=0.006 s, total=0.921 s; sync files=3, longest=0.006 s, average=0.002 s; distance=16 kB, estimate=16 kB 2020-08-07 10:22:06 UTC::@:[28964]:LOG: checkpoint starting: time 2020-08-07 10:22:06 UTC::@:[28964]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.678 s, sync=0.006 s, total=0.712 s; sync files=1, longest=0.006 s, average=0.006 s; distance=65521 kB, estimate=65521 kB 2020-08-07 10:27:06 UTC::@:[28964]:LOG: checkpoint starting: time 2020-08-07 10:27:07 UTC::@:[28964]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.693 s, sync=0.006 s, total=0.727 s; sync files=1, longest=0.006 s, average=0.006 s; distance=65536 kB, estimate=65536 kB 2020-08-07 10:32:06 UTC::@:[28964]:LOG: checkpoint starting: time 2020-08-07 10:32:06 UTC::@:[28964]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.101 s, sync=0.005 s, total=0.131 s; sync files=1, longest=0.005 s, average=0.005 s; distance=65536 kB, estimate=65536 kB 2020-08-07 10:37:06 UTC::@:[28964]:LOG: checkpoint starting: time 2020-08-07 10:37:06 UTC::@:[28964]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.101 s, sync=0.005 s, total=0.131 s; sync files=1, longest=0.005 s, average=0.005 s; distance=65535 kB, estimate=65536 kB 2020-08-07 10:42:06 UTC::@:[28964]:LOG: checkpoint starting: time 2020-08-07 10:42:06 UTC::@:[28964]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.101 s, sync=0.005 s, total=0.132 s; sync files=1, longest=0.005 s, average=0.005 s; distance=65536 kB, estimate=65536 kB 2020-08-07 10:47:06 UTC::@:[28964]:LOG: checkpoint starting: time 2020-08-07 10:47:06 UTC::@:[28964]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.101 s, sync=0.005 s, total=0.135 s; sync files=1, longest=0.005 s, average=0.005 s; distance=65536 kB, estimate=65536 kB 2020-08-07 10:52:06 UTC::@:[28964]:LOG: checkpoint starting: time 2020-08-07 10:52:07 UTC::@:[28964]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.101 s, sync=0.006 s, total=0.133 s; sync files=1, longest=0.006 s, average=0.006 s; distance=65535 kB, estimate=65536 kB 2020-08-07 10:57:06 UTC::@:[28964]:LOG: checkpoint starting: time 2020-08-07 10:57:06 UTC::@:[28964]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.101 s, sync=0.005 s, total=0.137 s; sync files=1, longest=0.005 s, average=0.005 s; distance=65536 kB, estimate=65536 kB ----------------------- END OF LOG --
I tried testing the port on the server, rebooting my RDS instance nothing works, I have also uninstalled and re installed psycopg2 and sqlalchemy.
I can connect to the db from the command line. I tested the port with nmap, giving the following result:
PORT STATE SERVICE 5432/tcp filtered postgresql
I am still super confused and the app not working for the past 10 hours! I cannot find out where this is coming from, I would love some help on this issue, I start to get pressured at work. thank you
UPDATE: I have just noticed that in the aws console in
database/mydb/configurations/parameters group
, it saysdefault.postgres11 (in-sync)
. Could that be the cause of the sudden issue after the upgrade? I don't remember saying in-sync before, I wonder how long it is going to last-
dortegaoh almost 4 yearsA timeout usually points toward a firewall issue.
-
Murcielago almost 4 yearswhere could I start debugging?
-
-
Murcielago almost 4 yearsI wrote IP to not display the IP address of my rds instance. From what you are saying, I should rather write the amazon endpoint address? would that mean that I should also have it in my postgresql.conf file, as well as pg_hba.conf file?
-
MLu almost 4 yearsYes, use myrds.xxx.us-west-1.rds.amazonaws.com rather then the IP like 172.31.x.x. The IP does change from time to time. The DNS name doesn’t.
-
Murcielago almost 4 yearsthank you for your help, I am able to manually connect to the db from command and psql now. When the app try to access the db, it still output the same error, would you have a hint on where to look at to modify the outdated IP (knowing that django setting file is already good with the dns name?
-
MLu almost 4 yearsHave you restarted Apache / Nginx? Django only reads the settings file on start.
-
Murcielago almost 4 yearsyes nginx and supervisorctl are re loaded
-
MLu almost 4 yearsReboot the instance then. The IP may be cached somewhere else. Also check that it’s not in some other config file with
grep -r 172.31.x.x
or whatever the IP is.