Re: [GENERAL] idle in transaction, why

2017-11-07 Thread Scott Marlowe
On Tue, Nov 7, 2017 at 7:44 AM, Rob Sargent wrote: > > >> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer wrote: >> >> I would figure values in "minutes" to be more realistic depending on the >> workload and characteristics of the application. >> >> A transaction that has several seconds of "think

Re: [GENERAL] explain analyze output: 0 rows, 1M loopa

2017-11-01 Thread Scott Marlowe
On Wed, Nov 1, 2017 at 1:19 PM, David G. Johnston wrote: > On Wed, Nov 1, 2017 at 11:59 AM, Scott Marlowe > wrote: >> >> So some of my output from an explain analyze here has a line that says >> this: >> >> ex Scan using warranty_order_item_warranty_order_id

[GENERAL] explain analyze output: 0 rows, 1M loopa

2017-11-01 Thread Scott Marlowe
So some of my output from an explain analyze here has a line that says this: ex Scan using warranty_order_item_warranty_order_id_idx on warranty_order_item woi_1 (cost=0.57..277.53 rows=6 width=137) (actual time=0.110..0.111 rows=0 loops=1,010,844) How can you have 1M loops over 0 rows? Running

Re: [GENERAL] Announcing PostgreSQL SLES RPM Repository

2017-10-27 Thread Scott Marlowe
On Thu, Oct 26, 2017 at 4:09 PM, Devrim Gündüz wrote: > > Hi, > > I am proud to announce the new and shiny PostgreSQL RPM repository for SLES > 12: > https://zypp.postgresql.org/. SNIP > This is a part of EnterpriseDB's contribution to the community: EDB provided > hardware, and let me to use my

Re: [GENERAL] table corruption

2017-10-23 Thread Scott Marlowe
On Mon, Oct 23, 2017 at 9:35 AM, Peter Geoghegan wrote: > On Mon, Oct 23, 2017 at 7:44 AM, Peter Hunčár wrote: >> I know that zero_damaged_pages and vacuum (or restore the table from backup) >> will help, but I want to ask if there is a way to identify affected >> rows/datafiles, so we can 'fix'

Re: [GENERAL] Monitoring Tool for PostgreSQL

2017-10-18 Thread Scott Marlowe
On Wed, Oct 18, 2017 at 11:37 AM, Fabricio Pedroso Jorge wrote: > Hi all, > >is there a "official" monitoring tool for PostgreSQL databases? For > example, i come from Oracle Database, and there, we have Enterprise Manager > to monitor and administrer the product... is there such a similar too

Re: [GENERAL] Finally upgrading to 9.6!

2017-10-18 Thread Scott Marlowe
On Wed, Oct 18, 2017 at 11:26 AM, Joshua D. Drake wrote: > On 10/18/2017 08:49 AM, Ron Johnson wrote: >> >> On 10/18/2017 10:16 AM, Igal @ Lucee.org wrote: >>> >>> On 10/18/2017 7:45 AM, Ron Johnson wrote: On 10/18/2017 09:34 AM, Igal @ Lucee.org wrote: > > A bit off-topic here,

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

2017-10-12 Thread Scott Marlowe
On Tue, Oct 10, 2017 at 4:28 PM, pinker wrote: > > Yes, it would be much easier if it would be just single query from the top, > but the most cpu is eaten by the system itself and I'm not sure why. You are experiencing a context switch storm. The OS is spending so much time trying to switch betwe

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

2017-10-10 Thread Scott Marlowe
On Tue, Oct 10, 2017 at 3:53 PM, pinker wrote: > Victor Yegorov wrote >> Can you provide output of `iostat -myx 10` at the “peak” moments, please? > > sure, please find it here: > https://pastebin.com/f2Pv6hDL Ouch, unless I'm reading that wrong, your IO subsystem seems to be REALLY slow. -- S

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

2017-10-10 Thread Scott Marlowe
On Tue, Oct 10, 2017 at 2:40 PM, pinker wrote: > Hi to all! > > We've got problem with a very serious repetitive incident on our core > system. Namely, cpu load spikes to 300-400 and the whole db becomes > unresponsive. From db point of view nothing special is happening, memory > looks fine, disks

Re: [GENERAL] time series data

2017-10-02 Thread Scott Marlowe
On Sun, Oct 1, 2017 at 2:17 AM, Khalil Khamlichi wrote: > Hi everyone, > > I have a data stream of a call center application coming in to postgres in > this format : > > user_name, user_status, event_time > > 'user1', 'ready', '2017-01-01 10:00:00' > 'user1', 'talking', '2017-01-01 10:02:00' > 'u

Re: [GENERAL] Up to date conventional wisdom re max shared_buffer size?

2017-09-20 Thread Scott Marlowe
On Tue, Sep 19, 2017 at 4:00 PM, Jerry Sievers wrote: > Briefly, just curious if legacy max values for shared_buffers have > scaled up since 8G was like 25% of RAM? > > Pg 9.3 on monster 2T/192 CPU Xenial thrashing > > Upgrade pending but we recently started having $interesting performance > issue

