0

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...

  • You can also try pressing F5 on a table in DataGrip and copy it to the needed server – moscas Jun 5 at 16:25
  • Where is your attempt 1 query executing? "localhost" is interpreted as local to the machine on which the statement is executing, not local to the machine where the statement was issued from. – jjanes Jun 5 at 18:18
  • @jjanes thanks but can I then change 'localhost' to say, my computer IP address or something to point the query to my local disk? – Theo F Jun 6 at 8:39
  • Yes, as long your firewall/router allows a connection back to it from aws (by using port forwarding, for example). By default, it probably does not. If you do set up your home network to accept PostgreSQL connections from the internet, be careful to harden your security settings – jjanes Jun 6 at 11:32
  • Attempts 2 and 3 sound like the index already exists when you try to insert the data. Your descriptions of the processes are rather vague so it is hard to know (pg_dump has lots of options, which ones were used? which machine was each step run on?) – jjanes Jun 6 at 11:58
1
CREATE TABLE destschema.dest_table AS
SELECT fid, wkb_geometry FROM dblink('host=localhost
                     user=postgres...

This statement could be used to transfer between databases on the same machine, but not across different machines. When you're logged to the AWS database, localhost designates the AWS host, not your PC. This is why the credentials of your PC don't work.

Copying the output of pg_dump to the remote, as a file and playing it there locally is probably the fastest way to achieve the transfer. Steps:

  • on the PC: pg_dump.exe -Fc [other options] dbname -f dumpfile
    -Fc as an option selects the custom format, which has the advantage of compressing the dump.
  • transfer dumpfile to the remote AWS host, with a method of your choice. On Unix scp would probably be used: scp dumpfile username@remotehostname.amazonaws.com:. On Windows, it may also be available if it got installed, but otherwise any sftp client should work.
  • in a shell on the remote host, restore the dump: pg_restore dumpfile | psql [options] dbname.

If you only need to dump one table (as opposed to an entire database), see the -t option of pg_dump.

For the method executing SQL remotely (that is, on your PC, psql.exe -h remotehostname.amazonaws.com [other options] or the equivalent with pgAdmin connecting to the remote), note that compression is not used unless it happens at the network layer, and with 20GB to transfer, you really want to benefit from compression.

  • Can you explain your second paragraph a bit more? 'as a file' - a .backup file? 'playing it there locally' I don't understand... – Theo F Jun 6 at 9:13
  • @TheoF: I've put some more details, see edit. – Daniel Vérité Jun 6 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.