Re: PostgreSQL on Amazon RDS

[Nicklas Avén]

On 5/7/19 9:25 AM, Rashmi V Bharadwaj wrote:

Hi,

That query will only give the PostgreSQL database server's IP address 
right? That is true while we do a remote access to the non-cloud 
database from another machine also.
I want to know if there is anything specific that can be used to 
determine a cloud environment.


-Rashmi

-"Nicklas Avén" > wrote: -

To: Rashmi V Bharadwaj mailto:rvbha...@in.ibm.com>>
From: "Nicklas Avén" >

Date: 07/05/2019 12:46PM
Subject: Re: PostgreSQL on Amazon RDS

Hi


You can resolv the ip-address


select inet_server_addr();


/Nicklas



On 5/7/19 8:27 AM, Rashmi V Bharadwaj wrote:

Hi,

Is there a SQL query or a database parameter setting that I can use 
from an external application to determine if the PostgreSQL database 
is on cloud (like on Amazon RDS or IBM cloud) or on a non-cloud 
on-prem environment?


Thanks,
Rashmi






Sorry I didn't reply to list last time, and I top posted. I will be 
banned :-)



Well, that is true that you will get an ip-address in both cases, but in 
one case the ip-address will point to a cloud server and in the other 
case to some other server.


What you define as a cloud server is up to you.


I don't fully understand what you are want to get. I mean the postgresql 
installation doesn't have any definition about what is a cloud server 
and what is not.


Some vendors probably have some special settings that can be detected, 
but you can install postresql on any server, hosted by you, a company, a 
company named Amazon or whatever.


From the ip-address you might be able to find out if it seems to be an 
rds server.


But I see that it seems to only give the private address in Amazon rds. 
So then it might be difficult.


So, back to your original question.

I don't think there are anything specific for a cloud installation since 
a cloud server is just a server like any other server.


If there is something special it is vendor specific.


/Nicklas

random generated string matching index in inexplicable ways

[Myles Miller]

PROBLEM:
Strings or characters generated by any random function (including pg_crypto
gen_random_bytes) are matching a string/char index in surprising ways.

Reduced down to its simplest example:

-- use random 0 or 1, plus 65, to get 'A' or 'B'

# SELECT chr(round(random())::int + 65);
chr 
-
B
(1 row)

# SELECT chr(round(random())::int + 65);
chr 
-
A
(1 row)


-- simple table for matching:
CREATE TABLE x( y char(1) primary key );
INSERT INTO x(y) VALUES ('A');
INSERT INTO x(y) VALUES ('B');


-- if I query 'A' or 'B' it works as expected

# SELECT y FROM x WHERE y = 'A';
y
---
A
(1 row)

# SELECT y FROM x WHERE y = 'B';


y
---
B
(1 row)



-- if we use random-generated 'A' or 'B', things get inexplicable

# SELECT y FROM x WHERE y = chr(round(random())::int + 65);
y 
---
A
B
(2 rows)

# SELECT y FROM x WHERE y = chr(round(random())::int + 65);
y 
---
(0 rows)

# SELECT y FROM x WHERE y = chr(round(random())::int + 65);
y 
---
B
(1 row)



I've been wrestling with this for hours. Any suggestions? Thank you.

Question on binding VIP to Postgresql instance

[Lu, Dan]

Good day,

Would you be able to help direct me to instruction or implementation step to 
bind a VIP to a Postgresql instance?

I did research and understand that Postgresql does support binding of VIP, but 
I can't find documentation to implement the solution.

Any feedback is greatly appreciated.

Dan



IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.

RE: Question on binding VIP to Postgresql instance

[Lu, Dan]

Thanks Chris!

Our Network Admin have the instruction to bind a VIP, but regarding Postgresql 
what is needed to be done?

We don't have to change any parameter?

How about "listen_addresses = ', ' # what 
IP address(es) to listen on;" or we simply use "*"?

