[GENERAL] Questionaire: Common WAL write rates on busy servers.

[Andres Freund]

Hi,

I've lately seen more and more installations where the generation of
write-ahead-log (WAL) is one of the primary bottlenecks.  I'm curious
whether that's primarily a "sampling error" of mine, or whether that's
indeed more common.

The primary reason I'm curious is that I'm pondering a few potential
optimizations, and would like to have some guidance which are more and
which are less important.

Questions (answer as many you can comfortably answer):
- How many MB/s, segments/s do you see on busier servers?
- What generates the bulk of WAL on your servers (9.5+ can use
  pg_xlogdump --stats to compute that)?
- Are you seeing WAL writes being a bottleneck?OA
- What kind of backup methods are you using and is the WAL volume a
  problem?
- What kind of replication are you using and is the WAL volume a
  problem?
- What are your settings for wal_compression, max_wal_size (9.5+) /
  checkpoint_segments (< 9.5), checkpoint_timeout and wal_buffers?
- Could you quickly describe your workload?

Feel free to add any information you think is pertinent ;)

Greetings,

Andres Freund


-- 
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] Protocol 2 and query parameters support

[Tatsuo Ishii]

> On 2017-04-24 16:07:01 -0400, Rader, David wrote:
>> As Tom mentioned, it sounds like the issue is that Presto expects to only
>> use simple query, not extended query (no server-side prepared statements).
>> The JDBC driver supports setting the prepare threshold to 0 to disable
>> using server-side prepares.
>> 
>> https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold-int-
>> 
>> "If threshold is zero, server-side prepare will not be used."
> 
> Note that you can use the extended protocol without server side
> prepares...

Quite correct. Just setting the prepare threshold to 0 does not
prevent PostgreSQL from using extended queries. So the only way not
using extended queires with JDBC is setting the protocol version to 2.
I wonder why PostgreSQL JDBC does not provide similar API to ODBC...

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
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] Protocol 2 and query parameters support

[Andres Freund]

On 2017-04-24 16:07:01 -0400, Rader, David wrote:
> As Tom mentioned, it sounds like the issue is that Presto expects to only
> use simple query, not extended query (no server-side prepared statements).
> The JDBC driver supports setting the prepare threshold to 0 to disable
> using server-side prepares.
> 
> https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold-int-
> 
> "If threshold is zero, server-side prepare will not be used."

Note that you can use the extended protocol without server side
prepares...


-- 
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] Protocol 2 and query parameters support

[Rader, David]

On Sun, Apr 23, 2017 at 10:33 PM, Tatsuo Ishii  wrote:

> > Andres Freund  writes:
> >> On 2017-04-23 12:08:51 -0700, Konstantin Izmailov wrote:
> >>> Some systems (Presto) are still using protocol 2, and I need to
> understand
> >>> the scope of changes in the middleware to support protocol 2.
> >
> >> Could you provide a reference about presto using v2 protocol?  A quick
> >> search didn't turn anything up.  Presto seems a bit too new to rely on
> >> v2, given how long ago v3 has been introduced.
> >
> > There's been moderately serious discussion about dropping v2 support
> > altogether, so I sure hope there isn't anything in the wild that still
> > depends on it.
> >
> > Also, moving from v2 to v3 per se just isn't very hard, if you aren't
> > worried about making use of new-in-v3 protocol features.  I wonder
> whether
> > Konstantin is confusing v2 vs v3 with use of simple vs. extended query
> > protocol within v3.
>
> It seems Presto uses v3 protocol only when ODBC driver is used. If
> JDBC is used, it is required to set "protocolVersion=2" property of
> JDBC driver, which means v2 protocol is used I think.
>
> https://github.com/treasure-data/prestogres#limitation
>
>
As Tom mentioned, it sounds like the issue is that Presto expects to only
use simple query, not extended query (no server-side prepared statements).
The JDBC driver supports setting the prepare threshold to 0 to disable
using server-side prepares.

https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold-int-

"If threshold is zero, server-side prepare will not be used."


> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
>
> --
> 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] Postgres 9.6.2 and pg_log

[Mark Watson]

De : David G. Johnston [mailto:david.g.johns...@gmail.com]
Envoyé : Monday, April 24, 2017 3:15 PM
À : Mark Watson
Cc : (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log

On Mon, Apr 24, 2017 at 11:27 AM, Mark Watson 
> wrote:

log_destination = 'stderr' # Valid values are 
combinations of

# stderr, csvlog, syslog, and eventlog,

# depending on platform.  csvlog

# requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on# Enable capturing of stderr and 
csvlog

# into log files. Required to be on for

# csvlogs.

# (change requires restart)


​I'm out of ideas...

David J.​
Not to worry. This week I’m uninstalling and reinstalling postgres 9.6. I’ll do 
some tests and let this list know.

Mark Watson

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

[Scott Marlowe]

On Mon, Apr 24, 2017 at 12:43 PM, pinker  wrote:
> I've seen very big differences with huge_pages set to on, especially in
> context of CPU usage on multiple socket servers.
>
> You could play as well with storage options, for instance inode size and
> check if there is any advantage for your db from inlining, which is
> supported by xfs. You can find more informations here:
> http://beegfs.com/wiki/StorageServerTuning
>
> An interesting option for WAL would be to add the mount option- allocsize -
> and set it to 16MB - so the exact size of WAL segment to reduce the risk of
> fragmentation and optimal streaming write throughput.
>

All good options. Also make sure zone reclaim mode is set to 0 on big
memory machines. It's a great setting for big VM hosts but a terrible
one for file or db servers.


-- 
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] Postgres 9.6.2 and pg_log

