date:20171018

Re: [GENERAL] Log storage

2017-10-18 Thread Ivan Sagalaev

Can't get to hard data right now, but those are app logs that try to be 
no more than ~100 bytes characters long for readability, and also HTTP 
logs with long-ish request lines which might put it in the neighborhood 
of 2K characters.


On 10/18/2017 02:30 AM, legrand legrand wrote:


What is the (min, max, avg) size of the inserted text ?



-
PAscal
SQLeo projection manager
Senior Oracle dba migrating towards PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Table partionning : INSERT with inconsistent return ligne inserted.

2017-10-18 Thread STERBECQ Didier

Hi,

I use PostgreSQL 9.6 (9.6.3)  with table partitioning, when I use INSERT order 
psql, it does not show the number of lines inserted.
I do not see any information in the version notes from the PostgreSQL 
documentation, even with the 9.6.5 update, is it some bug ?

Here is a short test case :

--
-- table
--

CREATE TABLE t1_part

   ( id BIGINT ,
 libelle   VARCHAR(30 )
   )
   WITH ( FILLFACTOR = 70 )
   TABLESPACE data_1
;

--
-- partitions : 2.
--
CREATE TABLE t1_part_01
  ( CHECK ( id >= 1 AND id <= 1000 )
  )
  INHERITS ( t1_part ) ;

CREATE TABLE t1_part_02
  ( CHECK ( id > 1000 AND id <= 2200 )
  )
  INHERITS ( t1_part ) ;

--
-- function of partitionning
--

CREATE FUNCTION t1_part_test_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.id >= 1 AND NEW.id <= 1000 THEN
INSERT INTO t1_part_01 VALUES (NEW.*);
ELSIF NEW.id > 1000 AND NEW.id <= 2200 THEN
INSERT INTO t1_part_02 VALUES (NEW.*);
END IF ;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

--
-- trigger
--

CREATE TRIGGER insert_t1_part
BEFORE INSERT ON t1_part
FOR EACH ROW EXECUTE PROCEDURE t1_part_test_trigger();

--
-- Insert
--

hba=> insert into t1_part values (301, '301' ) ;
INSERT 0 0-- should be "INSERT 0 1"

hba=> select * from t1_part ;
   id| libelle
-+--
301 | 301
(1 row) -- proof of successful insert.

Thanks by advance.
Didier Sterbecq

Re: [GENERAL] Log storage

2017-10-18 Thread legrand legrand


What is the (min, max, avg) size of the inserted text ?



-
PAscal
SQLeo projection manager
Senior Oracle dba migrating towards PostgreSQL 
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgpass file type restrictions

2017-10-18 Thread Desidero

Hello,



I’m running into problems with the restriction on pgpass file types. When
attempting to use something like an anonymous pipe for a passfile, psql
throws an error stating that it only accepts plain files. If it matters,
I'm trying to use that so I can pass a decrypted pgpassfile into postgres
since my company is not allowed to have unencrypted credentials on disk
(yes, I know that it's kind of silly to add one layer of abstraction, but
it's an industry rule we can't avoid). I know that we can also just avoid
using psql, but there are benefits to using it for simple scripts, so if we
can make this work fairly easily we'd like to do that.



I looked around to see if I could figure out why that restriction was put
there in the first place, but the only reference I found was this entry in
the 8.2.6 release notes which I wasn’t able to trace back to anything in
particular:

Fix libpq crash when PGPASSFILE refers to a file that is not a plain file
(Martin Pitt)



I was also unable to find anything useful in the source code. There were no
comments around this snippet indicating why it was limited to plain files
(it was implemented this way back in 2005!):

https://github.com/postgres/postgres/blame/d3a0c8dce9380e77734e41becd9aa3
5618030352/src/interfaces/libpq/fe-connect.c#L3138

if (!S_ISREG(stat_buf.st_mode))

{

fprintf(stderr,


libpq_gettext("WARNING: Password file %s is not a plain file.\n"),

pgpassfile);

free(pgpassfile);

return NULL;

}



Does anyone know why it’s set up to avoid using things like anonymous pipes
(or anything but "plain files")?



Regards,

Matt

Re: [GENERAL] Log storage

2017-10-18 Thread Laurent Laborde

Friendly greetings !

