Incidentally, pg2disk can also be used to generate the DDL corresponding to the tables that it loads. On the same command you issued to load the tables, simply add --ddl-only to the command to generate the DDL for the tables loaded.

The DDL files will have extensions .sql or .xsql. The .sql will contain foreign table definitions for Vitesse DB, while the .xsql will contain external table definitions for Deepgreen DB. You should obviously choose the files that match the database software you are using.

There is one more task we must do before we can use the DDL files. We need to substitute the pattern __LOFTD_ADDR__ with the hostname and port that correspond to our LOFT service. For example, if we run the LOFT service on localhost and port 8787, we should replace that pattern __LOFTD_ADDR__ with localhost:8787.

The following commands will generate DDL for all tables in the schedule file, concatenate them together, substitute localhost:8787 for __LOFTD_ADDR__, and send the DDL to the database:

% pg2disk --ddl-only -D /data/loftdata/base __all__ ~/schedfile
% find /data/loftdata/base --name '*.sql' --exec cat {} \; > myddl
% perl -pi -e 's/__LOFTD_ADDR__/localhost:8787/' myddl
% psql mydatabase -f myddl

Note: if you run Deepgreen DB, change *.sql to *.xsql in the find command above.

Once the DDL are executed on the database, you can perform SQL SELECT on the foreign tables or external tables as if they are native tables. Vitesse DB or Deepgreen DB will automatically connect to LOFT to read the table data on demand.