-Original Message-
From: Chris Coutinho [mailto:c.couti...@redstack.nl]
Sent: Tuesday, May 07, 2019 8:40 AM
To: Lu, Dan ; David G. Johnston 
; Alvaro Aguayo Garcia-Rada 
; pgsql-gene...@postgresql.org
Subject: RE: Question on binding VIP to Postgresql instance

I think getting postgres to work with VIP (assuming virtual IP) is not a 
postgres issue, but rather a server/network issue.

Redhat seems to have some documentation on enabling nonlocal binding, which may 
be useful for you:

   
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/load_balancer_administration/s1-initial-setup-forwarding-vsa

Met vriendelijke groet,
REDstack BV

Chris Coutinho
Researcher/Data Analyst

Van: Lu, Dan 
Verzonden: dinsdag 7 mei 2019 14:12
Aan: David G. Johnston ; Alvaro Aguayo Garcia-Rada 
; pgsql-gene...@postgresql.org
Onderwerp: Question on binding VIP to Postgresql instance

Good day,

Would you be able to help direct me to instruction or implementation step to 
bind a VIP to a Postgresql instance?

I did research and understand that Postgresql does support binding of VIP, but 
I can't find documentation to implement the solution.

Any feedback is greatly appreciated.

Dan



IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.



IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.

RE: Question on binding VIP to Postgresql instance

[Chris Coutinho]

I think getting postgres to work with VIP (assuming virtual IP) is not a 
postgres issue, but rather a server/network issue.

Redhat seems to have some documentation on enabling nonlocal binding, which may 
be useful for you:

   
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/load_balancer_administration/s1-initial-setup-forwarding-vsa

Met vriendelijke groet,
REDstack BV

Chris Coutinho
Researcher/Data Analyst

Van: Lu, Dan  
Verzonden: dinsdag 7 mei 2019 14:12
Aan: David G. Johnston ; Alvaro Aguayo Garcia-Rada 
; pgsql-gene...@postgresql.org
Onderwerp: Question on binding VIP to Postgresql instance

Good day,

Would you be able to help direct me to instruction or implementation step to 
bind a VIP to a Postgresql instance?

I did research and understand that Postgresql does support binding of VIP, but 
I can't find documentation to implement the solution.

Any feedback is greatly appreciated.

Dan



IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.

Re: random generated string matching index in inexplicable ways

[Alban Hertroys]

> On 7 May 2019, at 13:53, Myles Miller  wrote:
> 
> PROBLEM:
> Strings or characters generated by any random function (including pg_crypto
> gen_random_bytes) are matching a string/char index in surprising ways.

I fail to see anything surprising in your examples.

> Reduced down to its simplest example:

(…)

> -- if we use random-generated 'A' or 'B', things get inexplicable
> 
> # SELECT y FROM x WHERE y = chr(round(random())::int + 65);
> y 
> ---
> A
> B
> (2 rows)

Here you got a random value in the lower range of 0..1 for the record with 
value ‘A’, so that’s a match, and one in the higher range for value ‘B’, a 
match again, so you get 2 rows.

> 
> # SELECT y FROM x WHERE y = chr(round(random())::int + 65);
> y 
> ---
> (0 rows)

Here you got a random value in the higher range for the record with value ‘A’, 
so no match, and one in the lower range for value ‘B’, no match again, so you 
get 0 rows.


> # SELECT y FROM x WHERE y = chr(round(random())::int + 65);
> y 
> ---
> B
> (1 row)

Here you got two random values in the higher range, so only the row with ‘B’ 
matches.

You could also get two random values in the lower range and only get a match 
against ‘A’.

Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: random generated string matching index in inexplicable ways [EXT]

[Myles Miller]

On Tue, May 07, 2019 at 12:17:12PM +, Daniel Perrett wrote:
> The WHERE expression (and therefore the random function) is being evaluated 
> once per row, not once per query.
> If you run this query a few times, you will see the problem:
> SELECT y, chr(round(random())::int + 65) FROM (SELECT 'A' UNION SELECT 'B') 
> x(y);

