Обсуждение: Postgres database : Out of balance problem from the same table
Hi
We have a problem, we have a master database as dbmaster sitting in the certain server and a backup database sitting in another server called dbbackup
Using the latest transaction number(aud_id = 1669629) that got generated from a table called audmth we wanted to check if the sum of the transactions from this table called audmth is the same.
DBMASTER – Database:
dbmaster =# select to_char(sum(aud_id),9999999999999.99) from audmth where aud_id <1669629;
to_char
-------------------
2774814647.00
(1 row)
DBBACKUP – Database:
dbbackup =# select to_char(sum(aud_id),9999999999999.99) from audmth where aud_id <1669629;
to_char
-------------------
2773145126.00
(1 row)
Due to the above issue, we restored the database from dbbackup to masterdb and visa versa, but we are still getting difference from the two databases which is very strange.
Any idea what could be the problem?
Thanks in advance
Khangelani Gama
Confidentiality Notice:http://ucs.co.za/conf.html
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of UCS Group and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroy the e-mail. UCS Group Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Hi all
Can anyone please assist or give an advise on the issue stated below
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Khangelani Gama
Sent: Monday, November 22, 2010 10:31 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Postgres database : Out of balance problem from the same table
Hi
We have a problem, we have a master database as dbmaster sitting in the certain server and a backup database sitting in another server called dbbackup
Using the latest transaction number(aud_id = 1669629) that got generated from a table called audmth we wanted to check if the sum of the transactions from this table called audmth is the same.
DBMASTER – Database:
dbmaster =# select to_char(sum(aud_id),9999999999999.99) from audmth where aud_id <1669629;
to_char
-------------------
2774814647.00
(1 row)
DBBACKUP – Database:
dbbackup =# select to_char(sum(aud_id),9999999999999.99) from audmth where aud_id <1669629;
to_char
-------------------
2773145126.00
(1 row)
Due to the above issue, we restored the database from dbbackup to masterdb and visa versa, but we are still getting difference from the two databases which is very strange.
Any idea what could be the problem?
Thanks in advance
Khangelani Gama
Confidentiality Notice:http://ucs.co.za/conf.html
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of UCS Group and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroy the e-mail. UCS Group Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of UCS Group and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroy the e-mail. UCS Group Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Khangelani Gama <Khangelani.Gama@ucs-software.co.za> wrote: > dbmaster =# select to_char(sum(aud_id),9999999999999.99) from > audmth where aud_id <1669629; > to_char > ------------------- > 2774814647.00 > (1 row) > dbbackup =# select to_char(sum(aud_id),9999999999999.99) from > audmth where aud_id <1669629; > to_char > ------------------- > 2773145126.00 > (1 row) How are you getting the data from the master to the backup? What does the definition of the audmth table look like (including indexes)? How many rows are in it? Have you gotten any unusual messages in the log? > Due to the above issue, we restored the database from dbbackup to > masterdb and visa versa, but we are still getting difference from > the two databases which is very strange. How did you restore from one to the other, exactly? Where on each machine are you deploying the data? Which port is being used in each case? How are you starting and stopping the databases? When you say you still get the difference, do you mean the total is staying with the machine or following the copy of the database? What version of PostgreSQL is this? How did you install it? Since identical databases running identical software don't return different results for the same query, there is some important fact you haven't told us yet. Maybe one of the questions I've asked will draw that out, but it would be best if you took some time to think about what else might be different that you haven't told us yet. -Kevin
Hi Kevin Thanks for feedback, I was going to answer your questions and I believe that your questions were going to lead us to thesolution due to the fact that one of my seniors discovered that there is a bug on getting the data from master to thebackup affecting only this table audmth, which is what you asked below. Many Thanks -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Tuesday, November 23, 2010 4:48 PM To: pgsql-admin@postgresql.org; Khangelani Gama Subject: Re: [ADMIN] Postgres database : Out of balance problem from the same table Khangelani Gama <Khangelani.Gama@ucs-software.co.za> wrote: > dbmaster =# select to_char(sum(aud_id),9999999999999.99) from > audmth where aud_id <1669629; > to_char > ------------------- > 2774814647.00 > (1 row) > dbbackup =# select to_char(sum(aud_id),9999999999999.99) from > audmth where aud_id <1669629; > to_char > ------------------- > 2773145126.00 > (1 row) How are you getting the data from the master to the backup? What does the definition of the audmth table look like (including indexes)? How many rows are in it? Have you gotten any unusual messages in the log? > Due to the above issue, we restored the database from dbbackup to > masterdb and visa versa, but we are still getting difference from > the two databases which is very strange. How did you restore from one to the other, exactly? Where on each machine are you deploying the data? Which port is being used in each case? How are you starting and stopping the databases? When you say you still get the difference, do you mean the total is staying with the machine or following the copy of the database? What version of PostgreSQL is this? How did you install it? Since identical databases running identical software don't return different results for the same query, there is some important fact you haven't told us yet. Maybe one of the questions I've asked will draw that out, but it would be best if you took some time to think about what else might be different that you haven't told us yet. -Kevin The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential informationof UCS Group and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intendedaddressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroythe e-mail. UCS Group Limited and its subsidiaries distance themselves from and accept no liability for unauthoriseduse of their e-mail facilities or e-mails sent other than strictly for business purposes.