RE: [GENERAL] Postgre compatible version with RHEL 7.5

[Deepti Sharma S]

Hi Team,

Can you please confirm if PostgreSQL version 9.4.x is compatible with RHEL7.5?


DEEPTI SHARMA 
Specialist 
ITIL 2011 Foundation Certified 
BDGS, R&D

Ericsson
3rd Floor, ASF Insignia - Block B Kings Canyon,
Gwal Pahari, Gurgaon, Haryana 122 003, India
Phone 0124-6243000
deepti.s.sha...@ericsson.com
www.ericsson.com 


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Wednesday, May 23, 2018 6:44 PM
To: Deepti Sharma S ; David G. Johnston 

Cc: pgsql-gene...@postgresql.org
Subject: Re: [GENERAL] Postgre compatible version with RHEL 7.5

On 05/23/2018 03:59 AM, Deepti Sharma S wrote:
> Hi David,
> 
> “9.6.6 is compatible but not supported”, what does this means?
For details see:

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

Basically it is supported by the community, but keeping up to date with the 
latest minor release(9.6.9) is strongly advised. The bigger issue is that the 
repo is going to be at the latest release.

> 
> Ericsson 
> 
> *DEEPTI SHARMA *
> Specialist
> ITIL 2011 Foundation Certified
> BDGS, R&D
> 
> 
> *Ericsson*
> 3rd Floor, ASF Insignia - Block B Kings Canyon, Gwal Pahari, Gurgaon, 
> Haryana 122 003, India Phone 0124-6243000 deepti.s.sha...@ericsson.com 
> 
> www.ericsson.com 
> 
> *From:*David G. Johnston [mailto:david.g.johns...@gmail.com]
> *Sent:* Monday, May 21, 2018 6:54 PM
> *To:* Deepti Sharma S 
> *Cc:* Adrian Klaver ; 
> pgsql-gene...@postgresql.org
> *Subject:* Re: [GENERAL] Postgre compatible version with RHEL 7.5
> 
> On Sun, May 20, 2018 at 10:15 PM, Deepti Sharma S 
> mailto:deepti.s.sha...@ericsson.com>> wrote:
> 
> Hello Team,
> 
> Can you please let us know what postgre version is compatible with
> RHEL7.5? We are currently using Postgre version 9.6.6.
> 
> ​9.6.6 is compatible but not supported - the current supported release 
> for that 9.6 version is ​9.6.9 (more generally, the 5 releases that 
> are shown on the home page).
> 
> David J.
> 


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

PG security alerts

[Ravi Krishna]

Hi

 

Is there a place to get all PG related security alerts?  I saw this in IBM site:

 

https://www-01.ibm.com/support/docview.wss?uid=ibm10730491

 

which points to this:

 

http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2018-10915

 

>From the looks of it, it seems to be a generic libpq security issue affecting 
>all platform, not necessarily IBM PowerPC alone.

 

thanks

Re: PG security alerts

[Stephen Frost]

Greetings,

* Ravi Krishna (srkrish...@aol.com) wrote:
> Is there a place to get all PG related security alerts?  I saw this in IBM 
> site:

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

Thanks!

Stephen


signature.asc
Description: PGP signature

Trouble referencing a multi-column unique constraint by name in ON CONFLICT clause

[Charles Leifer]

Hi,

I'm running into behavior I don't understand when trying to do an UPSERT
with Postgres. The docs would seem to indicate that the conflict target of
the INSERT statement can be either an index expression or a constraint
name. However, when attempting to reference the constraint name, I get a
"column ... does not exist" error.

My first attempt was to just create a UNIQUE index, which works fine with
the constraint inference:

create table kv (key text, value text, extra text);create unique index
kv_key_value on kv(key, value);insert into kv (key, value) values
('k1', 'v1');-- this works:insert into kv (key, value, extra) values
('k1', 'v1', 'e1')
  on conflict (key, value) do update set extra=excluded.extra;
-- this does notinsert into kv (key, value, extra) values ('k1', 'v1', 'e1')
  on conflict (kv_key_value) do update set extra=excluded.extra;

Describing the above table, I see the following under "Indexes:"

"kv_key_value" UNIQUE, btree (key, value)

