Thread: Function tracking

Hello

2013/6/7 Rebecca Clarke <r.clarke83@gmail.com>:
> Hi all
>
> I'm looking for suggestions on the best way to track the updates to a
> function.
>
> We have two databases, Dev & Live, so I want to update Live with just the
> functions that have been modified in the DEV databas3e.
> Is there another, easier way to track the updates than manually recording it
> in a document? (I'm trying to eliminate human interference).
>

There is a few tools

http://stackoverflow.com/questions/4804779/how-to-check-difference-between-two-databases-in-postgressql
http://pgdiff.sourceforge.net/

But I prefer editing files for storing schema and function
definitions. And I use a git. I dislike direct object modifying via
tools like pgAdmin and similar.

Regards

Pavel

>
> Many thanks in advance
>
> Rebecca
>
>
>
>
>
>
>

On 6/7/2013 6:30 AM, Rebecca Clarke wrote:
> I'm looking for suggestions on the best way to track the updates to a
> function.
>
> We have two databases, Dev & Live, so I want to update Live with just
> the functions that have been modified in the DEV databas3e.
> Is there another, easier way to track the updates than manually
> recording it in a document? (I'm trying to eliminate human interference).
>

Script the changes out and put them in source control. The small amount
of effort and discipline required will pay off big time.

-- Stephen

2013/6/7 Rebecca Clarke <r.clarke83@gmail.com>:
> Hi all
>
> I'm looking for suggestions on the best way to track the updates to a
> function.
>
> We have two databases, Dev & Live, so I want to update Live with just the
> functions that have been modified in the DEV databas3e.
> Is there another, easier way to track the updates than manually recording it
> in a document? (I'm trying to eliminate human interference).

You might want to take a look at Sqitch ( http://sqitch.org/ ), which
is a little
complex to set up, but works along similar lines to GIT.

As others have said, you definitely need to use some kind of source control
to track changes. With the systems I've managed, I've ensured that releases
are tagged in the source control system with a release number, and that
database script files exist for each release (if needed) which are applied
when the release is rolled out to the respective environment. It's still a bit
of a manual process and requires a bit of (self) discipline, but has worked
quite well for me.

Regards

Ian Barwick

Rebecca Clarke wrote on 07.06.2013 12:30:
> I'm looking for suggestions on the best way to track the updates to a
> function.
>
> We have two databases, Dev & Live, so I want to update Live with just
> the functions that have been modified in the DEV databas3e. Is there
> another, easier way to track the updates than manually recording it
> in a document? (I'm trying to eliminate human interference).

We are pretty happy using Liquibase for all our schema migrations.

For stored procedures/functions it's best to use a changeSet that includes a SQL file
and is marked as runOnChange=true

The Liquibase changelog files are then stored in Subversion.
A little shell script applies the changes to any environment we want

Thomas

> From: Pavel Stehule <pavel.stehule@gmail.com>
> To: Rebecca Clarke <r.clarke83@gmail.com>
> Cc: pgsql-general@postgresql.org
> Sent: Friday, 7 June 2013, 11:44
> Subject: Re: [GENERAL] Function tracking
>
> Hello
>
> 2013/6/7 Rebecca Clarke <r.clarke83@gmail.com>:
>>  Hi all
>>
>>  I'm looking for suggestions on the best way to track the updates to a
>>  function.
>>
>>  We have two databases, Dev & Live, so I want to update Live with just
> the
>>  functions that have been modified in the DEV databas3e.
>>  Is there another, easier way to track the updates than manually recording
> it
>>  in a document? (I'm trying to eliminate human interference).
>>
>
> There is a few tools
>
> http://stackoverflow.com/questions/4804779/how-to-check-difference-between-two-databases-in-postgressql
> http://pgdiff.sourceforge.net/
>
> But I prefer editing files for storing schema and function
> definitions. And I use a git. I dislike direct object modifying via
> tools like pgAdmin and similar.
>

I agree, things can get a bit chaotic with everyone using pgAdmin. We do similiar with a set of script files in source
control.In addition some sort of automated deployment process helps. 

My soloution is probably overkill, but we have to deploy over a number of slony nodes in a big two phase commit.  I
havea controlled deployment process that checks the changes against a small list of things I don't want the devs doing,
checksfor any errors by testing against a special clone, and then records the actual effects of the changes in the
scripts(i.e. drop cascaded etc) before anything is actually deployed. 

> From: Rebecca Clarke <r.clarke83@gmail.com>
>To: pgsql-general@postgresql.org
>Sent: Friday, 7 June 2013, 11:30
>Subject: [GENERAL] Function tracking
>
>
>
>Hi all
>
>
>I'm looking for suggestions on the best way to track the updates to a function.
>
>
>We have two databases, Dev & Live, so I want to update Live with just the functions that have been modified in the DEV
databas3e. 
>Is there another, easier way to track the updates than manually recording it in a document? (I'm trying to eliminate
humaninterference). 
>