Re: [GENERAL] Any known issues Pg 9.3 on Ubuntu Xenial kernel 4.4.0?

2017-09-20 Thread Scott Marlowe
On Wed, Sep 20, 2017 at 12:14 PM, Jerry Sievers wrote: > Basically as per $subject. > > We took a perf hit moving up to newer hardware and OS version which > might in some cases be OK but admittedly there is some risk running a > much older app (Pg 9.3) on a kernel/OS version that nowhere near exi

Re: [GENERAL] contrecord is requested

2017-09-12 Thread Scott Marlowe
On Tue, Sep 12, 2017 at 10:19 AM, Jeff Janes wrote: > 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

[GENERAL] contrecord is requested

2017-09-11 Thread Scott Marlowe
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. Is this a known issue? Possible bug in the continuation record co

Re: [GENERAL] Numeric numbers

2017-09-02 Thread Scott Marlowe
On Sat, Sep 2, 2017 at 10:10 AM, Melvin Davidson wrote: > > > On Sat, Sep 2, 2017 at 11:54 AM, Francisco Olarte > wrote: >> >> On Sat, Sep 2, 2017 at 4:16 PM, Олег Самойлов wrote: >> > Hi all. I have silly question. Look at "numeric" type. According to >> > docs it must be "up to 131072 digits

Re: [GENERAL] PG and database encryption

2017-08-22 Thread Scott Marlowe
On Tue, Aug 22, 2017 at 3:13 PM, PT wrote: > On Tue, 22 Aug 2017 12:48:13 -0700 (MST) > rakeshkumar464 wrote: > >> We have a requirement to encrypt the entire database. What is the best tool >> to accomplish this. Our primary goal is that it should be transparent to the >> application, with no c

Re: [GENERAL] make postgresql 9.5 default on centos 7

2017-08-22 Thread Scott Marlowe
On Tue, Aug 22, 2017 at 12:44 PM, Devrim Gündüz wrote: > > Hi, > > On Fri, 2017-08-18 at 13:50 -0400, Steve Clark wrote: >> I loaded 9.5 on CentOS 7 but by default every thing wants to use the default >> 9.2 version that comes with CentOS 7. >> >> Is there a simple way to fix this so the 9.5 versi

Re: [GENERAL] archive_command fails but works outside of Postgres

2017-08-18 Thread Scott Marlowe
On Fri, Aug 18, 2017 at 12:40 PM, twoflower wrote: > I changed my archive_command to the following: > > archive_command = 'gsutil cp /storage/postgresql/9.6/main/%p > gs://my_bucket/pg_xlog/' > > and it fails, leaving the following in the log: > > 2017-08-18 18:34:25.057 GMT [1436][0]: [104319] LO

Re: [GENERAL] make postgresql 9.5 default on centos 7

2017-08-18 Thread Scott Marlowe
On Fri, Aug 18, 2017 at 11:50 AM, Steve Clark wrote: > Hi List, > > I loaded 9.5 on CentOS 7 but by default every thing wants to use the default > 9.2 version that comes with CentOS 7. > > Is there a simple way to fix this so the 9.5 version of tools and libraries > are used. As Melvin mentions,

Re: [GENERAL] Queries for Diagramming Schema Keys

2017-08-14 Thread Scott Marlowe
Just to add that running psql with the -E switch is REALLY handy for seeing how psql executes queries to find how tables etc are put together.

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

2017-08-09 Thread Scott Marlowe
On Wed, Aug 9, 2017 at 6:27 AM, ADSJ (Adam Sjøgren) wrote: > On 2017-06-21 Adam Sjøgren wrote: > >> Adam Sjøgren wrote: > >>> Meanwhile, I can report that I have upgraded from 9.3.14 to 9.3.17 and >>> the errors keep appearing the log. > > Just to close this, for the record: We haven't seen the

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

2017-08-09 Thread Scott Marlowe
On Wed, Aug 9, 2017 at 10:10 AM, Tom Lane wrote: > Melvin Davidson writes: >> *UPDATE pg_extensionSET extowner = {oid_of_new_owner} WHERE extowner = >> {oid_from_above_statement};* > > Note you'll also have to modify the rows in pg_shdepend that reflect > this ownership property. Seems like

Re: [GENERAL] Do not INSERT if UPDATE fails

2017-08-02 Thread Scott Marlowe
Does insert's "on conflict" clause not work for this usage? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Question about loading up a table

2017-08-02 Thread Scott Marlowe
On Tue, Aug 1, 2017 at 4:27 PM, Alex Samad wrote: > Hi > > So just to go over what i have > > > server A (this is the original pgsql server 9.2) > > Server X and Server Y ... PGSQL 9.6 in a cluster - streaming replication > with hot standby. > > > I have 2 tables about 2.5T of diskspace. > > I wan

Re: [GENERAL] Question about loading up a table

