[GENERAL] in defensive of zone_reclaim_mode on linux

Over the last several months, I've seen a lot of grumbling about how zone_reclaim_mode eats babies, kicks puppies, and basically how you should just turn it off and live happily ever after. I thought I should add a counterexample, because that advice has not proven very good for us. Some facts

Re: [GENERAL] in defensive of zone_reclaim_mode on linux

> On Sep 6, 2015, at 4:07 AM, Andres Freund wrote: > > To me that sounds like the negative impact of transparent hugepages > being mitigated to some degree by zone reclaim mode (which'll avoid some > cross-node transfers). FWIW: $ cat /sys/kernel/mm/transparent_hugepage/enabled always madvise

[GENERAL] pgpass (in)flexibility

We're in a situation where we would like to take advantage of the pgpass hostname field to determine which password gets used. For example: psql -h prod-server -d foo # should use the prod password psql -h beta-server -d foo # should use the beta password This would *seem* to be simple, just put

Re: [GENERAL] pgpass (in)flexibility

On Sep 15, 2015, at 12:27 AM, Jim Nasby wrote: > > On 9/15/15 12:48 AM, Ben Chobot wrote: >> We're in a situation where we would like to take advantage of the pgpass >> hostname field to determine which password gets used. For example: >> >> psql -h prod-

Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?

On Aug 31, 2016, at 2:55 PM, Nicolas Grilly wrote: > > It looks like Instagram has been using pg_reorg (the ancestor of pg_repack) > to keep all likes from the same user contiguous on disk, in order to minimize > disk seeks. > > http://instagram-engineering.tumblr.com/post/40781627982/handling

Re: [GENERAL] RAM of Postgres Server

On Jan 7, 2016, at 10:32 PM, Sachin Srivastava wrote: > > > Dear John, > > We are looking at more like 500-600 connections simultaneously in 1 day and I > want to say we get 1 to 12000 connections a day per db. Unless you have 300 cores to service those 500-600 simultaneous connections,

Re: [GENERAL] PostgreSQL hanging on new connections?

On Nov 29, 2010, at 12:57 PM, Vick Khera wrote: > On Mon, Nov 29, 2010 at 1:23 PM, Tom Lane wrote: >> hubert depesz lubaczewski writes: >>> straced postmaster when the problem was happening, and I was opening new >>> connections. strace looks like this: >>> [ backend hangs on semop immediately a

Re: [GENERAL] how can i bugfix "idle in transaction" lockups ?

On Nov 30, 2010, at 7:21 AM, Jonathan Vanasco wrote: > on a project, i find myself continually finding the database locked up with > "idle in transaction" connections > > are there any commands that will allow me to check exactly what was going on > in that transaction ? > > i couldn't find an

Re: [GENERAL] Tool for data modeling and ER diagram

On Dec 7, 2010, at 10:39 AM, Jaiswal Dhaval Sudhirkumar wrote: > Hi List, > > What is the best tool of data modeling and ER diagram for PostgreSQL. > http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools

Re: [GENERAL] Inconsistent time interval formatting

On Jan 13, 2011, at 9:34 AM, Allen Chen wrote: > Has anyone else out there noticed inconsistencies in how pgsql formats time > intervals over 1 day? > > For example, I have a query that returns a column of intervals and I get > output like this: > > 30:30:00 > 1 day 03:02:47 > 1 day 01:38:34 >

Re: [GENERAL] Inconsistent time interval formatting

On Jan 13, 2011, at 11:03 AM, Tom Lane wrote: > If you don't care about that, you can use justify_hours (I think that's > the right function) to smash them to the same thing. I use justify_hours, and I still get entries like '1 day 35:31:10' intermixed with the entires I'd expect like '2 days 03

Re: [GENERAL] Inconsistent time interval formatting

On Jan 13, 2011, at 1:15 PM, John R Pierce wrote: > On 01/13/11 1:08 PM, Ben Chobot wrote: >> On Jan 13, 2011, at 11:03 AM, Tom Lane wrote: >> >>> If you don't care about that, you can use justify_hours (I think that's >>> the right function

Re: [GENERAL] Postgresql as a dictionary coder backend?

On Jan 23, 2011, at 3:29 AM, Attila Nagy wrote: > Hello, > > I'm looking for a database backend for a dictionary coder project. It would > have three major tasks: > - take a text corpus, get their words and substitute each word by a 64 bit > integer (the word:integer is always constant) and sto

Re: [GENERAL] Hit by the out of memory killer last night

On Jan 31, 2011, at 7:55 AM, Bryan Murphy wrote: > Last night we were hit by the out of memory killer. Looking at the following > graph, you can clearly see unusual memory growth. This is a database server > running Postgres 9.0.0. [snip] > Any advice? What should I be looking for? Any pa

[GENERAL] multi-tenant vs. multi-cluster

We're considering using postgres as a way to host database services for many, many independent applications. One obvious way to do this is with schemas, roles, and proper permissions, but that still leaves open the possibility for some poorly written application to leave open transactions and af

Re: [GENERAL] multi-tenant vs. multi-cluster

On Mar 18, 2011, at 11:47 AM, Ivan Voras wrote: > On 18/03/2011 19:17, Ben Chobot wrote: > >> if we're talking an extra 50MB of memory per cluster, that will start to add >> up. > > Consider this: each such cluster will have: > > a) its own database files on

Re: [GENERAL] multi-tenant vs. multi-cluster

On Mar 18, 2011, at 12:34 PM, Nicholson, Brad (Toronto, ON, CA) wrote: >>> b) its own postgresql processes (many of them) running in memory >> >> I believe this is entirely a function of client connections. > > With a single instance, you can use connection pooling to reduce the overall > numb

