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?
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
-- go to the postgresl_source directory. If you installed via yum or apt-get you will have to install the postgresql_source packages
-- install the dblink binaries
-- Connect to 9.1 listening on 5433
-- Connect to the database that will be receiving data
-- Add in the dblink extension
-- Verify that the extension exists
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
-- copy in records to the temporary storage table on the new server
If your server is on a different ip address, you would run this instead
-- Copy only new records into the new sometable
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.