You may want to take a look at a postgresql "fork" called pipelinedb :
https://www.pipelinedb.com/
https://github.com/pipelinedb/pipelinedb

I'm not working for them, not using it, but i happen to know it exist :)

*hugs*

-- 
Laurent "ker2x" Laborde

Re: [GENERAL] Finally upgrading to 9.6!

2017-10-18 Thread Ron Johnson


On 10/17/2017 11:17 AM, Tom Lane wrote:

Ron Johnson  writes:

Where can I look to see (roughly) how much more RAM/CPU/disk needed when
moving from 8.4 and 9.2?

It's entirely possible you'll need *less*, as you'll be absorbing the
benefit of several years' worth of performance improvements.  But this
is such a workload-dependent thing that there's no general answer.


XML stored in blobs (not sure whether text or bytea) and b-tree indexes.

--
World Peace Through Nuclear Pacification



--
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] Table partionning : INSERT with inconsistent return ligne inserted.

2017-10-18 Thread Vik Fearing

On 10/18/2017 10:24 AM, STERBECQ Didier wrote:
> Hi,
> 
> I use PostgreSQL 9.6 (9.6.3)  with table partitioning, when I use INSERT
> order psql, it does not show the number of lines inserted.
> 
> I do not see any information in the version notes from the PostgreSQL
> documentation, even with the 9.6.5 update, is it some bug ?

It's not really a bug, but it is quite annoying.

> CREATE FUNCTION t1_part_test_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
>     IF NEW.id >= 1 AND NEW.id <= 1000 THEN
>     INSERT INTO t1_part_01 VALUES (NEW.*);
>     ELSIF NEW.id > 1000 AND NEW.id <= 2200 THEN
>     INSERT INTO t1_part_02 VALUES (NEW.*);
>     END IF ;
>     RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;

The problem is here.  You returned null in a BEFORE trigger which
cancels the insert.  As far as PostgreSQL is concerned, you didn't
insert anything into this table (because you didn't) and so it correctly
but annoyingly returns 0.

> hba=> insert into t1_part values (301, '301' ) ;
> INSERT 0 0    -- should be “INSERT 0 1”

To get this effect, which I believe is required for Hibernate and some
other frameworks, you need to create a view with an INSTEAD OF trigger
that inserts into the table, which then get rerouted with your BEFORE
trigger.  Then you insert into the view and get the desired result.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
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] Monitoring Tool for PostgreSQL

2017-10-18 Thread Basques, Bob (CI-StPaul)

We use a PERL script to handle this sort of thing.  It’s nice this way since we 
can run them from just about anywhere.

bobb


On Oct 18, 2017, at 12:37 PM, 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 tool for PostgreSQL?

Thanks for the attention.

--
Fabrício Pedroso Jorge.

Administrador de Banco de Dados

Resumo Profissional:
http://br.linkedin.com/in/fabriciojorge

Contatos:
+ 55 91 988991116
skype: fabricio.pedroso.jorge
fpjb...@gmail.com


"Get your facts first, then you can distort them as you please. “
- Mark Twain

Re: [GENERAL] Monitoring Tool for PostgreSQL

2017-10-18 Thread Thomas Kellerer


Fabricio Pedroso Jorge schrieb am 18.10.2017 um 19:37:

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 tool for PostgreSQL?


There is nothing "official" in the sense that it's developed by the Postgres 
development group.

But OPM looks quite promising:

http://opm.io/

There is a pretty extensive list in the Postgres wiki:

https://wiki.postgresql.org/wiki/Monitoring





--
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] Finally upgrading to 9.6!

2017-10-18 Thread Joshua D. Drake


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, but why upgrade to 9.6 when you can upgrade to 
10.0?


There's no way we're going to put an x.0.0 version into production.


Then think of it as 9.7.0 but with an easier name to pronounce ;)


No .0 is going into production...



I am not sure why this is even a question. There are plenty of 
businesses that can risk the deployment of a .0 release but there are 
also *MANY THAT CAN NOT*. The proper way to do this is to have a staging 
server running the .0 release that gets beaten on by the application for 
a few months and reports anything back to the community they find.


JD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
* Unless otherwise stated, opinions are my own.   *


--
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] 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, but why upgrade to 9.6 when you can upgrade to
> 10.0?

 There's no way we're going to put an x.0.0 version into production.