Re: [GENERAL] Named advisory locks

On Apr 5, 2011, at 7:35 AM, rihad wrote: > No, what I meant was that we're already using ints for a different purpose in > another app on the same server, so I cannot safely reuse them. Aren't > advisory lock ID's unique across the whole server? The sole purpose of the > string ID is to be abl

Re: [GENERAL] How to store clickmap points?

On Jan 8, 2013, at 2:12 AM, aasat wrote: > Hi, > > I want to store clickmap points (X, Y and hits value) for website > > I currently have table like this > > CREATE TABLE clickmap ( > page_id integer, > date date, > x smallint, > y smallint, > hits integer > ) > > But this generated abou

Re: [GENERAL] Determine if an index is a B-tree, GIST, or something else?

On Jan 17, 2013, at 10:03 AM, Paul Jungwirth wrote: > Is there any way to determine, by querying pg_index and other pg_* tables, > whether an index was created as `USING something`? I've already got a big > query joining pg_class, pg_index, etc. to pull out various attributes about > the index

[GENERAL] bug, bad memory, or bad disk?

We have a Postgres server (PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit) which does streaming replication to some slaves, and has another set of slaves reading the wal archive for wal-based replication. We had a bit of fun yesterday w

[GENERAL] speeding up ALTER ... SET NOT NULL

I'm in an unfortunate position of needing to add a unique, not null index to a very large table with heavy churn. Without having much impact, I can add a NULL column that reads default values from a sequence for new rows, and then do batch updates over time to fill in the old values but then

Re: [GENERAL] Splitting Postgres into Separate Clusters?

On Mar 9, 2013, at 11:54 AM, Paul Jungwirth wrote: > Hello, > > I'm running a specialized search engine that indexes a few tens of millions > of web pages, keeping everything in Postgres, and one problem I'm starting to > see is poor cache hit rates. My database has two or three tables just for

Re: [GENERAL] Hosting PG on AWS in 2013

On Apr 6, 2013, at 6:51 PM, David Boreham wrote: > First I need to say that I'm asking this question on behalf of "a friend", > who asked me what I thought on the subject -- I host all the databases > important to me and my livelihood, on physical machines I own outright. That > said, I'm curi

Re: [GENERAL] Hosting PG on AWS in 2013

On Apr 8, 2013, at 2:15 AM, Vincent Veyron wrote: > Could someone explain to me the point of using an AWS instance in the > case of the OP, whose site is apparently very busy, versus renting a > bare metal server in a datacenter? Well, at least in my experience, you don't go to AWS because the da

Re: [GENERAL] Basic question on recovery and disk snapshotting