Ah! Thank you! I understand now.

Re: random generated string matching index in inexplicable ways

[Myles Miller]

> > # SELECT y FROM x WHERE y = chr(round(random())::int + 65);
> > y 
> > ---
> > A
> > B
> > (2 rows)
> Here you got a random value in the lower range of 0..1 for the record with 
> value ‘A’, so that’s a match, and one in the higher range for value ‘B’, a 
> match again, so you get 2 rows.

No, the function is returning just one letter, either 'A' or 'B', not multiple 
values.

# SELECT * FROM chr(round(random())::int + 65);
 chr
-
 B
(1 row)

# SELECT * FROM chr(round(random())::int + 65);
 chr
-
 A
(1 row)

Re: Postgres for SQL Server users

[Steven Lembark]

> Next month I'll be making a presentation about Postgres to a SQL
> Server crowd in L.A. at their SQL Saturday event.
> 
> I was wondering if anyone has any tips that are specific for SQL
> Server users?  Best features?  Known issues?  Common rebuttals?

PosgreSQL is more than a "database", it's more of an ecosystem. The
combination of open source,well-described interfacs, and simplicity
of "create extension" allows us all to develop and add features,
functionality to PG. One example: "exclude using gist" with
timestamp ranges make time-series databases nearly trivial. PostGIS
is another.

Another is that PG is written and developed by people who actually
use it. Wander through psql: things like \gset and \gexec with 
format() make introspective tasks quite doable with SQL. 

pgadmin is another nice "addon" that exists partly because PG was
designed from the ground up to support external access. Hell, throw
in EnterpriseDB if you want even more addons; there aren't third-
party SQL Server support distributions (I'm a capatilist: competition
can be Good Thing).

However much people rag on it, a *tunable* vacuum system is quite
helpful. The "I don't to worry about that with " starts
to fall apart when the system starts unncessary cleanups in the 
middle of a large daily dump... Being able to control the process
makes it a whole lot easier to forget.

Foreign Data Wrappers are a nice feature, the byproduct of a system
that was designed for open access. Instead of waiting for MS to 
decide that any one external resource is profitable, we have quite a
few quite usable interfaces that support what people need.

The combination of extensions and FDW also gives companies to add
whatever they need when they need it. If anyone tells you that "X
isn't available in PG" the simplest answer is "Fine, then add it!"
For the cost of a MS product license you can pay someone to write
whatever you want as an extension or special-purpose FDW. Pay them
for support and you can have whatever you need.

Which gets back to the ecosystem: The PG *product* includes a level
of flexability not available in SQL Server -- or Oracle for that
matter. SQL Server was heavily influenced by Sybase, which was based
on Ingres, which worked because it was an open, flexible platform.
Maybe you could just tell them PG gives them the chance to evolve
full-circle :-)


-- 
Steven Lembark 3920 10th Ave South
Workhorse Computing   Birmingham, AL 35222
lemb...@wrkhors.com+1 888 359 3508

Re: random generated string matching index in inexplicable ways

[Francisco Olarte]

On Tue, May 7, 2019 at 3:12 PM Myles Miller  wrote:
> No, the function is returning just one letter, either 'A' or 'B', not 
> multiple values.

Your random function is being evaluated ONCE FOR EACH ROW.

i.e, it's doing something like:
select y from
   ( SELECT y, chr(round(random())::int + 65) as z FROM x ) aux
WHERE y = z;
 o
with aux as ( SELECT y, chr(round(random())::int + 65) as z FROM x )
select y from aux WHERE y = z;

You may want to try something like this:

with aux as ( SELECT chr(round(random())::int + 65) as z )
select y from x,aux WHERE y = z;