>>>
>>>
>>> Then think of it as 9.7.0 but with an easier name to pronounce ;)
>>
>>
>> No .0 is going into production...
>>
>
> I am not sure why this is even a question. There are plenty of businesses
> that can risk the deployment of a .0 release but there are also *MANY THAT
> CAN NOT*. The proper way to do this is to have a staging server running the
> .0 release that gets beaten on by the application for a few months and
> reports anything back to the community they find.

In a past job I would routinely setup a slony slave running the new
version to check to make sure the new version wouldn't choke on the
data in the master etc, then start using it as a read slave after a
few months to make sure the app got along with it as a read only
source, then finally look at promoting it to master, with the option
to unpromote it should things explode. Minimal downtime for upgrades
AND a path back to the old version quickly if needed.

All while having setup dev and stage servers ahead of time to get
beaten on of course.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Monitoring Tool for PostgreSQL

2017-10-18 Thread Fabricio Pedroso Jorge

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 tool for
PostgreSQL?

Thanks for the attention.

-- 
*Fabrício Pedroso Jorge.*

Administrador de Banco de Dados

*Resumo Profissional:*
http://br.linkedin.com/in/fabriciojorge

*Contatos:*
+ 55 91 988991116
skype: fabricio.pedroso.jorge
fpjb...@gmail.com

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 tool for
> PostgreSQL?

The most commonly used monitoring tool is either zabbix or nagios
using the check_postgres perl script. That's as close to official as
I'm aware (and no, it's not "official" really, just the most common
and well supported method I'm aware of).

-- 
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] Finally upgrading to 9.6!

2017-10-18 Thread Vik Fearing

On 10/18/2017 05:57 PM, Melvin Davidson wrote:
> 
> On Wed, Oct 18, 2017 at 11:46 AM, David G. Johnston
> > wrote:
> 
> The contributors do an excellent job but the reality of this
> community is that a critical mass of people do not start seriously
> testing and using a new version until it is officially released. 
> The first couple of bug-fix releases are thus, unfortunately, likely
> to be non-trivial as the masses flex the system at scales and using
> workloads that were not known or available to the developers.  Its a
> balancing act for most and falling on the side of waiting for a few
> point releases before promoting to production is, I suspect, common.
> 
> I support the policy of using caution with regards to new versions. They
> are often thought of as "bleeding edge" for the reason described by
> David G Johnston. The fact that PostgreSQL 10 was only released this
> month is critical and therefore is should not be a production server. It
> should be used as development, or QA, at best.

No, the Betas and RC should have been used in development and QA.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
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] Log storage

2017-10-18 Thread legrand legrand

I saw a similar project on oracle that was storing (long) messages (clob).
Partionning by creation date was in place, as btree indexes to access data
per id. It was working fine for inserts, as for sélect, but purges (delete)
where not freeing space. In fact rétention was  not the  same for all
records. We changed partitionning key to deletion date ans replaced  deletes
per partition drops.

Maybe, if all your records have the same rétention , partition per création
can help for purge. Local index on création date would Aldo help.

PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
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] Finally upgrading to 9.6!

2017-10-18 Thread Igal @ Lucee.org


On 10/18/2017 6:24 AM, Ron Johnson wrote:

On 10/17/2017 11:17 AM, Tom Lane wrote:

Ron Johnson  writes:
Where can I look to see (roughly) how much more RAM/CPU/disk needed 
when

moving from 8.4 and 9.2?

It's entirely possible you'll need *less*, as you'll be absorbing the
benefit of several years' worth of performance improvements. But this
is such a workload-dependent thing that there's no general answer.


XML stored in blobs (not sure whether text or bytea) and b-tree indexes.



A bit off-topic here, but why upgrade to 9.6 when you can upgrade to 10.0?

Obviously you're not one to upgrade often so shouldn't you take 
advantage of all of the new features and improvements when "finally" (to 
use your own word) upgrading?


Igal Sapir
Lucee Core Developer
Lucee.org

[GENERAL] pg_dump throws too many command-line arguments in Postgres 10

2017-10-18 Thread Andrus

In Postgres 10   Windows 


invoking g_dump exe with

pg_dump.exe -b -f b.backup -Fc -h  -U admin -p 5432 mydb

causes error

pg_dump: too many command-line arguments (first is "-p")
Try "pg_dump --help" for more information.