On Apr 27, 2013, at 10:40 AM, Yang Zhang wrote: > My question really boils down to: if we're interested in using COW > snapshotting (a common feature of modern filesystems and hosting > environments), would we necessarily need to ensure the data and > pg_xlog are on the same snapshotted volume? I

Re: [GENERAL] 2 postgresql server on the same station : conflict?

On May 28, 2013, at 2:54 AM, image wrote: > Hello, > > On the same station, i have 2 postgresql server: one for my postgis db > (v9.1) and so another installed with opener^7 (9.2). Unfortunalty, i noticed > i'm obliged to stop service for my postgresql postgis (9.1) in order to use > openerp7 (po

[GENERAL] async streaming and recovery_target_timeline=latest

We have an async streaming setup using 9.1.9 and 3 nodes - let's call them A, B, and C. A is the master, B and C are slaves. Today, A crashed, so we made B be the master and told C to follow along with the switch by changing the primary_conninfo in it's recovery.conf, making sure the history fil

Re: [GENERAL] async streaming and recovery_target_timeline=latest

Anybody? On Jul 3, 2013, at 3:23 PM, Ben Chobot wrote: > We have an async streaming setup using 9.1.9 and 3 nodes - let's call them A, > B, and C. A is the master, B and C are slaves. Today, A crashed, so we made B > be the master and told C to follow along with the switch b

Re: [GENERAL] async streaming and recovery_target_timeline=latest

On Jul 28, 2013, at 5:29 PM, Amit Langote wrote: > I think, the WAL recycling on standby names the recycled segments with > the latest timelineID (in this case it's 0x10) which creates WALs that > there shouldn't have been like 0010146A0001 instead of > 000F146A0001. This p

Re: [GENERAL] Google Cloud Platform, snapshots and WAL

> On Mar 20, 2017, at 6:31 AM, Moreno Andreo wrote: > > Hi everyone, > >I have my PostgreSQL 9.5 server running on a VM instance on Google Compute > Engine (Google Cloud Platform) on Debian Jessie (8.3), and I have another > dedicated VM instance that, every night at 3.00, takes a snapsho

Re: [GENERAL] Is there an md5sum for tables?

On Apr 1, 2008, at 8:56 PM, Michael Enke wrote: Hi all, I need to know if multiple tables have (may have most probably) identical content. Since I want a fast solution (which means not comparing tables row by row), I thought it would be a good idea to have an sql function operating on a t

Re: [GENERAL] How to modify ENUM datatypes?

On Apr 25, 2008, at 4:49 AM, Giorgio Valoti wrote: And reorder them, too. Why would you want to reorder an enum? It seems to me the point of them is to hold a small list of valid values. The order the list is described in surely should be irrelevant? -- Sent via pgsql-general mailing l

[GENERAL] Really out of memory?

I have a linux postgres server in the field. Its version is: PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-51) (aka postgresql-8.2.4-1PGDG) A few days ago, its log started showing this: May 31 02:59:40 sfmelwss postgres[30103]: [1-1] ERROR:

Re: [GENERAL] Really out of memory?

On Tue, 2 Jun 2009, Martijn van Oosterhout wrote: On Tue, Jun 02, 2009 at 11:10:04AM -0700, Ben Chobot wrote: May 31 02:59:40 sfmelwss postgres[30103]: [1-1] ERROR: out of memory May 31 02:59:40 sfmelwss postgres[30103]: [1-2] DETAIL: Failed on request of size 16777212. Add even more

Re: [GENERAL] Really out of memory?

On Tue, 2 Jun 2009, John R Pierce wrote: Ben Chobot wrote: May 31 02:59:40 sfmelwss postgres[30103]: [1-1] ERROR: out of memory May 31 02:59:40 sfmelwss postgres[30103]: [1-2] DETAIL: Failed on request of size 16777212. Thats a 16MB request is that your work_mem size or something by

Re: [GENERAL] Really out of memory?

On Tue, 2 Jun 2009, Tom Lane wrote: It's possible you are running out of 32-bit address space in the backend process, but what seems more likely is that the per-process ulimit is unreasonably small. I only have 1GB in the machine, and another 1GB of swap, so running out of 32-bit address spac