( Untested, but that's the idea )

Francisco Olarte

Re: duplicate key value violates unique constraint "chinese_price_infos_pkey"

[Frank Alberto Rodriguez]

You can fix the problem with this query:

SELECT setval('chinese_price_infos_id_seq', sq.val) from ( SELECT
MAX(id) as val FROM chinese_price_infos ) sq;

But you have to search in your application because in some point the
app are inserting the id column instead of leave this task to the DB. 
If you are using some entity framework and the app fill the ID field in
a new entity in some point of the workflow, then when you save the
entity, the framework automatically saves the ID in the DB without
checking if the ID already exist.


Sheers

On Mon, 2019-05-06 at 16:40 +0530, Arup Rakshit wrote:
> Hi,
> 
> Thanks for your reply. It is automatic, my app don’t creates ID, it delegates 
> it to the DB. I am using Ruby on Rails app, where we use Postgresql. 
> 
> docking_dev=# \d chinese_price_infos;
>  Table "public.chinese_price_infos"
>Column|Type | Collation | Nullable |   
>   Default 
> -+-+---+--+-
>  id  | integer |   | not null | 
> nextval('chinese_price_infos_id_seq'::regclass)
>  created_at  | timestamp without time zone |   |  | 
>  updated_at  | timestamp without time zone |   |  | 
>  item_code   | character varying(255)  |   |  | 
>  description | character varying(255)  |   |  | 
>  unit| character varying(255)  |   |  | 
>  price_cents | integer |   |  | 
>  uuid| uuid|   |  | 
> uuid_generate_v4()
>  company_id  | uuid|   |  | 
> Indexes:
> "chinese_price_infos_pkey" PRIMARY KEY, btree (id)
> "index_chinese_price_infos_on_company_id" btree (company_id)
> 
> 
> 
> 
> Thanks,
> 
> Arup Rakshit
> a...@zeit.io
> 
> 
> 
> On 06-May-2019, at 4:38 PM, Ray O'Donnell  wrote:
> 
> On 06/05/2019 12:05, Arup Rakshit wrote:
> Every time I try to insert I get the error:
> docking_dev=# INSERT INTO "chinese_price_infos" ("item_code",
> "price_cents", "unit", "description", "company_id", "created_at",
> "updated_at") VALUES ('01GS10001', 6000, 'Lift', 'Shore Crane
> Rental', '9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06
> 10:49:03.894725', '2019-05-06 10:49:03.894725'); ERROR:  duplicate
> key value violates unique constraint "chinese_price_infos_pkey" DETAIL:  Key 
> (id)=(71165) already exists. docking_dev=# INSERT INTO
> "chinese_price_infos" ("item_code", "price_cents", "unit",
> "description", "company_id", "created_at", "updated_at") VALUES
> ('01GS10001', 6000, 'Lift', 'Shore Crane Rental',
> '9ae3f8b8-8f3f-491c-918a-efd8f5100a5e', '2019-05-06 10:49:03.894725',
> '2019-05-06 10:49:03.894725'); ERROR:  duplicate key value violates
> unique constraint "chinese_price_infos_pkey" DETAIL:  Key
> (id)=(71166) already exists.
> Then I found:
> docking_dev=# SELECT MAX(id) FROM chinese_price_infos; max  128520 (1 
> row)
> docking_dev=# SELECT nextval('chinese_price_infos_id_seq'); nextval - 
> 71164 (1 row)
> Not sure how it is out of sync. How can I fix this permanently. I ran
> vacuum analyze verbose; still same error.
> 
> 
> You can fix it by using setval() to set the sequence manually to something 
> higher than the highest current id value in the table. However, it sounds as 
> if something in the application code may be causing problems For example, 
> is something generating id values without reference to the sequence?
> 
> Ray.
> 
> 
> 
> -- 
> Raymond O'Donnell // Galway // Ireland
> r...@rodonnell.ie
> 
> 
> 
> 

PG version recommendation