[David G. Johnston]

On Mon, Apr 24, 2017 at 11:27 AM, Mark Watson 
wrote:

>
> log_destination = 'stderr' # Valid values are
> combinations of
>
>
> # stderr, csvlog, syslog, and eventlog,
>
>
> # depending on platform.  csvlog
>
>
> # requires logging_collector to be on.
>
>
>
> # This is used when logging to stderr:
>
> logging_collector = on# Enable capturing of stderr and
> csvlog
>
>
> # into log files. Required to be on for
>
>
> # csvlogs.
>
>
> # (change requires restart)
>
>
>
​I'm out of ideas...

David J.​

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

[pinker]

I've seen very big differences with huge_pages set to on, especially in
context of CPU usage on multiple socket servers.

You could play as well with storage options, for instance inode size and
check if there is any advantage for your db from inlining, which is
supported by xfs. You can find more informations here:
http://beegfs.com/wiki/StorageServerTuning 

An interesting option for WAL would be to add the mount option- allocsize -
and set it to 16MB - so the exact size of WAL segment to reduce the risk of
fragmentation and optimal streaming write throughput.



--
View this message in context: 
http://www.postgresql-archive.org/Block-size-recommendation-for-Red-Hat-Linux-7-2-tp5958026p5958066.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Postgres 9.6.2 and pg_log

[Mark Watson]

De : David G. Johnston [mailto:david.g.johns...@gmail.com]
Envoyé : Monday, April 24, 2017 2:18 PM
À : Mark Watson
Cc : (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log

On Mon, Apr 24, 2017 at 10:58 AM, Mark Watson 
> wrote:

The lines log_rotation_age and log_rotation_size are commented, and currently 
are:
#log_rotation_age = 1d # Automatic rotation of 
logfiles will

# happen after that time.  0 disables.
#log_rotation_size = 10MB  # Automatic rotation of 
logfiles will

# happen after that much log output.

# 0 disables.


I see from your reference article that the log_rotation_age is now in minutes, 
and I will adjust that to 1440 (1 day). I don’t know where the “1d” came from. 
I know it used to be like this in earlier versions.


​Those are fine:  See "Numeric with Unit" @​

​https://www.postgresql.org/docs/9.6/static/config-setting.html

​I think the actual missing, and (just checked) defaulted off, parameter is 
"logging_collector"

David J.
I have (copy/paste) :
log_destination = 'stderr' # Valid values are 
combinations of

# stderr, csvlog, syslog, and eventlog,

# depending on platform.  csvlog

# requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on# Enable capturing of stderr and 
csvlog

# into log files. Required to be on for

# csvlogs.

# (change requires restart)

Mark Watson

[GENERAL] changing type of column on partitionated table.

[Edmundo Robles]

Hi !

I have  Postgresql 9.4,  a partitionated table and   i must change the type
of column.

The changes over  a master table  DDL are  applied to the childs?

or   must I change  the inheritance to off and  change it  one by one? for
each child and master table.


what is the best way/practice   to change the type column over partitioned
tables?

Regards.
--

Re: [GENERAL] Postgres 9.6.2 and pg_log

[David G. Johnston]

On Mon, Apr 24, 2017 at 10:58 AM, Mark Watson 
wrote:

>
> The lines log_rotation_age and log_rotation_size are commented, and
> currently are:
>
> #log_rotation_age = 1d # Automatic
> rotation of logfiles will
>
>
> # happen after that time.  0 disables.
>
> #log_rotation_size = 10MB  # Automatic rotation of
> logfiles will
>
>
> # happen after that much log output.
>
>
> # 0 disables.
>
>
>
>
>
> I see from your reference article that the log_rotation_age is now in
> minutes, and I will adjust that to 1440 (1 day). I don’t know where the
> “1d” came from. I know it used to be like this in earlier versions.
>
>
>
​Those are fine:  See "Numeric with Unit" @​

​https://www.postgresql.org/docs/9.6/static/config-setting.html

​I think the actual missing, and (just checked) defaulted off, parameter is
"logging_collector"

David J.

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

[Scott Marlowe]

Stick to 4k linux block size and you should be OK. I've yet to run
into a situation where changing either has made any measurable
difference.

On Mon, Apr 24, 2017 at 11:58 AM, chiru r  wrote:
> Thanks Scott.
> Please suggest the OS block sizes for Linux redhat 7.2, where as default
> Linux block size is 4k.
>
> If we keep 8k block size at OS level is it improves PostgreSQL performance?
> Please suggest what is the suggestible default OS block size for Linux
> systems to install PostgreSQL.
>
> Thanks,
> Chiru
>
> On Mon, Apr 24, 2017 at 12:29 PM, Scott Marlowe 
> wrote:
>>
>> On Mon, Apr 24, 2017 at 9:41 AM, chiru r  wrote:
>> > Hello,
>> >
>> > I am building new server to run PostgreSQL 9.5.4 version on it. Please
>> > provide the recommended Block size for Linux systems.
>> >
>> > We are using PostgreSQL blocks size is 8k default one.
>> >
>> > postgres=# show block_size ;
>> >  block_size
>> > 
>> >  8192
>> > (1 row)
>> >
>> > Is there any recommendation for separate block sizes on OS level for
>> > Pg_xlog, pg_log  and  Actual data files to improve the performance for
>> > reads/Writes?.
>>
>> Unless you've done some testing to show some other block size is
>> better, it's best to stick to 8k block size. Keep in mind that while
>> it is configurable at compile time, it doesn't get much testing at
>> other sizes and you could run into corner cases where there are
>> problems and the only acceptable fix is to compile with 8k blocks and
>> reload your whole db etc.
>>
>> tl;dr: Stick to 8k blocks.
>>
>> --
>> To understand recursion, one must first understand recursion.
>
>



-- 
To understand recursion, one must first understand recursion.


-- 
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] Postgres 9.6.2 and pg_log

