RE: Any community members in Phoenix, AZ?

[Will LaShell]

Josh,

I'm out in the southeast valley (Chandler / Gilbert)  as well.  We can likely 
help with the facilities portion. Hit me up as well!

(Probably should change my email address to my company email)

Sincerely,

Will LaShell

-Original Message-
From: Joshua D. Drake [mailto:j...@commandprompt.com] 
Sent: Tuesday, January 23, 2018 4:14 PM
To: pgsql-general@lists.postgresql.org
Subject: Any community members in Phoenix, AZ?

Folks,

We have a Postgres meetup we have been trying to get off the ground in
Phoenix:

https://www.meetup.com/Phoenix-Postgres/

We could use some help with facilities and speakers. Do we have any locals down 
there on this list? It would be great to get a team together to help with this 
newer meetup. We are having lots of success with organization of multiple 
groups but boots on the ground is always the hardest of the variables to solve 
for.

Thanks,

JD

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

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

Re: Any community members in Phoenix, AZ?

[David G. Johnston]

On Tue, Jan 23, 2018 at 4:14 PM, Joshua D. Drake 
wrote:

> Folks,
>
> We have a Postgres meetup we have been trying to get off the ground in
> Phoenix:
>
> https://www.meetup.com/Phoenix-Postgres/
>
> We could use some help with facilities and speakers. Do we have any locals
> down there on this list? It would be great to get a team together to help
> with this newer meetup. We are having lots of success with organization of
> multiple groups but boots on the ground is always the hardest of the
> variables to solve for.
>

​I'm in the extreme South East area, Queen Creek / San Tan Valley.  I'm
more likely to be in a chair than at the podium though.

David J.​

Re: Is it possible to sort strings in EBCDIC order in PostgreSQL server?

[Jeremy Schneider]

On 12/12/17 10:21, Peter Geoghegan wrote:
> ICU supports creating custom collations that reorder upper and lower
> case, or digits with scripts (e.g. Latin alphabet characters). See the
> documentation -- "23.2.2.3.2. ICU collations". Advanced customization
> is possible.

I just gave this a try and I'm not sure I completely understand what
it's doing but it definitely doesn't look like it's sorting according to
EBCDIC byte orders. (This was on centos 7 with libicu-50.1.2) I might
have created the collation incorrectly. I'd love to hear if anyone else
finds a way to get this working outside of custom comparison operators.

pops-10.1 root@db1=# create collation "ebcdic" (provider=icu,
locale='cp037');
CREATE COLLATION

pops-10.1 root@db1=# create table test (data text);
CREATE TABLE

pops-10.1 root@db1=# insert into test
values('a'),('b'),('A'),('B'),('1'),('2'),('!'),('^');
INSERT 0 8

pops-10.1 root@db1=# select * from test order by data collate "ebcdic";
 data
--
 !
 ^
 1
 2
 a
 A
 b
 B
(8 rows)


-- 
http://about.me/jeremy_schneider

Any community members in Phoenix, AZ?

[Joshua D. Drake]

Folks,

We have a Postgres meetup we have been trying to get off the ground in 
Phoenix:


https://www.meetup.com/Phoenix-Postgres/

We could use some help with facilities and speakers. Do we have any 
locals down there on this list? It would be great to get a team together 
to help with this newer meetup. We are having lots of success with 
organization of multiple groups but boots on the ground is always the 
hardest of the variables to solve for.


Thanks,

JD

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

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

Re: FW: Setting up streaming replication problems

[Martin Goodson]

On 23/01/2018 18:08, Pavan Teja wrote:



On Jan 23, 2018 11:34 PM, "Martin Goodson" > wrote:




On 23/01/2018 07:36, Thiemo Kellner, NHC Barhufpflege wrote:

1) I am not sure whether to put the md5 value of the repuser
password

into primary conninfo or the plain one. I don't feel
the documentation
or the book is clear on that.


Anyone two dimes on that?

Password, not an md5. Or, at least, that's what's been working for
me :)

Even better, I guess, would be to not put a password in there at
all and use a .pgpass.

-- 
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."


Hello,

Instead you can keep trust connection for that user from only that ip 
instead specifying in  .Pgpass file.



Absolutely you can do that, yup. But I'm super paranoid, and don't trust 
anyone enough to use 'trust' :)


M.

--
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."

Re: Hardware advice

[Tomas Vondra]

Hi,

On 01/22/2018 09:46 PM, Alban Hertroys wrote:
> Hi all,
> 
> At work we are in the process of setting up a data-warehouse using PG
> 10. I'm looking for a suitable server, but I hardly know anything
> about server-grade hardware.
> 
> Hence, we're looking for some advice, preferably with some
> opportunity to discuss our situation and possibly things we did not
> take into account etc. A face to talk to would be appreciated. Who
> provides that in or near the eastern border of the Netherlands?
> 

Coincidentally, there's a big conference (FOSDEM) in that area next
week, with a smaller PostgreSQL conference (FOSDEM PgDay) the day
before. Might be a good opportunity to talk to PostgreSQL people.