[David Gauthier]

Hi:

I'm going to be requesting a PG instance supported by an IT team in a large
corp.  They will be creating the server as a VM.  We will be loading the DB
using scripts (perl/dbi) on linux, possibly using bulk loading techniques
if that's required.  Queries will come from both linux and the web, but
typically the number of concurrent users will be on the order of 10 reads,
maybe a couple writers.  < 1T total disk, no partitioning.  I will be
requesting PITR.

I need to pick a PG version in my request.  I want something that will be
stable and reliable while, of course, being able to perform well.  What
would be a good choice for PG version ?

Also, since the server will be a VM, are there any special
recommendations/suggestions might I forward in the request (install
options, tuning options, other) ?

Thanks !

Re: PG version recommendation

[Adrian Klaver]

On 5/7/19 11:52 AM, David Gauthier wrote:

Hi:

I'm going to be requesting a PG instance supported by an IT team in a 
large corp.  They will be creating the server as a VM.  We will be 
loading the DB using scripts (perl/dbi) on linux, possibly using bulk 
loading techniques if that's required.  Queries will come from both 
linux and the web, but typically the number of concurrent users will be 
on the order of 10 reads, maybe a couple writers.  < 1T total disk, no 
partitioning.  I will be requesting PITR.


I need to pick a PG version in my request.  I want something that will 
be stable and reliable while, of course, being able to perform well.  
What would be a good choice for PG version ?


The latest production release v 11. You will have community support 
until November 2023:


https://www.postgresql.org/support/versioning/

As will all suggestions test under your conditions to verify.




Also, since the server will be a VM, are there any special 
recommendations/suggestions might I forward in the request (install 
options, tuning options, other) ?


Thanks !



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

Re: PG version recommendation

[Thomas Kellerer]

David Gauthier schrieb am 07.05.2019 um 20:52:

I need to pick a PG version in my request.  I want something that
will be stable and reliable while, of course, being able to perform
well.  What would be a good choice for PG version?


There is no reason to not choose the latest version.
So I would suggest to pick Postgres 11

Re: Postgres for SQL Server users

[Brent Wood]

 I have not used SS for spatial data, but I don't have a Postgres database 
without Postgis installed. The OSGEO ecosystem and synergies with other FOSS 
GIS tools is fantastic.
And it does not stop with the Postgis extension. For time series data (anything 
from fleet management to sensor data) Postgres has the (new) TimescaleDB 
extension. I ran this very effectively with a 600,000,000 record database of 
sensor readings from a research vessel - on a $400 laptop (with an SSD) for 
testing/prototyping. The sensor data was stored in Timescaledb Hypertables & 
the location data in Postgis geometry columns in those tables. Significantly 
better performance than native Postgres.
Also consider language support for database functions... pl/R supports some 
very nice capabilities, especially supporting websites. Instead if running a 
Postgres query to return the data to plot via the web page, or storing static 
plots in your CMS that need updating when you get new data, you can use 
Postgres functions in pl/R to render the plot of the data in a file, and return 
the name of the file. The web site does no rendering, just invokes the SQL & 
displays the file that is returned. So the DB can return the data and/or the 
graphic. Back up your database & back up your functions. This paradigm can work 
very effectively...
Generally, the FOSS ecosystem around Postgres offers an incredible array of 
tools and capabilities that I don't think any other db - FOSS or not - can 
provide. I have had limited exposure to Oracle, SQL Server, Sybase, Empress, 
Teradata, Netezza, DB2, Sqlite/Spatialite, Interbase & Informix. Of these, 
Postgres & Sqlite3 (which one depends on use cases) are all I use these days.


On Tuesday, May 7, 2019, 5:36:00 PM GMT+12, Tony Shelver 
 wrote:  
 
 I have to agree on the geospatial (GIS) features.   
