Обсуждение: Different transaction log for database/schema
Hello,
I would like kindly ask for an advice whether and how the transaction log behavior could be configured.
I would like to have possibility to differentiate transaction logs for two databases or schema. I would need such configuration for my testing environment where I need to run simultaneously different tests using transactions. And I need to separate those tests for being sure that they do not interfere with each other. I'm using XA transactions in Java.
Is there some way how to reach such behavior? Or is it possible to define where to store prepared transaction for a current session?
Thank you for any hint or explanation how the things work in PostgreSQL
Regards
Ondrej Chaloupka
I would like kindly ask for an advice whether and how the transaction log behavior could be configured.
I would like to have possibility to differentiate transaction logs for two databases or schema. I would need such configuration for my testing environment where I need to run simultaneously different tests using transactions. And I need to separate those tests for being sure that they do not interfere with each other. I'm using XA transactions in Java.
Is there some way how to reach such behavior? Or is it possible to define where to store prepared transaction for a current session?
Thank you for any hint or explanation how the things work in PostgreSQL
Regards
Ondrej Chaloupka
>where I need to run simultaneously different tests using transactions Are you running tests simultaneously on different databases? i.e. access to different databases within one transaction. If not -- after each transaction you can do pg_switch_xlog() then new transaction log will be created and next transaction details will store in new log file. make sure that you set proper configuration parameter to keep maximum transactions logs. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Different-transaction-log-for-database-schema-tp5764604p5764622.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi Sachin,
thank you for the idea. I didn't know about the function.
Just this probably won't work for my use case. I do not access to different databases in one transaction. I access different databases simultaneously each with it's own xa transaction. What I understand the switch_xlog servers for using new file for transaction log. What I would understand is supposed to be used e.g. for cleaning.
In my case it's normal that the two tests uses database (their own databases but under one PostgreSQL server) and each one creates an record in the log. E.g. when first test would call the switch_xlog and continue with work then the second test would start and call the switch_xlog then the both tests will still use the same transaction log despite the fact that it would be some new one. In case that I understand the function right.
I would need to force of using different files based on user/schema/database. Is there such possibility?
Thank you
Ondra
thank you for the idea. I didn't know about the function.
Just this probably won't work for my use case. I do not access to different databases in one transaction. I access different databases simultaneously each with it's own xa transaction. What I understand the switch_xlog servers for using new file for transaction log. What I would understand is supposed to be used e.g. for cleaning.
In my case it's normal that the two tests uses database (their own databases but under one PostgreSQL server) and each one creates an record in the log. E.g. when first test would call the switch_xlog and continue with work then the second test would start and call the switch_xlog then the both tests will still use the same transaction log despite the fact that it would be some new one. In case that I understand the function right.
I would need to force of using different files based on user/schema/database. Is there such possibility?
Thank you
Ondra
---------- Původní zpráva ----------
Od: sachin kotwal <kotsachin@gmail.com>
Datum: 22. 7. 2013
Předmět: Re: [GENERAL] Different transaction log for database/schema
>where I need to run simultaneously different tests using transactions
Are you running tests simultaneously on different databases?
i.e. access to different databases within one transaction.
If not --
after each transaction you can do pg_switch_xlog() then new transaction log
will be created and next transaction details will store in new log file.
make sure that you set proper configuration parameter to keep maximum
transactions logs.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Different-transaction-log-for-database-schema-tp5764604p5764622.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello 2013/7/22 Ondrej Chaloupka <chalda@seznam.cz>: > Hi Sachin, > > thank you for the idea. I didn't know about the function. > Just this probably won't work for my use case. I do not access to different > databases in one transaction. I access different databases simultaneously > each with it's own xa transaction. What I understand the switch_xlog servers > for using new file for transaction log. What I would understand is supposed > to be used e.g. for cleaning. > > In my case it's normal that the two tests uses database (their own databases > but under one PostgreSQL server) and each one creates an record in the log. > E.g. when first test would call the switch_xlog and continue with work then > the second test would start and call the switch_xlog then the both tests > will still use the same transaction log despite the fact that it would be > some new one. In case that I understand the function right. > > I would need to force of using different files based on > user/schema/database. Is there such possibility? No, it is not possible in one PostgreSQL database cluster. You can create more than one PostgreSQL clusters on one computer. Regards Pavel > > Thank you > Ondra > > > ---------- Původní zpráva ---------- > Od: sachin kotwal <kotsachin@gmail.com> > Datum: 22. 7. 2013 > Předmět: Re: [GENERAL] Different transaction log for database/schema > > >>where I need to run simultaneously different tests using transactions > > Are you running tests simultaneously on different databases? > i.e. access to different databases within one transaction. > > If not -- > > after each transaction you can do pg_switch_xlog() then new transaction log > will be created and next transaction details will store in new log file. > > make sure that you set proper configuration parameter to keep maximum > transactions logs. > > > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Different-transaction-log-for-database-schema-tp5764604p5764622.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On 7/22/2013 9:33 AM, Ondrej Chaloupka wrote: > thank you for the idea. I didn't know about the function. > Just this probably won't work for my use case. I do not access to > different databases in one transaction. I access different databases > simultaneously each with it's own xa transaction. What I understand > the switch_xlog servers for using new file for transaction log. What I > would understand is supposed to be used e.g. for cleaning. postgres servers don't really have a 'transaction log' in the same sense as Oracle etc. they have a 'write-ahead log' which works quite differently. in the context of postgres's WAL logs, your question doesn't make any sense at all. -- john r pierce 37N 122W somewhere on the middle of the left coast
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of John R Pierce > Sent: Monday, July 22, 2013 2:32 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Different transaction log for database/schema > > On 7/22/2013 9:33 AM, Ondrej Chaloupka wrote: > > thank you for the idea. I didn't know about the function. > > Just this probably won't work for my use case. I do not access to > > different databases in one transaction. I access different databases > > simultaneously each with it's own xa transaction. What I understand > > the switch_xlog servers for using new file for transaction log. What I > > would understand is supposed to be used e.g. for cleaning. > > postgres servers don't really have a 'transaction log' in the same sense > as Oracle etc. they have a 'write-ahead log' which works quite > differently. > > in the context of postgres's WAL logs, your question doesn't make any sense > at all. > > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > Oracle doesn't have transaction log either, MVCC implemented through Redo Logs and Undo tablespaces, and those are per database,not per schema. On the other hand, MS SQL Server has Transaction Logs, and they are per database. Still, I don't quite understand, what goal original poster is trying to achieve. Regards, Igor Neyman
On Mon, Jul 22, 2013 at 10:09 AM, Ondrej Chaloupka <chalda@seznam.cz> wrote: > Hello, > > I would like kindly ask for an advice whether and how the transaction log > behavior could be configured. > > I would like to have possibility to differentiate transaction logs for two > databases or schema. I would need such configuration for my testing > environment where I need to run simultaneously different tests using > transactions. And I need to separate those tests for being sure that they do > not interfere with each other. I'm using XA transactions in Java. It is not clear to me to which logs you are referring: the commit logs or the WALs? Anyway, transaction are numbered in an unique way around the cluster, and therefore it does not matter on which database you are going to run the transaction, it will not interfere with other databases within the same cluster. I don't get what advantage will give you having logs different for each database/schema, considering that it is not clear what kind of maintenance you are going to do on such logs. Can you please better explain what is your aim and use case? Luca
Hi,
I'm talking about transaction log for XA/2PC transactions. My tests crash application in some phase of the transaction. After restart I would like check how the recovery proceeded and I would like verify that all the transaction records (I mean info about prepare tx) are gone on DB site.
Nevertheless what I took from discussion so far is that for my use case I need more PostgreSQL installations or to synchronize test runs for not being run in parallel.
Thank you for all reactions
Ondra
I'm talking about transaction log for XA/2PC transactions. My tests crash application in some phase of the transaction. After restart I would like check how the recovery proceeded and I would like verify that all the transaction records (I mean info about prepare tx) are gone on DB site.
Nevertheless what I took from discussion so far is that for my use case I need more PostgreSQL installations or to synchronize test runs for not being run in parallel.
Thank you for all reactions
Ondra
---------- Původní zpráva ----------
Od: Luca Ferrari <fluca1978@infinito.it>
Datum: 23. 7. 2013
Předmět: Re: [GENERAL] Different transaction log for database/schema
On Mon, Jul 22, 2013 at 10:09 AM, Ondrej Chaloupka <chalda@seznam.cz> wrote:
> Hello,
>
> I would like kindly ask for an advice whether and how the transaction log
> behavior could be configured.
>
> I would like to have possibility to differentiate transaction logs for two
> databases or schema. I would need such configuration for my testing
> environment where I need to run simultaneously different tests using
> transactions. And I need to separate those tests for being sure that they do
> not interfere with each other. I'm using XA transactions in Java.
It is not clear to me to which logs you are referring: the commit logs
or the WALs? Anyway, transaction are numbered in an unique way around
the cluster, and therefore it does not matter on which database you
are going to run the transaction, it will not interfere with other
databases within the same cluster.
I don't get what advantage will give you having logs different for
each database/schema, considering that it is not clear what kind of
maintenance you are going to do on such logs. Can you please better
explain what is your aim and use case?
Luca
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general