Re: [GENERAL] Really out of memory?

On Tue, 2 Jun 2009, Tom Lane wrote: Is there a way to see what the limits are for a given pid? I don't see anything obviously relevant in /proc// You don't have /proc//limits ? Nope. I'd like to believe I would consider that "obviously relevant." :) This server is running 2.6.20-1.2962.

Re: [GENERAL] Really out of memory?

On Tue, 2 Jun 2009, Martijn van Oosterhout wrote: It's got nothing to do with how much swap is in use. It's preventing you from allocating memory that *hypothetically* might not be available if every byte of allocated memory were actually used. For example, on my desktop I have 1GB of RAM of wh

Re: [GENERAL] Server Backup: pg_dump vs pg_dumpall

APseudoUtopia wrote: I'm writing a backup script. Right now, I only have one database on my postgresql server. I'm deciding if I should use pg_dump or pg_dumpall when backing up the server. As far as I can tell, pg_dumpall cannot compress the dumps automatically and it only dumps data in the s

Re: [GENERAL] Postgresql Hardware

How much reading? Writing? Concurrent transactions? How much data will you have? These are some of the things you need to provide to get a reasonable answer. Psicopunk wrote: Hi, We are developing a web application that will work on Postgresql. My doubt is about the hardware that I can use fo

Re: [GENERAL] Multiple counts on criteria - Approach to a problem

Neil Saunders wrote: Hi all, I maintain an online property rental application. The main focus of the UI is the search engine, which I'd now like to improve by allowing filtering of the search results shown on some criteria, but provide a count of the number of properties that meet that criter

Re: [GENERAL] generic modelling of data models; enforcing constraints dynamically...

InterRob wrote: Dear List, I am trying to implement the following: [snip] All suggestions are very much appreciated, regards, Rob It's not clear to me what you're asking, but I suspect the suggestion you need is the same as if you had asked how to best implement an Entity-Attribute-Val

Re: [GENERAL] generic modelling of data models; enforcing constraints dynamically...

Rob Marjot wrote: Thank you, Ben. Well, I'm afraid you got the basic idea... I intend to implement a hybrid between a fixed schema and an Entity-Attribute-Value scheme. The schema will be able to cover 90% of the data needs; in other cases (specific projects) additional fields (and/or tables/r

[GENERAL] 9.1.9 -> 9.1.10 causing corruption

I've got a bunch of independent database clusters, each with a couple of streaming replication slaves. I'm starting to upgrade them to 9.1.10, but on 3 of the 3 I've tried so far, this has somehow resulted in data corruption. I'm hoping it was the upgrade itself that caused the corruption, inste

Re: [GENERAL] 9.1.9 -> 9.1.10 causing corruption

I'll have drained the clients from it anyway, so that's not a big deal for a temporary thing. On Nov 1, 2013, at 1:44 PM, Ben Chobot wrote: > I've got a bunch of independent database clusters, each with a couple of > streaming replication slaves. I'm starting to upgrade

Re: [GENERAL] 9.1.9 -> 9.1.10 causing corruption

For posterity, it appears my issues were https://wiki.postgresql.org/wiki/Nov2013ReplicationIssue. On Nov 4, 2013, at 3:48 PM, Ben Chobot wrote: > Anybody? I've tried this again on another streaming replication server, and > again had pg_toast errors until I re-basebackup'd

Re: [GENERAL] PG replication across DataCenters

On Dec 9, 2013, at 8:09 AM, Thomas Harold wrote: > On 11/22/2013 5:57 AM, Albe Laurenz wrote: >> Kaushal Shriyan wrote: >>> I have read on the web that Postgresql DB supports replication >>> across data centers. Any real life usecase examples if it has been >>> implemented by anyone. >> >> Well,

Re: [GENERAL] Optimizing tables for known queries?

On Feb 9, 2014, at 2:48 PM, John Anderson wrote: > What I'm wondering is if there is a more denormalized view of this type of > data that would make those of types of queries quicker? That sounds like a materialized view?

Re: [GENERAL] SSD Drives