I converted from SQL Server to Postgresql for our extended tracking database.  
The SS geospatial feature set doesn't seem nearly as robust or complete or 
perfoirmant as that supplied by PostGIS. 
The PostGIS ecosystem of open source / 3rd party tools is also far bigger, for 
anything to do with mapping.  Openstreetmaps.org stores their world dataset on 
Postgresql / PostGIS, and there a ton of mostly open source-based tools and 
organizations that work with it or any other PostGIS data to provide a complete 
GIS solution.
My first sS implementation had me backing out of storing geographic points in 
the relevant SQL Server datatype as the performance hit during loading was just 
too big.  Doing the same thing in Postgresql / PostGIS is nardly noticeable.
Another feature in Postgres is that you are not restricted to just plpgsql as 
an internal procedural language. 

I am not an expert, but it also seems far easier to create, install and work 
with major extensions to Postgresql than SQL Server.  I found installing the 
GIS featureset in SS to be a bit of a pain back oin the day..  

On Tue, 7 May 2019 at 00:53, Michel Pelletier  
wrote:

On Mon, May 6, 2019 at 2:49 PM Adam Brusselback  
wrote:

I think the main "gotcha" when I moved from SQL Server to Postgres was I didn't 
even realize the amount of in-line t-sql I would use to just get stuff done for 
ad-hoc analysis. Postgres doesn't have a good way to emulate this. DO blocks 
cannot return resultsets, so short of creating a function and dropping it, it's 
not possible to get the same workflow.

Just ruminating here, and this has probably already been discussed in the past, 
but I've always wanted something like a 'SELECT DO [LANGUAGE ...] RETURNS 
rettype | TABLE (...) $$ RETURN [NEXT | QUERY] ... $$; but haven't had any 
serious problem with creating/dropping functions like you mentioned.
-Michel 
The lack of GUI tooling was also a huge "whoa" moment for me, which I still 
grapple with.

  

Re: PG version recommendation

[Ron]

On 5/7/19 1:52 PM, David Gauthier wrote:

Hi:

I'm going to be requesting a PG instance supported by an IT team in a 
large corp.  They will be creating the server as a VM.  We will be loading 
the DB using scripts (perl/dbi) on linux, possibly using bulk loading 
techniques if that's required.  Queries will come from both linux and the 
web, but typically the number of concurrent users will be on the order of 
10 reads, maybe a couple writers.  < 1T total disk, no partitioning. I 
will be requesting PITR.


I need to pick a PG version in my request.  I want something that will be 
stable and reliable while, of course, being able to perform well. What 
would be a good choice for PG version ?


Also, since the server will be a VM, are there any special 
recommendations/suggestions might I forward in the request (install 
options, tuning options, other) ?


Home-rolled application, or third party?  If third party, they'll have 
something to say about supported version numbers.



--
Angular momentum makes the world go 'round.

Re: PG version recommendation

[David Gauthier]

>>Home-rolled application, or third party?
Are you asking about how they do VMs ?
They already provide PG v9.6.7 , so I gather they're not averse to
supporting PG DBs.


On Tue, May 7, 2019 at 4:05 PM Ron  wrote:

> On 5/7/19 1:52 PM, David Gauthier wrote:
>
> Hi:
>
> I'm going to be requesting a PG instance supported by an IT team in a
> large corp.  They will be creating the server as a VM.  We will be loading
> the DB using scripts (perl/dbi) on linux, possibly using bulk loading
> techniques if that's required.  Queries will come from both linux and the
> web, but typically the number of concurrent users will be on the order of
> 10 reads, maybe a couple writers.  < 1T total disk, no partitioning.  I
> will be requesting PITR.
>
> I need to pick a PG version in my request.  I want something that will be
> stable and reliable while, of course, being able to perform well.  What
> would be a good choice for PG version ?
>
> Also, since the server will be a VM, are there any special
> recommendations/suggestions might I forward in the request (install
> options, tuning options, other) ?
>
>
> Home-rolled application, or third party?  If third party, they'll have
> something to say about supported version numbers.
>
>
> --
> Angular momentum makes the world go 'round.
>