2017-08-01 Thread Scott Marlowe
On Mon, Jul 31, 2017 at 11:16 PM, Alex Samad wrote: > Hi > > I double checked and there is data going over, thought I would correct that. > > But it seems to be very slow. Having said that how do I / what tools do I > use to check through put Try the pg_current_xlog_location function on the sla

Re: [GENERAL] Manage slot in logical/pglogical replication

2017-07-31 Thread Scott Marlowe
On Mon, Jul 17, 2017 at 7:08 AM, dpat wrote: > yes i have estimated pg_xlog but server, some time, writes a lot of WAL file. > there are some robust alternative to replicate partial database in > Postgresql over WAN? > or, what's the best way to realign pglogical replica? pg_dump/restore?. > i hav

Re: [GENERAL] Schemas and serials

2017-07-31 Thread Scott Marlowe
On Sat, Jul 29, 2017 at 1:17 PM, Melvin Davidson wrote: > > On Sat, Jul 29, 2017 at 3:38 PM, tel medola wrote: > >> Depends. >> When you create your tables in new schema, the script was the same from >> "qa"? >> Sequences, tables, etc.. belong to the schema where was created. >> >> Roberto. >> >

Re: [GENERAL] partitioning question

2017-07-31 Thread Scott Marlowe
On Sun, Jul 30, 2017 at 7:13 PM, Alex Samad wrote: > How expensive is dynamic over static. I'm looking at storing yearly now, so > I figure if my if then clause has the latest year at the top it should be > very quick. Assuming you're not doing anything particularly crazy it's minimal. But what

Re: [GENERAL] Question about loading up a table

2017-07-31 Thread Scott Marlowe
On Mon, Jul 31, 2017 at 2:31 AM, vinny wrote: > On 2017-07-31 11:02, Alex Samad wrote: >> >> Hi >> >> I am using pg_dump | psql to transfer data from my old 9.2 psql into a >> 9.6 psql. Note that you should be doing pg_dump with 9.6's pg_dump, as it's possible for 9.2's pg_dump to not know about

Re: [GENERAL] Interesting streaming replication issue

2017-07-27 Thread Scott Marlowe
On Wed, Jul 26, 2017 at 11:55 PM, James Sewell wrote: > On Thu, Jul 27, 2017 at 4:41 PM, Gunnar "Nick" Bluth < > gunnar.bl...@pro-open.de> wrote: > >> >> are you sure you're scp'ing from the archive, not from pg_xlog? >> > > Yes: > > restore_command = 'scp -o StrictHostKeyChecking=no > 10.154.19

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

2017-06-19 Thread Scott Marlowe
On Mon, Jun 19, 2017 at 1:53 PM, Dmitry O Litvintsev wrote: > yes, we had to restart database 4 days ago (and vacuum has resumed on start). > I checked the log files and discovered that autovacuum on this table takes > > pages: 0 removed, 14072307 remain > tuples: 43524292 removed,

Re: [GENERAL] Postgres Data Encryption Using LUKS with dm-crypt ?

2017-06-19 Thread Scott Marlowe
On Sun, Jun 18, 2017 at 2:20 PM, Condor wrote: > Hello ppl, > > a few years ago I asked the same question but did not receive valued answers > and we use different way to realize the project. > Today I wanna ask did some one do it and most important for me, can some one > share his experience ? >

Re: [GENERAL] Rounding Double Precision or Numeric

2017-06-01 Thread Scott Marlowe
On Thu, Jun 1, 2017 at 10:42 AM, Steve Atkins wrote: > >> On Jun 1, 2017, at 9:26 AM, Louis Battuello >> wrote: >> >> Is the round() function implemented differently for double precision than >> for numeric? Forgive me if this exists somewhere in the documentation, but I >> can't seem to find

Re: [GENERAL] Current best practice for maximum shared_buffers settings on big hardware?

2017-05-24 Thread Scott Marlowe
On Wed, May 24, 2017 at 6:24 AM, Bill Moran wrote: > > A few years ago, I was working with "big" servers. At least, they were > big for that age, with *128G* of RAM!!!1 Holy mackeral, right?!!? > > Anyway, at that time, I tried allocating 64G to shared buffers and we > had a bunch of problems with

Re: [GENERAL] Call for users to talk about table partitioning

2017-05-18 Thread Scott Marlowe
On Thu, May 18, 2017 at 3:40 PM, Scott Marlowe wrote: > I would say that the best thing to do is to run 9.6 grab pgadmin4 and do all > the examples in the doc page on partitioning. > > https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html > > If that works well

Re: [GENERAL] Call for users to talk about table partitioning

2017-05-18 Thread Scott Marlowe
I would say that the best thing to do is to run 9.6 grab pgadmin4 and do all the examples in the doc page on partitioning. https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html If that works well then the question becomes are there any esoteric cases where pgadmin4 won't quite get you

Re: [GENERAL] Call for users to talk about table partitioning

