Re: [GENERAL] Waiting on ExclusiveLock on extension

On Fri, Apr 17, 2015 at 1:14 AM, Andres Freund wrote: > On 2015-04-16 14:23:25 -0700, Qingqing Zhou wrote: > > On Thu, Apr 16, 2015 at 1:24 PM, Andomar wrote: > > > > b) How can you find the name of the relation being extended? based on > the > > > relation number. > > select ::regclass; > > Tha

Re: [GENERAL] Running pg_upgrade under Debian

On Sun, Apr 19, 2015 at 9:34 AM, rob stone wrote: > > > > On Fri, 2015-04-17 at 16:16 -0700, Adrian Klaver wrote: > > On 04/17/2015 03:09 PM, rob stone wrote: > > > Hello, > > > > > > I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering > this > > > error:- > > > > > > > > > postgr

Re: [GENERAL] plpgsql functions organisation

On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver wrote: > On 05/02/2015 10:12 AM, Melvin Davidson wrote: > >> AFAIK, you cannot "package" functions in PostgreSQL, but it is possible >> to >> call a function from within a function. >> >> That being said, I would seriously look at how and why you are

Re: [GENERAL] finding tables about to be vacuum freezed

On Tue, May 5, 2015 at 6:40 PM, Steve Kehlet wrote: > Hello, recently one of my tables needed a vacuum (to prevent wraparound) > and of course it happened at a really bad time, so since then I've been > learning about how Transaction ID Wraparound works and its associated > parameters. > > I'm tr

Re: [GENERAL] finding tables about to be vacuum freezed

On Wed, May 6, 2015 at 10:51 AM, Steve Kehlet wrote: > On Wed, May 6, 2015 at 9:46 AM Jeff Janes wrote: > >> vacuum_freeze_table_age controls when it promotes a vacuum *which is >> already going to occur* so that it scans the whole table. It doesn't >> special

Re: [GENERAL] WAL Streaming Failure PostgreSQL 9.4

On May 28, 2015 9:58 AM, "Ivann Ruiz" wrote: > When I execute pg_ctl start on my standby I get the following > > LOG: database system was interrupted; last known up at 2015-05-27 14:16:41 EDT > LOG: entering standby mode > LOG: restored log file "00010028" from archive > LOG:

Re: [GENERAL] Thousands of schemas and ANALYZE goes out of memory

On Wed, May 13, 2015 at 3:10 PM, Tom Lane wrote: > Bruce Momjian writes: > > Is there a reason the following patch wasn't applied? > > I don't think anybody ever did the legwork to verify it was a good idea. > In particular, it'd be good to check if sending a tabstat message for each > table add

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On Wed, Jun 10, 2015 at 7:16 PM, Noah Misch wrote: > On Mon, Jun 08, 2015 at 03:15:04PM +0200, Andres Freund wrote: > > One more thing: > > Our testing infrastructure sucks. Without writing C code it's basically > > impossible to test wraparounds and such. Even if not particularly useful > > for

Re: [GENERAL] Is there any way to measure disk activity for each query?

On Thu, Jun 18, 2015 at 3:05 PM, Oleg Serov wrote: > Hello! > > I'm wondering, if there any way to measure how much disk-io were generated > by a query? > For an individual query execution, you can explain it with explain (analyze, buffers) select . It will report on the pages hit in the b

Re: [GENERAL] How to speed up pg_trgm / gin index scan

On Mon, Jun 22, 2015 at 4:51 AM, Christian Ramseyer wrote: > Hi > > I have a pretty large table with syslog messages. > > It is already partitioned by month, and for a single month I have e.g. > > > DM=# \d+ logs_01 > > Column|Type | > --+--

Re: [GENERAL] How to speed up pg_trgm / gin index scan

On Mon, Jun 22, 2015 at 10:39 AM, Christian Ramseyer wrote: > On 22/06/15 19:00, Jeff Janes wrote: > > > > > > > A typical query on this table looks like this: > > > > explain analyze > > select log_date, host, msg > > from logs

Re: [GENERAL] create index on a field of udt

On Sun, Jun 28, 2015 at 10:31 PM, Shujie Shang wrote: > Oh, I didn't explain my question well, actually I want to create an index > on an udt in a table. > > e.g. > create type info as (id int, name text); > creat table test (i info); > I want to run: > create index myindex on test (i.id) > > > I

Re: [GENERAL] Turn off streaming replication - leaving Master running