My second try was to put the unique constraint explicitly in the create
table:

create table kv (
  key text,
  value text,
  extra text,
  constraint kv_key_value unique(key, value));

Describing the above table, the output of "Indexes:" is slightly different
("UNIQUE CONSTRAINT" vs "UNIQUE" in previous example):

"kv_key_value" UNIQUE CONSTRAINT, btree (key, value)

However I am still unable to specify the constraint name as the conflict
target:

insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
  on conflict (kv_key_value) do update set extra=excluded.extra;
ERROR:  column "kv_key_value" does not exist
LINE 2:   on conflict (kv_key_value) do update set extra=exclude...

Am I misunderstanding something here? I totally get that I can use the
equivalent expression and rely on constraint inference, but I'd like to
know why the constraint name doesn't appear to work when the docs make it
sound like it should?

Thanks so much for your help,

Charlie

PS - StackOverflow question of the above, if anyone wants to answer there:
https://stackoverflow.com/questions/52542845/postgresql-on-conflict-with-multi-column-unique-constraint-name

Re: Trouble referencing a multi-column unique constraint by name in ON CONFLICT clause

[Tom Lane]

Charles Leifer  writes:
> I'm running into behavior I don't understand when trying to do an UPSERT
> with Postgres. The docs would seem to indicate that the conflict target of
> the INSERT statement can be either an index expression or a constraint
> name. However, when attempting to reference the constraint name, I get a
> "column ... does not exist" error.

What I see in the INSERT reference page is

where conflict_target can be one of:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ 
opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name

So you can write a parenthesized list of column names, or you can write
"ON CONSTRAINT constraint_name".  Given your second example with

create table kv (
  key text,
  value text,
  extra text,
  constraint kv_key_value unique(key, value));

either of these work for me:

regression=# insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
  on conflict (key, value) do update set extra=excluded.extra;
INSERT 0 1
regression=# insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
  on conflict on constraint kv_key_value do update set extra=excluded.extra;
INSERT 0 1

regards, tom lane

Re: Trouble referencing a multi-column unique constraint by name in ON CONFLICT clause

[Charles Leifer]

Many thanks, sorry for missing something so obvious!

On Thu, Sep 27, 2018 at 1:45 PM, Tom Lane  wrote:

> Charles Leifer  writes:
> > I'm running into behavior I don't understand when trying to do an UPSERT
> > with Postgres. The docs would seem to indicate that the conflict target
> of
> > the INSERT statement can be either an index expression or a constraint
> > name. However, when attempting to reference the constraint name, I get a
> > "column ... does not exist" error.
>
> What I see in the INSERT reference page is
>
> where conflict_target can be one of:
>
> ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [
> opclass ] [, ...] ) [ WHERE index_predicate ]
> ON CONSTRAINT constraint_name
>
> So you can write a parenthesized list of column names, or you can write
> "ON CONSTRAINT constraint_name".  Given your second example with
>
> create table kv (
>   key text,
>   value text,
>   extra text,
>   constraint kv_key_value unique(key, value));
>
> either of these work for me:
>
> regression=# insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
>   on conflict (key, value) do update set extra=excluded.extra;
> INSERT 0 1
> regression=# insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
>   on conflict on constraint kv_key_value do update set
> extra=excluded.extra;
> INSERT 0 1
>
> regards, tom lane
>

Re: [GENERAL] Postgre compatible version with RHEL 7.5

[Laurenz Albe]

Deepti Sharma S wrote:
> Can you please confirm if PostgreSQL version 9.4.x is compatible with RHEL7.5?

Yes; there are even PGDG binaries on 
https://www.postgresql.org/download/linux/redhat/:

yum install 
https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-7-x86_64/pgdg-redhat94-9.4-3.noarch.rpm
yum install postgresql94-server

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Out of Memory

[Laurenz Albe]

Christoph Moench-Tegeder wrote:
> ## Laurenz Albe (laurenz.a...@cybertec.at):
> 
> > vm.overcommit_memory = 2
> > vm_overcommit_ratio = 100
> > 
> > Linux commits (swap * overcommit_ratio * RAM / 100),
> 
>   ^
>   That should be a "+".

Yes; shame on me for careless typing, and thank you for the
correction.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Lost permission