How to fix this ?
In earlier versions it worked.

Andrus


--
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] Finally upgrading to 9.6!

2017-10-18 Thread Igal @ Lucee.org


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, but why upgrade to 9.6 when you can upgrade to 
10.0?


There's no way we're going to put an x.0.0 version into production.


Then think of it as 9.7.0 but with an easier name to pronounce ;)


Igal Sapir
Lucee Core Developer
Lucee.org

Re: [GENERAL] Problems with the time in data type timestamp without time zone

2017-10-18 Thread Basques, Bob (CI-StPaul)

Where is the machine running the database physically located, in another 
timezone possibly?

bobb

On Oct 18, 2017, at 10:33 AM, David G. Johnston 
> wrote:

On Wed, Oct 18, 2017 at 8:21 AM, américo bravo astroña 
> wrote:
Hi,

I have a program that saves information in a DB Postgresql need to extract data 
from date and time of that DB but when I retrieve the date and time information 
is always ahead 3 hours, the type of data that has that field is timestamp 
without time zone,

Please forgive my english I'm using translator.

A minimal SQL example of your problem would help.

David J.

Don’t ever wrestle with a pig. You’ll both get dirty, but the pig will enjoy it.
—Cale Yarborough

Re: [GENERAL] Finally upgrading to 9.6!

2017-10-18 Thread Melvin Davidson

On Wed, Oct 18, 2017 at 11:46 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Oct 18, 2017 at 8: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, but why upgrade to 9.6 when you can upgrade to
>> 10.0?
>>
>>
>> There's no way we're going to put an x.0.0 version into production.
>>
>>
>> Then think of it as 9.7.0 but with an easier name to pronounce ;)
>>
>
> The OP likely intended to say "x.0" version; which a "[9.7].0" version is
> just the same as a [10].0 version
>
> The contributors do an excellent job but the reality of this community is
> that a critical mass of people do not start seriously testing and using a
> new version until it is officially released.  The first couple of bug-fix
> releases are thus, unfortunately, likely to be non-trivial as the masses
> flex the system at scales and using workloads that were not known or
> available to the developers.  Its a balancing act for most and falling on
> the side of waiting for a few point releases before promoting to production
> is, I suspect, common.
>
> David J.
>
>
I support the policy of using caution with regards to new versions. They
are often thought of as "bleeding edge" for the reason described by David G
Johnston. The fact that PostgreSQL 10 was only released this month is
critical and therefore is should not be a production server. It should be
used as development, or QA, at best.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Finally upgrading to 9.6!

2017-10-18 Thread David G. Johnston

On Wed, Oct 18, 2017 at 8: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, but why upgrade to 9.6 when you can upgrade to
> 10.0?
>
>
> There's no way we're going to put an x.0.0 version into production.
>
>
> Then think of it as 9.7.0 but with an easier name to pronounce ;)
>

The OP likely intended to say "x.0" version; which a "[9.7].0" version is
just the same as a [10].0 version

The contributors do an excellent job but the reality of this community is
that a critical mass of people do not start seriously testing and using a
new version until it is officially released.  The first couple of bug-fix
releases are thus, unfortunately, likely to be non-trivial as the masses
flex the system at scales and using workloads that were not known or
available to the developers.  Its a balancing act for most and falling on
the side of waiting for a few point releases before promoting to production
is, I suspect, common.

David J.

Re: [GENERAL] Finally upgrading to 9.6!

2017-10-18 Thread Ron Johnson


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, but why upgrade to 9.6 when you can upgrade to 10.0?


There's no way we're going to put an x.0.0 version into production.


Then think of it as 9.7.0 but with an easier name to pronounce ;)


No .0 is going into production...

--
World Peace Through Nuclear Pacification



--
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] Finally upgrading to 9.6!

2017-10-18 Thread Ron Johnson


On 10/18/2017 09:34 AM, Igal @ Lucee.org wrote:

On 10/18/2017 6:24 AM, Ron Johnson wrote:

On 10/17/2017 11:17 AM, Tom Lane wrote:

Ron Johnson  writes:

Where can I look to see (roughly) how much more RAM/CPU/disk needed when
moving from 8.4 and 9.2?

It's entirely possible you'll need *less*, as you'll be absorbing the
benefit of several years' worth of performance improvements. But this
is such a workload-dependent thing that there's no general answer.