[Mark Watson]

De : David G. Johnston [mailto:david.g.johns...@gmail.com]
Envoyé : Monday, April 24, 2017 1:34 PM
À : Mark Watson
Cc : (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log

On Mon, Apr 24, 2017 at 8:43 AM, Mark Watson 
> wrote:
Good day all,

I just noticed an anomaly regarding the logging. I have my logging set up as 
follows:
log_filename = 'postgresql-%d.log'
log_truncate_on_rotation = on

​I don't see "log_rotation_age" and/or "log_rotation_size" here [1] and at 
least one needs to be set in order to enable actual rotation; the "truncate" 
option simply tells PostgreSQL what to do when encountering a file with the 
same name during the rotation process.​

log_rotation_age apparently has under-documented intelligence since I would 
expect a server that starts up mid-hour and uses a 60 minute rotation to rotate 
mid-hour as well so the log would contain 1 hours worth of data but the leading 
hours would be different.  The examples in log_truncate_on_rotation indicate 
that this isn't the case.  I have not tested reality or read the source.

This is on Windows 10, 64-bit
PostgreSQL 9.2.2, compiled by Visual C++ build 1800, 64-bit
(EnterpriseDB installer)

Note that this is not a major concern on my end; postgres 9.6.2 has otherwise 
been running flawlessly.


​Um...you're reporting a very outdated 9.2 release in the supposed copy-paste 
job above but claiming 9.6.2 ...

[1] 
https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE

David J.
Sorry about the version; I queried SELECT version() in pgAdmin4 and did not 
copy/paste the results. It is version 9.6.2.

Re: [GENERAL] Postgres 9.6.2 and pg_log

[Mark Watson]

De : David G. Johnston [mailto:david.g.johns...@gmail.com]
Envoyé : Monday, April 24, 2017 1:34 PM
À : Mark Watson
Cc : (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log

On Mon, Apr 24, 2017 at 8:43 AM, Mark Watson 
> wrote:
Good day all,

I just noticed an anomaly regarding the logging. I have my logging set up as 
follows:
log_filename = 'postgresql-%d.log'
log_truncate_on_rotation = on

​I don't see "log_rotation_age" and/or "log_rotation_size" here [1] and at 
least one needs to be set in order to enable actual rotation; the "truncate" 
option simply tells PostgreSQL what to do when encountering a file with the 
same name during the rotation process.​

log_rotation_age apparently has under-documented intelligence since I would 
expect a server that starts up mid-hour and uses a 60 minute rotation to rotate 
mid-hour as well so the log would contain 1 hours worth of data but the leading 
hours would be different.  The examples in log_truncate_on_rotation indicate 
that this isn't the case.  I have not tested reality or read the source.

This is on Windows 10, 64-bit
PostgreSQL 9.2.2, compiled by Visual C++ build 1800, 64-bit
(EnterpriseDB installer)

Note that this is not a major concern on my end; postgres 9.6.2 has otherwise 
been running flawlessly.


​Um...you're reporting a very outdated 9.2 release in the supposed copy-paste 
job above but claiming 9.6.2 ...

[1] 
https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE

David J.

Thanks, David,
The lines log_rotation_age and log_rotation_size are commented, and currently 
are:
#log_rotation_age = 1d # Automatic rotation of 
logfiles will

# happen after that time.  0 disables.
#log_rotation_size = 10MB  # Automatic rotation of 
logfiles will

# happen after that much log output.

# 0 disables.


I see from your reference article that the log_rotation_age is now in minutes, 
and I will adjust that to 1440 (1 day). I don’t know where the “1d” came from. 
I know it used to be like this in earlier versions.

Mark Watson

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

[chiru r]

Thanks Scott.
Please suggest the OS block sizes for Linux redhat 7.2, where as default
Linux block size is 4k.

If we keep 8k block size at OS level is it improves PostgreSQL performance?
Please suggest what is the suggestible default OS block size for Linux
systems to install PostgreSQL.

Thanks,
Chiru

On Mon, Apr 24, 2017 at 12:29 PM, Scott Marlowe 
wrote:

> On Mon, Apr 24, 2017 at 9:41 AM, chiru r  wrote:
> > Hello,
> >
> > I am building new server to run PostgreSQL 9.5.4 version on it. Please
> > provide the recommended Block size for Linux systems.
> >
> > We are using PostgreSQL blocks size is 8k default one.
> >
> > postgres=# show block_size ;
> >  block_size
> > 
> >  8192
> > (1 row)
> >
> > Is there any recommendation for separate block sizes on OS level for
> > Pg_xlog, pg_log  and  Actual data files to improve the performance for
> > reads/Writes?.
>
> Unless you've done some testing to show some other block size is
> better, it's best to stick to 8k block size. Keep in mind that while
> it is configurable at compile time, it doesn't get much testing at
> other sizes and you could run into corner cases where there are
> problems and the only acceptable fix is to compile with 8k blocks and
> reload your whole db etc.
>
> tl;dr: Stick to 8k blocks.
>
> --
> To understand recursion, one must first understand recursion.
>

Re: [GENERAL] Postgres 9.6.2 and pg_log

[David G. Johnston]

On Mon, Apr 24, 2017 at 8:43 AM, Mark Watson 
wrote:

> Good day all,
>
>
>
> I just noticed an anomaly regarding the logging. I have my logging set up
> as follows:
>
> log_filename = 'postgresql-%d.log'
>
> log_truncate_on_rotation = on
>

​I don't see "log_rotation_age" and/or "log_rotation_size" here [1] and at
least one needs to be set in order to enable actual rotation; the
"truncate" option simply tells PostgreSQL what to do when encountering a
file with the same name during the rotation process.​

log_rotation_age apparently has under-documented intelligence since I would
expect a server that starts up mid-hour and uses a 60 minute rotation to
rotate mid-hour as well so the log would contain 1 hours worth of data but
the leading hours would be different.  The examples in
log_truncate_on_rotation indicate that this isn't the case.  I have not
tested reality or read the source.

This is on Windows 10, 64-bit
>
> PostgreSQL 9.2.2, compiled by Visual C++ build 1800, 64-bit
>
> (EnterpriseDB installer)
>
>
>
> Note that this is not a major concern on my end; postgres 9.6.2 has
> otherwise been running flawlessly.
>
>
>
​Um...you're reporting a very outdated 9.2 release in the supposed
copy-paste job above but claiming 9.6.2 ...

[1]
https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE

David J.

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

[Scott Marlowe]

On Mon, Apr 24, 2017 at 9:41 AM, chiru r  wrote:
> Hello,
>
> I am building new server to run PostgreSQL 9.5.4 version on it. Please
> provide the recommended Block size for Linux systems.
>
> We are using PostgreSQL blocks size is 8k default one.
>
> postgres=# show block_size ;
>  block_size
> 
>  8192
> (1 row)
>
> Is there any recommendation for separate block sizes on OS level for
> Pg_xlog, pg_log  and  Actual data files to improve the performance for
> reads/Writes?.

Unless you've done some testing to show some other block size is
better, it's best to stick to 8k block size. Keep in mind that while
it is configurable at compile time, it doesn't get much testing at
other sizes and you could run into corner cases where there are
problems and the only acceptable fix is to compile with 8k blocks and
reload your whole db etc.

tl;dr: Stick to 8k blocks.

-- 
To understand recursion, one must first understand recursion.


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

[GENERAL] Postgres 9.6.2 and pg_log

[Mark Watson]

Good day all,

I just noticed an anomaly regarding the logging. I have my logging set up as 
follows:
log_filename = 'postgresql-%d.log'
log_truncate_on_rotation = on

My log file postgresql-21.log contains only entries for today (April 24). When 
I restart the service, entries correctly start accumulating in 
postgresql-24.log.  For example, my postgresql-21.log contains:
2017-04-24 11:19:34 EDT LOG:  received fast shutdown request
2017-04-24 11:19:34 EDT LOG:  aborting any active transactions
2017-04-24 11:19:34 EDT LOG:  autovacuum launcher shutting down
2017-04-24 11:19:35 EDT LOG:  shutting down
2017-04-24 11:19:35 EDT LOG:  database system is shut down

My postgresql-24.log contains:
2017-04-24 11:19:40 EDT LOG:  database system was shut down at 2017-04-24 
11:19:35 EDT
2017-04-24 11:19:40 EDT LOG:  MultiXact member wraparound protections are now 
enabled
2017-04-24 11:19:40 EDT LOG:  database system is ready to accept connections
2017-04-24 11:19:40 EDT LOG:  autovacuum launcher started

This is on Windows 10, 64-bit
PostgreSQL 9.2.2, compiled by Visual C++ build 1800, 64-bit
(EnterpriseDB installer)

Note that this is not a major concern on my end; postgres 9.6.2 has otherwise 
been running flawlessly.

Mark Watson

[GENERAL] Block size recommendation for Red Hat Linux 7.2

[chiru r]

Hello,

I am building new server to run PostgreSQL 9.5.4 version on it. Please
provide the recommended Block size for Linux systems.

We are using PostgreSQL blocks size is 8k default one.

postgres=# show block_size ;
 block_size

 8192
(1 row)

Is there any recommendation for separate block sizes on OS level for
Pg_xlog, pg_log  and  Actual data files to improve the performance for
reads/Writes?.


Thanks,
Chiru

Re: [GENERAL] Failed dependencies for Pgadmin4 Web in Centos 7

[Clodoaldo Neto]

On Mon, Apr 24, 2017 at 11:15 AM, Adrian Klaver 
wrote:

> On 04/24/2017 05:06 AM, Clodoaldo Neto wrote:
> Please reply to list also
> Ccing list
>
> On Sun, Apr 23, 2017 at 4:58 PM, Adrian Klaver
>> > wrote:
>>
>
>
>> I disabled the priorities plugin and got no excluded packages but still
>> the same failed dependencies.
>>
>>
>> Installed Packages
>> pgadmin4-python-beautifulsoup4.noarch
>> 4.5.1-2.rhel7  @pgdg96
>> pgadmin4-python-fixtures.noarch
>> 3.0.0-4.rhel7  @pgdg96
>> pgadmin4-python-flask-principal.noarch
>> 0.4.0-13.rhel7 @pgdg96
>> pgadmin4-python-itsdangerous.noarch
>> 0.24-9.rhel7   @pgdg96
>>
>> # yum install pgadmin4-v1-web
>>
>>
>> Do you have the PGDG repos installed on your machine?:
>>
>> https://www.postgresql.org/download/linux/redhat/
>> 
>>
>> Or did you install the pgadmin4* packages above directly?
>>
>>
>> I have the pgdg-96 repo installed.
>>
>> The first problem is that the four packages above have the "pgadmin4"
>> string prefixed to their names whereas the spec file aks for the name
>> without the prefix like in "pgadmin4-python-fixtures" vs
>> "python-fixtures".
>>
>
> To be clear I am not a Yum/RPM expert.
>
> Where is the spec file coming from?
>
>
>From the pgadmin4-v1-web package:
https://yum.postgresql.org/srpms/9.6/redhat/rhel-7-x86_64/pgadmin4-v1-1.4-1.rhel7.src.rpm


>
>> The second problem is that the other packages do not exist in pgdg-96 in
>> either form.
>>
>
> Looks to me they do:
>
> https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/
>
>
Missing packages: python-dateutil, python-mimeparse, python-flask-security,
python-flask-wtf


Clodoaldo


>
>> IMHO prefixing the package names with "pgadmin4" just adds confusion as
>> there will be different packages installing the same file names in the
>> same places. The different repo sources can be managed with priorities
>> if necessary.
>>
>> Clodoaldo
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Failed dependencies for Pgadmin4 Web in Centos 7

[Adrian Klaver]

On 04/24/2017 05:20 AM, Clodoaldo Neto wrote:

Ccing list.




BTW aren't the Python packages better left in the epel repo if possible?
I think the Python people will not care since they rely mostly in Pip.


Agreed, when I tried out pgAdmin4, I found the easiest way was to set up 
a virtualenv and use the Wheel:


https://www.pgadmin.org/download/pip4.php



Clodoaldo





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


--
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] Failed dependencies for Pgadmin4 Web in Centos 7

[Adrian Klaver]

On 04/24/2017 05:06 AM, Clodoaldo Neto wrote:
Please reply to list also
Ccing list


On Sun, Apr 23, 2017 at 4:58 PM, Adrian Klaver
> wrote:




I disabled the priorities plugin and got no excluded packages but still
the same failed dependencies.


Installed Packages
pgadmin4-python-beautifulsoup4.noarch
4.5.1-2.rhel7  @pgdg96
pgadmin4-python-fixtures.noarch
3.0.0-4.rhel7  @pgdg96
pgadmin4-python-flask-principal.noarch
0.4.0-13.rhel7 @pgdg96
pgadmin4-python-itsdangerous.noarch
0.24-9.rhel7   @pgdg96

# yum install pgadmin4-v1-web


Do you have the PGDG repos installed on your machine?:

https://www.postgresql.org/download/linux/redhat/


Or did you install the pgadmin4* packages above directly?


I have the pgdg-96 repo installed.

The first problem is that the four packages above have the "pgadmin4"
string prefixed to their names whereas the spec file aks for the name
without the prefix like in "pgadmin4-python-fixtures" vs "python-fixtures".


To be clear I am not a Yum/RPM expert.

Where is the spec file coming from?



The second problem is that the other packages do not exist in pgdg-96 in
either form.


Looks to me they do:

https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/



IMHO prefixing the package names with "pgadmin4" just adds confusion as
there will be different packages installing the same file names in the
same places. The different repo sources can be managed with priorities
if necessary.

Clodoaldo





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


--
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] [pgadmin-hackers] file permission on ssl key

