Cross site database replication

#1

Hi, we have a main studio and a secondary studio linked over a codec and a VPN for data.
At the remote site I access the main DB via the VPN. However the response times are quite poor at the moment when accessing playlists and doing database searches…
So im exploring options so have a local database copy of the main one.
I managed to do backup and restore but it does keep the original mairlist DB which means I have to disconnect the MainDB from mairlist to use the local copy.

I wondered if anyone had managed replication in postgresql or any other suggestions to help please so I can keep the databases in sync.

Im open to any ideas on this one. Even changing database manager if I have to.

Many thanks - Adrian
PS - getting a bigger data connection is not an option unfortunatly!

#2

PostgreSQL has built-in replication; but it is only master-slave replication, in other words, the slave can only be used read-only, and write access must only be done on the remote master.

True multi-master replication is something only found in very expensive systems like MSSQL Enterprise or Oracle.

MySQL claims to have something like that, but the only time I tried it, it turned out to be almost unusable because the underlying storage engine missed essential features like indexing and foreign key constraints.

#3

master slave is fine but im struggling to find a decent article that fully explains the steps to set it up.
i use MS sql at work and be have full sync running but here I only require master/slave with slave’s being read only.
I used to be an oracle DBA but thats some years ago on VAX/VMS so that will show my age!

If you have any references to help but if not ill carry on googling.

Thanks

#4

As a test I have installed mariadb and have replication master->single slave working fine.
Installed mairlist and using the demo created a db which replicated ok. I then tried using it at the slave site an all fine.

So to solve my requirement as I cant get replication working on postgres I could use mariadb.

Any advise on data migration from postgres to mariadb? If thats my best way to go?

Thanks - Adrian