XML stored in blobs (not sure whether text or bytea) and b-tree indexes.



A bit off-topic here, but why upgrade to 9.6 when you can upgrade to 10.0?

Obviously you're not one to upgrade often so shouldn't you take advantage 
of all of the new features and improvements when "finally" (to use your 
own word) upgrading?




There's no way we're going to put an x.0.0 version into production.

--
World Peace Through Nuclear Pacification

Re: [GENERAL] Finally upgrading to 9.6!

2017-10-18 Thread Achilleas Mantzios


On 18/10/2017 17:34, Igal @ Lucee.org wrote:

On 10/18/2017 6:24 AM, Ron Johnson wrote:

On 10/17/2017 11:17 AM, Tom Lane wrote:

Ron Johnson  writes:

Where can I look to see (roughly) how much more RAM/CPU/disk needed when
moving from 8.4 and 9.2?

It's entirely possible you'll need *less*, as you'll be absorbing the
benefit of several years' worth of performance improvements. But this
is such a workload-dependent thing that there's no general answer.


XML stored in blobs (not sure whether text or bytea) and b-tree indexes.



A bit off-topic here, but why upgrade to 9.6 when you can upgrade to 10.0?


Had the same question, we are moving from 9.3 -> 10.0 near the start of summer 
(I hope).
10.0's pg_upgrade supports 8.4 . One reason to upgrade in smaller steps is 
maybe to grasp the new changes / features better?



Obviously you're not one to upgrade often so shouldn't you take advantage of all of the 
new features and improvements when "finally" (to use your own word) upgrading?

Igal Sapir
Lucee Core Developer
Lucee.org 



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: [GENERAL] pg_dump throws too many command-line arguments in Postgres 10

2017-10-18 Thread Paul A Jungwirth

On Wed, Oct 18, 2017 at 8:05 AM, Andrus  wrote:
> pg_dump.exe -b -f b.backup -Fc -h  -U admin -p 5432 mydb
>
> causes error
>
> pg_dump: too many command-line arguments (first is "-p")

Don't you need a hostname after -h? I think right now pg_dump thinks
your hostname is "-U", your database is "admin", and everything after
that is extra.

Yours,
Paul


-- 
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] Problems with the time in data type timestamp without time zone

2017-10-18 Thread David G. Johnston

On Wed, Oct 18, 2017 at 8:21 AM, américo bravo astroña <
americobr...@gmail.com> wrote:

> Hi,
>
> I have a program that saves information in a DB Postgresql need to extract
> data from date and time of that DB but when I retrieve the date and time
> information is always ahead 3 hours, the type of data that has that field
> is timestamp without time zone,
>
> Please forgive my english I'm using translator.
>

A minimal SQL example of your problem would help.

David J.

[GENERAL] Problems with the time in data type timestamp without time zone

2017-10-18 Thread américo bravo astroña

 Hi,

I have a program that saves information in a DB Postgresql need to extract
data from date and time of that DB but when I retrieve the date and time
information is always ahead 3 hours, the type of data that has that field
is timestamp without time zone,

Please forgive my english I'm using translator.

I appreciate your time and attention.

Best regards.

Re: [GENERAL] Finally upgrading to 9.6!

2017-10-18 Thread David G. Johnston

On Wednesday, October 18, 2017, Joshua D. Drake 
wrote:
>
> I am not sure why this is even a question. There are plenty of businesses
> that can risk the deployment of a .0 release but there are also *MANY THAT
> CAN NOT*. The proper way to do this is to have a staging server running the
> .0 release that gets beaten on by the application for a few months and
> reports anything back to the community they find.
>

The continuum goes from having a staging server follow master/HEAD to
upgrading one version once a year as the earliest supported release gets
de-supported.  The closer to the first position you are contributing back
to the community and also the more quickly you can benefit from the new
features and enhancements each new release brings.

David J.

Re: [GENERAL] Finally upgrading to 9.6!

2017-10-18 Thread Don Seiler

On Wed, Oct 18, 2017 at 1:08 PM, Vik Fearing 
wrote:

> On 10/18/2017 05:57 PM, Melvin Davidson wrote:
> >
> > I support the policy of using caution with regards to new versions. They
> > are often thought of as "bleeding edge" for the reason described by
> > David G Johnston. The fact that PostgreSQL 10 was only released this
> > month is critical and therefore is should not be a production server. It
> > should be used as development, or QA, at best.
>
> No, the Betas and RC should have been used in development and QA.

