Re: [GENERAL] pg_basebackup running from a remote machine

On Tue, Nov 14, 2017 at 8:28 AM, Laurenz Albe wrote: > rakeshkumar464 wrote: > > If pg_basebackup is run from a remote machine with compress option > --gzip , > > compress level 9, > > will the compression occur prior to the data being sent on the network or > > after it has been received > > at

Re: [GENERAL] filter records by substring match of an postgresql array column

On Wed, Nov 8, 2017 at 4:28 AM, Arup Rakshit wrote: > > And to do I wanted to add an index like: > > CREATE INDEX trgm_idx_video_tags ON videos USING gist > ((array_to_string(tags, ', ')) gist_trgm_ops) > > But on running this I get an error as: > > ERROR: functions in index expression must be m

Re: [GENERAL] filter records by substring match of an postgresql array column

On Nov 8, 2017 02:34, "Arup Rakshit" wrote: Hi, I do have a videos table, and it has a column called `tags` of type array. I would like to select all videos where any string inside tag column matches a given substring. What method should I use? The *Contains `@>` operator* will do full string co

Re: [GENERAL] [HACKERS] SSL and Encryption

On Thu, Nov 2, 2017 at 10:22 PM, John R Pierce wrote: > On 11/2/2017 10:12 PM, Jeff Janes wrote: > >> https://wiki.postgresql.org/wiki/List_of_drivers >> >> What is 'python native'? psycopg works as long you update your libpq. >> > > > I thought

Re: [GENERAL] [HACKERS] SSL and Encryption

On Thu, Nov 2, 2017 at 9:58 PM, John R Pierce wrote: > On 11/2/2017 9:39 PM, Michael Paquier wrote: > > The SCRAM discussion is spread across two threads mainly with hundreds > of emails, which may discourage even the bravest. Here are links to > the important > documentation:https://www.postgre

Re: [GENERAL] Where to find development builds of pg for windows

On Sat, Oct 14, 2017 at 12:18 AM, legrand legrand < legrand_legr...@hotmail.com> wrote: > Hello, > > Using PG mainly on windows, I would have liked to test new releases / > development versions before they are available in > https://www.postgresql.org/download/windows/ > > Are there some build far

Re: [GENERAL] Using cp to back up a database?

On Mon, Oct 9, 2017 at 9:41 AM, Ron Johnson wrote: > On 10/09/2017 11:33 AM, Jeff Janes wrote: > > On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson wrote: > >> Hi, >> >> v8.4.20 >> >> This is what the current backup script uses: >> >>

Re: [GENERAL] Using cp to back up a database?

On Mon, Oct 9, 2017 at 7:15 AM, Ron Johnson wrote: > > Sure I want a consistent database. Why doesn't? > > But log shipping requires you to rsync/var/lib/pgsql/data to the remote > server, and that's consistent, so why wouldn't rsync to a local directory > also be consistent? > But it isn't co

Re: [GENERAL] Using cp to back up a database?