[Adrian Klaver]

On 04/23/2017 07:42 PM, Ashesh Vashi wrote:

Hi Jeroen,

This is pgAdmin hackers list.
Please send mail to pgsql-general@postgresql.org
 mailing list for your postgresql
related queries.

--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company



/http://www.linkedin.com/in/asheshvashi/


On Sun, Apr 23, 2017 at 11:25 PM, Jeroen Jacobs
> wrote:

Hi,

I'm getting this error when I try to configure ssl with postgres:


What version of Postgres?

https://www.postgresql.org/docs/9.6/static/release-9-6.html

"Allow the server's SSL key file to have group read access if it is 
owned by root (Christoph Berg)


Formerly, we insisted the key file be owned by the user running the 
PostgreSQL server, but that is inconvenient on some systems (such as 
Debian) that are configured to manage certificates centrally. Therefore, 
allow the case where the key file is owned by root and has group read 
access. It is up to the operating system administrator to ensure that 
the group does not include any untrusted users.

"



pr 23 13:12:47 pgmaster01 pg_ctl: FATAL:  private key file
"/etc/ssl/pgmaster01-key.pem" has group or world access
Apr 23 13:12:47 pgmaster01 pg_ctl: DETAIL:  Permissions should be
u=rw (0600) or less.

The actual permission is:

