How to move data from one postgres database to another using dblink. Useful for cherry-picking data from a remote database

dblink-demo

[~/src] heroku apps:create dblink-demo
Creating dblink-demo... done, stack is cedar
http://dblink-demo.herokuapp.com/ | [email protected]:dblink-demo.git
[~/src] heroku addons:add heroku-postgresql:dev -a dblink-demo
Adding heroku-postgresql:dev on dblink-demo... done, v3 (free)
Attached as HEROKU_POSTGRESQL_IVORY_URL
Database has been created and is available
 ! This database is empty. If upgrading, you can transfer
 ! data from another database with pgbackups:restore.
Use `heroku addons:docs heroku-postgresql:dev` to view documentation.
[~/src] heroku addons:add heroku-postgresql:dev -a dblink-demo
Adding heroku-postgresql:dev on dblink-demo... done, v4 (free)
Attached as HEROKU_POSTGRESQL_ONYX_URL
Database has been created and is available
 ! This database is empty. If upgrading, you can transfer
 ! data from another database with pgbackups:restore.
Use `heroku addons:docs heroku-postgresql:dev` to view documentation.
[~/src] heroku pg:psql ivory -a dblink-demo
Timing is on.
Expanded display is used automatically.
psql (9.2.2, server 9.1.8)
WARNING: psql version 9.2, server version 9.1.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

ec2-54-243-235-100.compute-1.amazonaws.com/dfbfai41dup1qc=> CREATE TABLE test(id integer);
CREATE TABLE
Time: 120.763 ms
ec2-54-243-235-100.compute-1.amazonaws.com/dfbfai41dup1qc=> INSERT INTO TEST (id) SELECT generate_series(1, 1000);
INSERT 0 1000
Time: 336.833 ms
ec2-54-243-235-100.compute-1.amazonaws.com/dfbfai41dup1qc=> SELECT count(*) FROM test;
 count
-------
  1000
(1 row)

Time: 112.251 ms
ec2-54-243-235-100.compute-1.amazonaws.com/dfbfai41dup1qc=> \q
[~/src] heroku config:get HEROKU_POSTGRESQL_IVORY_URL -a dblink-demo
postgres://wxhsfkmmtwbzbx:ZClwyRM97V5UWNL7L-GnnvRHrI@ec2-54-243-235-100.compute-1.amazonaws.com:5432/dfbfai41dup1qc
[~/src] heroku pg:psql onyx -a dblink-demo
Timing is on.
Expanded display is used automatically.
psql (9.2.2, server 9.1.8)
WARNING: psql version 9.2, server version 9.1.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

ec2-107-21-107-194.compute-1.amazonaws.com/dc65cidl1138ui=> CREATE EXTENSION dblink;
CREATE EXTENSION
Time: 425.336 ms
ec2-107-21-107-194.compute-1.amazonaws.com/dc65cidl1138ui=> CREATE TABLE test(id integer);
CREATE TABLE
Time: 127.794 ms
ec2-107-21-107-194.compute-1.amazonaws.com/dc65cidl1138ui=> INSERT INTO test (id) SELECT * FROM dblink('postgres://wxhsfkmmtwbzbx:ZClwyRM97V5UWNL7L-GnnvRHrI@ec2-54-243-235-100.compute-1.amazonaws.com:5432/dfbfai41dup1qc', 'SELECT id FROM test WHERE id % 10 = 0') AS test(id integer);
INSERT 0 100
Time: 759.587 ms
ec2-107-21-107-194.compute-1.amazonaws.com/dc65cidl1138ui=> SELECT count(*) FROM test;
 count
-------
   100
(1 row)

Time: 111.383 ms
ec2-107-21-107-194.compute-1.amazonaws.com/dc65cidl1138ui=> \q
[~/src] heroku apps:destroy dblink-demo

 !    WARNING: Potentially Destructive Action
 !    This command will destroy dblink-demo (including all add-ons).
 !    To proceed, type "dblink-demo" or re-run this command with --confirm dblink-demo

> dblink-demo
Destroying dblink-demo (including all add-ons)... done