On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson wrote: > Hi, > > v8.4.20 > > This is what the current backup script uses: > > /usr/bin/psql -U postgres -c "SELECT pg_start_backup(' > Incrementalbackup',true);" > cp -r /var/lib/pgsql/data/* $dumpdir/data/ > /usr/bin/psql -U postgres template1 -c "SELE

Re: [GENERAL] pgcrypto encrypt

On Tue, Sep 19, 2017 at 12:20 PM, Bruce Momjian wrote: > On Wed, Sep 6, 2017 at 04:19:52PM -0400, Stephen Cook wrote: > > Hello! > > > > Is there a way to decrypt data encrypted with the pgcrypto "encrypt" > > function, outside the database? Assuming that I know the key etc... > > Yes, I think s

Re: [GENERAL] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

On Sep 14, 2017 7:07 AM, "Arthur Zakirov" wrote: On Wed, Sep 13, 2017 at 02:42:18PM +, Troy Hardin wrote: > Putting either of these two lines in the .conf file cause it to fail to start. Can you show error messages from logs? And the version.

Re: [GENERAL] contrecord is requested

On Mon, Sep 11, 2017 at 1:27 PM, Scott Marlowe wrote: > So we have a db we're trying to rewind and get synced to the master. > pg_rewind says it doesn't need rewinding, and when we try to bring it > up, it gets this error: > > "contrecord is requested by 2E7/4028" > > And fails to get back up

Re: [GENERAL] pg_ident mapping Kerberos Usernames

On Sun, Sep 10, 2017 at 4:31 PM, wrote: > > GSSAPI is the authentication mechanism of choice, and it's working fine. > > Here is what I'm trying to accomplish. > > 'user1' == 'user1' and 'us...@a.domain.tld' == 'user1'. > > From reading the docs, this is done via the pg_ident.conf file, and from

Re: [GENERAL] Needing verification on instructions for streaming replication

On Mon, Sep 11, 2017 at 7:02 AM, Dave Florek wrote: > Hi, > > I'm trying to setup streaming replication on a cluster of postgresql > databases and I followed the instructions outlined here ( > https://wiki.postgresql.org/wiki/Streaming_Replication) but I'm stuck on > the archive and restore comma

Re: [GENERAL] pg_ident mapping Kerberos Usernames

On Sun, Sep 10, 2017 at 11:25 AM, wrote: > On 09/10/2017 02:39 AM, Magnus Hagander wrote: > >> On Sat, Sep 9, 2017 at 6:44 PM, > > wrote: >> >> Hi, >> >> I'm trying to get pg_ident to map "user1" and "us...@a.domain.tld" >> to "user1" in postgres,

Re: [GENERAL] Performance with high correlation in group by on PK

On Tue, Aug 29, 2017 at 1:20 AM, Alban Hertroys wrote: > On 28 August 2017 at 21:32, Jeff Janes wrote: > > On Mon, Aug 28, 2017 at 5:22 AM, Alban Hertroys > wrote: > >> > >> Hi all, > >> > >> It's been a while since I actually got to use

Re: [GENERAL] Confused about max_standby_streaming_delay

On Thu, Sep 7, 2017 at 1:16 AM, Robert Inder wrote: > > > On 6 September 2017 at 20:47, Jeff Janes wrote: > >> >>> Have I misunderstood something? Or is Postgres not actually configured >>> the way I think it is? >>> >> >> The standby

Re: [GENERAL] Confused about max_standby_streaming_delay

On Wed, Sep 6, 2017 at 9:43 AM, Robert Inder wrote: ... > And I've read that the answer to this is to set > max_standby_streaming_delay in postgresql94.conf. > So I've set it to "600s" -- ten minutes. > > I thought this would mean that when there was a conflict with an update > from the live ser

Re: [GENERAL] Performance with high correlation in group by on PK

On Mon, Aug 28, 2017 at 5:22 AM, Alban Hertroys wrote: > Hi all, > > It's been a while since I actually got to use PG for anything serious, > but we're finally doing some experimentation @work now to see if it is > suitable for our datawarehouse. So far it's been doing well, but there > is a part

Re: [GENERAL] install the oracle data wrapper extension

On Tue, Aug 22, 2017 at 2:47 PM, Peter Koukoulis wrote: > > Hi > > Is there a reliable way to create a database link from PosgreSQL 9.6 to > Oracle 11g? > I am running 9.6 on Linux 64 bit. > I can connect to the Oracle database with sqlplus from the PostgreSQL > server. > > Also, I have in attem

Re: [GENERAL] A question on GIN indexes and arrays

On Sun, Aug 20, 2017 at 1:28 PM, Wells Oliver wrote: > > Why is this happening and what can I do to get my GIN indexes working? > Thanks! > > What extensions do you have installed in each database? I bet one of them (like intarray) redefines @> for one of your databases. Try fully qualifying th

Re: [GENERAL] How to delete default privileges

On Tue, Aug 15, 2017 at 3:02 PM, Francisco Reyes wrote: > I have a DB where we changed ownership of all objects. > > We had: > > ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT ON tablesTO > dbgroup_ro_group; > ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT ON sequences

Re: [GENERAL] How to make server generate more output?

On Fri, Aug 11, 2017 at 1:14 PM, Rui Pacheco wrote: > Hello, > > I know this is a bit vague but I’m looking for a configuration > parameter/startup switch that once set or enabled would make Postgresql > return more data that normal. Specifically the wire protocol would return > more notification

Re: [GENERAL] hot standby questions

On Wed, Aug 2, 2017 at 8:19 PM, armand pirvu wrote: > > Hi > > Just trying to put together the hot_standby setup > All docs I read are pointing to use as prefered method to use > pg_basebackup to set the base > So far so good > But > > psql postgres -c "select pg_start_backup('backup')" > pg_base

Re: [GENERAL] select md5 result set

On Wed, Aug 2, 2017 at 4:25 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis > wrote: > >> >> SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value >> from dual; >> >> MD5_VALUE >> -

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Tue, Aug 1, 2017 at 9:24 AM, Dmitry Lazurkin wrote: > On 08/01/2017 07:13 PM, Jeff Janes wrote: > > I think that HashSet is a Java-specific term. It is just a hash table in > which there is no data to store, just the key itself (and probably a cash > of the hashcode of tha

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 31, 2017 at 12:29 PM, Dmitry Lazurkin wrote: > On 31.07.2017 19:42, Jeff Janes wrote: > > I think it is simply because no one has gotten around to implementing it > that way. When you can just write it as a values list instead, the > incentive to make the regular in-l

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 8:03 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jul 24, 2017 at 7:58 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane wrote: >> >>> >>> The cost to form the inner hash is basically negligi

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane wr > > > regression=# EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ids WHERE id > IN > :values_clause; > QUERY PLAN > >

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Tue, Jul 25, 2017 at 2:03 AM, Dmitry Lazurkin wrote: > On 25.07.2017 05:50, Jeff Janes wrote: > >> It isn't either-or. It is the processing of millions of rows over the >> large in-list which is taking the time. Processing an in-list as a hash >> table would be

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 3:12 PM, Dmitry Lazurkin wrote: > On 25.07.2017 00:31, David G. Johnston wrote: > > > Basically you want to write something like: > > SELECT * > FROM ids > JOIN ( :values_clause ) vc (vid) ON (vc.vid = ids.id)​ > > or > > WITH vc AS (SELECT vid FROM ORDER BY ... LIMIT

Re: [GENERAL] Index Only Scan and Heap Fetches

On Tue, Jul 18, 2017 at 7:21 AM, Mikhail wrote: > Hi guys, > > I'm running the process, that executes "select * from sr where sr.id=210 > for update;", then some calculations and finally "update sr set usage = > where sr.id = 210;". That operation is done in a loop. > > In parallel session i'm r

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

On Wed, Jul 26, 2017 at 1:44 AM, Michael Paquier wrote: > On Mon, Jul 24, 2017 at 9:08 PM, Jeff Janes wrote: > > On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier < > michael.paqu...@gmail.com> > > wrote: > >> What do you think about the patch attached? >

Re: [GENERAL] Indexes being ignored after upgrade to 9.5

On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan wrote: > Hi, > > We have recently promoted our Prod DB slave (2TB) to migrate to new > hardware, and upgraded from v9.2.9.21 to 9.5.1.6 using pg_upgrade. > > > The upgrade went without incident and we have been running for a week, but > the optimizer

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Jul 24, 2017 14:19, "PT" wrote: On Mon, 24 Jul 2017 13:17:56 +0300 Dmitry Lazurkin wrote: > On 07/24/2017 01:40 AM, PT wrote: > > In this example you count approximately 40,000,000 values, which is > > about 40% of the table. > > 4 000 000 (: > > > If you really need these queries to be fast

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier wrote: > On Fri, Jul 14, 2017 at 9:11 PM, Jeff Janes wrote: > > On Thu, Jul 13, 2017 at 10:38 AM, Michael Paquier > > wrote: > >> > >> On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes > wrote: > >> >

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

On Thu, Jul 13, 2017 at 10:38 AM, Michael Paquier wrote: > On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes wrote: > > > > I think that pg_stat_wal_receiver should be crossreferenced in > > https://www.postgresql.org/docs/9.6/static/hot-standby.html, near the >

Re: [GENERAL] LDAP authentication without OU in ldapbasedn

On Thu, Jul 13, 2017 at 2:46 AM, Gregory Nicol wrote: > Good morning all, > > > > I can’t seem to get LDAP Authentication working without an OU in the > ldapbasedn. My users are spread across multiple OUs without a common root > OU which is why I’m trying to authenticate with just the DC. > > Ha

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

On Thu, Jul 13, 2017 at 1:15 AM, Michael Paquier wrote: > On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes wrote: > > > > I think that none of the recovery information functions > > (https://www.postgresql.org/docs/9.6/static/functions-admin. > html#FUNCTIONS-RECOVERY-INFO-TA

[GENERAL] Monitoring of a hot standby with a largely idle master

I think that none of the recovery information functions ( https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE) can distinguish a hot standby which is connected to an idle master, versus one which is disconnected. For example, because the master has crashed

[GENERAL] debugging SSL connection problems

Is there a way to get libpq to hand over the certificate it gets from the server, so I can inspect it with other tools that give better diagnostic messages? I've tried to scrape it out of the output of "strace -s8192", but since it is binary it is difficult to figure out where it begins and ends w

Re: [GENERAL] Strange case of database bloat

On Tue, Jul 4, 2017 at 10:18 PM, Chris Travers wrote: > > Questions > === > I assume that it is the fact that rows update frequently which is the > problem here? But why doesn't Postgres re-use any of the empty disk pages? > Can you install the contrib extension pg_freespacemap and use "

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

On Mon, Jul 3, 2017 at 10:39 AM, rajan wrote: > Thanks, Jeff. > > Now I am going back to my old question. > > Even though *Session 2* fails to update with UPDATE 0 message, its txid is > saved in xmax of updated(by *Session 1*) tuple. > > As it becomes an old txid, how come new txids are able to

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

On Mon, Jul 3, 2017 at 3:02 AM, rajan wrote: > Thanks for the explanation. > > will I be able to view the information using this function, > SELECT * FROM heap_page_items(get_raw_page('testnumbers', 0)); > > Also, please let me know which column I should refer for viewing the > pointer. > It is

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

On Sat, Jul 1, 2017 at 8:55 PM, rajan wrote: > Thanks, Jeff. That helps understanding it 50%. > > *Session 2* fails to UPDATE the record which is in *(0,2)* and this tuple > is > marked for deletion. It means that *(0,2) never exists* when Session 2 is > trying to perform the update. > That it n

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

On Sat, Jul 1, 2017 at 6:32 PM, rajan wrote: > hello, > > thanks for replies, Adrian, Steven. > > >So calling it can advance the xid manually. Some testing here showed > >that what xmin or xmax is created depends on when you call txid_current > >in either the original session or the concurrent se

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

On Thu, Jun 29, 2017 at 12:05 AM, Ken Tanzer wrote: > Thanks for the responses. For me, using the 9.2 binary was the winner. > Shoulda thought of that! > > On Wed, Jun 28, 2017 at 1:30 PM, Tom Lane wrote: > >> >> Generally speaking, it helps a lot if you don't insist on restoring the >> output

Re: [GENERAL] 10beta1 role

On Thu, Jun 22, 2017 at 1:39 PM, Jeff Janes wrote: > On Thu, Jun 22, 2017 at 1:34 PM, Adrian Klaver > wrote: > >> On 06/22/2017 01:29 PM, Jeff Janes wrote: >> >>> On Thu, Jun 22, 2017 at 1:22 PM, Adrian Klaver < >>> adrian.kla...@aklaver.com

Re: [GENERAL] 10beta1 role

On Thu, Jun 22, 2017 at 1:34 PM, Adrian Klaver wrote: > On 06/22/2017 01:29 PM, Jeff Janes wrote: > >> On Thu, Jun 22, 2017 at 1:22 PM, Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote: >> >> On 06/22/2017 01:13 PM, Jeff Janes wrote: >> >

Re: [GENERAL] 10beta1 role

On Thu, Jun 22, 2017 at 1:22 PM, Adrian Klaver wrote: > On 06/22/2017 01:13 PM, Jeff Janes wrote: > >> On Thu, Jun 22, 2017 at 12:06 PM, Ray Stell > ste...@vt.edu>> wrote: >> >> I used "initdb -U" to specify an alternate superuser. On startup it

Re: [GENERAL] 10beta1 role

On Thu, Jun 22, 2017 at 12:06 PM, Ray Stell wrote: > I used "initdb -U" to specify an alternate superuser. On startup it > throws these msgs: > > 2017-06-22 14:36:34 EDT,0,startup FATAL: 28000: role "postgresql" does > not exist > > 2017-06-22 14:36:34 EDT,0,startup LOCATION: InitializeSessionU

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

On Mon, Jun 19, 2017 at 10:33 AM, Dmitry O Litvintsev wrote: > Hi > > Since I have posted this nothing really changed. I am starting to panic > (mildly). > > The source (production) runs : > > relname | mode | granted | > substr

Re: [GENERAL] effective_io_concurrency increasing

On Mon, Jun 19, 2017 at 8:49 AM, Merlin Moncure wrote: > On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes wrote: > > If you have a RAID, set it to the number of spindles in your RAID and > forget > > it. It is usually one of the less interesting knobs to play with. > (Unless &

Re: [GENERAL] effective_io_concurrency increasing

On Sun, Jun 18, 2017 at 7:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sun, Jun 18, 2017 at 6:02 PM, Patrick B > wrote: > >> Hi guys. >> >> I just wanna understand the effective_io_concurrency value better. >> >> My current Master database server has 16 vCPUS and I use >> ​​

Re: [GENERAL] workaround for column cross-correlation

On Mon, Jun 12, 2017 at 8:17 PM, Justin Pryzby wrote: > I know PG 10 will have support "CREATE STATISTICS.." for this.. > > ..but I wondered if there's a recommended workaround in earlier versions ? > Not without seeing the query > > 2) memory explosion in hash join (due to poor estimate?)

Re: [GENERAL] Performance issue with Pointcloud extension

On Fri, Jun 9, 2017 at 9:13 AM, Eric Lemoine wrote: > On 06/08/2017 10:41 PM, Éric wrote: > > > > > > > >> Have you experimented with other queries that don't involve PostGIS? > >> I'm wondering if your hook-installation code fails to work properly > >> unless PostGIS was loaded first. This woul

Re: [GENERAL] Why is posgres picking a suboptimal plan for this query?

On Wed, May 24, 2017 at 1:42 PM, Sam Saffron wrote: > I have this query that is not picking the right index unless I hard code > dates: > > > SELECT "topics".* FROM "topics" > WHERE topics.last_unread_at >= '2017-05-11 20:56:24' > > "Index Scan using index_topics_on_last_unread_at on topics > (co

Re: [GENERAL] Encrypt with BouncyCastle and decrypt with pg_pub_decrypt

On Thu, May 18, 2017 at 4:57 PM, Kang, Kamal wrote: > Hi all, > > > > I am trying to encrypt a string using Bouncy Castle PGP Java apis, Base64 > encode the encrypted string and then decrypt using pg_pub_decrypt but it is > failing with error “Wrong Key”. Just wanted to know if this is doable or

Re: [GENERAL] database is not accepting commands

On Tue, May 16, 2017 at 1:28 AM, reem wrote: > We have 1.5 TB database that's shown an error and block all commands. > The error is : > "ERROR: database is not accepting commands to avoid wraparound data loss > in > database "dbname" > HINT: Stop the postmaster and use a standalone backend to v

Re: [GENERAL] Caching and Blobs in PG? Was: Can PG replace redis, amqp, s3 in the future?

On Wed, May 3, 2017 at 3:57 AM, Thomas Güttler wrote: > Am 02.05.2017 um 05:43 schrieb Jeff Janes: > >> On Sun, Apr 30, 2017 at 4:37 AM, Thomas Güttler < >> guettl...@thomas-guettler.de <mailto:guettl...@thomas-guettler.de>> >> wrote: >> >> I

Re: [GENERAL] Can PG replace redis, amqp, s3 in the future?

On Sun, Apr 30, 2017 at 4:37 AM, Thomas Güttler < guettl...@thomas-guettler.de> wrote: > Is is possible that PostgreSQL will replace these building blocks in the > future? > > - redis (Caching) > PostgreSQL has its own caching. It might not be quite as effective as redis', but you can us it if

Re: [GENERAL] full text search on hstore or json with materialized view?

Please don't top-post, thanks. On Tue, Apr 18, 2017 at 3:18 PM, Rj Ewing wrote: > On Tue, Apr 18, 2017 at 3:00 PM, Bruce Momjian wrote: > >> >> Full text search of JSON and JSONB data is coming in Postgres 10, which >> is to to be released in September of this year: >> >> https://www.de

Re: [GENERAL] Why so long?

On Wed, Apr 19, 2017 at 8:24 AM, Steve Clark wrote: > Hello, > > I am confused. I have a table that has an incrementing primary key id. > > When I select max(id) from table is returns almost instantly but > when I select min(id) from table it takes longer than I want to wait. > > Shouldn't postgr

Re: [GENERAL] streaming replication and archive_status

On Tue, Apr 18, 2017 at 5:20 AM, Luciano Mittmann wrote: > > > Hi Jeff, > > **Does each file in pg_xlog/archive_status/ have a corresponding file one > directory up? > > no corresponding file on pg_xlog directory. That is the question.. for > some reason or some parameter that I do not know, the

Re: [GENERAL] # of connections and architecture design

On Tue, Apr 18, 2017 at 2:42 AM, Moreno Andreo wrote: > Hi all, > As many of you has read last Friday (and many has tired to help, too, > and I still thank you very much), I had a bad service outage. > I was pointed to reduce number of maximum connections using a pooler, and > that's what I'm

Re: [GENERAL] streaming replication and archive_status

2017-04-17 17:08 GMT-03:00 Jeff Janes : > On Mon, Apr 17, 2017 at 12:22 PM, Luciano Mittmann > wrote: > >> Hi All, >> >> anyone knows why there are so many files in the directory >> pg_xlog/archive_status/ in replication server? >> >> # pg_xlog

Re: [GENERAL] streaming replication and archive_status

On Mon, Apr 17, 2017 at 12:22 PM, Luciano Mittmann wrote: > Hi All, > > anyone knows why there are so many files in the directory > pg_xlog/archive_status/ in replication server? > > # pg_xlog/archive_status/ | wc -l > > 75217 > > Is possible to clean this .done files or just don't need to worry

Re: [GENERAL] Service outage: each postgres process use the exact amount of the configured work_mem

On Fri, Apr 14, 2017 at 10:12 AM, Moreno Andreo wrote: > Hi all, > About 2 hours and half ago, suddenly (and on the late afternoon of the > Easter Friday), customers reported failing connections to our server, or > even very slow. > After a bit of checking (that also involved server reboot) I not

Re: [GENERAL] store key name pattern search

On Tue, Apr 4, 2017 at 8:41 AM, Armand Pirvu (home) wrote: > Hi > > I have the following case > > > select * from foo; > col1 > > - > "show_id"=>"1", "group_id"=>"32", "group_na

[GENERAL] Trigger based logging alternative to table_log

I have some code which uses table_log ( http://pgfoundry.org/projects/tablelog/) to keep a log of changes to selected tables. I don't use the restore part, just the logging part. It creates a new table for each table being logged, with several additional columns, and adds triggers to insert rows

Re: [GENERAL] Table not cleaning up drom dead tuples

On Tue, Mar 14, 2017 at 5:09 AM, Антон Тарабрин wrote: > Good day. It seems that we have some strange case of VACUUM malfunction > and table bloating. > > PostgreSQL 9.5.3 > Are you using replication slots? See this, fixed in 9.5.5: commit de396a1cb34626619ddc6fb9dec6d12abee8b589 Author: Andre

Re: [GENERAL] hight cpu %sy usage

On Mon, Feb 27, 2017 at 6:13 AM, dby...@163.com wrote: > hello everyone, > > i have PostgreSQL 9.5.3 server running on redhalt 6.6 > when i run one query with pgbench the cpu is 80% and sy% is 60% > > Why is this a problem? If you run the query as fast as you can, all of the time spent running

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Thu, Feb 23, 2017 at 2:42 AM, Bill Moran wrote: > On Wed, 22 Feb 2017 13:19:11 -0800 > Jeff Janes wrote: > > > On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure > wrote: > > > > > > On Thursday, February 16, 2017, Tom Lane wrote: > > > > >

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Fri, Feb 17, 2017 at 10:36 AM, Tim Bellis wrote: > > > > > *From:* Jeff Janes [mailto:jeff.ja...@gmail.com] > *Sent:* 17 February 2017 02:59 > *To:* Tim Bellis > *Cc:* pgsql-general@postgresql.org > *Subject:* Re: [GENERAL] Autovacuum stuck for hours, blocking que

Re: [GENERAL] bloat indexes - opinion

On Tue, Feb 21, 2017 at 1:44 PM, Patrick B wrote: > Hi guys, > > I've got a lot of bloat indexes on my 4TB database. > > Let's take this example: > > Table: seg > Index: ix_filter_by_tree > Times_used: 1018082183 > Table_size: 18 GB -- wrong. The table is mostly on pg_toast table. Its > real size

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure wrote: > > > On Thursday, February 16, 2017, Tom Lane wrote: > >> Tim Bellis writes: >> > Even though this is a read only query, is it also expected to be >> blocked behind the vacuum? Is there a way of getting indexes for a table >> which won't b

Re: [GENERAL] Indexes and MVCC

On Sun, Feb 19, 2017 at 8:52 AM, Rakesh Kumar wrote: > > https://www.youtube.com/watch?v=8mKpfutwD0U&t=1741s > > Somewhere around 13th minute, Chris Tavers mentions this: > > 1 - In the queuing table, the application deletes lot of rows (typical for > a queuing table). > 2 - Query trying to find

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

On Thu, Feb 16, 2017 at 3:27 PM, David Hinkle wrote: > I guess this doesn't work, latest test run crashed. It still uses the > bad plan for the hostid column even after n_distinct is updated. > > cipafilter=# select attname, n_distinct from pg_stats where tablename > cipafilter-# = 'log_raw' and

Re: [GENERAL] disk writes within a transaction

On Thu, Feb 16, 2017 at 11:33 AM, 2xlp - ListSubscriptions < postg...@2xlp.com> wrote: > Can someone enlighten me to how postgres handles disk writing? I've read > some generic remarks about buffers, but that's about it. > > We have a chunk of code that calls Postgres in a less-than-optimal way >

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Wed, Feb 15, 2017 at 9:30 AM, Tim Bellis wrote: > I have a postgres 9.3.4 database table which (intermittently but reliably) > gets into a state where queries get blocked indefinitely (at least for many > hours) behind an automatic vacuum. I was under the impression that vacuum > should never

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

On Mon, Feb 13, 2017 at 12:43 PM, David Hinkle wrote: > Thanks Jeff, > > No triggers or foreign key constrains: > > psql:postgres@cipafilter = \d+ titles > Table "public.titles" > Column │ Type│Modifiers

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

On Mon, Feb 13, 2017 at 11:53 AM, David Hinkle wrote: > Thanks guys, here's the information you requested: > > psql:postgres@cipafilter = show work_mem; > work_mem > ── > 10MB > (1 row) > OK, new theory then. Do you have triggers on or foreign key constraints to the table you are del

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

On Mon, Feb 13, 2017 at 9:40 AM, David Hinkle wrote: > I'm having trouble with purges related to a large table. The delete > query consumes ram until postgres crashes due to OOM. I have a very > large table called log_raw. There are half a dozen related tables, > such as 'urls' and 'titles'.

Re: [GENERAL] Locks Postgres

On Thu, Feb 9, 2017 at 9:00 PM, Patrick B wrote: > Hi guys > > I just wanna understand the locks in a DB server: > [image: Imagem inline 1] > > Access share = Does that mean queries were waiting because an > update/delete/insert was happening? > It would seem more plausible that your chart is

Re: [GENERAL] Transaction apply speed on the standby

On Thu, Jan 26, 2017 at 8:34 AM, Rakesh Kumar wrote: > Ver 9.6.1 > > In a streaming replication can it be assumed that if both primary and > standby are of the same hardware, then the rate at which transactions are > applied on the standby will be same as that on primary. Or standbys are > always

Re: [GENERAL] Why autvacuum is not started?

On Mon, Jan 9, 2017 at 8:45 AM, Edmundo Robles wrote: > I have running Postgresql 9.4 and... if i have a table with following > configuration: > autovacuum_vacuum_scale_factor=0.0, > autovacuum_analyze_scale_factor=0.0, > autovacuum_vacuum_threshold=1000, > autovacuum_analyze_threshold=1000

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wed, Dec 14, 2016 at 1:17 PM, Patrick B wrote: > > > 2. To call the function, I have to login to postgres and then run: select > logextract(201612015, 201612015); > How can I do it on cron? because the dates will be different every time. > PostgreSQL already knows what date today is. Why doe

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

On Tue, Dec 6, 2016 at 2:44 AM, Tom DalPozzo wrote: > Hi, > about SSD light: > > I guessed it was WAL -> actual db files data traffic. It explains why the > light stops blinking after shutting down the server (I did it via kill > command) . > Do you kill with -15 (the default) or -9? And whic

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

On Fri, Dec 2, 2016 at 9:40 AM, Tom DalPozzo wrote: > Hi, > I've two tables, t1 and t2, both with one bigint id indexed field and one > 256 char data field; t1 has always got 1 row, while t2 is increasing as > explained in the following. > > My pqlib client countinously updates one row in t1

Re: [GENERAL] Moving pg_xlog

On Thu, Dec 1, 2016 at 6:17 PM, Michael Paquier wrote: > On Thu, Dec 01, 2016 at 05:48:51PM +0200, Achilleas Mantzios wrote: > > > > Performance is the reason. You would benefit from moving pg_xlog to a > > different controller with its own write cache or to a different SSD with > a > > write cac

Re: [GENERAL] Moving pg_xlog

On Thu, Dec 1, 2016 at 5:55 AM, Robert Inder wrote: > I'm running Postgres9.4 in master/hot-standby mode on a few pairs of > servers. > > While recovering from A Bit Of Bother last week, I came across a > posting saying that pg_xlog should be on a separate partition. > > I tried to find out more

Re: [GENERAL] Backup "Best Practices"

On Mon, Nov 28, 2016 at 11:20 PM, Thomas Kellerer wrote: > Israel Brewster schrieb am 28.11.2016 um 23:50: > >> >>> pg_archivecleanup -n /mnt/server/archiverdir >>> 00010010.0020.backup >>> >> >> Ok, but where does that "00010010.0020.backup" >> come from?

Re: [GENERAL] Backup "Best Practices"

On Mon, Nov 28, 2016 at 2:50 PM, Israel Brewster wrote: > > - What is the "best" (or just a good) method of keeping the WAL archives >> under control? Obviously when I do a new basebackup I can "cleanup" any old >> files that said backup doesn't need, >> > > You have said you might be interested

Re: [GENERAL] Query regarding deadlock

On Thu, Nov 24, 2016 at 5:44 PM, Yogesh Sharma < yogesh1.sha...@nectechnologies.in> wrote: > Dear All, > > Thanks in advance. > I found below deadlock in postgresql logs. > I cannot change calling of REINDEX and insert query sequence because it is > execute automatically through some cron script.

Re: [GENERAL] Backup "Best Practices"

On Wed, Nov 23, 2016 at 10:16 AM, Israel Brewster wrote: > I was wondering if someone could inform me about, or point me to an online > article about best practices for backing up a postgresql database cluster? > At the moment, I have two servers running with streaming replication for > failover

Re: [GENERAL] Strict min and max aggregate functions

On Sun, Nov 20, 2016 at 2:45 AM, Pavel Stehule wrote: > > > 2016-11-19 22:12 GMT+01:00 Jeff Janes : > >> I need "strict" MIN and MAX aggregate functions, meaning they return NULL >> upon any NULL input, and behave like the built-in aggregates if none of the &g

[GENERAL] Strict min and max aggregate functions

I need "strict" MIN and MAX aggregate functions, meaning they return NULL upon any NULL input, and behave like the built-in aggregates if none of the input values are NULL. This doesn't seem like an outlandish thing to want, and I'm surprised I can't find other discussion of it. Perhaps because n

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

On Sat, Nov 12, 2016 at 5:33 PM, otar shavadze wrote: > Tried > > OPERATOR(pg_catalog.@>) > > > as Tom mentioned, but still, don't get fast performance when value does > not existed in any array. > Did you build the correct index? > > Also "played" with many different ways, gin, gist inde

Re: [GENERAL] Trigram is slow when 10m rows

On Sun, Nov 13, 2016 at 3:54 AM, Aaron Lewis wrote: > I have a simple table with Trigram index, > > create table mytable(hash char(40), title text); > create index title_trgm_idx on mytable using gin(title gin_trgm_ops); > > When I run a query with 10m rows, it uses the Trigram index, but takes >

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

On Thu, Nov 10, 2016 at 7:11 AM, Tom Lane wrote: > otar shavadze writes: > >> Hmmm ... actually, I wonder if maybe '@>' here is the contrib/intarray > >> operator not the core operator? The intarray operator didn't get > plugged > >> into any real estimation logic until 9.6. > > > So, you mean

  1   2   3   4   5   6   7   >