I disagree with this. It isn't my company's business to test the Postgres
software in development, as much as it would be needed and appreciated by
the community. We're testing our own applications and processes, and this
should be done with a "stable" product, more or less. So I'd only ever
think to have them use an official release versus a beta or release
candidate.

That said, count me in the same camp with the "Never .0" folks. I'm
planning a mass upgrade to 9.6 soon as well and the question was raised as
to whether or not to go right to 10.0, and I quickly put that down. Oracle
DBAs have a similar rule of thumb with anything less than .2 (Oracle starts
at .1).

Don.

-- 
Don Seiler
www.seiler.us

[GENERAL] Log storage

2017-10-18 Thread Ivan Sagalaev


Hello everyone,

An inaugural poster here, sorry if I misidentified a list for my question.

I am planning to use PostgreSQL as a storage for application logs (lines 
of text) with the following properties:


- Ingest logs at high rate: 3K lines per second minimum, but the more 
the better as it would mean we could use one Postgres instance for more 
than one app.


- Only store logs for a short while: days, may be weeks.

- Efficiently query logs by an arbitrary time period.

- A "live feed" output, akin to `tail -f` on a file.

For context, I only used Postgres for a bog standard read-heavy web 
apps, so I'm completely out of expertise for such a case. Here are my 
questions:


- Is it even possible/advisable to use an actual ACID RDBMS for such a 
load? Or put another way, can Postgres be tuned to achieve the required 
write throughput on some mid-level hardware on AWS? May be at the 
expense of sacrificing transaction isolation or something…


- Is there an efficient kind of index that would allow me to do `where 
'time' between ... ` on a constantly updated table?


- Is there such a thing as a "live cursor" in Postgres for doing the 
`tail -f` like output, or I should just query it in a loop (and skip 
records if the client can't keep up)?


Thanks in advance for all the answers!


--
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] Finally upgrading to 9.6!

2017-10-18 Thread Joshua D. Drake

On 10/18/2017 11:17 AM, Don Seiler wrote:
On Wed, Oct 18, 2017 at 1:08 PM, Vik Fearing 
> wrote:

On 10/18/2017 05:57 PM, Melvin Davidson wrote:
>
> I support the policy of using caution with regards to new versions. They
> are often thought of as "bleeding edge" for the reason described by
> David G Johnston. The fact that PostgreSQL 10 was only released this
> month is critical and therefore is should not be a production server. It
> should be used as development, or QA, at best.

No, the Betas and RC should have been used in development and QA.

I disagree with this. It isn't my company's business to test the 
Postgres software in development, as much as it would be needed and 
appreciated by the community.

Exactly.

JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
* Unless otherwise stated, opinions are my own.   *

--
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] Finally upgrading to 9.6!

2017-10-18 Thread David G. Johnston

On Wed, Oct 18, 2017 at 2:34 PM, Don Seiler  wrote:

> On Wed, Oct 18, 2017 at 4:17 PM, Vik Fearing 
> wrote:
>
>> On 10/18/2017 08:17 PM, Don Seiler wrote:
>>
>> > I disagree with this. It isn't my company's business to test the
>> > Postgres software in development, as much as it would be needed and
>> > appreciated by the community.
>>
>> Yeah, let others do it for you!  Great attitude.
>>
>
> It's a realistic, practical attitude. I'm sorry that not every company
> wants to offer the resources to contribute back to the community as much as
> you want. But it's foolish to expect a company to perform their development
> lifecycle against betas and RCs. They have their own products to worry
> about. A gallant few may let their DBAs do some sandbox testing to
> contribute time back to the community, but you can't expect them to.
>

Both sides have made their point here - any more opinions or
justifications are going to just end up devolving into commentary that is
unacceptable on these lists.  The community benefits from people who do
more than just run production servers while the business world has limited
resources to do not directly business related activities.  I feel that
those familiar with those dynamics are not surprised that someone would
choose to upgrade to 9.6.5 now since the 10.x series is still .0

If someone wants to espouse the business benefits of running pre-release
versions in staging environments against stable business code please start
a new thread focused on the "process" and not the "people".

David J.