Re: PG version recommendation

[Tim Cross]

I would find out if the IT team who will maintain the system are running
a specific Linux distribution, such as RHEL and just go with the PG
version that is on that distribution.

Large corp rarely have sufficient IT resources. Unless you specifically
need a particular PG version (which does not seem to be the case based
on your info), you are better off sticking with the version provided by
whatever distro they use. This will ensure reasonable updates and
patches. In corp environments, where IT resources are thin on the
ground, any custom install often results in poor patching and update
cycles because it falls outside 'standard procedures'.

With respect to hardware specifications, it really depends a lot on what
the infrastructure is. Typically, you would be better off specifying the
performance and storage (size) you require and leave it to the IT team
to work out how to best satisfy those requirements e.g. support x
concurrent connections, y Tb/Gb of storage, backup requirements i.e. how
frequent, how many versions and retention requirements. Include details
of any additional PG packages you may need/want and how/where the
database will need to be accessed from.

As you indicate the host will be a VM, it should be relatively easy to
scale up/down cpus or memory as required, unless you have special
requirements (very complex queries, very large data sets, complex data
models involving GIS, XML, etc that may exceed resources available in
their VM infrastructure).

>From your description, your database sounds fairly standard with no
unusual requirements. The number of concurrent users is low and it
sounds like it may be a new application where you probably don't yet
know where performance or resource bottlenecks will be. A standard Linux
server with 16+Gb memory and a couple of Gb for storage running PG 9.6
or higher is likely to be a reasonable starting point.

It would also be a good idea to speak to the IT team and see if they
have any procedures/policies for requesting resources. Ask them what
info they need to know and then gather that. It is unlikely to help if
yuou specify hardware requirements they cannot easily support,
especially if those requirements are really just arbitrary and based on
external recommendations from people who don't know what the
infrastructure is. Nothing frustrates IT teams more than being require
to provide over specified systems which consume valuable resources that
are never used or demand custom configurations which are unnecessary and
just add to their maintenance overheads. 

Tim

David Gauthier  writes:

> Hi:
>
> I'm going to be requesting a PG instance supported by an IT team in a large
> corp.  They will be creating the server as a VM.  We will be loading the DB
> using scripts (perl/dbi) on linux, possibly using bulk loading techniques
> if that's required.  Queries will come from both linux and the web, but
> typically the number of concurrent users will be on the order of 10 reads,
> maybe a couple writers.  < 1T total disk, no partitioning.  I will be
> requesting PITR.
>
> I need to pick a PG version in my request.  I want something that will be
> stable and reliable while, of course, being able to perform well.  What
> would be a good choice for PG version ?
>
> Also, since the server will be a VM, are there any special
> recommendations/suggestions might I forward in the request (install
> options, tuning options, other) ?
>
> Thanks !


--
Tim Cross

Re: PG version recommendation

[Ron]

No, I'm asking about the application.

On 5/7/19 3:51 PM, David Gauthier wrote:

>>Home-rolled application, or third party?
Are you asking about how they do VMs ?
They already provide PG v9.6.7 , so I gather they're not averse to 
supporting PG DBs.



On Tue, May 7, 2019 at 4:05 PM Ron > wrote:


On 5/7/19 1:52 PM, David Gauthier wrote:

Hi:

I'm going to be requesting a PG instance supported by an IT team in a
large corp.  They will be creating the server as a VM.  We will be
loading the DB using scripts (perl/dbi) on linux, possibly using bulk
loading techniques if that's required.  Queries will come from both
linux and the web, but typically the number of concurrent users will
be on the order of 10 reads, maybe a couple writers.  < 1T total
disk, no partitioning.  I will be requesting PITR.

I need to pick a PG version in my request.  I want something that
will be stable and reliable while, of course, being able to perform
well.  What would be a good choice for PG version ?

