Hello,I am trying to load about 1.4 billion records into a postgresql table.
The data is in about 100 individual CSV files which I load individually into the table using the COPY command.
After the data is loaded I perform updates to the data, and this is when I get errors. Some updates work, some crash.
I am using psql and an SQL script file (update.sql) to perform the updating (UPDATE table SET field=UPPER(field), etc.).
The errors are all "Server closed the connection unexpectedly"
The problem is that the errors are never the same and occur at different times and at different steps in the process.
If I update the machine (apt update/upgrade), reboot and try again, the errors occur at different places.
The errors messages and logs are not helpful and I have not been able to determine the cause.
I decided to load the CSV files into temporary tables and then perform the updates on the temporary tables before moving the data to the main table.
This works for most of the tables, but there are always 1 or 2 that crash when I try to update them.
In my last attempt, all tables updated successfully except the last one, with about 100 million records.
I tried changing these settings to 60 minutes and it still crashes.
SET statement_timeout = '60min';
SET idle_in_transaction_session_timeout = '60min';
Then I tried to update the data in blocks of 100,000 records and it crashed 4 times on 4 different blocks.
So I updated the first crashed block down to the a block of 10 records, until it crashed.
Then I updated each of the 10 records individually to identify the record that is problemantic, but then all 10 records updated without crashing!
Pure insanity!
I am using a dedicated machine which is a 10 core i9 with 128 GIG of RAM and 2 x 4 TB NVMEs.
There is only 1 user on this stand-alone machine, me, and the machine is not connected to the internet or a network.
I have reinstalled ubuntu 22.04 server many times, wiping out the discs and starting over.
Last attempts have been with ubuntu 22.04.04 and postgreSQL 16.
But the errors persist.
I have been working on this for just over 1 year now, documenting every step, and I am still unable to get this to work without it crashing somewhere along the way.
I am beginning to wonder if postgreSQL is bi-polar.
Any help would be greatly appreciated.
Thank you