Postgres 11, Windows 10, pgAdmin 4
I have a large post-gis enabled table (29million rows, 20GB + a 5GB spatial index) stored on my local machine (localhost).
I want to move this table to an AWS hosted server instance (also Postgres). The table has 2 columns, and id column and a geometry column.
Attempt 1: Using dblink, the transfer doesn't work:
CREATE TABLE destschema.dest_table AS SELECT fid, wkb_geometry FROM dblink('host=localhost user=postgres password=password dbname=sourcedb', 'select fid, wkb_geometry from sourceschema.sourcetable') as linktable( fid VARCHAR, wkb_geometry geometry)
Error is: 'FATAL: password authentication failed for user "postgres"'
Even though the localhost connection credentials are correct. Why does it throw this error? I have previously had success using dblink to transfer tables from one remote cloud server to another remote cloud server. It just seems to not like moving a table from localhost to a remote server.
Attempt 2: using pg_dump to create a .backup file of the table (works ok), then upload it to the destination server using psql:
psql -U your_user_name your_db_name < your_dump_file
This method took 12 hours to insert 5million rows, so I cancelled the process. I don't want to have to wait 72 hours to move this table...
Attempt 3: Using PgAdmin's interface to backup the table to a .backup file and then restoring that .backup file to the destination database. However, after waiting 6 hours it was still uploading... I stop the process.
Attempt 4: Eventually I got around this issue by purchasing DataGrip (by Jetbrains) and exporting the 29mil rows to 3 CSV files, then importing the data back into the destination db. All the while using the DataGrip GUI. The exporting phase took around 8 mins in total, the uploading took 2.5 hours in total. Then I had to manually add a spatial index to the new table.
What is the quickest, simplest way of moving a large table from localhost to remote? Surely I've missed a trick or two...