On Apr 3, 2014, at 12:47 PM, John R Pierce wrote: > On 4/3/2014 9:26 AM, Joe Van Dyk wrote: >> Related, anyone have any thoughts on using postgresql on Amazon's EC2 SSDs? >> Been looking at >> http://aws.amazon.com/about-aws/whats-new/2013/12/19/announcing-the-next-generation-of-amazon-ec2-hi

[GENERAL] understanding pg_locks

We recently had an issue where a misbehaving application was running a long transaction that modified a bunch of rows, and this was holding up other transactions that wanted to do similar modifications. No surprising there. But what I'm unclear of is how this was showing up in pg_locks. The bloc

Re: [GENERAL] understanding pg_locks

On May 21, 2011, at 8:53 AM, Tom Lane wrote: > Ben Chobot writes: >> We recently had an issue where a misbehaving application was running a long >> transaction that modified a bunch of rows, and this was holding up other >> transactions that wanted to do similar modific

[GENERAL] temp files getting me down

I'm running 9.0.3, and recently started getting temp files being created. This is a problem because it's making a bunch of dirty buffers that have to be flushed to disk and my poor little disk isn't up to the task. I'm not sure why though, because this is the explain verbose for the queries that

Re: [GENERAL] temp files getting me down

On May 25, 2011, at 9:31 AM, Ben Chobot wrote: > I'm running 9.0.3, and recently started getting temp files being created. > This is a problem because it's making a bunch of dirty buffers that have to > be flushed to disk and my poor little disk isn't up to the task. I&

Re: [GENERAL] temp files getting me down

On May 25, 2011, at 2:57 PM, Merlin Moncure wrote: > On Wed, May 25, 2011 at 2:14 PM, Ben Chobot wrote: >> On May 25, 2011, at 9:31 AM, Ben Chobot wrote: >> >>> I'm running 9.0.3, and recently started getting temp files being created. >>> This is a proble

Re: [GENERAL] temp files getting me down

On May 25, 2011, at 7:36 PM, Merlin Moncure wrote: > On Wed, May 25, 2011 at 6:44 PM, Ben Chobot wrote: >> >> >> Well, the query itself was calling a plpgsql function, and the function >> itself was doing: >> >> DECLARE >>row

Re: [GENERAL] Need suggestion

On Jun 1, 2011, at 1:08 AM, Carl von Clausewitz wrote: > Hello Everyone, > > I got a new project, with 100 user in Europe. In this case, I need to handle > production and sales processes an its documentations in PostgreSQL with PHP. > The load of the sales process is negligible, but every user

Re: [GENERAL] Detecting memory leaks with libpq?

On Jul 19, 2011, at 6:28 AM, Craig Ringer wrote: > Note that some "leaks" that are reported are _normal_ in most software. There > is absolutely no harm in not free()ing a structure that's allocated only once > during init and never messed with afterwards. The OS clears the memory > anyway, so

Re: [GENERAL] variant column type