2017-05-18 Thread Scott Marlowe
On Thu, May 18, 2017 at 1:21 PM, Melvin Davidson wrote: > Shirley, > I am a bit confused. AFAIK, it is PostgreSQL that is responsible for table > partitioning. PgAdmin4 is just an administrative tool. > Are you saying that PgAdmin4 now can make partition tables automatically? > I think maybe she

Re: [GENERAL] Top posting....

2017-05-14 Thread Scott Marlowe
On Sat, May 13, 2017 at 7:48 PM, Bruce Momjian wrote: > On Thu, May 11, 2017 at 01:43:52PM -0400, Tom Lane wrote: >> Absolutely. The point of quoting previous messages is not to replicate >> the entire thread in each message; we have archives for that. The point >> is to *briefly* remind readers

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

2017-05-08 Thread Scott Marlowe
On Mon, May 1, 2017 at 2:59 PM, Sven R. Kunze wrote: > On 30.04.2017 16:25, Steve Atkins wrote: > > You can use postgresql for caching, but caches don't require the data > durability that a database offers, and can be implemented much more > efficiently. > > > I for one can understand Thomas' need

Re: [GENERAL] PostgreSQL Required Monitoring

2017-04-28 Thread Scott Marlowe
On Fri, Apr 28, 2017 at 8:39 AM, Andrew Kerber wrote: > I am a fairly experienced Oracle DBA, and we are starting to move in to the > PostgreSQL world. I would expect the standard monitoring items are required > for mission critical postgres apps, Ie, disk space, wal log space, log > monitoring,

Re: [GENERAL] Block size recommendation for Red Hat Linux 7.2

2017-04-24 Thread Scott Marlowe
On Mon, Apr 24, 2017 at 12:43 PM, pinker wrote: > I've seen very big differences with huge_pages set to on, especially in > context of CPU usage on multiple socket servers. > > You could play as well with storage options, for instance inode size and > check if there is any advantage for your db fr

Re: [GENERAL] Block size recommendation for Red Hat Linux 7.2

2017-04-24 Thread Scott Marlowe
t; Linux block size is 4k. > > If we keep 8k block size at OS level is it improves PostgreSQL performance? > Please suggest what is the suggestible default OS block size for Linux > systems to install PostgreSQL. > > Thanks, > Chiru > > On Mon, Apr 24, 2017 at 12:29 PM, Sco

Re: [GENERAL] Block size recommendation for Red Hat Linux 7.2

2017-04-24 Thread Scott Marlowe
On Mon, Apr 24, 2017 at 9:41 AM, chiru r wrote: > Hello, > > I am building new server to run PostgreSQL 9.5.4 version on it. Please > provide the recommended Block size for Linux systems. > > We are using PostgreSQL blocks size is 8k default one. > > postgres=# show block_size ; > block_size > --

Re: [GENERAL] pg_basebackup issue

2017-04-23 Thread Scott Marlowe
On Sat, Apr 22, 2017 at 8:03 PM, David G. Johnston wrote: > On Saturday, April 22, 2017, chiru r wrote: >> >> Thank you Adrian. >> >> It seems the code is allowing only who has Superuser/Replication role >> directly. >> >> Is there any possibility in future releases they allow both case A & B >>

Re: [GENERAL] Not sure this should be asked here but...

2017-04-23 Thread Scott Marlowe
Well no one is stopping you from starting a forum. But then you've got the herculean task of getting people to move to it from the comfort of the mailing list. I'm guessing that's a non-starter. On Sun, Apr 23, 2017 at 4:31 AM, Ron Ben wrote: > > A simple open source forum system can be enough si

Re: [GENERAL] Large data and slow queries

2017-04-20 Thread Scott Marlowe
On Thu, Apr 20, 2017 at 6:30 AM, Vick Khera wrote: > I'm curious why you have so many partial indexes. Are you trying to make > custom indexes per query? It seems to me you might want to consider making > the indexes general, and remove the redundant ones (that have the same > prefix list of index

Re: [GENERAL] Advise on primary key for detail tables (OS: Raspberry Pi)

2017-04-06 Thread Scott Marlowe
On Thu, Apr 6, 2017 at 4:27 AM, Ertan Küçükoğlu wrote: > Sorry for top posting. > > I have a serial in master table because I need to know data insertion order. > DateTime on Raspberry Pi is not accurate due to power loss and lack of > internet access to fetch correct time from. Note that serial

Re: [GENERAL] How does hot_standby_feedback work

2017-04-05 Thread Scott Marlowe
On Wed, Apr 5, 2017 at 3:37 AM, Александр Киселев wrote: > Hello! > > My name is Alexander. I am an administarator PostgreSQL. > I am studying PostgreSQL's 9.6 documentation. > I am interested in parameter hot_standby_feedback in postgresql.conf > Can you explain more detail than in documentation

Re: [GENERAL] WAL being written during SELECT * query

2017-04-04 Thread Scott Marlowe
On Tue, Apr 4, 2017 at 9:46 AM, Tom DalPozzo wrote: > Hi, > I have a very big table (10GB). > I noticed that many WAL segments are being written when elaborating read > only transactions like this: > select * from dati256 where id >4300 limit 100; > I don't understand why are there

