Обсуждение: track ddl changes on single database

Hi!

I'm looking for recommendation for tracking DDL changes on
single database instance.

Currently I'm using pg_log to extract DDL changes, but those changes
are cluster wide.

Ideally I would like to enable option in pg_log to give me info about
in which database changes were made.
Something like
timestamp DB: my_database LOG: statement: "DDL statement here..."

Is there such an option, or any other suggestion how to track
these changes?

Thanks,
Rikard

On Mon, Jun 11, 2007 at 12:55:08PM +0200, hubert depesz lubaczewski wrote:
> you can modify log_line_prefix to contain database name.

%d = database name
restart required

a trigger might be cleaner.




> depesz
>
> On 6/11/07, Rikard Pavelic <rikard.pavelic@zg.htnet.hr> wrote:
> >
> >Hi!
> >
> >I'm looking for recommendation for tracking DDL changes on
> >single database instance.
> >
> >Currently I'm using pg_log to extract DDL changes, but those changes
> >are cluster wide.
> >
> >Ideally I would like to enable option in pg_log to give me info about
> >in which database changes were made.
> >Something like
> >timestamp DB: my_database LOG: statement: "DDL statement here..."
> >
> >Is there such an option, or any other suggestion how to track
> >these changes?
> >
> >Thanks,
> >Rikard
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 9: In versions below 8.0, the planner will ignore your desire to
> >       choose an index scan if your joining column's datatypes do not
> >       match
> >
>
>
>
> --
> http://www.depesz.com/ - nowy, lepszy depesz

Rikard Pavelic <rikard.pavelic@zg.htnet.hr> writes:
> I'm looking for recommendation for tracking DDL changes on
> single database instance.

Perhaps "ALTER DATABASE mydb SET log_statement = ddl" would do what
you want.

            regards, tom lane

Tom Lane wrote:
> Rikard Pavelic <rikard.pavelic@zg.htnet.hr> writes:
>
>> I'm looking for recommendation for tracking DDL changes on
>> single database instance.
>>
>
> Perhaps "ALTER DATABASE mydb SET log_statement = ddl" would do what
> you want.
>
>             regards, tom lane
>
>
;(
I'm having trouble with this one.

If postgresql.conf has log_statement=none
and I execute log_statement=ddl on mydb
I don't see any ddl in pg_log

So I tried setting log_statement=ddl in postgresql.conf
and I see ddl from all databases in pg_log
I tried "Alter database other_db set log_statement=none"
but it's ddl changes still appears in pg_log

Anyway, it's not a big deal,
I can extract ddl which I need based on log_line_prefix and %d

Regards,
Rikard