On Jul 26, 2011, at 10:02 AM, salah jubeh wrote: > > Hello, > > suppose the following scenario > > the car speed is 240 > the car has an airbag > > Here the first value is integer and the second value is boolean. Consider > that I have this table structure > > feature (feature id feature n

Re: [GENERAL] Syncing Data to Production DB Server

On Aug 18, 2011, at 5:36 AM, Adarsh Sharma wrote: > Dear All, > > I want some views on the below requirements : > > 1. I have a Postgres DB server with 25 GB database. It has more than 110 > tables. > I am using Postgresql 8.3 on a CentOs. > 2. I have another system laptop that contains the s

[GENERAL] regression between 8.4.8 and 8.4.2?

We recently took a copy of our production data (running on 8.4.2), scrubbed many data fields, and then loaded it onto a qa server (running 8.4.8). We're seeing some odd planner performance that I think might be a bug, though I'm hoping it's just idiocy on my part. I've analyzed things and looked

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On Aug 31, 2011, at 10:47 AM, Tom Lane wrote: > Peter Eisentraut writes: >> I don't have an answer for you, but this report looks suspiciously >> similar to the one I posted the other day at >> , >> which, now that I think about i

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On Aug 31, 2011, at 11:10 AM, Tom Lane wrote: > Ben Chobot writes: >> Tom, if there's anything else we can provide that might you out, let me know. > > If you could extract a self-contained test case for the bad estimation, > that would be useful. OK, we'll pul

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On Aug 31, 2011, at 11:53 AM, Ben Chobot wrote: > On Aug 31, 2011, at 11:10 AM, Tom Lane wrote: > >> Ben Chobot writes: >>> Tom, if there's anything else we can provide that might you out, let me >>> know. >> >> If you could extract a self-cont

Re: [GENERAL] Materialized views in Oracle

On Sep 21, 2011, at 1:17 PM, Mike Christensen wrote: > So I used to think materialized views in Postgres would be an awesome > feature. That is until I had to endure the hell hole which is Oracle's > implementation.. what a complete joke.. did MS SQL's indexed views do any > better? Hopeful

Re: [GENERAL] Quick Date/Time Index Question

On Sep 22, 2011, at 2:13 PM, David Johnston wrote: > Hey, > > On 9.0.4 > > I have a database field that stores a timestamp to second+ precision; > however, I want to search against it only to day precision. If I leave the > field in second precision and try to “WHERE field BETWEEN date0 AND

Re: [GENERAL] Any was to prevent a join if no columns are selected from a view?

On Sep 29, 2011, at 4:57 PM, Jason Long wrote: > I thought I had read somewhere that Postges could ignore a join if it > was not necessary because there were no columns from the table or view > selected in the query. Is this possible? This sounds like incorrect logic to me, so I would be surpris

Re: [GENERAL] why jasperserver has been changed from MySQL to PostGreSQL

On May 22, 2012, at 7:31 AM, farhad koohbor wrote: > My question is that why jasperserver changed its mind to PostGreSQL. Which of > the features of PostGreSQL are powerful than MySQL? > Could you please give me a clue? Postgres is more SQL-compliant and tends to work better at larger scale than

Re: [GENERAL] evaluating subselect for each row

On Jun 3, 2012, at 10:55 AM, Scott Ribe wrote: > As part of anonymizing some data, I want to do something like: > > update foo set bar = (select bar2 from fakes order by random() limit 1); it may or may not be an option, but "update foo set bar=md5(bar)" is a pretty simple way to redact data. -

Re: [GENERAL] Simple method to format a string?

On Jun 20, 2012, at 7:43 AM, Emi Lu wrote: > Good morning, > > Is there a simply method in psql to format a string? > > For example, adding a space to every three consecutive letters: > > abcdefgh -> *** *** *** > > Thanks a lot! > Emi I'm unaware of such a function (it seems like a generic f

Re: [GENERAL] Promotion of standby to master

On Jun 29, 2012, at 12:16 PM, Andy Chambers wrote: > I understand that it's possible to promote a "hot standby" pg server > simply by creating the failover file. In a scenario where there are > multiple standby servers, is it possible to point the other standby > servers to the new master without

[GENERAL] can we avoid pg_basebackup on planned switches?

We make heavy use of streaming replication on PG 9.1 and it's been great for us. We do have one issue with it, though, and that's when we switch master nodes - currently, the documentation says that you must run pg_basebackup on your old master to turn it into a slave. That makes sense when the

Re: [GENERAL] can we avoid pg_basebackup on planned switches?

Anybody? On Jul 27, 2012, at 10:00 AM, Ben Chobot wrote: > We make heavy use of streaming replication on PG 9.1 and it's been great for > us. We do have one issue with it, though, and that's when we switch master > nodes - currently, the documentation says that you must r

Re: [GENERAL] can we avoid pg_basebackup on planned switches?

On Aug 5, 2012, at 11:12 AM, Fujii Masao wrote: > On Sat, Jul 28, 2012 at 2:00 AM, Ben Chobot wrote: >> We make heavy use of streaming replication on PG 9.1 and it's been great for >> us. We do have one issue with it, though, and that's when we switch master

Re: [GENERAL] maximum number of databases and / or schemas in a single database instance

On Aug 4, 2012, at 12:24 PM, Menelaos PerdikeasSemantix wrote: > The following page: > > http://www.postgresql.org/about/ > > mentions some limits but not the following: > > [1] maximum number of databases per database server instance > [2] maximum number of schemas per database > > Is there e

Re: [GENERAL] can we avoid pg_basebackup on planned switches?

On Aug 7, 2012, at 9:32 AM, Fujii Masao wrote: > On Mon, Aug 6, 2012 at 3:29 AM, Ben Chobot wrote: >> >> Oh, I would have though that doing a clean shutdown of the old master (step >> 1) would have made sure that all the unstreamed wal records would be flushed >>

[GENERAL] bgwriter and pg_locks

In an attempt to get a hackfix for http://pgfoundry.org/tracker/index.php?func=detail&aid=1011203&group_id=1000411&atid=1376, I'm wonder if it's true that, when looking at pg_locks, the only pid which will have virtualxid = '1/1' and virtualtransaction = '-1/0' will be the bgwriter. That seems

[GENERAL] surprising behavior or nothing to see here?

Today we saw a couple behaviors in postgres we weren't expecting, and I'm not sure if there's something odd going on, or this is all business as usual and we never noticed before. In steady-state, we have a 32-core box with a fair amount of ram acting as a job queue. It's constantly busy insert

Re: [GENERAL] surprising behavior or nothing to see here?

On Oct 3, 2012, at 11:50 AM, Tom Lane wrote: > Ben Chobot writes: >> 4. What might cause autovacuum analyze to make an index perform worse >> immediately, when a manual vacuum analyze does not have the same affect? And >> I'm not talking about changing things so t

Re: [GENERAL] Trajectory of a [Pg] DBA

On Oct 4, 2012, at 1:44 PM, Thalis Kalfigkopoulos wrote: > Hi all. > > I'd like to tap into the list's experience regarding the job of a DBA > in general and Pg DBA in particular. > > I see that most of the DBA job posts ask for Sr or Ssr which is > understandable given that databases are among

Re: [GENERAL] Streaming replication failed to start scenarios

On Oct 22, 2012, at 6:57 AM, chinnaobi wrote: > Hi Laurenz Albe, > > I have tested using cygwin rsync in windows 2008 R2, just after restart the > server. > > for 10 GB it took nearly 5 minutes to sync, > for 50 GB it took nearly 30 minutes, -- too long Though there were no big > changes. > >

Re: [GENERAL] PSA: XFS and Linux Cache Poisoning

On Nov 12, 2012, at 7:37 AM, Shaun Thomas wrote: > Hey everyone, > > We recently got bit by this, and I wanted to make sure it was known to the > general community. > > In new(er) Linux kernels, including late versions of the 2.6 tree, XFS has > introduced dynamic speculative preallocation. Wh

Re: [GENERAL] large database

On Dec 11, 2012, at 2:25 AM, Chris Angelico wrote: > On Tue, Dec 11, 2012 at 7:26 AM, Mihai Popa wrote: >> Second, where should I deploy it? The cloud or a dedicated box? > > Forget cloud. For similar money, you can get dedicated hosting with > much more reliable performance. We've been looking

Re: [GENERAL] How to read the execution Plan

On Apr 22, 2010, at 5:43 AM, akp geek wrote: > Hi all - > > I would request, If any one has document on how to read and interpret > the postgres execution plan, can you please share it? http://wiki.postgresql.org/wiki/Using_EXPLAIN -- Sent via pgsql-general mailing list (pgsql-general@

[SPAM] Re: [GENERAL] Best way to replicate to large number of nodes

On Apr 21, 2010, at 1:41 PM, Brian Peschel wrote: > I have a replication problem I am hoping someone has come across before and > can provide a few ideas. > > I am looking at a configuration of on 'writable' node and anywhere from 10 to > 300 'read-only' nodes. Almost all of these nodes will b

[GENERAL] how to invalidate a stored procedure's plan?

I have a procedure that queries a table. This should be fast because of an index, but some index bloat has caused the index to become expensive, and so the procedure has cached a plan that uses a full table scan. I've since fixed the index bloat, but the procedure still seems to be doing full ta

Re: [GENERAL] how to invalidate a stored procedure's plan?

On Apr 23, 2010, at 6:00 PM, Tom Lane wrote: > Ben Chobot writes: >> I have a procedure that queries a table. This should be fast because of an >> index, but some index bloat has caused the index to become expensive, and so >> the procedure has cached a plan that uses a

Re: [GENERAL] Count actual transaction per minute?

On May 12, 2010, at 9:12 AM, Melvin Davidson wrote: > Can anyone tell me how to measure _actual_ transactions per minute on a > PostgreSQL server. I am not talking about using pgbench, as I am not > interested in determining what is possible, but rather the actual count of > queries / transacti

Re: [GENERAL] getting the last N tuples of a query

On Jul 8, 2010, at 4:17 PM, Edmundo Robles L. wrote: > Hi! > > if a want the first 5,10,N tuples of a query (even without order) > i just have to do a: > select * from table limit 10; That does not get the first 10 tuples, it merely gets 10 tuples. The database is free to return whiche

Re: [GENERAL] Efficient Way to Merge Two Large Tables

On Jul 13, 2010, at 1:46 PM, Joshua Rubin wrote: > Hi, > > I have two tables each with nearly 300M rows. There is a 1:1 > relationship between the two tables and they are almost always joined > together in queries. The first table has many columns, the second has > a foreign key to the primary k

[GENERAL] text vs. varchar

Is there any difference between "text" and "varchar" data types? (Not varchar(n), just varchar.) I can't see a different from the manual page, but I'm wondering about index usage or something similarly subtle. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] text vs. varchar

