Postgresql dblink creation instructions on AWS RDS

aws_rds_postgresql_dblink_instructions.txt

1)  create user on target-db/scraper-db that will connect remotely

CREATE USER rkadmin WITH PASSWORD '<password>';
GRANT SELECT ON TABLE listing_analytics_details TO  rkadmin;
GRANT SELECT ON TABLE listing_analytics_contacts TO  rkadmin;
/*
-- backout DML
    REVOKE SELECT ON TABLE listing_analytics_details FROM  rkadmin;
    REVOKE SELECT ON TABLE listing_analytics_contacts FROM  rkadmin;
    DROP USER rkadmin;
*/


2)
— add new fdw extension and verify w/ sql stint
CREATE EXTENSION postgres_fdw;
----  verify it worked
SELECT pg_namespace.nspname, pg_proc.proname FROM pg_proc, pg_namespace WHERE pg_proc.pronamespace=pg_namespace.oid AND pg_proc.proname LIKE '%postgres_fdw%';

3)
—  Add dblink as extension
CREATE EXTENSION dblink;
----  verify it worked
SELECT pg_namespace.nspname, pg_proc.proname FROM pg_proc, pg_namespace WHERE pg_proc.pronamespace=pg_namespace.oid AND pg_proc.proname LIKE '%dblink%';

4)
—  Add Firewall rule from calling database (i.e., EBDB/renterkit) to the target database (i.e., tzscraperdb prod instance)
- DONE
From 		To			Port		RuleType
52.22.99.197	52.86.33.74	5432		Postgres

5)
-- on client db, grant user db connect priv
GRANT EXECUTE ON FUNCTION dblink_connect(text) TO rkadmin;
/*
-- backout DML
    REVOKE EXECUTE ON FUNCTION dblink_connect(text)  from rkadmin;
*/


6)
-- create the server on calling db
CREATE SERVER tzscraper_dblink  FOREIGN DATA WRAPPER postgres_fdw    OPTIONS (host '52.86.33.74', port '5432', dbname 'tzscraperdb');
/*
-- backout DML
    DROP SERVER tzscraper_dblink;
*/


7)
-- create the user mapping
CREATE USER MAPPING FOR rkadmin SERVER tzscraper_dblink OPTIONS (user 'rkadmin', password '<password>');
/*
-- backout DML
    REVOKE USAGE ON FOREIGN SERVER tzscraper_dblink FROM rkadmin;
    DROP USER MAPPING FOR rkadmin SERVER tzscraper_dblink;
*/

8)
-- run a test to confirm
SELECT dblink_connect('myconn' ,'tzscraper_dblink')
SELECT * FROM dblink('myconn','select count(DISTINCT(post_id)) as num from listing_analytics_contacts') AS t(op_col varchar);

-- or just do the following

SELECT * FROM dblink('tzscraper_dblink','select count(DISTINCT(post_id)) as num from listing_analytics_contacts') AS t(op_col varchar);


SELECT * FROM dblink('tzscraper_dblink',
	'select
		post_id, posted, updated, hidden_title, viewable_title,	desc_short, desc_long, price, map_address, google_map_link,
		lat, long, town, beds, bath, sq_footage, available_date, contact_url, created_on, listing_url, reply_url,neighborhood
	from listing_analytics_details
	LIMIT 1')
	AS  f(post_id	bigint,
	      posted	bigint,
	      updated	bigint,
	      hidden_title	text,
	      viewable_title	text,
	      desc_short	text,
	      desc_long	text,
	      price	text,
	      map_address	text,
	      google_map_link	text,
	      lat	numeric,
	      long	numeric,
	      town	text,
	      beds	text,
	      bath	text,
	      sq_footage	text,
	      available_date	text,
	      contact_url	text,
	      created_on	timestamp,
	      listing_url	text,
	      reply_url	text,
	      neighborhood	text);