Re: [GENERAL] Finally upgrading to 9.6!

2017-10-18 Thread Christopher Browne

On 18 October 2017 at 17:17, Vik Fearing  wrote:
> On 10/18/2017 08:17 PM, Don Seiler wrote:
>> On Wed, Oct 18, 2017 at 1:08 PM, Vik Fearing
>> > wrote:
>>
>> On 10/18/2017 05:57 PM, Melvin Davidson wrote:
>> >
>> > I support the policy of using caution with regards to new versions. 
>> They
>> > are often thought of as "bleeding edge" for the reason described by
>> > David G Johnston. The fact that PostgreSQL 10 was only released this
>> > month is critical and therefore is should not be a production server. 
>> It
>> > should be used as development, or QA, at best.
>>
>> No, the Betas and RC should have been used in development and QA.
>>
>> I disagree with this. It isn't my company's business to test the
>> Postgres software in development, as much as it would be needed and
>> appreciated by the community.
>
> Yeah, let others do it for you!  Great attitude.

We need *some* people doing this; I don't think beating people up for
not being "beta testers" is terribly friendly.

The one thing I'd want to poke at is "don't avoid 10.0 simply because of
there being a 0 there."

If someone would have declined to use 9.7.0, considering that "too beta,"
then it's reasonable to decline 10.0, as that is a reasonably similar policy.

On the other hand, if you'd have accepted 9.7.0, and are declining
10.0.0 just "because too many 0's", that's not so right, and I'd want
to encourage considering it.

>> We're testing our own applications and
>> processes, and this should be done with a "stable" product, more or
>> less. So I'd only ever think to have them use an official release versus
>> a beta or release candidate.
>
> And how do you think the product becomes stable?  By magic?
>
>> That said, count me in the same camp with the "Never .0" folks.
>
> Yes, I gathered that.
>
>> I'm planning a mass upgrade to 9.6 soon as well and the question was raised
>> as to whether or not to go right to 10.0, and I quickly put that down.
>
> Right, because when you say "official release versus a beta or release
> candidate", you don't actually mean it.

I'm inclined to wait a bit on 10.0, not because I expect it to be
particularly problematic, but because it's fairly reasonable to expect
other things to not be ready instantaneously.  And that includes some
things one might reasonably hope for.

- We have a Slony release that supports 10.0, but were there to be
  some lag, some people might be inclined to wait for that.

- The same applies to any number of interesting third party
  applications or libraries.

- Might want to wait until binaries are included in Debian Stable
  or some favored Red Hat or CentOS release or such.

There's plenty of reasonable arguments for waiting a bit.

I'd contend that "otta be running 10.0 in Dev/QA" isn't so
obviously apropos.  I tend to have my workstation running
"bleeding edge" versions of things so that I notice
sharp edges early, but what we always want to have most
folks running are the *same* versions in Dev+QA+Prod,
so that there aren't unexpected differences.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


-- 
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] Finally upgrading to 9.6!

2017-10-18 Thread Don Seiler

On Wed, Oct 18, 2017 at 4:17 PM, Vik Fearing 
wrote:

> On 10/18/2017 08:17 PM, Don Seiler wrote:
>
> > I disagree with this. It isn't my company's business to test the
> > Postgres software in development, as much as it would be needed and
> > appreciated by the community.
>
> Yeah, let others do it for you!  Great attitude.
>

It's a realistic, practical attitude. I'm sorry that not every company
wants to offer the resources to contribute back to the community as much as
you want. But it's foolish to expect a company to perform their development
lifecycle against betas and RCs. They have their own products to worry
about. A gallant few may let their DBAs do some sandbox testing to
contribute time back to the community, but you can't expect them to.

> > I'm planning a mass upgrade to 9.6 soon as well and the question was
> raised
> > as to whether or not to go right to 10.0, and I quickly put that down.
>
> Right, because when you say "official release versus a beta or release
> candidate", you don't actually mean it.

I don't even know what you mean here. You're responding like I ran over
your dog and it's quite ridiculous.

Plain and simple, I wouldn't expect any DBA responsible for production
databases to run on a new major release, regardless of platform/vendor.
It's asking for a headache and maybe a few noisy pager nights. It doesn't
matter how much faith I have in the Postgres contributors/developers, I
have a responsibility to my employer to keep their database platforms up
and running. That is first and foremost. I'm sure if I found myself with
time to spare, I'll test upgrading a prod clone to 10 and asking some devs
to run it through its paces, but spare time is a luxury that you can't just
expect people to have.