[Jan Kowalski]

Hello!

I've strange problem with Pyramid application working with postgresql
over psycopg2.

After some period of operation, my application insert new row to one of
tables with the message:

ERROR:  permission denied for relation ...

After restart of application, it starts to work properly again.

I've carefully checked that there are no GRANT/REVOKE statements related
to this table. Application connects to PostreSQL from dedicated account
using peer authentication. Restart of application doesn't change any
permissions.

The problem is related only to this one table (but this table has
biggest number of inserts).

How to diagnose this problem ?

debian stretch, PostgreSQL 9.6.10, all packages updated.

best regards
J.

Re: Lost permission

[Tom Lane]

Jan Kowalski  writes:
> I've strange problem with Pyramid application working with postgresql
> over psycopg2.
> After some period of operation, my application insert new row to one of
> tables with the message:
> ERROR:  permission denied for relation ...
> After restart of application, it starts to work properly again.

Couple of possible ideas:

* something in your app sometimes issues SET ROLE or SET SESSION
AUTHORIZATION.

* you're using a connection pooler and it sometimes reconnects you
to a connection with the wrong user ID.

Neither of these seem terribly likely, but whatever it is has to
be weird ...

regards, tom lane

Re: Out of Memory

[Rob Sargent]

> On Sep 27, 2018, at 3:45 PM, Laurenz Albe  wrote:
> 
> Christoph Moench-Tegeder wrote:
>> ## Laurenz Albe (laurenz.a...@cybertec.at):
>> 
>>> vm.overcommit_memory = 2
>>> vm_overcommit_ratio = 100
>>> 
>>> Linux commits (swap * overcommit_ratio * RAM / 100),
>> 
>>  ^
>>  That should be a "+".
> 
> Yes; shame on me for careless typing, and thank you for the
> correction.
Are there any parentheses needed in that formula?

How to maintain the csv log files in pg_log directory only for past 30 days

[Raghavendra Rao J S V]

Hi All,

Log file will be generated in *csv* format at *pg_log* directory in our
PostgreSQL. Every day we are getting one log file. We would like to
maintain only max 30 days. Which setting need to modify by us in
“postgresql.conf” in order to recycle the log files after 30 days.
-- 
Regards,
Raghavendra Rao J S V
Mobile- 8861161425

Re: Out of Memory

[Laurenz Albe]

Rob Sargent wrote:
> > Christoph Moench-Tegeder wrote:
> > > ## Laurenz Albe (laurenz.a...@cybertec.at):
> > > 
> > > > vm.overcommit_memory = 2
> > > > vm_overcommit_ratio = 100
> > > > 
> > > > Linux commits (swap * overcommit_ratio * RAM / 100),
> > > 
> > >  ^
> > >  That should be a "+".
> > 
> > Yes; shame on me for careless typing, and thank you for the
> > correction.
> 
> Are there any parentheses needed in that formula?

No.  It is swap space plus a certain percentage of RAM.

I don't know how the Linux developers came up with that
weird formula.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: How to maintain the csv log files in pg_log directory only for past 30 days

[Michael Paquier]

On Fri, Sep 28, 2018 at 10:33:30AM +0530, Raghavendra Rao J S V wrote:
> Log file will be generated in *csv* format at *pg_log* directory in our
> PostgreSQL. Every day we are getting one log file. We would like to
> maintain only max 30 days. Which setting need to modify by us in
> “postgresql.conf” in order to recycle the log files after 30 days.

If you use for example log_filename = 'postgresql-%d.log', then the
server uses one new file every day.  This truncates the contents from
the last month automatically.
--
Michael


signature.asc
Description: PGP signature

Replication Issues

[bhargav kamineni]

Hi Team,

I have configured replication using slot ,But it failed by throwing the
ERROR  *pg_basebackup: could not get transaction log end position from
server: ERROR:  requested WAL segment 00012C9D0085 has already
been removed *, which is  unexpected because i have created the slot on
master first and then issued the base backup command from slave's end
the command is
*usr/lib/postgresql/9.5/bin/pg_basebackup -U  user --max-rate='150 M'
--progress --verbose --write-recovery-conf --status-interval='10 s' -D
data_dir  -h host_ip  -p 5433*
These  are the settings on my master
archive_mode=on
archive_command='/bin/true'
wal_keep_segments=512
max_wal_senders=4
Series of steps i have followed :
1) Enabled password less authentication between master and slave
2)created slot on master (assuming it will store wal's regardless of other
settings)
3)started basebackup from slave's end
4)Issued checkpoint at master's end