On Sun, Jun 28, 2015 at 11:34 PM, Andy Erskine wrote: > no snapshot available .. i don't mind running basebackup once i've > finished my test. > > So if someone could help with the steps to turn off replication and bring > secondary up as a standalone db that would be great > thanks. > If people

Re: [GENERAL] Which replication is the best for our case ?

On Mon, Jun 29, 2015 at 6:02 AM, ben.play wrote: > Hi guys, > > We have a PG database with more than 400 GB of data. > At this moment, a cron runs each ten minutes and updates about 10 000 lines > with complex algorithms in PHP. > > Each time the cron runs, the website is almost down because some

Re: [GENERAL] Feature request: fsync and commit_delay options per database

2015-06-29 15:18 GMT-07:00 Bráulio Bhavamitra : > Hello all, > > After reading > http://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing > I've tried to use commit_delay to make commits really slow on a test > environment. Unfortunetely, the maximum value is 100ms (100_000

Re: [GENERAL] How to test SSL cert from CA?

On Thu, Jul 9, 2015 at 9:29 PM, Francisco Reyes wrote: > On 07/09/2015 03:07 PM, Vick Khera wrote: > >> >> On Wed, Jul 8, 2015 at 10:17 PM, Francisco Reyes > > wrote: >> >> openssl s_client -connect HOST:PORT -CAfile /path/to/CA.pem >> > > According to this post: > http:

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

On Mon, Jul 20, 2015 at 7:01 AM, Geoff Winkless wrote: > On 20 July 2015 at 14:33, Rafal Pietrak wrote: > >> If I'm not mistaken, the conclusions from posts in this thread are: >> >> 3. there are methods (like cryptographic "random" sequence), which >> guarantee no conflicts. So one should resor

Re: [GENERAL] Q: text query search and

On Thu, Jul 23, 2015 at 2:55 AM, amihay gonen wrote: > Hi I'm trying to implement a text search in PG . > > My goal to enable the user search on several columns also on partial words. > > here is sample code : > create table test_test( text_data tsvector, text_a varchar,text_b varchar); > > inse

Re: [GENERAL] Using the database to validate data

On Thu, Jul 23, 2015 at 5:55 AM, JPLapham wrote: > Hello, > > I have an application that occasionally performs large batch inserts of > user > hand-generated data. Input is a tab delimited file with typically hundreds > to a thousand lines of data. > > Because the data is generated by hand, there

Re: [GENERAL] Using the database to validate data

On Fri, Jul 24, 2015 at 5:17 AM, Zdeněk Bělehrádek < zdenek.belehra...@superhosting.cz> wrote: > What about creating a SAVEPOINT before each INSERT, and if the INSERT > returns > an error, then ROLLBACK TO SAVEPOINT? Make sure you release the savepoint if there was no error. Otherwise you will

Re: [GENERAL] pgbench tps drop from 5000 to 37 going from localhost to a server 13ms away

On Fri, Jul 24, 2015 at 12:13 PM, Chris Withers wrote: > On 24/07/2015 19:21, Jan Lentfer wrote: > >> >> I've been doing some lightweight load testing with “pgbench -c8 -j8 -T10” When run locally on the postgres server I've testing, this gives around 5000tps When I

Re: [GENERAL] pgbench tps drop from 5000 to 37 going from localhost to a server 13ms away

On Mon, Jul 27, 2015 at 3:19 AM, Chris Withers wrote: > On 24/07/2015 22:51, Jeff Janes wrote: > > starting vacuum...end. > >> transaction type: TPC-B (sort of) >> scaling factor: 1 >> > > This is your problem. There is only one row in the pgbench_branch &

[GENERAL] Extension to rewrite queries before execution

I am looking for an extension or a technique that will allow me to intercept a query by the exact query text, and replace that query with a different one. The context is running a third-party app which issues queries I have no control over. I'd like to intercept a specific query (which has no bin

Re: [GENERAL] Extension to rewrite queries before execution

> On Thu, Aug 13, 2015 at 1:37 PM, Melvin Davidson wrote: > On Thu, Aug 13, 2015 at 3:49 PM, Jeff Janes wrote: > >> I am looking for an extension or a technique that will allow me to >> intercept a query by the exact query text, and replace that query with a >&g

Re: [GENERAL] Extension to rewrite queries before execution

