Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two databases, I'm going to need to get more recent data from one database table and place it in another table that is on another PostgresSQL database on a different machine. The structure of the tables is identical. How is the best way to go about this?

share|improve this question
    
How do you determine what counts as newer? –  dezso Dec 3 '13 at 22:00

1 Answer 1

up vote 1 down vote accepted

Using dblink, you can copy records from one server to another. I installed dblink, using this Installation guide. The databases are the same and have the same table called sometable. This is similar to federated tables on MySQL and openquery on SQL Server.

The older server is listening on port 5432 and the new server is listening on port 5433. I only installed dblink on the new server. The dblink extension was only added to the receiving database on the new server. In my example, I didn't include the host because both postgresql servers are on the same machine.

Here are the basic steps I took using a 9.1 on a CentOS 6 server

Install dblink

-- go to the postgresl_source directory. If you installed via yum or apt-get you will have to install the postgresql_source packages

cd /postgresql_source_directory/contrib/dblink

-- install the dblink binaries

make install

-- Connect to 9.1 listening on 5433

psql -p5433

-- Connect to the database that will be receiving data

\connect somedatabase

-- Add in the dblink extension

CREATE EXTENSION dblink;

-- Verify that the extension exists

\dx
dblink  | 1.0     | public     | connect to other PostgreSQL databases from within a database

Copy the data to the new server

I am using a temporary storage table, because it made using an EXISTS statement easier when copying in new records.

-- truncate the temporary storage table on the new server

truncate table sometable_2;

-- copy in records to the temporary storage table on the new server

insert into sometable_2 select * from dblink('port=5432 dbname=somedatabase', 'select * from sometable') as t1(t int);

If your server is on a different ip address, you would run this instead

insert into sometable_2 select * from dblink('port=5432 dbname=somedatabase host=some.remote.ip.address', 'select * from sometable') as t1(t int);

-- Copy only new records into the new sometable

insert into sometable select * from sometable_2 st2 where not exists(select * from sometable st where st.t = st2.t);
INSERT 0 5

If I run the same queries again, only new records will be copied in. This is because the correlated subquery (select * from sometable st where st.t = st2.t) only retrieves records that don't already exist in the table "sometable" on the new server.

share|improve this answer
    
Thanks great, but I'm struggling to get it installed on windows? –  James Dec 4 '13 at 13:12
    
@James Have you tried this? stackoverflow.com/questions/16649200/… –  Craig Efrein Dec 4 '13 at 13:17
    
Outstanding, thank you for your help, –  James Dec 4 '13 at 17:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.