Re: 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 <mailto: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