On Jul 21, 2010, at 9:05 AM, Thom Brown wrote: > On 21 July 2010 16:58, Ben Chobot wrote: >> Is there any difference between "text" and "varchar" data types? (Not >> varchar(n), just varchar.) I can't see a different from the manual page, but >>

Re: [GENERAL] Getting statistics for each sql statement?

On Jul 22, 2010, at 4:50 AM, Stefan-Michael Guenther wrote: > Hello, > > is it possible to get statistics on the usage of different sql statements, > e.g. how many INSERT or UPDATE statements per day? > > log_statement_stats doesn't seem to be the right parameter or I haven't found > the outpu

Re: [GENERAL] Integral PG DB replication

On Jul 26, 2010, at 9:09 AM, Gauthier, Dave wrote: > Hi: > > Will DB replication be integral in v9? If so, when (approx) will that be out? > > I have a need for this functionality to replicate a read-only copy of a DB > where the master and slave are 2 time zones away. Estimating DML traffi

[GENERAL] detecting recurring appointment conflicts

I'd like to store recurring appointments in my database, and be pretty accepting in the scheduling of those appointments. For instance, I want to accept both "every other Tuesday starting 2008-11-04" as well as "every 3rd October 13th starting 2009." Storing those appointments isn't that ha

[GENERAL] Can update triggers detect the column in the update statement?