centos@pgmaster01 ~]$ ls -l /etc/ssl/pgmaster01-key.pem
-r--r- 1 root ssl-read 3243 Apr 23 00:00 /etc/ssl/pgmaster01-key.pem

postgres user is part of the ssl-read group. Thi ssl key is shared
with other software as well, so giving exclusive access to the
postgres user is NOT an option.

I understand why postgres complains, but I'm pretty sure about what
I'm doing here. How can I tell postgres to start anyway, even when
it doesn't like those permissions? There should be a way to override
this, I'm the admin here, it's up to me to decide to implement my
security setup, not the software itself.

So basically I have three options:

- don't use ssl at all (not an option at all, actually)
- create a separate copy of my ssl key file with the correct
permissions that postgres likes (ugly workaround)
- use another database server which allows me to configure it how I
want it.

I'm actually considering settling for the last solution, due to this
crazy restriction you put in place...


Regards,

Jeroen.





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


--
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] DROP INDEX CASCADE doesn't want to drop unique constraints?

[Andreas Kretschmer]

Ivan Voras  wrote:

> Hello,
> 
> On trying to drop an index named "employer_employerid_key" which supports a
> unique constraint:
> 
>     "employer_employerid_key" UNIQUE CONSTRAINT, btree (employerid)
> 
> I get this error:
> 
> ERROR:  cannot drop index employer_employerid_key because constraint
> employer_employerid_key on table employer requires it
> HINT:  You can drop constraint employer_employerid_key on table employer
> instead.
> 
> I'm using the CASCADE and IF EXISTS arguments and the docs say nothing about
> any special cases (https://www.postgresql.org/docs/9.3/static/
> sql-dropindex.html). This is with PostgreSQL 9.3.15. 
> 
> The actual command is:
> 
> drop index if exists employer_employerid_key cascade;
> 
> Any ideas if this is normal or why it happens?


