0

I am running a django app with postgresql, nginx and gunicorn. I have a script where data is pulled from one table in the DB, modified and then needs to replace the existing data in that one table. In the same script, a few tables are also being updated.

When runnning the script, it always results in 502 Bad Gateway because the server times out because of something in the script. Being fairly new the topic, I am struggling to figure out what is going on with the following error.

I only have the following log to work with from postgres:

2020-08-22 14:57:59 UTC::@:[8228]:LOG: checkpoint starting: time
2020-08-22 14:57:59 UTC::@:[8228]: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=70182 kB
2020-08-22 14:58:50 UTC:address-ip(45726):pierre@dbexostock:[25618]:LOG: could not receive data from client: Connection reset by peer
2020-08-22 14:58:50 UTC:address-ip(45724):pierre@dbexostock:[25617]:LOG: could not receive data from client: Connection reset by peer

I think the issue is inside of the connections to the database in the script:

#connect to db
engine = create_engine('postgresql+psycopg2://user:password@amazon.host.west',
                               connect_args={'options': '-csearch_path={}'.format(dbschema)})

#access the data in the historical_data table 
conn = engine.connect()
metadata = sqlalchemy.MetaData()
histoo = sqlalchemy.Table('uploadfiles_historical_data', metadata, autoload=True, autoload_with=engine)
query = sqlalchemy.select([histoo])
resultproxy = conn.execute(query)
        
        
result_set = resultproxy.fetchall()

#update the historical table 
histo = histo2.head(0).to_sql('uploadfiles_histoo', engine, if_exists='replace')
cur = conn.cursor()
output = io.StringIO()
histo2.to_csv(output, sep='\t', header=False, encoding='utf8')
output.seek(0)
cur.copy_from(output,'uploadfiles_histoo')
conn.commit()

#update other tables (example)
itemmdb = df559.to_sql('dashboard_item', engine, if_exists='replace')

I am really confused and been banging my head on this problem for a while and nothing seem to be working. Hoping that someone could see where I am messing up.

Murcielago
  • 905
  • 1
  • 8
  • 30
  • Add a `print("whatever")` after `conn = engine.connect()` and after `print("whatever 2")` after `conn.execute(query)`. We'll be able to see where the error occurs. It kinda looks like connection error. You sure that you are able to connect to that db from the machine you're running the script from (ie using some postgres utility, not python API)? – Marek Piotrowski Aug 22 '20 at 16:46
  • yes the connection is fine, it works well from Pg admin4 as well as psql. I think that something could be wrong with a datatype issue between what the posgresql table "historical_data" is expecting and the datatypes of the pandas df that comes into that table. a date as datetime64[ns] compatible with a posgresql date format? – Murcielago Aug 22 '20 at 17:02
  • You showed the error as experience by the server. What about the error as experience by the client? – jjanes Aug 22 '20 at 17:12
  • how would I go about finding out? my django log does not tell me something is wrong, it tells me that the script is being accessed, but that's all, so I assume that this code is causing the issue since everything is straightforward – Murcielago Aug 22 '20 at 17:18
  • i think that the issue is happening because of unmatching datatypes between the dataframe "histo" and the columns from "historical_data" , i saw this post https://stackoverflow.com/questions/19931300/how-to-debug-could-not-receive-data-from-client-connection-reset-by-peer – Murcielago Aug 22 '20 at 17:21
  • Have you looked in the nginx and gunicorn logs? Maybe you should inject an intentional error (like misspelling a table name), to make sure you know how to find them on the client side. – jjanes Aug 22 '20 at 17:28
  • As I suggested, add some prints to see where does the code hangs... – Marek Piotrowski Aug 22 '20 at 19:10
  • i did what you suggested, it looks like the issue is in creating the engine, I dont get why, I did not touch it and it was working just fine before – Murcielago Aug 22 '20 at 20:57

0 Answers0