Say I have: create t (c1 int not null, c2 int); Is it possible to create an update trigger on t such updates will only be allowed if the update statement explicitly sets c1, even if the new value is the same? (I'm trying to write a change log system where the users are application users

Re: [GENERAL] Can update triggers detect the column in the update statement?

On Oct 6, 2008, at 5:53 PM, Tom Lane wrote: Ben Chobot <[EMAIL PROTECTED]> writes: Say I have: create t (c1 int not null, c2 int); Is it possible to create an update trigger on t such updates will only be allowed if the update statement explicitly sets c1, even if the new value

Re: [GENERAL] Need schema design advice

On Oct 12, 2008, at 5:51 PM, Martin Gainty wrote: could you provide a brief explanation of EAV ? Instead of: create table vehicles ( kind text primary key, wheels int ); insert into vehicles (kind, wheels) values ('car',4); insert into vehicles (kind, wheels) values ('bike',2

Re: [GENERAL] How to get schema name which violates fk constraint

On Oct 22, 2008, at 6:50 AM, Tom Lane wrote: In the second place, the reason most of our messages don't already contain schema names is that in the past we've judged it would be mostly clutter; and given the infrequency of complaints I see no reason to change that opinion. Well, FWIW, I also

Re: [GENERAL] Incremental Backups in postgres

Saving off the transaction log WAL files is a good way to do this. Read this part of the manual: http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html ...and see if that answers your questions. On Nov 9, 2009, at 6:48 PM, akp geek wrote: Dear all - Is t

  1   2   >