Re: [GENERAL] Replication and fsync
Dear According to manual, when you set "synchronous_commit" to on, the transaction commits will wait until master and slave flush the commit record of transaction to the physical storage, so I think even if turn off the fsync on master is safe for data consistency and data will not be lost if slave physical storage is not damaged. If "synchronous_commit" is set to remote_write, the transaction commits will wait until slave to write the data to os. Data will lose if master crashed and slave os crashed. But acording to WAL documents it will not crash the data consistency on slave if slave fsync not off. Otherwise fsync off on master may will result in losing data when master crashes, but still keep consistency on the slave if the slave is not crashed or slave's fsync is on . See follow: http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-FSYNC fsync and synchronous_commit http://www.postgresql.org/docs/current/static/wal-intro.html -- Original -- From: "maillists0";; Date: Thu, Oct 24, 2013 09:39 AM To: "pgsql-general"; Subject: [GENERAL] Replication and fsync Newb question. I'm running 9.1 with a slave using streaming replication. A coworker wants to turn off fsync on the master and insists that the slave will still be in a usable state if there is a failure on the master. We all know that turning off fsync is a bad idea, but I was under the impression that the fsync setting would be replicated to the slave, making it useless as a backup in this scenario. Am I wrong? If I'm wrong, is there still danger to the slave in this kind of setup? Can I count on it remaining unharmed if the master suffers unrecoverable corruption?
Re: [GENERAL] Need help how to manage a couple of daily DB copies.
Dear, Append following command to crontab: D=`date -d -5day +"%Y%m%d"`;echo "DROP DATABASE test_db_$D" | psql Maybe you should change the "psql" to your psql path. -- Original -- From: "Andreas";; Date: Fri, Oct 25, 2013 09:53 AM To: "pgsql-general"; Subject: [GENERAL] Need help how to manage a couple of daily DB copies. Hi, I'd like to set up a DB-Server that keeps copies of our productive db for an external db-assistant. He should prepare chores on the test-server and mail the sql scripts to me. I'll look over those scripts and run them against the productive db myself. So I'd like to have a daily cron job dump the main db, rename the test-db to something with a date in it. Like test_db --> test_db_20131024 Create a new test_db and import the dump of the main db. So far no problem but how could I limit the number of test_dbs to 5? I'd like to keep those test_dbs 5 days and then drop them.
Re: [GENERAL] Deduplication and transaction isolation level
Is your table have only pkey one field and the sql is the exact same sql you use? If you have more than one field to insert, DISTINCT ensure the whole row values set are distinct instead of one field value. that't maybe a reason for your situation. -- Original -- From: "Steven Schlansker";; Date: Thu, Sep 26, 2013 01:50 AM To: "Merlin Moncure"; Cc: "François Beausolei"; "Forums postgresql"; Subject: Re: [GENERAL] Deduplication and transaction isolation level On Sep 25, 2013, at 6:04 AM, Merlin Moncure wrote: > On Tue, Sep 24, 2013 at 10:19 PM, François Beausoleil > wrote: >> Hi all! >> >> I import many, many rows of data into a table, from three or more computers, >> 4 times per hour. I have a primary key, and the query I use to import the >> data is supposed to dedup before inserting, but I still get primary key >> violations. >> >> The import process is: >> >> * Load CSV data into temp table >> * INSERT INTO dest SELECT DISTINCT (pkey) FROM temp WHERE NOT >> EXISTS(temp.pkey = dest.pkey) >> >> I assumed (erroneously) that this would guarantee no duplicate data could >> make it into the database. The primary key violations are proving me wrong. > > Right. Transaction A and B are interleaved: they both run the same > check against the same id at the same time. Both checks pass because > neither transaction is committed. This problem is not solvable by > adjusting the isolation level. Are you sure that this is the case? It is my understanding that since 9.1 with SSI (https://wiki.postgresql.org/wiki/SSI) if you set the transaction isolation level to SERIALIZABLE, this problem is solved, as the insert will take a "predicate lock" and the other insert cannot succeed. We use this to detect / resolve concurrent inserts that violate primary keys and it works great. However in this case it probably doesn't help the OP because the cost of restarting the entire import is likely too high. > > Typical solutions might be to: > A. Lock the table while inserting > B. Retry the transaction following an error. > C. Import the records to a staging table, then copy the do the > deduplication check when moving from the staging table > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general .
[GENERAL] Re: [GENERAL] ??????[GENERAL] SP to calc shipments vs receipts
thanks. -- Original -- From: "Chris Travers";; Date: Wed, Sep 25, 2013 10:46 PM To: "DDT"<410845...@qq.com>; Cc: "bret_stern"; "pgsql-general"; Subject: Re: [GENERAL] ??[GENERAL] SP to calc shipments vs receipts On Wed, Sep 25, 2013 at 7:27 AM, DDT <410845...@qq.com> wrote: By the way, you can try to save the current totals to another table. update it through triggers when the inventory transactions changed. it may lead to better performance on a large set of inventory transactions for query current totals If you are going to do this, my recommendation is to store periodic summaries (i.e. for sum through date) and then aggregate rolling forward. This vastly simplifies querying and data validation if you are only appending data. -- Best Wishes,Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more.shtml
[GENERAL] ??????[GENERAL] SP to calc shipments vs receipts
By the way, you can try to save the current totals to another table.
update it through triggers when the inventory transactions changed.
it may lead to better performance on a large set of inventory transactions for
query current totals
-
Think I'll just do an UPDATE which changes the ALLOCATED
transaction to a SHIP transaction and uses the current
Ship Date/Time
On Tue, 2013-09-24 at 07:38 -0700, Bret Stern wrote:
> Support at it's finest.
> Thinking maybe ALLOCATED transactions zero out
> when the allocated qty is shipped, but I would like to save
> the original allocated qty..maybe add another field in my
> transaction table to save the allocated transaction qty.
>
> Also don't see any problem with deleting the ALLOCATED
> transaction record..normally I don't like deleting any transaction
> records, but at this moment don't see the harm.
>
> my table.
>
> id serial NOT NULL,
> trans_date character varying(20),
> trans_time character varying(20),
> trans_type character varying(8),
> trans_user character varying(10),
> trans_qty real,
> trans_reference character varying(40),
> trans_comment character varying(80),
> part_no character varying(40),
> part_desc character varying(40),
> part_owner_id character varying(20),
> building character varying(4),
> isle character varying(2),
> rack character varying(2),
> shelf character varying(2),
> matrix character varying(2),
> CONSTRAINT ss_item_tran_key PRIMARY KEY (id)
>
> You'all have me thinking. Thanks for taking time to
> educate me.
>
>
> On Tue, 2013-09-24 at 14:22 +0800, DDT wrote:
> > hello, is the output calculated by following rule?
> >
> > on_hand SUM(receipt) - SUM(shipment) - SUM(allocated)
> > available SUM(receipt) - SUM(shipment)
> >
> > sql can be:
> > sum(case when trans_type='REC' then trans_qty when trans_type IN
> > ('SHP', 'ALL') then -trans_qty else 0) as on_hand
> > sum(case when trans_type='REC' then trans_qty when trans_type = 'SHP'
> > then -trans_qty else 0) as on_hand
> >
> > but i'm courise about if something is allocated and then it shipped,
> > will you delete the record or allocation?
> >
> >
> >
> >
> > On 9/23/2013 10:13 PM, Bret Stern wrote:
> > > I have an inventory transaction table with several fields,
> > > specifically:
> > > part_no
> > > trans_type
> > > trans_qty
> > >
> > > part_no | trans_type | trans_qty
> > > abc REC 5000 (receipt)
> > > abc REC 400 (receipt)
> > > abc SHP 1000 (shipment)
> > > abc ALL 1000 (allocated)
> > >
> > > Looking for the best way to show following totals with SQL
> > >
> > > on_hand | allocated | available
> > > 3400 1000 4400
> >
> > select part_no,
> > sum(cast when trans_type='REC' then trans_qty else 0) as
> > "on_hand",
> > sum(cast when trans_type='ALL' then trans_qty else 0) as
> > "allocated",
> > sum(cast when trans_type='SHP' then trans_qty else 0) as
> > "allocated"
> > from inventory_transaction_table
> > group by part_no;
> >
> >
> > except, your example output doesn't correlate with your sample input
> > according to any rules I can see.
> >
> >
> > --
> > john r pierce 37N 122W
> > somewhere on the middle of the left coast
> >
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> > .
> >
>
>
>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
.
[GENERAL] ?????? [GENERAL] SP to calc shipments vs receipts
hello, is the output calculated by following rule?
on_hand SUM(receipt) - SUM(shipment) - SUM(allocated)
available SUM(receipt) - SUM(shipment)
sql can be:
sum(case when trans_type='REC' then trans_qty when trans_type IN ('SHP', 'ALL')
then -trans_qty else 0) as on_hand
sum(case when trans_type='REC' then trans_qty when trans_type = 'SHP' then
-trans_qty else 0) as on_hand
but i'm courise about if something is allocated and then it shipped, will you
delete the record or allocation?
On 9/23/2013 10:13 PM, Bret Stern wrote:
> I have an inventory transaction table with several fields,
> specifically:
> part_no
> trans_type
> trans_qty
>
> part_no | trans_type | trans_qty
> abc REC 5000(receipt)
> abc REC 400 (receipt)
> abc SHP 1000(shipment)
> abc ALL 1000(allocated)
>
> Looking for the best way to show following totals with SQL
>
> on_hand | allocated | available
> 3400 1000 4400
select part_no,
sum(cast when trans_type='REC' then trans_qty else 0) as
"on_hand",
sum(cast when trans_type='ALL' then trans_qty else 0) as
"allocated",
sum(cast when trans_type='SHP' then trans_qty else 0) as
"allocated"
from inventory_transaction_table
group by part_no;
except, your example output doesn't correlate with your sample input
according to any rules I can see.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
.
