[GENERAL] change management

[Matthew Nuzum]

Well, business is good I guess.  We're getting ready to deploy a few new
installations of our software which is raising some questions.  Most of them
I have answers to, but one area of development I haven't had to deal with
much is managing changes to the database structure.

I would really like to know what others consider best practice for
distributing changes to database structure and data.  Currently we have one
production server and one test server.  We simply copy and paste our
database change queries to get the same result on both servers.

I'm also concerned with the differences of sequence values.  There are a
couple of tables that will need to be replicated from time to time.  For
now data will get copied from one master server out to several other
installations.  There may come a point some day when the other installations
add data to these tables that may or may not need to get pushed back to the
main server.

Any suggestions?


Matthew Nuzum   | Makers of Elite Content Management System
www.followers.net   | View samples of Elite CMS in action
[EMAIL PROTECTED]   | http://www.followers.net/portfolio/




---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Incremental backups, and backup history

[Matthew Nuzum]

Regarding backup history:

I have an application designed for novices.  Apparently it's easy to hit the
Delete button, and then say yes to the Are you sure you want to delete
this? question even when they don't want to. Therefore I simply mark a
record as deleted.  For example, 
UPDATE table SET deleted='t' WHERE something=true;

Then my application logic pretends it doesn't really exist until two days
later the user decides they want it back.

It works very well for me.

--
Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]
 

 -Original Message-
 From: Bruno Wolff III [mailto:[EMAIL PROTECTED]
 Sent: Thursday, June 19, 2003 6:59 AM
 To: Antonios Christofides
 Cc: [EMAIL PROTECTED]
 Subject: Re: Incremental backups, and backup history
 
 On Thu, Jun 19, 2003 at 11:42:28 +0300,
   Antonios Christofides [EMAIL PROTECTED] wrote:
 
  My second question is a general relational database backup question, not
  specifically related to pgsql. Sometimes a user accidentally
  deletes/corrupts a file, and discovers it three days later. After they
  come panicing to me, I can give them their file as it was three days
  ago, because of the backup tape rotation. Now suppose a user deletes
  ten employees from the database, and three days later they understand
  that this was a bad thing. Now what? I can restore the entire database
  and make it as it was three days ago, but I can't restore the particular
  deleted records in the current database, as the relations make the
  database behave as a single unit.
 
 This isn't a good situation to be in. If you log all of the transactions,
 one option may be to restore the database as of the last time it was
 valid, remove the bogus transactions from the transaction log and then
 replay
 the transactions from the time of the backup to the present.
 This will mean downtime for the database and it is possible that removing
 the bogus tranasctions will affect things as you reapply other
 transactions
 in ways you don't want.
 
 Another option is to figure out what the bogus tranasctions did (which
 keeping
 a history will help out with) and try to undo them as best as possible
 with
 new transactions.
 
 Either of the above are going to need manual oversight.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org