Re: [GENERAL] PostgreSQL general discussions list - 2010 Thread: Wikipedia entry - AmigaOS port - error?

2017-03-13 Thread Scott Marlowe
On Mon, Mar 13, 2017 at 11:15 PM, John R Pierce wrote: > On 3/13/2017 10:06 PM, Scott Marlowe wrote: > > I am pretty sure it is: > > https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Operating_system_support > > PostgreSQL Yes Yes

Re: [GENERAL] PostgreSQL general discussions list - 2010 Thread: Wikipedia entry - AmigaOS port - error?

2017-03-13 Thread Scott Marlowe
On Mon, Mar 13, 2017 at 10:41 PM, Adrian Klaver wrote: > On 03/13/2017 02:13 AM, Raffaele Irlanda wrote: >> http://aminet.net/package/dev/gg/postgresql632-mos-bin >> >> In 2010 it has been sure completely outdated but sure you can see proof >> it exists, and that Amiga had full dignity of being p

Re: [GENERAL] Request to confirm which command is use for exclusive operation

2017-03-07 Thread Scott Marlowe
On Tue, Mar 7, 2017 at 11:55 PM, Scott Marlowe wrote: > On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma wrote: >> Dear David, >> >> I want to apply explicitly lock mechanism once inset operation is in >> progress then REINDEX will wait. >> And vice versa. >&

Re: [GENERAL] Request to confirm which command is use for exclusive operation

2017-03-07 Thread Scott Marlowe
On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma wrote: > Dear David, > > I want to apply explicitly lock mechanism once inset operation is in > progress then REINDEX will wait. > And vice versa. > So, please let me know this type of handling is possible. > Regrds, > Yogesh Create two roles grant

Re: [GENERAL] PG on SSD