If you're looking for an actual consulting, there are multiple companies
that might help you with this (including ours).

> More details:
> 
> We're planning to deploy on bare-metal hardware, with a fallback
> server with similar or lesser specs for emergencies and upgrades and
> perhaps some (read-only) load balancing of different kinds of loads.
> 
> The server will be accessed for reporting and ETL (or ELT) mostly.
> Both reporting servers (test/devel and production) are configured for
> at most 40 agents, so that's max 40 connections each to the warehouse
> for now. So far, we haven't reached that number in real loads, but
> reports are requested ~40,000 times a month (we measure HTTP requests
> minus static content).
> 
> We will also be doing ETL of (very) remote (SAP) tables to the
> warehouse server; in what we got so far in our limited test
> environment we have tables of over 30GB, most of which is from the
> last 4 to 5 years.
> 

That's nice, but it does not really tell us how much work that means for
the database :-( Those queries might be touching tiny subset of the
data, or it might touch the whole data set. That will have significant
impact on the hardware requirements.

> The biggy though is that we also plan to store factory process
> measurements on this server (temperatures, pressures, etc. at 5s
> intervals).

So, time series data. I wonder if timescale [1] would be appropriate
here (never used it, but seems to be designed for exactly this use
case). And built on PostgreSQL.

[1] http://www.timescale.com/

> Part of one factory has already been writing that data to
> a different server, but that's already 4.3 billion records (140GB)
> for about a year of measuring and that's not even half of the
> factory. We will be required to retain 10-15 years of data from
> several factories (on the short term, at least 2). The expectancy is
> that this will grow to ~15TB for our factory alone.
> 

Storing this amounts of data is not that difficult - the DL360 machines
can handle 40TB+ for example. The question is how intensive the data
access and processing will be, so that you can pick the right storage
configuration, size the amount of RAM etc.

FWIW it makes no sense to size this for 10-15 years from the get go,
because (a) you won't get it right anyway (things change over time), and
(b) you're unlikely to keep the same hardware for 10+ years.

Get smaller but more powerful hardware, plan to replace it in a couple
of years with never machines.

> We also want to keep our options for growth of this data warehouse
> open. There are some lab databases, for example, that currently exist
> as two separate brand database servers (with different major versions
> of the lab software, so there are design differences as well), that
> aren't exactly small either.
> 
> I have been drooling over those shiny new AMD Epyc processors, which
> look certainly adequate with a truckload of memory and a good RAID-10
> array and some SSD(s) for the WAL, but it's really hard to figure out
> how many cores and memory we need. Sure, 2 7601's at 64 cores and 4TB
> of memory (if that's even for sale) would probably do the trick, but
> even I think that might be going a little overboard ;)
> 

Well, you can either find out what your actual needs are (by looking at
the current system and extrapolating it in some way) and sizing the
hardware accordingly. Or you can squeeze as much money from the
management as possible, and buying the shiniest stuff out possible.

> Oh yeah, apparently we're married to HP or something… At least, IT
> management told me to look at their offerings.
> 

I'd say Proliant machines are pretty solid.


regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: FW: Setting up streaming replication problems

[Pavan Teja]

On Jan 23, 2018 11:34 PM, "Martin Goodson"  wrote:



On 23/01/2018 07:36, Thiemo Kellner, NHC Barhufpflege wrote:

> 1) I am not sure whether to put the md5 value of the repuser password
>
>> into primary conninfo or the plain one. I don't feel the documentation
>>> or the book is clear on that.
>>>
>>
> Anyone two dimes on that?
>
Password, not an md5. Or, at least, that's what's been working for me :)

Even better, I guess, would be to not put a password in there at all and
use a .pgpass.

-- 
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."


Hello,

Instead you can keep trust connection for that user from only that ip
instead specifying in  .Pgpass file.

Anything can be done as per your​convenience.

Regards,
Pavan

Re: FW: Setting up streaming replication problems

[Martin Goodson]

On 23/01/2018 07:36, Thiemo Kellner, NHC Barhufpflege wrote:

1) I am not sure whether to put the md5 value of the repuser password

into primary conninfo or the plain one. I don't feel the documentation
or the book is clear on that.


Anyone two dimes on that?

Password, not an md5. Or, at least, that's what's been working for me :)

Even better, I guess, would be to not put a password in there at all and 
use a .pgpass.


--
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."

Re: FW: Setting up streaming replication problems

[Thiemo Kellner, NHC Barhufpflege]

Andreas, thanks for your reply.


I try to set up synchronous streaming replication as try-out. I use my
laptop with Debian 9 and PostgreSQL package 10+189.pgdg90+1. And of
this PostgreSQL installation I have two clusters main (master) and
main2 (hot standby). I tried with Rigg's book and the PostgreSQL
documentation and some pages on the web, but fail miserably.


you have one cluster with 2 nodes ;-)


Ähm, right. Been more in a hurry than I thought as it shows below as well.