Drop the constraint:

alter table employer drop constraint employer_employerid_key;





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


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

[GENERAL] DROP INDEX CASCADE doesn't want to drop unique constraints?

[Ivan Voras]

Hello,

On trying to drop an index named "employer_employerid_key" which supports a
unique constraint:

"employer_employerid_key" UNIQUE CONSTRAINT, btree (employerid)

I get this error:

ERROR:  cannot drop index employer_employerid_key because constraint
employer_employerid_key on table employer requires it
HINT:  You can drop constraint employer_employerid_key on table employer
instead.

I'm using the CASCADE and IF EXISTS arguments and the docs say nothing
about any special cases (
https://www.postgresql.org/docs/9.3/static/sql-dropindex.html). This is
with PostgreSQL 9.3.15.

The actual command is:

drop index if exists employer_employerid_key cascade;

Any ideas if this is normal or why it happens?

Re: [GENERAL] Memory consumption for Query

[dhaval jaiswal]

>> Other operations don't really consume much memory.


Is there any way to find out that as well.


>> You can run "EXPLAIN (ANALYZE) SELECT ..." to see how much memory is used
for memory intense operations like sort, hash or materialize.

I am aware of it.





Sent from Outlook



From: Albe Laurenz 
Sent: Monday, April 24, 2017 12:54 PM
To: 'dhaval jaiswal *EXTERN*'; PostgreSQL General
Subject: RE: Memory consumption for Query

dhaval jaiswal wrote:
> How to check how much memory query is consuming.
>
> Is there tool can check of query consuming memory for the execution or output.
>
> Let's say for following query how to calculate memory consumption.
>
> select * from test where id=1;

That query will not consume memory worth mention unless
"test" is a non-trivial view.

You can run "EXPLAIN (ANALYZE) SELECT ..." to see how much memory is used
for memory intense operations like sort, hash or materialize.

Other operations don't really consume much memory.

Yours,
Laurenz Albe

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

[Vincent Veyron]

On Sun, 23 Apr 2017 12:31:29 +0200
Ron Ben  wrote:

> 
> A simple open source forum system can be enough simetng like php-bb
> example: warez-bb.org
>  
> the installation of such system is like 1 hour of work.
>  
> In my point of view something like stack overflow is the best but i'm not 
> sure if it's open source.
>   

Don't waste your time, there is one already :

https://www.reddit.com/r/PostgreSQL/



-- 
Bien à vous, Vincent Veyron 

https://compta.libremen.com
Logiciel libre de comptabilité générale


-- 
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] Not sure this should be asked here but...

