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!
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.
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.
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?
Hi Torben. I wondered if you received my email requesting a quote to move our database across to mysql (mariadb).
Songs I can export / import but there have been a large amount of edits to hour restrictions and campaigns in the existing postgres DB so its vital I move all across.
The other option is help getting replication going in postgres which im afraid I hve failed to work out how to do.
I may have cracked it. I configured maradb to be UTF character set and now the DB is cloning from postgres to mariadb much further.
I Will check in the morning how its gone as its going to take a while.
Thanks for the clone advise Torben