Note: it's a bad idea to build a synchronous cluster with only 2 nodes,
you need at least 3 nodes


I am aware that synchronous clustering might deadlock the master. To  
have a hot standby on the same metal does not make much sense either.  
This is just a try out though. The proof of me being stupid, so to  
speak. ;-)



that's the recovery.conf, not pg_hba.conf. And you don't need it on the
master.


Copy paste blunder (see above ;-))


standby_mode = 'off'
primary_conninfo = 'host=localhost user=repuser port=5433
password='
restore_command = 'false'


why that?


Uhm, cannot remember *blush*, I removed it but it made no difference.


master or standby? confused...


C blunder again. Standby.


standby_mode = 'on'
primary_conninfo = 'host=localhost user=repuser port=5433
password='


the same port as above?


I keep repeating myself: C blunder again: nope


1) I am not sure whether to put the md5 value of the repuser password
into primary conninfo or the plain one. I don't feel the documentation
or the book is clear on that.


Anyone two dimes on that?


2) Starting the clusters, I do not see any attempt of the hot standby
to connect to the master.


I put logging back to more sane info as debug did not give me any  
useful information on that (maybe due to my noobdom). But I cannot see  
any connection attempt...



are the 2 nodes running on different ports?


Yes, 5432 master, 5433 standby


You need only 1 recovery.conf, on the standby.


But does it hurt? I just had the idea that role change would be  
easier. master <-> standby



restore_command = 'false'
is useless, i'm guessing that's the reason that the standby doesn't
connect to the master.


Uhm, is it useless or does it prevent connection? Anyway, I removed it  
and it did not make a difference.



Greetings from Dresden, Andreas


:-) Greetings from Bannholz@Hochrhein :-D

--
Öffentlicher PGP-Schlüssel:  
http://pgp.mit.edu/pks/lookup?op=get=0x8F70EFD2D972CBEF



This message was sent using IMP, the Internet Messaging Program.

Re: Changing locale/charset

[Adrian Klaver]

On 01/22/2018 01:18 PM, Martin Moore wrote:

I created a 10.1 cluster on Debian using UTF8.
I’d like to convert it to LATIN1, but am having various issues. So, it’s 
probably easiest to start again (I have a dump of the DB).

To ensure I get it right, what is the correct way to create a cluster with 
LATIN1 encoding, how to remove the existing cluster and how to ensure I have 
the right settings for LOCALE etc. in the OS?


Assuming you are using the Debian pg_ctlcluster command see 'Changing 
Debian default installation' in below:


https://wiki.debian.org/PostgreSql




Cheers.






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Using random() in update produces same random value for all

[Olleg Samoylov]

On 2018-01-22 23:15, Tom Lane wrote:
>
> It is honored as volatile: it will be re-evaluated every time the
> sub-select is re-evaluated.  It's just that there's no cause to
> re-evaluate the sub-select.
>
> I poked through the SQL standard to see if it spells out the semantics
> of uncorrelated subqueries anywhere, and couldn't find anything relevant
> at all.  But this is how Postgres has understood the behavior of
> sub-selects for a very long time (~20 years).  I'm pretty certain
> that there are people depending on it to behave this way.
>
>   regards, tom lane

The cause exists, the function is volatile and according to definition 
it must be recalculated every time. But well, one more example.

=> select generate_series,(select random+generate_series from random()) 
from generate_series(1,10);
  generate_series | ?column?
-+--
    1 | 1.94367738347501
    2 | 2.94367738347501
    3 | 3.94367738347501
    4 | 4.94367738347501
    5 | 5.94367738347501
    6 | 6.94367738347501
    7 | 7.94367738347501
    8 | 8.94367738347501
    9 | 9.94367738347501
   10 |  10.943677383475
(10 rows)

As you can see, sub-select is indeed recalculated, but not random(). And 
this is may be right, because random() is used as source off data. 
Another example.

=> select generate_series,(select random()+generate_series) from 
generate_series(1,10);
  generate_series | ?column?
-+--
    1 | 1.37678202055395
    2 |  2.5316761219874
    3 | 3.33511888468638
    4 |  4.0293406387791
    5 | 5.69305071979761
    6 | 6.33374964864925
    7 | 7.14478175388649
    8 |  8.1831739502959
    9 |  9.4472619513981
   10 | 10.2977624684572
(10 rows)

Here random() is recalculated as sub-select.

But in

=> select *,(select random()) from generate_series(1,10);
  generate_series |  random
-+---
    1 | 0.487761380150914
    2 | 0.487761380150914
    3 | 0.487761380150914
    4 | 0.487761380150914
    5 | 0.487761380150914
    6 | 0.487761380150914
    7 | 0.487761380150914
    8 | 0.487761380150914
    9 | 0.487761380150914
   10 | 0.487761380150914
(10 rows)

is not.

IMHO all this behavior may be not bad, but it must be well documented in 
manual in section about sub-selects. All sub-select must be documented 
as "stable" in terms of function definition. And thus will not be surprise.