Also, since the server will be a VM, are there any special
recommendations/suggestions might I forward in the request (install
options, tuning options, other) ?


Home-rolled application, or third party?  If third party, they'll have
something to say about supported version numbers.


-- 
Angular momentum makes the world go 'round.




--
Angular momentum makes the world go 'round.

Re: PG version recommendation

[Ian Barwick]

On Wed, 8 May 2019 at 07:19, Tim Cross  wrote:

>
> I would find out if the IT team who will maintain the system are running
> a specific Linux distribution, such as RHEL and just go with the PG
> version that is on that distribution.
>

Bear in mind, depending on the OS, the default version available may be
chronically outdated and no longer supported by the community. For example
the CentOS 7 out-of-the-box PostgreSQL version is 9.2.24.


Regards

Ian Barwick

-- 
  Ian Barwick   https://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

Re: Amazon Linux Support?

[Jeremy Schneider]

> On Apr 30, 2019, at 07:16, Adrian Klaver  wrote:
> 
>> On 4/30/19 2:02 AM, Lewis Shobbrook wrote:
>> Hi Guys,
>> With the repo changes associated with the April 17 changes,
>> https://pgstef.github.io/2019/04/17/one_rpm_to_rule_them_all.html
>> It is evident that support for amazon linux has been dropped.
>> While you can try to use redhat pgdp packages, they are not
>> installable on Amazon Linux, giving the following error...
>> /etc/redhat-release is needed by pgdg-redhat-repo-42.0-4.noarch
>> I confident that the rest rpms available in the repo can be used by
>> Amazon linux, but the redhat-release dependency is painful for
>> automation with existing recipes such as postgresql in chef.
>> Before I invest the effort to produce a pull request to address this,
>> I wanted to confirm that amazon linux has been dropped and will no
>> longer, nor likely ever be supported for the rpms repo's, or is it
>> still on the road map?
> 
> You will need a community account to see the issue:
> 
> https://redmine.postgresql.org/issues/4205
> 
> The relevant part:
> 
> Updated by Devrim Gündüz 4 days ago
> 
> "
> Hi,
> 
> We dropped Amazon Linux support years ago, there were lots of compatibility 
> issues. The new repo RPMs just reflect that.
> 
> Please switch to a supported distro, or rebuild RPMs from SRPMS.
> 
> Regards, Devrim
> 

I've been using the rhel6 repo with AL without issue for a long time now.  it 
looks like they added a new dependency that breaks this, but I recently tested 
this workaround and it seems to work:
`rpm -Uvh --nodeps 
https://download.postgresql.org/pub/repos/yum/testing/11/redhat/rhel-6-x86_64/pgdg-redhat11-11-2.noarch.rpm`

the other thing is that I do is a `s/$releasever/6/g` on 
`/etc/yum.repos.d/pgdg-*.repo` -- i have this all baked into some ansible 
scripts and my scripts are working fine just now when i spun up a new 
ec2/postgres test server

-Jeremy

Sent from my TI-83

Re: Relaxing NaN/Infinity restriction in JSON fields

[Mitar]

Hi!

On Mon, May 6, 2019 at 1:21 PM Tom Lane  wrote:
> Getting us to deviate from the RFC so blatantly would be a very hard sell.
> A large part of the point of the JSON datatype is to be interoperable;
> once you give that up you may as well use some not-standard-at-all
> representation.

Python supports that, enabled by default:

https://docs.python.org/3/library/json.html#infinite-and-nan-number-values

> There is not, and never has been, any claim that JSON numbers correspond
> to the IEEE spec.

There is note [1], but yes, it does not claim that nor I claimed that.
I am just saying that the reality is that most people these days use
IEEE spec floating numbers so it is sad that those cannot be easily
stored in JSON, or a database.


Mitar

[1] https://tools.ietf.org/html/rfc7159#page-7

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m