Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Question Unanswered: Easy database sync

    Hi Guys,

    I have a quick question for you.

    I have a ecommerce website and an administrative site where I do everything from site maintenance to bookeeping. I have a dns failover solution setup and we currently use a database dump and then restore on the remote database.

    Obviously, this is not the best way but is functional for now. I would like to get some opinions on the best way to sync both databases. Is there a cost effective and relatively painless way to write to both local and remote databases at the same time so failover is always up to date? Then, to update the live database with any changes should the failover be needed?

    Thanks in advance for any sugestions.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Yes.

    Search for Slony at postgresql.org.

    Essentially, you make a snapshot of the "master" server via backup and restore onto the backup server. This gets you a database copy valid as of the backup timestamp.

    Then, Slony uses the WAL logs from the master, and plays them back at near real-time on the backup server. Thus, copying all the database changes.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Oct 2006
    Posts
    2
    Thanks for the reply. Its really appreciated.

    We love the idea but we are very concerned because it fails to replicate the schema. is there any alternative that does both that you know of?

    Thanks again for the help.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by scotty1
    Thanks for the reply. Its really appreciated.

    We love the idea but we are very concerned because it fails to replicate the schema. is there any alternative that does both that you know of?

    Thanks again for the help.
    I'm not sure there IS such a thing as an "easy" database replication approach.

    Per Jan Weick, the author of Slony,
    "replication will never be something where you type SETUP and all of a sudden your existing enterprise system will nicely replicate in a disaster recovery scenario." Designing how to set up your replication is a complex problem.
    The pg_dumpall utility certainly copies the schema at the time of the backup.

    Slony does schema changes, but you need to implement the changes via script, and not via GUI packages like pgAdmin.
    Schema changes require special consideration. The bundling of the replication transactions must be able to join all of the pertinent schema changes together, whether or not they took place in the same transaction. Identifying these change sets is very difficult.

    In order to address this issue, Slony-I has a way to execute SQL scripts in a controlled fashion. This means that it is even more important to bundle and save your schema changes in scripts. Tracking your schema changes in scripts is a key DBA procedure for keeping your system in order and your database recreatable.
    http://www.onlamp.com/pub/a/onlamp/2.../18/slony.html
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •