Syncing a table between two PostgreSQL databases consistently

Question

I have 2 databases: postgres (new) ; tnpm (old). Currently, I need to copy data from [tnpm] to [postgres]. I am able to copy it using:

   INSERT INTO ip_cpu_mn(datetime, resource_name, cpu_utilization)

   SELECT cpu_mn.datetime,resource_name, cpu_utilization
   FROM dblink('host=10.0.32.175 user=postgres password=postgres dbname=TNPM_EXT',
 'SELECT datetime, resource_name, cpu_utilization
   FROM ext_ip.cpu_mn WHERE datetime =(SELECT MAX (datetime) - interval ''10 minutes'' as maxdate FROM ext_ip.cpu_mn)')

AS cpu_mn( datetime timestamp without time zone,
  resource_name character varying(150),
  cpu_utilization numeric(6,2))

Weirdly, when I start to verify between these database, the data is inconsistent in new database, seem it loss. The copy process is running using crontab 5 min. How can I avoid this ?

Don't try to home-brew this. Use a well established tool like Londiste that will get it right. — Craig Ringer
– Craig Ringer, Commented Nov 23, 2015 at 3:49
Do you really need to copy the data? Can't you use a foreign data wrapper in the new database to access the table directly in the old database? — user330315
– user330315, Commented Nov 23, 2015 at 6:58

Schwern · Accepted Answer · 2015-11-23 08:30:20Z

9

Don't try to do replication yourself, it is a hard problem. PostgreSQL has a number of different replication schemes, several are built in. All will be better than what you're doing.

What you're doing is creating a "hot standby" where copies of the database can be used for read-only queries. Here are instructions to set that up and detailed documentation.

answered Nov 23, 2015 at 8:30

Schwern

167k28 gold badges225 silver badges370 bronze badges

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

Collectives™ on Stack Overflow

Syncing a table between two PostgreSQL databases consistently

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related