2017-03-02 Thread Scott Marlowe
On Thu, Mar 2, 2017 at 12:42 PM, scott ribe wrote: > Is it reasonable to run PG on a mirrored pair of something like the Intel SSD > DC 3610 series? (For example: > http://ark.intel.com/products/82935/Intel-SSD-DC-S3610-Series-480GB-2_5in-SATA-6Gbs-20nm-MLC) > I'd *hope* that anything Intel clas

Re: [GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

2017-02-28 Thread Scott Marlowe
On Tue, Feb 28, 2017 at 10:00 AM, Lisandro wrote: > Hi Steve, thanks for your help. > Your comment made me realise that maybe the problem is my pgBouncer > configuration, specifically default_pool_size. It took me a while to > understand pgbouncer, and I still had some doubts when I configured it.

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

2017-02-17 Thread Scott Marlowe
On Fri, Feb 17, 2017 at 1:38 PM, Rakesh Kumar wrote: > LOCK TABLE yourtable ; > CREATE TEMPORARY TABLE keep AS SELECT * FROM yourtable WHERE ; > TRUNCATE yourtable; > INSERT INTO yourtable SELECT * from keep; > COMMIT; > === > the above snippet assumes truncate in PG can be in a transaction. In ot

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

2017-02-15 Thread Scott Marlowe
On Wed, Feb 15, 2017 at 3:26 PM, Scott Marlowe wrote: > On Wed, Feb 15, 2017 at 10: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 &

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

2017-02-15 Thread Scott Marlowe
On Wed, Feb 15, 2017 at 10: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 nev

Re: [GENERAL] PostgreSQL corruption

2017-02-13 Thread Scott Marlowe
On Mon, Feb 13, 2017 at 9:41 PM, Scott Marlowe wrote: > On Mon, Feb 13, 2017 at 9:21 PM, James Sewell > wrote: >> >> Hello All, >> >> I am working with a client who is facing issues with database corruption >> after a physical hard power off (the machines ar

Re: [GENERAL] PostgreSQL corruption

2017-02-13 Thread Scott Marlowe
On Mon, Feb 13, 2017 at 9:21 PM, James Sewell wrote: > > Hello All, > > I am working with a client who is facing issues with database corruption > after a physical hard power off (the machines are at remote sites, this could > be a power outage or user error). > > They have an environment made u

Re: [GENERAL] Disabling inheritance with query.

2016-12-21 Thread Scott Marlowe
On Wed, Dec 21, 2016 at 3:36 PM, Edmundo Robles wrote: > Hi! > > i need disable inheritance from many tables in a query like > > "delete from pg_inherits where inhparent=20473" instead alter table ... > > but is safe? which is the risk for database if i delete it? You could change the s

Re: [GENERAL] pg_dumpall: could not connect to database "template1": FATAL:

2016-11-12 Thread Scott Marlowe
On Sat, Nov 12, 2016 at 2:31 PM, Adrian Klaver wrote: > On 11/12/2016 01:20 PM, aws backup wrote: >> >> Hi, >> >> I try to make pg_dumpall backups from a PostgreSQL 9.5 server which is >> part of the DaVinci Resolve 12.5.3 App on a Mac OS X 10.11.6 system. >> >> Unfortunately I get following failu

Re: [GENERAL] Postgresql errors on Windows with F-Secure AntiVirus

2016-11-09 Thread Scott Marlowe
On Wed, Nov 9, 2016 at 11:56 AM, Scott Marlowe wrote: > On Wed, Nov 9, 2016 at 11:29 AM, Moreno Andreo > wrote: >> Hi again, >> our support team is reporting cases where postgres connections are >> suddenly dropped, but reconnecting again soon everyting is OK. >

Re: [GENERAL] Postgresql errors on Windows with F-Secure AntiVirus

2016-11-09 Thread Scott Marlowe
On Wed, Nov 9, 2016 at 11:29 AM, Moreno Andreo wrote: > Hi again, > our support team is reporting cases where postgres connections are > suddenly dropped, but reconnecting again soon everyting is OK. > Environment is PostgreSQL 9.1 on Windows (various versions) > Asfer some research, I found o

Re: [GENERAL] Hardware recommendations?

2016-11-02 Thread Scott Marlowe
On Wed, Nov 2, 2016 at 4:19 PM, John R Pierce wrote: > On 11/2/2016 3:01 PM, Steve Crawford wrote: >> >> After much cogitation I eventually went RAID-less. Why? The only option >> for hardware RAID was SAS SSDs and given that they are not built on >> electro-mechanical spinning-rust technology it

Re: [GENERAL] Hardware recommendations?

2016-11-02 Thread Scott Marlowe
On Wed, Nov 2, 2016 at 11:40 AM, Joshua D. Drake wrote: > On 11/02/2016 10:03 AM, Steve Atkins wrote: >> >> I'm looking for generic advice on hardware to use for "mid-sized" >> postgresql servers, $5k or a bit more. >> >> There are several good documents from the 9.0 era, but hardware has moved >>

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

2016-11-01 Thread Scott Marlowe
On Tue, Nov 1, 2016 at 1:43 PM, Patrick B wrote: > > > 2016-11-02 2:55 GMT+13:00 Scott Marlowe : >> >> On Mon, Oct 31, 2016 at 8:01 PM, Patrick B >> wrote: >> > If I change recovery.conf: >> > >> > recovery_target_time = '2016-

Re: [GENERAL] Statistics on index usage

2016-11-01 Thread Scott Marlowe
On Tue, Nov 1, 2016 at 8:43 AM, François Battail wrote: > Dear List, > > does pgsql maintains statistics on index usage? I mean just a counter > for each index in the database, incremented each time time it is used. > It would be useful to help cleaning almost unused index and to avoid > poisoning

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

2016-11-01 Thread Scott Marlowe
On Mon, Oct 31, 2016 at 8:01 PM, Patrick B wrote: > If I change recovery.conf: > > recovery_target_time = '2016-10-30 02:24:40' > > > I get error: > > FATAL: requested recovery stop point is before consistent recovery point You can try using pg_basebackup to get the replica setup. In 9.2 you got

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

2016-10-29 Thread Scott Marlowe
On Sat, Oct 29, 2016 at 6:55 AM, Kim Rose Carlsen wrote: >> try this :-D > >> create or replace function indf(anyelement, anyelement) returns anyelement >> as >> $$ >> select $1 = $2 or ($1 is null and $2 is null); >> $$ language sql; >> >> CREATE VIEW view_circuit_with_status AS ( >>SELECT

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

2016-10-28 Thread Scott Marlowe
On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen wrote: > Hi > > I was wondering if there is a way to hint that two columns in two different > tables IS NOT DISTINCT FROM each other. So that the optimizer may assume if > table_a.key = 'test' THEN table_b.key = 'test' . > > The equals operator al

Re: [GENERAL] Strange? BETWEEN behaviour.

2016-10-20 Thread Scott Marlowe
On Thu, Oct 20, 2016 at 6:27 AM, Bjørn T Johansen wrote: > On Thu, 20 Oct 2016 14:04:51 +0200 > vinny wrote: > >> On 2016-10-20 13:51, Bjørn T Johansen wrote: >> > I have the following SQL: >> > >> > SELECT * from table WHERE date BETWEEN to_timestamp('20.10.2016 >> > 00:00:00','DD.MM. HH24:M

Re: [GENERAL] Multiple multithreaded insert

2016-10-14 Thread Scott Marlowe
On Fri, Oct 14, 2016 at 7:12 AM, Арсен Арутюнян wrote: > Hi, everyone! > > I have a table: > > create table testpr(id serial,priority integer,unique(priority) DEFERRABLE, > primary key(id)); > This: > and a trigger which, when added to this table, automatically sets priority > as the maximum val

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

2016-09-12 Thread Scott Marlowe
On Sun, Sep 11, 2016 at 3:26 AM, Patrick B wrote: > > > 2016-09-11 14:09 GMT+12:00 Jim Nasby : >> >> 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 >>>

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-08 Thread Scott Marlowe
On Tue, Sep 6, 2016 at 5:25 PM, John R Pierce wrote: > On 9/6/2016 4:20 PM, Melvin Davidson wrote: >> >> If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on >> the slave. > > > does rackspace support slony? how about amazon dms ? > > slony requires configuring replication on

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

2016-09-08 Thread Scott Marlowe
On Wed, Sep 7, 2016 at 5:00 PM, Jim Nasby wrote: > On 9/2/16 11:44 AM, David Gibbons wrote: >> >> rsync -va /var/lib/pgsql/ /var/lib/pgsql2/ >> service postgres stop >> rsync -va /var/lib/pgsql/ /var/lib/pgsql2/ >> >> The second rsync will only copy the deltas from the first, it still has >> to go

Re: [GENERAL] PostgreSQL Database performance

2016-09-06 Thread Scott Marlowe
On Tue, Sep 6, 2016 at 1:18 PM, Steve Atkins wrote: > >> On Sep 6, 2016, at 12:08 PM, Scott Marlowe wrote: >> >> On Fri, Sep 2, 2016 at 9:38 PM, Pradeep wrote: >>> >>> max_connections = 100 >>> shared_buffers = 512MB >>> effective_cache_

Re: [GENERAL] PostgreSQL Database performance

2016-09-06 Thread Scott Marlowe
On Fri, Sep 2, 2016 at 9: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. > > > > Example: I am using Windows 2008 R2 server .F

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

2016-09-02 Thread Scott Marlowe
On Thu, Sep 1, 2016 at 8:48 PM, Patrick B wrote: > Hi guys, > > I'll be performing a migration on my production master database server, > which is running PostgreSQL 9.2 atm, from SATA disks to SSD disks. > I've got some questions about it, and it would be nice if u guys could share > your experie

Re: [GENERAL] What limits Postgres performance when the whole database lives in cache?

2016-09-02 Thread Scott Marlowe
On Fri, Sep 2, 2016 at 4:49 AM, dandl wrote: > Re this talk given by Michael Stonebraker: > > http://slideshot.epfl.ch/play/suri_stonebraker > > > > He makes the claim that in a modern ‘big iron’ RDBMS such as Oracle, DB2, MS > SQL Server, Postgres, given enough memory that the entire database liv

Re: [GENERAL] ON CONFLICT does not support deferrable unique constraints

2016-08-24 Thread Scott Marlowe
On Wed, Aug 24, 2016 at 6:05 AM, Andreas Joseph Krogh wrote: > > Hope some -hackers read this... > > Are there any plans to lift this restriction? I'm trying to figure out a method for making this work in my head. These two things seem kind of opposed to each other. -- Sent via pgsql-general m

Re: [GENERAL] Uber migrated from Postgres to MySQL

2016-07-28 Thread Scott Marlowe
On Thu, Jul 28, 2016 at 11:23 AM, Alex Ignatov wrote: > > On 28.07.2016 19:43, Scott Marlowe wrote: >> >> On Thu, Jul 28, 2016 at 10:32 AM, Alex Ignatov >> wrote: >>> >>> Oh, so in contrast to "Oracle world" "Postgres world" DBA i

Re: [GENERAL] Uber migrated from Postgres to MySQL

2016-07-28 Thread Scott Marlowe
On Thu, Jul 28, 2016 at 10:32 AM, Alex Ignatov wrote: > > Oh, so in contrast to "Oracle world" "Postgres world" DBA in their right > to do major upgrade without complete and tested backup? > Ok, I understand you. In Postgres world there always sky is blue and sun is > shining. Of course we hav

Re: [GENERAL] Uber migrated from Postgres to MySQL

2016-07-28 Thread Scott Marlowe
On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless wrote: > On 27 July 2016 at 15:22, Scott Mead wrote: >> >> "The bug we ran into only affected certain releases of Postgres 9.2 and >> has been fixed for a long time now. However, we still find it worrisome that >> this class of bug can happen at al

Re: [GENERAL] MediaWiki + PostgreSQL is not ready for production?

2016-07-20 Thread Scott Marlowe
On Mon, Jul 18, 2016 at 10:14 PM, Tatsuo Ishii wrote: > I found following comment for using PostgreSQL with MediaWiki: > > https://www.mediawiki.org/wiki/Compatibility#Database > > "Anything other than MySQL or MariaDB is not recommended for > production use at this point." > > This is a sad and d

Re: [GENERAL] psql connection option: statement_timeout

2016-07-03 Thread Scott Marlowe
correction: alter user reporting set statement_timemout=60 is handy for users that should never take a long time to connect. should read alter user reporting set statement_timemout=60 is handy for users that should never take a long time to run a statement. -- Sent via pgsql-general mailing l

Re: [GENERAL] psql connection option: statement_timeout

2016-07-03 Thread Scott Marlowe
On Sun, Jul 3, 2016 at 5:15 PM, Melvin Davidson wrote: > > > On Sun, Jul 3, 2016 at 6:54 PM, Craig Boyd wrote: > >> Hello All, >> >> I am something of a newbie and I am trying to understand how to pass >> connection options using the psql client. My understanding is that it is >> possible to do

Re: [GENERAL] Replication with non-read-only standby.

2016-06-30 Thread Scott Marlowe
On Thu, Jun 30, 2016 at 7:15 AM, Nick Babadzhanian wrote: > Setup: > 2 PostgreSQL servers are geographically spread. The first one is used for an > application that gathers data. It is connected to the second database that is > used to process the said data. Connection is not very stable nor is

Re: [GENERAL] Stored procedure version control

2016-06-29 Thread Scott Marlowe
On Wed, Jun 29, 2016 at 12:00 PM, Adrian Klaver wrote: > On 06/29/2016 09:37 AM, Mark Morgan Lloyd wrote: >> >> Elsewhere, somebody was asking how people implemented version control >> for stored procedures on (MS) SQL Server. >> >> The consensus was that this is probably best managed by using scr

Re: [GENERAL] Vacuum full: alternatives?

2016-06-20 Thread Scott Marlowe
On Mon, Jun 20, 2016 at 3:18 AM, Job wrote: > > Hello, > > we have a table with an heavy traffic of pg_bulkload and delete of records. > The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back. > > We have important problems on size and the only way to gain free space is > is

Re: [GENERAL] OT hardware recommend

2016-06-17 Thread Scott Marlowe
On Fri, Jun 17, 2016 at 2:36 PM, Andy Colson wrote: > Hi all. > > I have access to quite a few laptop HD's (10 to 15 of them at least), and > thought that might make a neat test box that might have some good IO speed. > > Needs to be cheap though, so linux with software raid, rack mount > preferr

Re: [GENERAL] Silent data loss in its pure form

2016-05-30 Thread Scott Marlowe
On Mon, May 30, 2016 at 10:57 AM, Alex Ignatov wrote: > Following this bug reports from redhat > https://bugzilla.redhat.com/show_bug.cgi?id=845233 > > it rising some dangerous issue: > > If on any reasons you data file is zeroed after some power loss(it is the > most known issue on XFS in the pas

Re: [GENERAL] Scaling Database for heavy load

2016-05-11 Thread Scott Marlowe
On Wed, May 11, 2016 at 4:09 AM, Digit Penguin wrote: > Hello, > > > we use PostgreSql 9.x in conjunction with BIND/DNS for some Companies with > about 1.000 queries per second. > Now we have to scale the system up to 100.000 queries per second (about). > > Bind/DNS is very light and i think can n

Re: [GENERAL] I/O - Increase RAM

2016-04-13 Thread Scott Marlowe
On Wed, Apr 13, 2016 at 2:43 PM, drum.lu...@gmail.com wrote: > > Hi all, > > At the moment I'm having 100% I/O during the day. My server has SATA HDs, > and it can't be changed now. > So, to solve the problem (or at least try) I was thinking about double the > RAM, and by doing that, increasing t

Re: [GENERAL] PostgreSQL advocacy

2016-03-22 Thread Scott Marlowe
On Tue, Mar 22, 2016 at 9:15 AM, Thomas Kellerer wrote: > Bruce Momjian schrieb am 22.03.2016 um 16:07: >> >> However, I do think database upgrades are easier with Oracle RAC > > I think you can do a rolling upgrade with a standby, but I'm not entirely > sure. I find Slony good for upgrading ver

Re: [GENERAL] PostgreSQL advocacy

2016-03-21 Thread Scott Marlowe
On Mon, Mar 21, 2016 at 7:44 AM, Mark Morgan Lloyd wrote: > If anybody puts together a "just the facts" document after Oracle's attack > on PostgreSQL in Russia, please make sure it's drawn to the attention of > this mailing list for the benefit of those who aren't in -advocacy. > > I was discussi

Re: [GENERAL] Regarding connection drops for every few minutes

2016-03-11 Thread Scott Marlowe
On Fri, Mar 11, 2016 at 3:22 AM, Durgamahesh Manne wrote: > Hi Sir > > As per above discussion.GOOD response from PostgreSQL > > i am very happy to work on PostgreSQL.Super fast response only from postgres > team regarding i asked any question related to postgres > > regards > mahesh > > On Fr

Re: [GENERAL] Regarding connection drops for every few minutes

2016-03-11 Thread Scott Marlowe
On Fri, Mar 11, 2016 at 2:22 AM, Durgamahesh Manne wrote: > hi > > the following info generating in pg_log > > LOG: could not receive data from client: Connection reset by peer > LOG: could not receive data from client: Connection reset by peer > LOG: could not receive data from client: Connect

Re: [GENERAL] Cannot create role, no default superuser role exists

2016-03-10 Thread Scott Marlowe
For future reference you can start the postmaster in single user mode and create a new postgres account or grant the current one superuser access if this happens again. Just google "Postgresql single user mode". -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make change

  1   2   3   4   5   6   7   8   9   10   >