Here my concern is , slave should recover WAL from replication slot but why
i got the above ERROR , Why slot removed  the requested wal file , Could
you please let me know the reason why it happened or did i miss something ?

Thanks,
Bhargav K

Re: How to maintain the csv log files in pg_log directory only for past 30 days

[Raghavendra Rao J S V]

Thanks for the prompt response.

On Fri 28 Sep, 2018, 10:55 AM Michael Paquier,  wrote:

> On Fri, Sep 28, 2018 at 10:33:30AM +0530, Raghavendra Rao J S V wrote:
> > Log file will be generated in *csv* format at *pg_log* directory in our
> > PostgreSQL. Every day we are getting one log file. We would like to
> > maintain only max 30 days. Which setting need to modify by us in
> > “postgresql.conf” in order to recycle the log files after 30 days.
>
> If you use for example log_filename = 'postgresql-%d.log', then the
> server uses one new file every day.  This truncates the contents from
> the last month automatically.
> --
> Michael
>

Re: Replication Issues

[Achilleas Mantzios]

On 28/9/18 9:00 π.μ., bhargav kamineni wrote:

Hi Team,

I have configured replication using slot ,But it failed by throwing the ERROR /pg_basebackup: could not get transaction log end position from server: ERROR:  requested WAL segment 
00012C9D0085 has already been removed /, which is  unexpected because i have created the slot on master first and then issued the base backup command from slave's end

the command is
/usr/lib/postgresql/9.5/bin/pg_basebackup -U  user --max-rate='150 M' 
--progress --verbose --write-recovery-conf --status-interval='10 s' -D data_dir 
 -h host_ip  -p 5433/


Either :
add  "-X stream" to the pg_basebackup command
or
increase wal_keep_segments on the server.


These are the settings on my master
archive_mode=on
archive_command='/bin/true'
wal_keep_segments=512
max_wal_senders=4
Series of steps i have followed :
1) Enabled password less authentication between master and slave
2)created slot on master (assuming it will store wal's regardless of other 
settings)
3)started basebackup from slave's end
4)Issued checkpoint at master's end

Here my concern is , slave should recover WAL from replication slot but why i got the above ERROR , Why slot removed  the requested wal file , Could you please let me know the reason why it happened 
or did i miss something ?


Thanks,
Bhargav K




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

Re: Replication Issues

[Laurenz Albe]

 bhargav kamineni wrote:
> Hi Team,
> 
> I have configured replication using slot ,But it failed by throwing the
> ERROR  pg_basebackup: could not get transaction log end position from server:
> ERROR:  requested WAL segment 00012C9D0085 has already been 
> removed ,
> which is  unexpected because i have created the slot on master first
> and then issued the base backup command from slave's end 
> the command is 
> usr/lib/postgresql/9.5/bin/pg_basebackup -U  user --max-rate='150 M' 
> --progress --verbose --write-recovery-conf --status-interval='10 s' -D 
> data_dir  -h host_ip  -p 5433
> These  are the settings on my master 
> archive_mode=on
> archive_command='/bin/true'
> wal_keep_segments=512
> max_wal_senders=4
> Series of steps i have followed :
> 1) Enabled password less authentication between master and slave 
> 2)created slot on master (assuming it will store wal's regardless of other 
> settings)
> 3)started basebackup from slave's end
> 4)Issued checkpoint at master's end
> 
> Here my concern is , slave should recover WAL from replication slot but why i
> got the above ERROR , Why slot removed  the requested wal file , Could you 
> please
> let me know the reason why it happened or did i miss something ?

I guess your base backup took long enough for the required WAL segments to be
removed by the time it was done.

To prevent that, create a replication slot *before* you perform pg_basebackup
and use the options "-S  -X stream" of pg_basebackup.

You then use the same slot in "recovery.conf".

That way you cannot lose any WAL.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com