-- 
Don Seiler
www.seiler.us

Re: [GENERAL] Monitoring Tool for PostgreSQL

2017-10-18 Thread Martin Goodson


On 18/10/2017 18:37, 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 tool for PostgreSQL?


Thanks for the attention.


Not an 'official' tool, but if you're familiar with OEM then EnterpriseDB does 
PostgreSQL Enterprise Manager.

https://www.enterprisedb.com/products/edb-postgres-platform/edb-postgres-enterprise-managerpem


--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."



--
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] Finally upgrading to 9.6!

2017-10-18 Thread Gavin Flower

On 19/10/17 10:34, Don Seiler wrote:
On Wed, Oct 18, 2017 at 4:17 PM, Vik Fearing 
> wrote:

On 10/18/2017 08:17 PM, Don Seiler wrote:

> I disagree with this. It isn't my company's business to test the
> Postgres software in development, as much as it would be needed and
> appreciated by the community.

Yeah, let others do it for you!  Great attitude.

It's a realistic, practical attitude. I'm sorry that not every company 
wants to offer the resources to contribute back to the community as 
much as you want. But it's foolish to expect a company to perform 
their development lifecycle against betas and RCs. They have their own 
products to worry about. A gallant few may let their DBAs do some 
sandbox testing to contribute time back to the community, but you 
can't expect them to.
Actually that attitude is short sighted, as your company might trigger 
problems no one else has (or at least not prepared to report bugs on).  
Surely you want such bugs fixed BEFORE you use pg in production???

It is also likely to take time to know how best to use the changes in a 
newer version of pg for your database in your operational environment.

So it is in the best self interests of your company to to test 
development versions of pg prior to final release.  Helping others, in 
this context, is a more sophisticated form of selfishness than you 
display at the moment.

Cheers,
Gavin

> I'm planning a mass upgrade to 9.6 soon as well and the question
was raised
> as to whether or not to go right to 10.0, and I quickly put that
down.

Right, because when you say "official release versus a beta or release
candidate", you don't actually mean it.

I don't even know what you mean here. You're responding like I ran 
over your dog and it's quite ridiculous.

Plain and simple, I wouldn't expect any DBA responsible for production 
databases to run on a new major release, regardless of 
platform/vendor. It's asking for a headache and maybe a few noisy 
pager nights. It doesn't matter how much faith I have in the Postgres 
contributors/developers, I have a responsibility to my employer to 
keep their database platforms up and running. That is first and 
foremost. I'm sure if I found myself with time to spare, I'll test 
upgrading a prod clone to 10 and asking some devs to run it through 
its paces, but spare time is a luxury that you can't just expect 
people to have.

--
Don Seiler
www.seiler.us 

--
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] Finally upgrading to 9.6!

2017-10-18 Thread Vik Fearing

On 10/18/2017 08:17 PM, Don Seiler wrote:
> On Wed, Oct 18, 2017 at 1:08 PM, Vik Fearing
> > wrote:
> 
> On 10/18/2017 05:57 PM, Melvin Davidson wrote:
> >
> > I support the policy of using caution with regards to new versions. They
> > are often thought of as "bleeding edge" for the reason described by
> > David G Johnston. The fact that PostgreSQL 10 was only released this
> > month is critical and therefore is should not be a production server. It
> > should be used as development, or QA, at best.
> 
> No, the Betas and RC should have been used in development and QA.
> 
> 
> I disagree with this. It isn't my company's business to test the
> Postgres software in development, as much as it would be needed and
> appreciated by the community.

Yeah, let others do it for you!  Great attitude.

> We're testing our own applications and
> processes, and this should be done with a "stable" product, more or
> less. So I'd only ever think to have them use an official release versus
> a beta or release candidate.

And how do you think the product becomes stable?  By magic?

> That said, count me in the same camp with the "Never .0" folks.

Yes, I gathered that.

> I'm planning a mass upgrade to 9.6 soon as well and the question was raised
> as to whether or not to go right to 10.0, and I quickly put that down.

Right, because when you say "official release versus a beta or release
candidate", you don't actually mean it.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

37 matches

Mail list logo