On Thu, Aug 13, 2015 at 2:02 PM, Adrian Klaver wrote: > On 08/13/2015 12:49 PM, Jeff Janes wrote: > >> I am looking for an extension or a technique that will allow me to >> intercept a query by the exact query text, and replace that query with a >> different one. >>

Re: [GENERAL] CentOS - PostgreSQL 9.2.13 -> 9.4

On Tue, Aug 18, 2015 at 8:01 AM, Michael H wrote: > Hi, > > I've been tuning our new database server, here's some info... > > CentOS Linux release 7.1.1503 (Core) > 3.10.0-229.11.1.el7.x86_64 > > 8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total > 2 x AMD Opteron 6386SE 2.8GHz/16-core

[GENERAL] Dangers of mislabelled immutable functions

I want to index the textual representations of a table's rows. You can cast a row to text by using the name of the table where you would usually use the name of a column, like "table_name::text". But this is not immutable and so can't be used in an expression index. I can easily make a pl/perl f

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 10:00 AM, Steve Kehlet wrote: > This is Postgres 9.4.4. I am troubleshooting some occasional (every 3-4 > hours) slowness with UPDATEs on a table that has a GIN index on a JSONB > column. During these episodes, UPDATEs that normally take < 1sec take > upwards of 2-4 minute

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 10:11 AM, Tom Lane wrote: > Steve Kehlet writes: > > This is Postgres 9.4.4. I am troubleshooting some occasional (every 3-4 > > hours) slowness with UPDATEs on a table that has a GIN index on a JSONB > > column. During these episodes, UPDATEs that normally take < 1sec ta

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 11:06 AM, Steve Kehlet wrote: > On Fri, Aug 28, 2015 at 10:42 AM Jeff Janes wrote: > > > >> Vacuum is overkill (and can be extremely slow to run a large gin index), >> you just need to get it to autoanalyze by changing

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 11:06 AM, Steve Kehlet wrote: > On Fri, Aug 28, 2015 at 10:42 AM Jeff Janes wrote: > >> > Vacuum is overkill (and can be extremely slow to run a large gin index), >> you just need to get it to autoanalyze by changing the

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 12:25 PM, Steve Kehlet wrote: > On Fri, Aug 28, 2015 at 12:10 PM Jeff Janes wrote: > >> Did you change the system-wide autovacuum_analyze_scale_factor? If so, >> don't do that. You can use a table's storage parameters to set a custom >&g

Re: [GENERAL] GIN Trigram Index Size

On Wed, Sep 9, 2015 at 2:54 PM, Christian Ramseyer wrote: > I have read some discussions about pending list bloat issues, but there > it was suggested that vacuuming the table should reclaim the space, and > this does not seem to the case. The only way I found to reduce the size > is by doing a

Re: [GENERAL] Forced external sort?

On Fri, Sep 11, 2015 at 11:45 AM, wrote: > I've got a poorly indexed query and was attempting a quick work around in > production by increasing work_mem when it was called. EXPLAIN ANALYZE is > telling me this: > > Sort Method: external sort Disk: 1253824kB > > > So I set the work_mem to 2gb, st

Re: [GENERAL] Broken primary key after backup restore.

On Fri, Sep 18, 2015 at 6:16 AM, Adrian Klaver wrote: > On 09/17/2015 11:15 PM, Guillaume Lelarge wrote: > >> Le 18 sept. 2015 5:23 AM, "Adrian Klaver" > > a écrit : >> > >> > On 09/17/2015 05:37 PM, Michael Chau wrote: >> >> >> >> To restore on test server:

Re: [GENERAL] Queuing query

On Mon, Sep 21, 2015 at 3:51 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > While awaiting the awesomeness of the upcoming "skip locked" feature in > 9.5 I need to handle a work queue. > > Does anyone see any glaring issues or subtle nuances with the basic method > below which combi

Re: [GENERAL] pgcrypto

On Wed, Sep 23, 2015 at 8:46 AM, Ramesh T wrote: > Hi, > i created extension pgcrypto on public with postgres user.But > while trying to use from my own schma suppose qa. > > when i run digest in function in my qa > > CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$ > SEL

[GENERAL] Version management for extensions

I am facing a scenario where I have different version of an extension, say 1.0 and 2.0, which have some different functionality between them (so not merely a bug fix), so people might want to continue to use 1.0. But changes to the PostgreSQL software between major versions requires changes to the

Re: [GENERAL] Pattern match against array elements?

