0

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!

Community
  • 1
  • 1
Pythonista anonymous
  • 8,140
  • 20
  • 70
  • 112
  • *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.*. Er, why? Especially if you're then attempting cross-database queries, this makes little sense. – Craig Ringer Nov 17 '14 at 12:15
  • I have been asking the same question! :) There are commercial/proprietary issues I cannot discuss openly. All I can say is that data in the two databases are managed by different departments, and each department wants its front-end systems to only connect to its own database. Regardless of what our opinion on this approach may be, this is not going to change... – Pythonista anonymous Nov 17 '14 at 12:26
  • You could keep a third database with the complete content, whereof none of the competing departments knows about ;o) – Uwe Allner Nov 17 '14 at 12:50
  • Uwe, jokes aside, I am in fact trying to do something very similar to what you mentioned. I cannot change the two production databases in any way, shape or form, but am free to export a copy of both to another server and do whatever I want on this new server, including merging the two into one. – Pythonista anonymous Nov 17 '14 at 13:31

1 Answers1

0

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.

Community
  • 1
  • 1
Pythonista anonymous
  • 8,140
  • 20
  • 70
  • 112