Thread: Updating database structure
I have a problem with finding a way to update a database structure. This might be a very simple problem, just cannot find the info. I am looking at updating the structure of my database. I put an application on my production server some time ago and further developed the application till a point I have to merge the changes. How can I update the structure on the production server to reflect the database on my test machine in an easy way with preservation of the data on the production server. There are no major changes in the fields types etc., mainly additions/deletions of fields in the tables. Thanks, Luuk
Luuk Jansen <subscribe@angelosystems.com> writes: > How can I update the structure on the production server to reflect the > database on my test machine in an easy way with preservation of the data > on the production server. There are no major changes in the fields types > etc., mainly additions/deletions of fields in the tables. ALTER TABLE ? -- Johan
Am Mittwoch, 22. März 2006 20:40 schrieb Luuk Jansen: > I have a problem with finding a way to update a database structure. > This might be a very simple problem, just cannot find the info. > > I am looking at updating the structure of my database. I put an > application on my production server some time ago and further developed > the application till a point I have to merge the changes. > > How can I update the structure on the production server to reflect the > database on my test machine in an easy way with preservation of the data > on the production server. There are no major changes in the fields types > etc., mainly additions/deletions of fields in the tables. usually you write some update SQL scripts which can be deployed to the production database. you have a base schema in sql/schema.sql and further changes are placed inside sql/update_0001.sql with content like BEGIN; ALTER TABLE ... COMMIT; now i use a script called update.pl and a version table inside my database to register which update scripts are already deployed. The script checks which changes have to be deployed and installs them to the production database. if you dont have those update SQL scripts it can become quite difficult. You have to compare original schema and current schema. To get a schema look at pg_dump option '-s'. Compare those schemas and write your update SQL scripts. kind regards janning
Quoting Janning Vygen <vygen@gmx.de>: > Am Mittwoch, 22. März 2006 20:40 schrieb Luuk Jansen: >> I have a problem with finding a way to update a database structure. >> This might be a very simple problem, just cannot find the info. >> >> I am looking at updating the structure of my database. I put an >> application on my production server some time ago and further developed >> the application till a point I have to merge the changes. >> >> How can I update the structure on the production server to reflect the >> database on my test machine in an easy way with preservation of the data >> on the production server. There are no major changes in the fields types >> etc., mainly additions/deletions of fields in the tables. > > usually you write some update SQL scripts which can be deployed to the > production database. > > you have a base schema in > > sql/schema.sql > > and further changes are placed inside > > sql/update_0001.sql > > with content like > BEGIN; > ALTER TABLE ... > COMMIT; > > now i use a script called update.pl and a version table inside my database to > register which update scripts are already deployed. The script checks which > changes have to be deployed and installs them to the production database. > > if you dont have those update SQL scripts it can become quite difficult. You > have to compare original schema and current schema. To get a schema look at > pg_dump option '-s'. Compare those schemas and write your update SQL scripts. > > kind regards > janning > > Thanks Janning, I have something like that in mind, but is there an easy way to create that script? Is there an easy way to let postgres keep track of the changes? I currently use phpPgAdmin to make changes to the database, so it would be very handy if Postgres could add a change made to a lable somewhere, after which I gather all the rows with changes and put them in a SQL query. Can a rule be made for that or so, or do you track it manually when you say: > and further changes are placed inside > > sql/update_0001.sql > > with content like > BEGIN; > ALTER TABLE ... > COMMIT; Thanks, Luuk
On 23.03.2006, at 9:50 Uhr, subscribe@angelosystems.com wrote: > I currently use phpPgAdmin to make changes to the database, so it > would > be very handy if Postgres could add a change made to a lable > somewhere, > after which I gather all the rows with changes and put them in a SQL > query. I mostly use the command line tools or a graphical tool to make my own sql calls for every schema change I make on the development database. Then all these changes come to a "script" in my application (it's not actually a script but similar). The database has a version tag in one table. The application has a bunch of these "scripts" to convert from one version to another. It knows the sequence in how they have to be applied by looking at the version numbers. So, let's say, the production database is on version 1.1 and you have made several test versions connecting to a test server, the test/dev db is on version 1.4 and now I want to put my new application online. I simply shut down all running instances of my app, replace the application binaries, start the application again (one single instance for now), the app checks the DB version, sees that changes have to be made and applies all scripts necessary for converting the db from version 1.1 to 1.4 automatically. When this is done, I start the other instances and everything is fine. For the kids: Don't do this at home without a fresh backup and without a lot of testing of this process! cug -- PharmaLine, Essen, GERMANY Software and Database Development
Attachment
Guido Neitzer wrote:
> I mostly use the command line tools or a graphical tool to make my own
> sql calls for every schema change I make on the development database.
>
> Then all these changes come to a "script" in my application (it's not
> actually a script but similar). The database has a version tag in one
> table. The application has a bunch of these "scripts" to convert from
> one version to another. It knows the sequence in how they have to be
> applied by looking at the version numbers.
Looks like it could be useful to make the database 'log' the DDL
statements and having the ability to export those to a script starting
from a certain version.
It would probably still need user intervention, as sometimes experiments
and mistakes require fixing things - which would result in bogus or
non-optimal DDL statements in the log.
There's also sometimes the need to update data between statements, for
example when adding a new NOT NULL column to a table. Not sure what to
do in that case. Automating that would require some server side
'intelligence'...
Regards,
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
Hello, I accidentaly came across this post. I didn't follow it so I don't know whether my posting is to the topic or not. I've justuploaded project at SourceForge.Net on topic of PostgreSQL database schema upgrades because I needed to find out differencesbetween current and new schemas. The project is called apgdiff (Another PostgreSQL Diff Tool) and can be foundat apgdiff.sourceforge.net. It is still in beta but might be useful. Sorry if this post is not to the topic. Miroslav Šulc Alban Hertroys napsal(a): > Guido Neitzer wrote: >> I mostly use the command line tools or a graphical tool to make my >> own sql calls for every schema change I make on the development >> database. >> >> Then all these changes come to a "script" in my application (it's >> not actually a script but similar). The database has a version tag >> in one table. The application has a bunch of these "scripts" to >> convert from one version to another. It knows the sequence in how >> they have to be applied by looking at the version numbers. > > Looks like it could be useful to make the database 'log' the DDL > statements and having the ability to export those to a script starting > from a certain version. > > It would probably still need user intervention, as sometimes > experiments and mistakes require fixing things - which would result in > bogus or non-optimal DDL statements in the log. > > There's also sometimes the need to update data between statements, for > example when adding a new NOT NULL column to a table. Not sure what to > do in that case. Automating that would require some server side > 'intelligence'... > > Regards, >
Attachment
On Mar 23, 2006, at 9:50 AM, subscribe@angelosystems.com wrote: > I currently use phpPgAdmin to make changes to the database, so it > would > be very handy if Postgres could add a change made to a lable > somewhere, > after which I gather all the rows with changes and put them in a SQL > query. My suggestion: don't do that. What I do is keep the files used to create a database from scratch under version control (such as subversion). Then, depending on how active you development is, you can either commit scripts to make schema changes every time they happen, or you can do a diff between two releases of your application (you'll want to tag each release) and see what's changed. By changing things on-the-fly, you end up with no history of what's changed, when it changed, and why it was changed (commit logs). You may not thing having such information is important if you're the only one working on something, but trust me, having that info available has saved my bacon many times. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461