On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster wrote: > Is there any way to do a pattern match against the elements of an array in > postgresql (9.4 if the version makes a difference)? I have a grouped query > that, among other things, returns an array of values, like: > > SELECT lognum, array

Re: [GENERAL] Pattern match against array elements?

On Mon, Oct 12, 2015 at 11:39 AM, Tom Lane wrote: > Jeff Janes writes: > > On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster > > > wrote: > >> My first thought was to do something like this: > >> > >> SELECT * FROM (SELECT lognum,array_agg(fligh

Re: [GENERAL] Not storing MD5 hashed passwords

On Wed, Oct 14, 2015 at 1:41 PM, John R Pierce wrote: > On 10/14/2015 1:31 PM, Quiroga, Damian wrote: > > > > Does postgres support other (stronger) hashing algorithms than MD5 to > store the database passwords at disk? > > If not, is there any plan to move away from MD5? > > There are proposals

Re: [GENERAL] Not storing MD5 hashed passwords

On Wed, Oct 14, 2015 at 5:49 PM, Michael Paquier wrote: > On Thu, Oct 15, 2015 at 7:19 AM, Jeff Janes wrote: > > On Wed, Oct 14, 2015 at 1:41 PM, John R Pierce > wrote: > >> > >> On 10/14/2015 1:31 PM, Quiroga, Damian wrote: > >> > >> > >

Re: [GENERAL] Version management for extensions

On Fri, Oct 9, 2015 at 1:36 AM, Albe Laurenz wrote: > Jeff Janes wrote: > > I am facing a scenario where I have different version of an extension, > say 1.0 and 2.0, which have > > some different functionality between them (so not merely a bug fix), so > people might want t

Re: [GENERAL] temporary indexes?

On Wed, Oct 21, 2015 at 11:50 AM, Adrian Klaver wrote: > On 10/21/2015 11:43 AM, Jonathan Vanasco wrote: > >> I couldn't find any mention of this on the archives... >> >> Have the project maintainers ever considered extending CREATE INDEX to >> support "temporary" indexes like CREATE TEMPORARY TA

Re: [GENERAL] Importing CSV File

On Mon, Oct 26, 2015 at 2:45 PM, David Blomstrom wrote: > I tried to import a CSV file into a PostgreSQL table using pgAdmin III. I > got an error message: "extra data after last column." > > > All my spreadsheets have an "end of data" column that has /r/n in each > cell. When I import a CSV file

[GENERAL] Re: [GENERAL] 回复: postgres cpu 100% need help

On Mon, Oct 26, 2015 at 8:30 PM, 657985...@qq.com <657985...@qq.com> wrote: > Dear sir: > Recently a wired question about postgresql database really > bothered me a lot, so i really need your help. Here is the problem, in the > most situations the postgre database work very well, Average

[GENERAL] Re: Re: [GENERAL] 回复: postgres cpu 100% need help

On Wed, Oct 28, 2015 at 12:12 AM, 657985...@qq.com <657985...@qq.com> wrote: > Thank you for your reply. > tshow=> explain (analyze, buffers) select count(t.*) from > tshow.res_room_weight t,tshow.res_room_info r > tshow-> where t.subcatlg_id=46 > tshow-> and t.roomid = r.actorid > tshow->

Re: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5

On Wed, Oct 28, 2015 at 8:43 AM, Tom Dearman wrote: > We have a performance problem when our postgres is under high load. The CPU > usage is very low, we have 48 cores for our postgres and the idle time > averages at 90%. The problem is we get spikes in our transaction times > which don’t appear

Re: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5

On Mon, Nov 2, 2015 at 7:32 AM, Tom Dearman wrote: > Thanks for the prompt replies so far, I have done some more investigation to > be able to clearly answer some of the question. > > The original shared-buffers was 8G and I have done another run on Friday > using this old value instead of my more

Re: [GENERAL] Is there bigintarray?

On Mon, Nov 2, 2015 at 1:41 PM, Thomas Kellerer wrote: > Igor Bossenko schrieb am 02.11.2015 um 14:20: >> >> What is the current plans for bigintarray? >> >> Igor >> >> > > The following works for me: > > create table foo > ( > bia bigint[] > ); But you can't build indexes on them using

Re: [GENERAL] Lock contention in TransactionIdIsInProgress()

On Thu, Nov 5, 2015 at 10:38 AM, Julian v. Bock wrote: > After doing some debugging it seems that the backend processes are > spending most of their time in spinlocks in TransactionIdIsInProgress() > trying to get a lock on ProcArrayLock. This function is called more > often (when processing sear

Re: [GENERAL] swarm of processes in BIND state?

On Sat, May 28, 2016 at 11:32 AM, hubert depesz lubaczewski wrote: > On Sat, May 28, 2016 at 10:32:15AM -0700, Jeff Janes wrote: >> If that wasn't informative, I'd attach to one of the processes with >> the gdb debugger and get a backtrace. (You might want to do tha

Re: [GENERAL] Checkpoint Err on Startup of Rsynced System

On Tue, May 31, 2016 at 10:13 AM, Jim Longwill wrote: > I am trying to setup a 2nd, identical, db server (M2) for development and > I've run into a problem with starting up the 2nd Postgres installation. > > Here's what I've done: > 1) did a 'clone' of 1st (production) machine M1 (so both machin

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Fri, Jun 3, 2016 at 12:13 PM, Greg Navis wrote: > Thanks for answers and sorry for not searching hard enough. > > I'm curious ... would it be difficult to modify PostgreSQL so that it'd use > the index for `similarity(lhs, rhs) >= show_limit()` too? Yes, that would be very difficult. The proje

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Fri, Jun 3, 2016 at 1:02 PM, Tom Lane wrote: > Jeff Janes writes: >> On Fri, Jun 3, 2016 at 12:13 PM, Greg Navis wrote: >>> I'm curious ... would it be difficult to modify PostgreSQL so that it'd use >>> the index for `similarity(lhs, rhs) >= show_li

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Sat, Jun 4, 2016 at 2:50 AM, Greg Navis wrote: > Thanks for your replies. > > Sorry for confusion. Instead of `similarity(lhs, rhs) >= show_limit()`, > which of course is completely equivalent to `lhs % rhs`, I wanted to write > `similarity(lhs, rhs) >= my_custom_threshold`. It seems that the a

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Thu, Jun 9, 2016 at 1:57 AM, Greg Navis wrote: > Artur, no worries, I'm not writing any code ;-) > > I did the following: > > CREATE TYPE trgm_match AS (match TEXT, threshold NUMERIC); I would probably use REAL, not NUMERIC. But maybe there is good reason to use NUMERIC. > CREATE OR REPLACE

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Fri, Jun 10, 2016 at 9:20 AM, Jeff Janes wrote: > On Thu, Jun 9, 2016 at 1:57 AM, Greg Navis wrote: >> Artur, no worries, I'm not writing any code ;-) >> >> I did the following: >> >> CREATE TYPE trgm_match AS (match TEXT, threshold NUMERIC); > >

Re: [GENERAL] Question about RUM-index

On Wed, Jun 15, 2016 at 3:56 AM, Andreas Joseph Krogh wrote: > Hi. > > First; Is this the correct forum to ask questions about the Postgres Pro's > new RUM-index? > > If not, please point me to the right forum. > I think that https://github.com/postgrespro/rum/issues might be the best forum. >

Re: R: [GENERAL] Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 3:13 AM, Andreas Kretschmer wrote: > > > Am 20.06.2016 um 11:43 schrieb Job: >> >> Hi Andreas, >> >>> I would suggest run only autovacuum, and with time you will see a not >>> more growing table. There is no need for vacuum full. >> >> So new record, when will be pg_bulkloa

Re: R: [GENERAL] Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 7:23 AM, Martín Marqués wrote: > El 20/06/16 a las 09:50, Melvin Davidson escribió: >> >> >>>but it won't let it grow too (or am I missing something). >> >> Yes, you are missing something. By partioning and {Vacuum Full only the >> table with data no longer needed}, the res

Re: [GENERAL] What Causes Access Exclusive Lock?

On Thu, Jun 23, 2016 at 8:14 AM, Sameer Kumar wrote: > > Hi, > > I just wanted to understand what are the commands which will acquire Access > Exclusive Lock on a table? In my knowledge below operations will acquire > access exclusive lock:- > > 1. VACUUM FULL > 2. ALTER TABLE > 3. DROP TABLE > 4.

Re: [GENERAL] What Causes Access Exclusive Lock?

On Thu, Jun 23, 2016 at 10:54 AM, Sameer Kumar wrote: > > > On Fri, 24 Jun 2016, 1:47 a.m. Jeff Janes, wrote: >> >> On Thu, Jun 23, 2016 at 8:14 AM, Sameer Kumar >> wrote: >> > >> > Hi, >> > >> > I just wanted to understand what

Re: [GENERAL] Log archiving failing. Seems to be wrong timeline

On Thu, Jun 30, 2016 at 3:53 AM, Chris Lewis wrote: > Hello, > > We have 2 postgresql servers (v 9.4.2) master and slave in streaming > replication. The overall cluster is controlled using pacemaker & corosync > and the pgsql cluster agent which handles failover to, and promotion of, the > slave.

Re: [GENERAL] Tracking IO Queries

On Wed, Jul 13, 2016 at 3:31 PM, Patrick B wrote: > Hi all, > > I got some IO spikes on my master server. How is that diagnosed? Is it read or write or can't you tell? > But the point is that I was unable > to find the query that caused that, because the query didn't take more than > 300ms to r

Re: [GENERAL] Unix Postgres 9.5. using pg_basebackup and WAL files. Can't get a PITR recovery

On Fri, Jul 15, 2016 at 10:09 AM, Pruett, Jennis wrote: > I can't cut and paste my issues, so I will explain as best I can. > > > > I’m testing PITR on a single 9.5 db (no replication setups). > > > > I have all the settings that I can find, wal_level, archive_command, > > restore_command, directo

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 7:23 PM, Greg Sabino Mullane wrote: > Marc wrote: >> I donât have a 1TB database to try it on, mind you, so your >> âwait couple of daysâ might be *with* the âlink option? > > I think you mean *without*, but yeah, there is no way the --link > option is going to take that l

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce wrote: > On 7/27/2016 9:39 PM, Jeff Janes wrote: >> >> That depends on how how many objects there are consuming that 1 TB. >> With millions of small objects, you will have problems. Not as many >> in 9.5 as there were in

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 3:16 PM, Bruce Momjian wrote: > On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote: >> On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce wrote: >> > On 7/27/2016 9:39 PM, Jeff Janes wrote: >> >> >> >> That depends on how how

Re: [GENERAL] Force pg_hba.conf user with LDAP

On Mon, Aug 1, 2016 at 11:40 AM, Joseph Kregloh wrote: > Hi, > > Is there a way to force the user being sent to LDAP? > > For example I have the following entry in my pg_hba.conf file: > hostapdb apuser 10.0.20.1/22 ldap > ldapserver="389-ds1.sl.com:389" ldapbasedn=

Re: [GENERAL] Force pg_hba.conf user with LDAP

On Mon, Aug 1, 2016 at 1:32 PM, John McKown wrote: > On Mon, Aug 1, 2016 at 2:49 PM, Jeff Janes wrote: >> >> On Mon, Aug 1, 2016 at 11:40 AM, Joseph Kregloh >> wrote: >> > Hi, >> > >> > Is there a way to force the user being sent to LDAP? >&g

Re: [GENERAL] WAL directory size calculation

On Thu, Jul 28, 2016 at 6:33 AM, David G. Johnston wrote: > On Thu, Jul 28, 2016 at 9:25 AM, Moreno Andreo > wrote: >> >> I've read somewhere that the formula should be 16 MB * 3 * >> checkpoint_segment in size. > > [...] > >> >> Using the above formula I have: >> 16 MB * 3 * 1 GB >> that lea

Re: [GENERAL] WAL directory size calculation

On Thu, Jul 28, 2016 at 6:25 AM, Moreno Andreo wrote: > Hi folks! :-) > I'm about to bring up my brand new production server and I was wondering if > it's possible to calculate (approx.) the WAL directory size. > I have to choose what's better in terms of cost vs. performance (we are on > Google C

Re: [GENERAL] Should a DB vacuum use up a lot of space ?

On Mon, Aug 8, 2016 at 12:08 AM, Philippe Girolami wrote: >>Not understanding; 'the auto-vacuum daemon kicks in and burns through >>the transactions'. >>Are you saying it is reclaiming xids for you or using them? >>If reclaiming that is what is supposed to do and is good thing. >>Or am I misunder

Re: [GENERAL] Postgres Pain Points: 1 pg_hba conf

On Thu, Aug 11, 2016 at 10:04 AM, support-tiger wrote: > We have always been impressed with the Postgres project and team. The whole > hybrid SQL / JSONB functionality rocks. The scalability rocks. The speed > and stability rock. At the command line, Postgres rocks. But in > applications we ha

Re: [GENERAL] pgbasebackup is failing after truncate

On Wed, Aug 10, 2016 at 11:06 PM, Yelai, Ramkumar wrote: > HI > > At present, I have some requirement to truncate the data base to reclaim > disk space and at the same time I need to take basebackup. > > Seems, truncate is successfully reclaimed the space but pgbasebackup failed > and reported th

Re: [GENERAL] Critical failure of standby

On Thu, Aug 11, 2016 at 10:39 PM, James Sewell wrote: > Hello, > > We recently experienced a critical failure when failing to a DR > environment. > > This is in the following environment: > > >- 3 x PostgreSQL machines in Prod in a sync replication cluster >- 3 x PostgreSQL machines in DR

Re: [GENERAL] RowExclusiveLock timeout while autovacuum

On Sun, Aug 14, 2016 at 9:33 PM, zh1029 wrote: > Hi, > We are using PostgreSQL 9.3.11. We are observing DB update failed due to > lock timeout. failure because waiting for RowExclusiveLock. Autovacuum uses > plain vacuum which uses ShareUpdateExclusiveLock. right? > But from Postgres Manual cha

Re: [GENERAL] Critical failure of standby

On Mon, Aug 15, 2016 at 7:23 PM, James Sewell wrote: > Those are all good questions. > > Essentially this is a situation where DR is network separated from Prod - > so I would expect the archive command to fail. > archive_command or restore_command? I thought it was restore_command. > I'll

Re: [GENERAL] Why insertion throughput can be reduced with an increase of batch size?

On Sun, Aug 21, 2016 at 11:53 PM, Павел Филонов wrote: > My greetings to everybody! > > I recently faced with the observation which I can not explain. Why > insertion throughput can be reduced with an increase of batch size? > > Brief description of the experiment. > >- PostgreSQL 9.5.4 as se

Re: [GENERAL] LOG: could not fork new process for connection: Cannot allocate memory

On Sun, Aug 28, 2016 at 5:18 PM, Jim Nasby wrote: > On 8/25/16 7:45 PM, Ahsan Ali wrote: > > Please don't top-post; it's harder to read. > >> On Thu, Aug 25, 2016 at 5:29 PM, John R Pierce > > wrote: >> so there were 1818 postgres client processes at the time it co

Re: [GENERAL] Help with slow query - Pgsql 9.2

On Mon, Sep 5, 2016 at 6:53 PM, Patrick B wrote: > Hi guys, > > I got this query: > >> SELECT id,jobid,description,serialised_data >> FROM logtable >> WHERE log_type = 45 >> AND clientid = 24011 >> ORDER BY gtime desc > > What is really going to help you here is multicolumn index on (clientid, l

Re: [GENERAL] PostgreSQL Database performance

On Fri, Sep 2, 2016 at 8:38 PM, Pradeep wrote: > Dear Team, > > > > Could you please help me, after changing the below parameters in > PostgreSQL configuration file it was not reflecting in OS level and also > Database performance is degrading. > What were they before you changed them? Do you

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

On Wed, Sep 7, 2016 at 4:49 PM, Jim Nasby wrote: > On 9/7/16 6:10 PM, David Gibbons wrote: > >> That is NOT safe. The problem is it allows rsync to use mtime alone >> to decide that a file is in sync, and that will fail if Postgres >> writes to a file in the same second that the first

Re: [GENERAL] Question about locking and pg_locks

On Thu, Sep 8, 2016 at 4:30 AM, Moreno Andreo wrote: > Hi folks! :-) > > This morning I was woken up by a call of a coworker screaming "Help, our > Postgres server is throwing strange errors!" > Not the best way to start your day... > > OK, to the serious part. > > "Strange errors" were (in postg

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

On Sep 12, 2016 1:12 AM, "Scott Marlowe" wrote: > > > > Why not subscribe a new cluster on the same box with pg_basebackup? +1. Maybe he is afraid of (or doesn't know how to) configuring things to run on a non standard port, for testing? Cheers, Jeff

Re: [GENERAL] Predicting query runtime

On Mon, Sep 12, 2016 at 7:03 AM, Vinicius Segalin wrote: > Hi everyone, > > I'm trying to find a way to predict query runtime (I don't need to be > extremely precise). I've been reading some papers about it, and people are > using machine learning to do so. For the feature vector, they use what t

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

On Sat, Sep 10, 2016 at 7:09 PM, Jim Nasby wrote: > On 9/8/16 3:29 PM, David Gibbons wrote: > >> >> Isn't this heading in the wrong direction? We need to be more >> precise than 0 (since 0 is computed off of rounded/truncated time >> stamps), not less precise than 0. >> >> Cheer

Re: [GENERAL] Maximum number of exclusive locks

On Tue, Sep 13, 2016 at 6:21 AM, Tom Lane wrote: > "Daniel Verite" writes: > > Nothing to complain about, but why would the above formula > > underestimate the number of object locks actually available > > to a transaction? Isn't it supposed to be a hard cap for such > > locks? > > No, it's a mi

Re: [GENERAL] PostgreSQL GIN index not used when ts_query language is fetched from a column

On Thu, Sep 15, 2016 at 5:46 AM, Jaap Roes wrote: > I've got a table that stores some multilingual content: > > CREATE TABLE search ( > content text NOT NULL, > language regconfig NOT NULL, > fulltext tsvector > ); > CREATE INDEX search_fulltext ON search USING GIN(f

Re: [GENERAL] performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

On Fri, Sep 16, 2016 at 2:01 AM, Chris Withers wrote: > Hi All, > > I have quite a few tables that follow a pattern like this: > > Table "public.my_model" > Column | Type| Modifiers > +---+--- > period | tsrange | not null > k

Re: [GENERAL] Index scan is not working

On Mon, Sep 19, 2016 at 5:10 AM, Kiran wrote: > Dear All, > > I have a table called question which has a ts_vector column *weighted_tsv* > . > I have gin indexed the weighted_tsv column. > > When I query using the following > > EXPLAIN ANALYZE select * from question where weighted_tsv @@ > to_tsq

Re: [GENERAL] Re: performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

On Wed, Sep 21, 2016 at 2:18 PM, pinker wrote: > Jeff Janes wrote > > Try swapping the order of the columns in the exclude constraint. You > want > > the more selective criterion to appear first in the index/constraint. > > Presumably "key with =" is the mos

[GENERAL] cluster on table rewrite

I need to add a new column to my largest table, something like: alter table foo add column col_15 text not null default 'foobar'; I am tempted to add the column as NULL, and then use coalesce and nullif in the client code to re-interpret NULL as being the value 'foobar'. But I think that that wo

Re: [GENERAL] Multi tenancy : schema vs databases

On Fri, Sep 30, 2016 at 2:06 AM, Rakesh Kumar wrote: > > We require complete data isolation. Absolutely nothing should be shared > between two tenants. > Then you need different clusters per tenant. Otherwise, the WAL records of different tenants are inextricably mingled together. Cheers, Jef

Re: [GENERAL] Multi tenancy : schema vs databases

On Thu, Sep 29, 2016 at 12:18 PM, Rakesh Kumar wrote: > > Hi > > I would like to know which technique is better for supporting > multi-tenancy= > applications, going upto hundreds or even thousands of tenants. > > 1 - One database with difference schemas (one schema per tenant) > or > 2 - One da

[GENERAL] postgres_fdw and permissions

Say user1 creates a foreign table, and grants select permissions on it to user2. When user2 selects from the table, whose USER MAPPING is going to be used, the table owner (user1) or the executing user (user2)? Is this part of the FDW infrastructure or SQL/MED spec, and so above the postgres_fdw

Re: [GENERAL] Dump all the indexes/constraints/roles

On Mon, Oct 17, 2016 at 8:32 PM, Patrick B wrote: > Hi guys, > > I need to export an entire database to another server, for testing purpose. > > Is there any way to export all indexes and constraints ? > Postgres 9.2 > Patrick > By default pg_dump will export the constraints. It will export the

[GENERAL] promoting a streaming warm standby once it catches up to the master.

When developing or testing application code, I like to do it against a pretty fresh snapshot of the production server. I have a script which refreshes my dev server by cloning the production server from its most recent base backup, and recovering it forward from files in the wal archive. With a f

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

On Tue, Nov 8, 2016 at 9:43 AM, otar shavadze wrote: > I have table with 500 000 rows, I have int[] column "my_array" in this > table, this array column contains minimum 1 and maximum 5 different values. > > I have GIN index on my_array column: > > * "CREATE INDEX idx ON table_name USING GIN(my_a

  1   2   3   4   5   6   7   >