Struggling to wrap my brain around this one...
So I need to execute a Postgres COPY command in a PHP program like the following:
COPY _data_20200404_193855 FROM '/mnt/files/imports/CopyFlatFile.csv.tab' DELIMITER E'\t' CSV HEADERThe /mnt/files/xls-imports/CopyFlatFile.csv.tab is the PVC location, but Postgres Pod is thinking it is a location in the Pod, so it says it can't find the file.
I can manually move the file out of the PVC and into the Postgres Pod with a combination of scp and kubectl cp and the command executes perfectly after copying it into pgAdmin and running it.
Not an ideal method and not very efficient as I'd like the program to handle it.
Any suggestions for how to handle this?
Worst case scenario, I rewrite it to construct a massive INSERT INTO statement, but not sure if it can handle that much data being held in memory. If not, I'll just break it into smaller chunks. Oof...
One possible option is to mount same external filesystem ( such as NFS, CephFS or corresponding SaaS versions like AWS EFS ) to all php and PostgreSQL pods in ReadWriteMany access mode and use it as a shared temporary directory between all pods.
It looks like I didn't even have my file storage PVC hooked up to the Postgres Deployment... did that and it is now working. Oversight on my part.
Was...
...
volumeMounts:
- name: postgres-storage
mountPath: /var/postgresql
volumes:
- name: postgres-storage
persistentVolumeClaim:
claimName: postgres-storage
...So I changed it to:
...
- mountPath: /var/postgresql
name: postgres-storage
- mountPath: /mnt/files/xls-imports
name: file-storage
subPath: xls-imports
volumes:
- name: postgres-storage
persistentVolumeClaim:
claimName: postgres-storage
- name: file-storage
persistentVolumeClaim:
claimName: file-storage
...