[Christofer C. Bell]

On Sun, Apr 23, 2017 at 5:31 AM, Ron Ben  wrote:

>
> A simple open source forum system can be enough simetng like php-bb
> example: warez-bb.org
>
> the installation of such system is like 1 hour of work.
>
> In my point of view something like stack overflow is the best but i'm not
> sure if it's open source.
>

Setup time doesn't matter, at all.  It's on-going maintenance and "everyone
has better things to do" that means there won't be a forum when the mailing
lists work fine.  Setting up a forum means you volunteer to keep it running
-- forever.  You can't just pack up and shut it down in 2 or 3 years when
you get bored with it.

Re: [GENERAL] Recover PostgreSQL database folder data

[Christofer C. Bell]

 On Sat, Apr 22, 2017 at 9:11 AM, Edson Lidorio 
wrote:

>
>
> On 22-04-2017 06:40, Magnus Hagander wrote:
>
>
>
> On Sat, Apr 22, 2017 at 3:05 AM, Cat  wrote:
>
>> On Fri, Apr 21, 2017 at 08:20:38PM -0300, Edson Lidorio wrote:
>> > Ls -la /var/lib/pgsql/9.6/data
>> >
>> > drwx--. 20 postgres postgres  4096 Abr 21 17:52 .
>> > drwx--.  4 root root51 Abr 21 06:33 ..
>>
>> Ensure that the user 'postgres' has permissions to get to
>> this dir from / up. This may either mean changing permissions
>> on some directories or changing ownership.
>>
>> More than likely / /var /lib are a permissions thing (likely
>> need to be u+rwx,g+rx,o+rx) and /var/lib/pgsql/ and up is an
>> ownership thing (postgres:postgres) but this is not guaranteed
>> so take care.
>
>
> Since this is CentOS, I would also look into if it's selinux things that
> are incorrect. The easiest way is to turn it off and see if that fixes it
> -- if it does, then read up on the selinux docs for how to figure out what
> is wrong and probably use restorecon to get things back in order.
>
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/ 
>  Work: https://www.redpill-linpro.com/ 
>
> Friends,
> The problem, was the selinux of CentOS, I disabled the selinux and applied
> the pemissions again and PostgreSQL started normally.
>
> Used Commands:
> # sudo /usr/sbin/setenforce 0
> # sudo chown postgres /var/lib/pgsql/9.6/
> #  sudo chown postgres:postgres /var/lib/pgsql/9.6/data
> # chmod 700 /var/lib/pgsql/9.6/
> # sudo systemctl start postgresql-9.6
>
> Thank you all
>
> Note: Looking at google, I noticed that there is more people with this
> problem. It's a problem with CentOS and PostgreSQL, which does not go
> down very well.
>

I think you may want to look into doing this:

# restorecon -R /var/lib/pgsql/9.6/

Then re-enable SELinux and try to start the database again.  You should
find that it works.  Rather, if it was working with SELinux on this system
before, then it should work after running the restorecon.  Forgetting to
reset the necessary SELinux file labels is a common mistake in this kind of
situation.

Note:  You don't need to use "sudo" when you're already root.

-- 
Chris

"If you wish to make an apple pie from scratch, you must first invent the
Universe." -- Carl Sagan

Re: [GENERAL] Memory consumption for Query

[Albe Laurenz]

