Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu


Previously, in order to create a foreign table in PostgreSQL, you would need to define the table, referencing the destination columns and data types, and if you have a lot of tables, this can become tedious and error-prone, and when those tables change, you need to do it all over again...
 CREATE FOREIGN TABLE remote.customers (
     id int NOT NULL,
     name text,
     company text,
     registered_date date,
     expiry_date date,
     active boolean,
     status text,
     account_level text) SERVER dest_server OPTIONS (schema_name 'public');
 CREATE FOREIGN TABLE remote.purchases (
     id int NOT NULL,
     purchase_time timestamptz,
     payment_time timestamptz,
     itemid int,
     volume int,
     invoice_sent boolean) SERVER dest_server OPTIONS (schema_name 'public');
As of PostgreSQL 9.5, you can import tables en masse:
   FROM SERVER dest_server INTO remote;
This would create foreign tables in the schema named "remote" for every table that appeared in the public schema on the foreign server labelled "dest_server".
You can also filter out any tables you don't wish:
   EXCEPT (reports, audit)
   FROM SERVER dest_server INTO remote;
Or limit it to just a specific set of tables:
   LIMIT TO (customers, purchases)

This command provides an automated way to create foreign table definitions
that match remote tables, thereby reducing tedium and chances for error.
In this patch, we provide the necessary core-server infrastructure and
implement the feature fully in the postgres_fdw foreign-data wrapper.
Other wrappers will throw a "feature not supported" error until/unless
they are updated.
I assume you know about Foreign Data Wrappers. If not, quick recap – it's a way to define tables that, upon access, will query in some way some external resource. Be it text file, or another postgres database, or search twitter.
While it is great, it required definition of each table separately. In a lot of cases it makes sense. But sometimes – you want to use fdw to link another database, and you might want to add all tables from this remote database so that it would be visible in your current one.
Of course – this could have been done with shell scripts, but now we have something nicer. It will require some support from FDW drivers (wrappers), and so far, only postgres_fdw understands it, but it's there, so let's see.
In my test Pg, I create 2 databases:
create database source;
create database destination;
In source, I'll create some test tables:
$ create table t1 as select generate_series(1,10) as id;
$ create table t2 as select generate_series(1,10) + 12 as id;
$ create table t3 as select generate_series(1,10) + 45 as id;
Now, in destination database, I load the extension, and configure it:
$ create extension postgres_fdw ;
$ create server src foreign data wrapper postgres_fdw options( dbname 'source' );
$ create user mapping for depesz server src options ( user 'depesz' );
With this in place, I can create foreign tables:
$ create foreign table table_from_source (id int4) server src options (table_name 't1' );
And it will work:
$ select * from table_from_source ;
(10 rows)
But now, I can do better.
First, I create some schema, so that foreign tables will not mix with local:
create schema from_source;
And now, I can:
$ import foreign schema public from server src into from_source;
After which:
$ \dE *.*
                    List of relations
   Schema    |       Name        |     Type      | Owner  
 from_source | t1                | foreign table | depesz
 from_source | t2                | foreign table | depesz
 from_source | t3                | foreign table | depesz
 public      | table_from_source | foreign table | depesz
(4 rows)
All the foreign tables from database source got “copied".
Of course this is configurable – I can limit the tables to be linked to certain list, or even use “except" to get all tables with some exceptions.
Details, as always, in the documentation.
Great stuff. Should greatly simplify creation of foreign tables – even in case of single table – as it does schema discovery, so I wouldn't need to specify all columns any more. Cool.



Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

7 Steps to configure BDR replication in postgresql

Postgres Streaming Replication Configuration

vacuumlo - removing large objects orphans from a database PostgreSQL