Skip site navigation (1) Skip section navigation (2)

Re: Synchronizing the schema of two PostgreSQL databases

From: "Jason Stelzel" <postgreslist(at)epigraf(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Cc: "Lacey Powers" <lacey(dot)powers(at)commandprompt(dot)com>
Subject: Re: Synchronizing the schema of two PostgreSQL databases
Date: 2009-07-31 05:16:58
Message-ID: 614D30DC6AB14545A4FB8E20C6090A6D@acer32061217d9 (view raw or flat)
Thread:
Lists: pgsql-novice
Wow this sounds like exactly what I'm looking for!  You RULE!!!


Jason Stelzel
postgreslist(at)epigraf(dot)com
  ----- Original Message ----- 
  From: Lacey Powers 
  To: Jason Stelzel 
  Cc: pgsql-novice(at)postgresql(dot)org 
  Sent: Thursday, July 30, 2009 7:03 PM
  Subject: Re: [NOVICE] Synchronizing the schema of two PostgreSQL databases


  Hello Jason, 

  Hm. 

  From the sound of your question, you may want to investigate changing the logging levels for your installation of PostgreSQL.

  PostgreSQL can log queries that come into the server, in varying amounts. 

  Carefully adjusting the settings listed here in the documentation should let you find the correct level for your particular situation. 

  http://www.postgresql.org/docs/8.3/interactive/runtime-config-logging.html

  From there, it should just be a matter of parsing the log and extracting the desired queries. =)

  Hopefully this puts you on the right track. 

  Good luck! =) 

  Lacey 
    Thank you for the insight Lacey.  Now I have another question.  If anyone is familiar with Apple's Final Cut Server program, you may know that it uses PostgreSQL for its backend database.  My question then becomes "If one were to be using this Final Cut Server program, would there be any way to listen in on the SQL happenings so that I could capture any changes made to the schema of the database?" (and then later paste them into a begin/commit block)  And again, I realize that these are obscure questions, but I've got to take a shot at it...  Thanks again in advance.


    Jason Stelzel
    postgreslist(at)epigraf(dot)com

      ----- Original Message ----- 
      From: Lacey Powers 
      To: Jason Stelzel 
      Cc: pgsql-novice(at)postgresql(dot)org 
      Sent: Tuesday, July 28, 2009 3:28 PM
      Subject: Re: [NOVICE] Synchronizing the schema of two PostgreSQL databases


      Hello Jason, 

      Welcome to the list. =) 

      Keeping schemas synchronized is a difficult task.

      You will definitely want a process for this, and a version control system like SVN or Mercurial to help keep track of the files and changes, since this could get quite complicated quite quickly.

      I'd recommend two people at most, with a process that has to be followed and apply the changes as patch sets, rather than dump the whole schema every time.

      BEGIN;
      ALTER TABLE foo ALTER COLUMN bar <type>;
      COMMIT;

      With a designated primary and secondary database.

      I'm sure you get the idea. =) 


      Additionally, you may find a tool that you like in here:

      http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools



      As for your other questions, there is a concept of a database not having a password, but it has some serious caveats associated with it.

      You can read more about it here.

      http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html

      http://www.postgresql.org/docs/8.3/interactive/auth-methods.html#AUTH-TRUST

      If you don't want to continually enter a password, it is better to use the .pgpass or pgpass.conf

      http://www.pgadmin.org/docs/1.4/pg/libpq-pgpass.html



      I hope this helps. =) 

      Lacey


        Hello, (my first post to this list)

        I'm trying to figure out if there is any easy way to keep the schema of two totally separate databases synchronized.  And by that, I mean that if a column is added to one database, is there an easy way to automagically have the other database also include that added column (and vice versa)?  I was thinking of something along the lines of capturing the confirmation commands that postgres spits out when a change is made to the database and somehow "playing those commands back" to the other database.  Another approach might be to "backup" a schema-only representation of a table and then "restore" it to the other database.  I realize that this may be a strange application of pgsql (I can hear people asking right now "Why don't you just use a single database and separate the uses logically?") but this is the requirement I've been tasked with and I understand the application needs as being valid.  Thanks in advance for any guidance anyone can provide.

        Also, is there a concept of a postgres database not having a password?  I was using a system which did not require a password (meaning the password field was empty when connecting to it) and the application that accesses it did a conversion of the database to a newer version and now I am locked out of it (using pgAdmin III).


        Jason Stelzel
        postgreslist(at)epigraf(dot)com


        __________ Information from ESET NOD32 Antivirus, version of virus signature database 4286 (20090728) __________

        The message was checked by ESET NOD32 Antivirus.

        http://www.eset.com




-- 
Lacey Powers

The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 104
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
    

      __________ Information from ESET NOD32 Antivirus, version of virus signature database 4286 (20090728) __________

      The message was checked by ESET NOD32 Antivirus.

      http://www.eset.com




-- 
Lacey Powers

The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 104
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


  __________ Information from ESET NOD32 Antivirus, version of virus signature database 4292 (20090730) __________

  The message was checked by ESET NOD32 Antivirus.

  http://www.eset.com



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4292 (20090730) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

In response to

pgsql-novice by date

Next:From: AlejandroDate: 2009-08-03 02:24:44
Subject: Retrieve the primary key of a table
Previous:From: Lacey PowersDate: 2009-07-31 02:03:20
Subject: Re: Synchronizing the schema of two PostgreSQL databases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group