PostgreSQL: how to periodically copy many tables from one database to another

Question

I have two databases running on the same PostgreSQL 9.3 server (running on Windows). The two databases have the very same structure: same tables, same keys, same constraints, etc; the only difference is that data gets periodically moved from one to the other. Think of it like a database with info on clients and orders, where every week data related to clients older than, say, 2 years is moved from one to the other.

Is there a quick way to create tables which combine data from the new and the old database? Something like:

select * from NewDatabase.MyTable
UNION
select * from OldDatabase.MyTable

to be run on about 25 of the 170 tables of the databases.

The 2 key problems I see are:

1) PostgreSQL doesn't really allow cross database queries. Foreign data wrappers or db links (as mentioned here: Possible to perform cross-database queries with postgres?) are very cumbersome to use because (if I understand correctly) I must pre-declare the entire structure of the tables I wish to access.

2) I'd need to find a way to automate this so that I can code it automatically for each of the 25 tables I need, and it runs every week.

I have no preference whatsoever on how this is done (Pentaho-Kettle, command script, sql script, etc.) as long as it can be automated efficiently.

Thanks!

Answer 1

I believe I may have found a way. Of course suggestions and comments are more than welcome. My idea is similar to what was suggested here: I want to restore the database with a different schema (not in the most voted answer, though).

Since I am only interested in tables in the public schema, I did the following:

Backup of the public schema of database1

"C:\Program Files\PostgreSQL\9.3\bin\pg_dump.exe" --host MYSQLHOST --port 5432 --username "JohnDoe"  --format custom --section pre-data --section data --section post-data --verbose -n "public" --file "\\mypath\db1.backup" "db1"

Renamed the public schema of db2 and created a new, empty, public schema:

ALTER SCHEMA public RENAME TO public_db2 ;
CREATE SCHEMA public ;

Restore the backup of db1’s public schema into the newcly created (and empty) public schema in db2:

"C:\Program Files\PostgreSQL\9.3\bin\pg_restore.exe" --host MYSQLHOST --port 5432 --username "JohnDoe" --dbname "db2"   --section pre-data --section data --section post-data --schema public --verbose "\\mypath\db1.backup"

Renamed the newly imported public schema in db2:

ALTER SCHEMA public RENAME TO public_db1 ;

My syntax above creates a binary (and automatically compressed) backup file. If you have a backup file in text format, you cannot restore just one schema. The only part I haven't managed to automate (yet) is that the shell command prompts me for a password.

I use Windows but I understand the syntax should be the same on other systems, too.