Postgresql – use postgres_fdw without foreign tables defined

dblinkpostgresql

I have a production database "PRODdb1", with a read-only user account. I have a need to query(select statement) this database and insert the data into a secondary database named "RPTdb1". I originally planned to just create a temp table in PRODdb1 from my select, but permissions are the issue.

I've read abut dblink & postgres_fdw, but are either of these a solution for my issue? I wouldn't be creating foreign tables because my SELECT is joining many tables from PRODdb1, so I'm unfamiliar if postgres_fdw would still be an option for my use case.

Another option would be any means of getting the results of the SELECT to a .CSV file or something. My main blocker here is that I only have a read-only user to work with, but no way around that issue.

Best Answer

postgres_fdw is definitely the better way. As you have read-only access to the production database, you can setup a FDW on your test (secondary) database provided you have superuser access to that (secondary) server. Or at least someone who can install the extension and setup the foreign server. Once those two things are there, you can proceed with a regular user.

One thing to keep in mind: if you do use the FDW, any select on the foreign table (in your test server) will generate some load on the production server. Whether or not that is acceptable only you can know.

my SELECT is joining many tables from PRODdb1

If you join multiple foreign tables in a single statement those joins will be pushed down to the foreign server and evaluated there (at least with the Postgres 10 and I think 9.6 as well) only the result of the join will be sent back to the calling server.

Another option is to use pg_dump to copy the data from your production server to your test server.

pg_dump --host=prodserver --dbname=prod_db --table=public.some_table --file=some_table.sql --no-owner

That will export the data for some_table into a SQL script, including the necessary CREATE TABLE statement.

You can then run that SQL script through psql to import the data into your test server:

psql --host=testserver --dbname=test_db --file=test_table.sql

If you already have the table definition in your test server, then you might want to use --data-only for pg_dump to skip that part.

That will be faster than trying to generate some CSV files.

Related Solutions

PostgreSQL user permissions

This should work (as superuser):

CREATE ROLE rwc;    -- read/write/create user
CREATE SCHEMA foo;
ALTER  SCHEMA foo OWNER TO rwc;
-- GRANT ALL ON SCHEMA foo TO rwc;  -- alternatively

CREATE ROLE r;      -- read user
GRANT USAGE ON SCHEMA foo TO r;

CREATE ROLE rw;     -- read/write user

GRANT r TO rw;
GRANT rw TO rwc;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT SELECT ON TABLES TO r;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT USAGE ON SEQUENCES TO r;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT INSERT, UPDATE, DELETE ON TABLES TO rw;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT SELECT, UPDATE ON SEQUENCES TO rw;

You probably missed one of these steps.
You may need do adjust existing objects additionally since default privileges are only granted to new objects.

Postgresql – Using postgres_fdw to connect from one read replica to another

You can create replicas using logical decoding. Check pglogical https://github.com/2ndQuadrant/pglogical

When creating replica using logical decoding it's not read-only.Might help in your case.

Best Answer

Related Solutions

PostgreSQL user permissions

Postgresql – Using postgres_fdw to connect from one read replica to another

Related Question