Обсуждение: Schema Upgrade Howto
Hi, is there a schema upgrade howto? I could not find much with google. There is a running DB and a development DB. The development DB has some tables, columns and indexes added. What is the preferred way to upgrade? I see these solutions: - pg_dump production DB. Install schema only from dev DB, restore data only from dump. - Use alter table. - Use a tool like apgdiff (never tried it). I guess all ways will be possible. But what do you suggest? Thomas -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de
On Thu, Oct 30, 2008 at 10:54:46AM +0100, Thomas Guettler wrote: > Hi, > > is there a schema upgrade howto? I could not find much with google. > > There is a running DB and a development DB. The development DB > has some tables, columns and indexes added. The only sure way to track such changes is by changing the databases--especially in development--only via scripts, all of which go into your source code management system. > What is the preferred way to upgrade? Via scripts, all of which go in a transaction. It's here that PostgreSQL's transactional DDL (CREATE, ALTER, DROP, for example) really shines. > I see these solutions: > - pg_dump production DB. Install schema only from dev DB, restore > data only from dump. This won't scale, but may work for now while you institute the development process outlined above. Test this very carefully, just as you would any other database change. > - Use alter table. Yep. See above for how. > - Use a tool like apgdiff (never tried it). These tools never have enough information to make a decision guaranteed to be correct, so the whole class of them is bogus. > I guess all ways will be possible. But what do you suggest? See above :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Hi, I found a way to do it. One problem remains: The order of the columns can't be changed. Any change to make postgres support this in the future? My way: pg_dump -s prod | strip-schema-dump.py - > prod.schema pg_dump -s devel | strip-schema-dump.py - > devel.schema strip-schema-dump.py removes some stuff which I don't care about (Owner, Comments, ...) kdiff3 prod.schema devel.schema You need to create an upgrade script by looking at the diff. But it is not difficult: -- update-YYYY-MM-DD.sql begin; alter table ... add column ...; ... commit; Execute on production: cat update-YYYY-MM-DD.sql | psql See http://www.djangosnippets.org/snippets/1160/ David Fetter schrieb: > On Thu, Oct 30, 2008 at 10:54:46AM +0100, Thomas Guettler wrote: > >> Hi, >> >> is there a schema upgrade howto? I could not find much with google. >> >> There is a running DB and a development DB. The development DB >> has some tables, columns and indexes added. >> > > The only sure way to track such changes is by changing the > databases--especially in development--only via scripts, all of which > go into your source code management system. > > -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de
On Thu, Oct 30, 2008 at 02:37:43PM +0100, Thomas Guettler wrote: > Hi, > > I found a way to do it. It's the wrong way. Trust me on this. > One problem remains: The order of the columns can't be changed. Any > change to make postgres support this in the future? It's been proposed several times :) > My way: > > pg_dump -s prod | strip-schema-dump.py - > prod.schema > pg_dump -s devel | strip-schema-dump.py - > devel.schema > > strip-schema-dump.py removes some stuff which I don't care about (Owner, Comments, ...) > > kdiff3 prod.schema devel.schema > > You need to create an upgrade script by looking at the diff. No. Really, no. You need to create the upgrade script by creating upgrade scripts, not by reverse engineering. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Thomas Guettler wrote: > Hi, > > is there a schema upgrade howto? I could not find much with google. > > There is a running DB and a development DB. The development DB > has some tables, columns and indexes added. What is the preferred way > to upgrade? > > I see these solutions: > - pg_dump production DB. Install schema only from dev DB, restore data > only from dump. > - Use alter table. > - Use a tool like apgdiff (never tried it). > > I guess all ways will be possible. But what do you suggest? Use three instances of the database: development, quality assurance testing, and production. DEV and QAT are occasionally refreshed from a pg_dump of PRD. Developers work against DEV for modify-compile-test-(doh, I broke it)-refix-compile-test cycles. All structural or development-related changes required to the data base are done with a SQL text file script. The script files are managed along with the source code in SVN. When developers are satisfied, the script is applied to QAT and then end-users test the modified application against QAT. When end-users sign off that they are satisfied, the same (*unmodifed from as run against QAT*) script is run on PRD at the same time the same (*unmodifed from as run against QAT*) application is deployed for production use.