dhaval jaiswal wrote:
> How to check how much memory query is consuming.
> 
> Is there tool can check of query consuming memory for the execution or output.
> 
> Let's say for following query how to calculate memory consumption.
> 
> select * from test where id=1;

That query will not consume memory worth mention unless
"test" is a non-trivial view.

You can run "EXPLAIN (ANALYZE) SELECT ..." to see how much memory is used
for memory intense operations like sort, hash or materialize.

Other operations don't really consume much memory.

Yours,
Laurenz Albe

-- 
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] Not sure this should be asked here but...

[vinny]

On 2017-04-23 12:31, Ron Ben wrote:

A simple open source forum system can be enough simetng like php-bb
example: warez-bb.org

the installation of such system is like 1 hour of work.

In my point of view something like stack overflow is the best but i'm
not sure if it's open source.




Setting up a forum is not the problem.
The problem is that the value of the mailinglists is in the contributors 
that use it,

and they, for the most part, really do not want to stop using it.

This is why the original thread was about expanding the web-interface to 
the mailinglist,
that would allow the current mailinglist users to keep working the way 
they like to,
while opening the system up to the rest of the world in a more 
millenial-friendly way.



Did I just use the word "millenial?

sorry... I'll just go and be ashamed in a corner for a while...


--
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] pgAdmin4 needs information of v10 SCRAM authentication

[Michael Paquier]

On Mon, Apr 24, 2017 at 3:04 PM, Akshay Joshi
 wrote:
>I have gone through this, but still facing issue to encrypt/change the 
> database server password. In pgAdmin4 we have "Change Password" feature where 
> user will enter the old and new password for the database server, now we will 
> have to encrypt it (in Python) as per SCRAM standards and set it to the 
> database.

By using SET password_encryption = 'scram-sha-256' and sending the raw
password you would be able to hash the password correctly. Or you
could just mimic scram_build_password() (routine in Postgres code to
generate that correctly).

>   The example you have given in 
> https://www.postgresql.org/message-id/76ac7e67-4e3a-f4df-e087-fbac90151...@iki.fi
>  I have below questions:
> To encode the password you already have entry from pg_authid table which 
> won't be possible for non superuser to access that table. How we can get that 
> value from pg_authid table or do we have any other solution to this.

I don't understand this question, any user can update this field using
CREATE/ALTER ROLE, and the client has no need to know this value for
the exchange.

> For constructing  the whole client-final-message, we need to calculate  
> ClientSignature and ClientProof, which depend on the nonces, and is  
> therefore different on every authentication exchange. How to calculate 
> ClientSignature and ClientProof?
>   Can you please guide me here, how can we achieve that in python.

You will need a C equivalent of what is proposed in fe-auth-scram.c in
the Postgres code to build the messages that are exchanged from the
server, see particularly calculate_client_proof() which describes step
by step the calculation of the client proof when building the last
message for the client. I didn't check in details, but the routines
are the same as in the message above. The format of the hashed
password has changed a bit since commit 68e61ee though.
-- 
Michael


-- 
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] pgAdmin4 needs information of v10 SCRAM authentication

[Akshay Joshi]

Hi Michael

On Fri, Apr 21, 2017 at 12:07 PM, Michael Paquier  wrote:

> On Fri, Apr 21, 2017 at 3:27 PM, Akshay Joshi
>  wrote:
> > from passlib.hash import scram
> > hash = scram.encrypt(data['newPassword']) -- This function provide
> password for all the supported digest like [md5, sha-1, sha-256, sha-512].
> Didn't work I have tried with all the passwords.
> > test = scram.extract_digest_info(hash, "sha-256") -- This function
> extract info for specified digest "sha-256". I have retrieve the password
> which was in hexadecimal. Didn't work as well.
> >
> > Now I am stuck here and no clue how to encrypt/decrypt the password for
> SCRAM authentication. Can someone guide me out here.
>
> Here you go:
> https://www.postgresql.org/message-id/76ac7e67-4e3a-f4df-
> e087-fbac90151...@iki.fi


   I have gone through this, but still facing issue to encrypt/change the
database server password. In pgAdmin4 we have "Change Password" feature
where user will enter the old and new password for the database server, now
we will have to encrypt it (in Python) as per SCRAM standards and set it to
the database.

  The example you have given in https://www.postgresql.org/
message-id/76ac7e67-4e3a-f4df-e087-fbac90151...@iki.fi I have below
questions:

   - To encode the password you already have entry from pg_authid table
   which won't be possible for non superuser to access that table. How we can
   get that value from pg_authid table or do we have any other solution to
   this.
   - For constructing  the whole client-final-message, we need to calculate
   * ClientSignature* and *ClientProof*, which depend on the nonces, and is
therefore different on every authentication exchange. How to calculate
   *ClientSignature* and *ClientProof?*

  Can you please guide me here, how can we achieve that in python.

>
> --
> Michael
>



-- 
*Akshay Joshi*
*Principal Software Engineer *



*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*