Automate build of the script to update DB in postgres

Question

We have a system made in java using a postgres database. This database changes often, and once a week or less we are updating it. These changes are in the struture of the DB (DDL), usually in functions and fields to add new functionality.

For the changes in the DB we usually use navicat as follows: 1- We made the change in the structure of the DB using navicat and we copy the SQL that gives us to an XML file for each change we made. 2- When we have to update the DB in production we check files, identified by a version number, and update the DB. 3- Then we repeat this for each DB installed (30 in total)

The problem that we are having is that as the whole process is manual and is very easy to forget to copy a change to the XML so when we use it the script does not work or even worse when the system needs this change fails.

Therefore we are looking for a way to automate this task and we came with the following idea: 1- We make changes in navicat 2- Configure the postgres to LOG the changes in the DDL into a CSV file 3- Later we read the CSV file and pass the changes to the XML to update the producction DB

The problem we are having is that the LOG will save all attempts to change the structure, including errors so if we use that script to update it will fail too.

Is there some way to save only successful DDL changes in the log in postgres?

Is there a script or application to get the DDL changes and put it in script automatically?

Is there a better way to automate this process?

I would suggest you modifying your question to be more laconic. — gsamaras
– gsamaras, Commented Nov 2, 2014 at 2:53

Greg · Accepted Answer · 2014-11-02 04:28:27Z

1

there are many answers for the questions above :-) i have managed rapidly changing databases using a number of schemes. one way to do it is maintain a master database (like you have). Use dbtoyaml to create a yaml description of the database. Then use yamltodb on all of the (30) targets, which will do everything necessary to make the target databases look exactly like the master. I have used this software for about 6 months, it is fantastic. pyrseas. -g

answered Nov 2, 2014 at 4:28

Greg

6,7892 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Net Wilson Over a year ago

i try it but it dont support function with setof and trigger function. u use the installed version with pip o compile it fron git?

Greg Over a year ago

as far as i know it supports everything in postgres. if not, it is a bug.

Collectives™ on Stack Overflow

Automate build of the script to update DB in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related