Replicating tables from different databases postgresql

Question

I have tables from different databases , and i want to create a data warehouse database that contains table replicas from different tables from different databases. I want the data in the warehouse to be synced with the data from the other tables everyday.I am using postgresql

I tried to do this using psql :

pg_dump -t table_to_copy source_db | psql target_db

However it didnt work as it keeps stating errors like table does no exist. It all worked when i dumped the whole dabatase not only a single table, but however i want the data to be synced and i want to copy tables from different databases not the whole database.

How can i do this? Thanks!

... it keeps stating errors like table does no exist. What was the error message you got? — joop
– joop, Commented Feb 6, 2017 at 12:54
@joopERROR: type "u_datetime" does not exist LINE 3: ts_created u_datetime DEFAULT now(), ^ ERROR: relation "customer" does not exist ERROR: relation "customer" does not exist — Abdalla Ismail
– Abdalla Ismail, Commented Feb 6, 2017 at 13:05
is u_datetime a UDT ? If it is, it must exist in the target database too -->> you'll need to create it before you create the table that depends on it. — joop
– joop, Commented Feb 6, 2017 at 13:06
@joop i dont want to use pg-dump since it doesnt allow data syncing between tables.Any other method? — Abdalla Ismail
– Abdalla Ismail, Commented Feb 6, 2017 at 13:06

Roman Tkachuk · Accepted Answer · 2017-02-06 13:14:19Z

1

Probably you need FDW - Foreign Data Wrapper. You can create foreign tables for different external db in different schemas on local db. All tables accessible by local queries. For storing snap you can use local tables with just INSERT INTO local_table_YYYY_MM SELECT * FROM remote_table; .

answered Feb 6, 2017 at 13:14

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

minigeek · Accepted Answer · 2017-02-06 13:22:06Z

0

1

 pg_dump -t <table name> <source DB> | psql -d <target DB>

(Check the table name correctly and it says for you , table doesn't exist)

2

pg_dump allows the dumping of only select tables:

pg_dump -Fc -f output.dump -t tablename databasename

(dump 'tablename' from database 'databasename' into file 'output.dump' in pg_dumps binary custom format)

You can restore that pg_restore:

pg_restore -d databasename output.dump

If the table itself already exists in your target database, you can import only the rows by adding the --data-only flag.

Dblink

You can not perform cross database query like SQL Server, PostgreSQL does not support this. DbLink extension of PostgreSQL which is used to connect one database to another database. You have to install and configure DbLink to execute cross database query.

Here is the step by step script and example for executing cross database query in PostgreSQL. Please visit this post:

edited Feb 6, 2017 at 13:22

answered Feb 6, 2017 at 12:54

minigeek

3,1961 gold badge30 silver badges39 bronze badges

4 Comments

joop Over a year ago

Postgres does not allow cross-database queries.

minigeek Over a year ago

@joop I didn't know that thanx. How about modified solution which also works for transfer from one server to another

joop Over a year ago

Well, that is basically the same as the method in the question. (he uses plain (text) format piped into psql, you use custom format + pg_restore)

minigeek Over a year ago

@joop i guess there is something wrong with table name ! Error itself says

Collectives™ on Stack Overflow

Replicating tables from different databases postgresql

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related