Re: WSL (windows subsystem on linux) users will need to turn fsync off as of 11.2

[Thomas Munro]

>> > Great.  Thanks for testing, and for the fix!  Well that all sounds
>> > like good news: it corrects the behaviour from 11.2, and also improves
>> > on the previous behaviour which I'd have accepted as a bug if anyone
>> > had reported it.  So the next problem is that we don't have a
>> > consensus on whether this is the right approach, so I don't feel like
>> > I can commit it yet.  Does any want to make another concrete proposal?
>>
>> Ok, here's the version I'm planning to push soon if there are no objections.
>> Re-adding Bruce to the thread, as I just noticed the CC list got
>> pruned at some point in this thread.

Pushed.

I also noticed that the call to sync_file_range() in file_utils.c used
by fsync_pgdata() ignores the return code, and in the 9.4 and 9.5
branches, the call in pg_flush_data() ignores the return code too.
This inconsistency should be fixed; I'll think about which direction
it should be fixed in (either we are convinced that
sync_file_range(SYNC_FILE_RANGE_WRITE) is non-destructive of error
state or we aren't, and should handle it everywhere), and maybe start
a new -hackers thread.

-- 
Thomas Munro
https://enterprisedb.com

Re: Foreign table & Connection reset by peer

[Lukáš Sobotka]

Thanks for quick answer. I am feeling a little embarrassed that I did not
find this before... The thread definitely helped me to understand more.

When I execute query with foreign tables, the connection is created
(according pg_stat_activity). The question is what I should do for ending
the connection. I can get PID of connection and force to end it by
pg_terminate_backend, but that is not "clean way".

Unfortunately I can not disable SSL encryption as it is mentioned in the
thread. But it is interesting for me, that the log message (resetting by
peer) is created only in encrypted connection, because in both way the
connection stayed defined in pg_stat_activity after query on foreign
table.

so 23. 2. 2019 v 22:48 odesílatel Adrian Klaver 
napsal:

> On 2/23/19 1:28 PM, Lukáš Sobotka wrote:
> > Hi guys,
> >
> > I would be grateful for some advice about foreign tables. I attached
> > simplified script describing the problem for better understanding.
> >
> > What I am trying to do:
> >
> > I am copying tables between two databases and for copying is used
> > function which are called from bash script.
> >
> > What is problem:
> >
> > I am creating foreign tables, selecting from them, dropping them and
> > everything works well. But always when I am working with them and the
> > session ends, message about connection resetting appears in log file of
> > remote database. It is showing "postgres@adam_db LOG:  could not
> receive
> > data from client: Connection reset by peer". I am calling the command
> > often, so this message fill my log file quite a fast. I tried to find
> > some info about this message, without success.
> >
> > Where can be problem?
>
> See if the thread below applies:
>
> https://www.postgresql.org/message-id/4004.1521759312%40sss.pgh.pa.us
>
> >
> > Is there some different/correct way how to use foreign tables?
> > How can I closed connection properly?
> >
> > For reproducing you can use attached script and after that execute next
> > command:
> > /psql -c 'SELECT * FROM ft_numbers'
> > "postgresql://localhost:5432/bety_db?user=postgres&password=postgres"/
> >
> > I also tried to drop server after selection but it also logs the
> > message. Command which I executed:/
> > psql -c 'SELECT * FROM ft_numbers; DROP SERVER adam_server CASCADE;'
> > "postgresql://localhost:5432/bety_db?user=postgres&password=postgres"/
> >
> > I tried to replicate problem in databases with different versions - all
> > of them ended with the message in log.Used versions:
> >
> >   * PostgreSQL 9.6.10 on x86_64-pc-linux-gnu (Ubuntu
> > 9.6.10-1.pgdg16.04+1), compiled by gcc (Ubuntu
> > 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
> >   * PostgreSQL 10.5 (Ubuntu 10.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
> > compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609,
> 64-bit
> >   * PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
> > compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609,
> 64-bit
> >
> > Best regards,
> >
> > Lukas
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Future Non-server Windows support???

[Bill Haught]

I noticed that for 11.2, non-server versions of Windows are not listed 
as supported platforms.  Is support in the process of being dropped for 
non-server editions of Windows when 10.7 is no longer supported 
(apparently years away though)?  Or will such support be added to 11.2 
or some later versions in the future?


Thanks in advanced.

Re: Size estimation of postgres core files

[Peter J. Holzer]

On 2019-02-15 13:01:50 -0600, Jeremy Finzel wrote:
> It doesn't write out all of RAM, only the amount in use by the
> particular backend that crashed (plus all the shared segments attached
> by that backend, including the main shared_buffers, unless you disable
> that as previously mentioned).

> Based on the Alvaro's response, I thought it is reasonably possible that that
> *could* include nearly all of RAM, because that was my original question.  If
> shared buffers is say 50G and my OS has 1T, shared buffers is a small portion
> of that.  But really my question is what should we reasonably assume is
> possible

The size of the core dump will be roughly the same as the VM used by the
process - so that will be the initial size of the process plus shared
buffers plus a (usually small) multiple of work_mem or
maintenance_work_mem plus whatever memory the process allocates.

The big unknown is that "(usually small) multiple of work_mem". I've
seen a process use 8 times work_mem for a moderately complex query, so
depending on what you do it might be worse.

The extra memory allocated by processes is usually small (after all, if
some datastructure were expected to be potentially large it would
probably be limited by work_mem), but if there is a bug (and that's what
you are looking for) it might really grow without bounds.

If you know some upper bound for a reasonable size of your processes you
could set the address space limit - not only will this limit the core
dump size, but it will also prevent a single process from consuming all
RAM and triggering the OOM killer.

You probably don't want to limit the core dump size directly (another
process limit you can set) as that will result in a truncated (and
possibly useless) core dump. For similar reasons I'm not convinced that
omitting the shared memory is a good idea.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: Future Non-server Windows support???

[Tom Lane]

Bill Haught  writes:
> I noticed that for 11.2, non-server versions of Windows are not listed 
> as supported platforms.  Is support in the process of being dropped for 
> non-server editions of Windows when 10.7 is no longer supported 
> (apparently years away though)?  Or will such support be added to 11.2 
> or some later versions in the future?

Well, there are two independent questions there: what does the source
code support, and what platforms are people distributing prebuilt
packages for.

I'm not aware of any community policy change with respect to what the
source code supports.  The oldest active Windows machine I see in the
buildfarm is Windows 7 + MSVC 2013, so we can reasonably confidently say
that it still works that far back.  If you are concerned about something
older, the right way to make it happen is to host a buildfarm animal [1]
on a machine running the version you care about.  (We have semi-officially
given up on Windows XP, for what that's worth --- but that was a couple
years ago.)

As for prebuilt packages, that's something you'd have to discuss with
the package builders --- or else build your own packages.  I do vaguely
recall hearing that EDB had decided to cut back the number of distinct
Windows versions they build packages for, but that's theirs to decide
not a community matter.

regards, tom lane

[1] https://buildfarm.postgresql.org/cgi-bin/register-form.pl

Re: Future Non-server Windows support???

[Adrian Klaver]

On 2/24/19 4:05 AM, Bill Haught wrote:
I noticed that for 11.2, non-server versions of Windows are not listed 
as supported platforms.  Is support in the process of being dropped for 


Did you look here?:

https://www.openscg.com/bigsql/postgresql/installers.jsp/

non-server editions of Windows when 10.7 is no longer supported 
(apparently years away though)?  Or will such support be added to 11.2 
or some later versions in the future?


Thanks in advanced.





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

Re: Foreign table & Connection reset by peer

[Adrian Klaver]

On 2/24/19 2:25 AM, Lukáš Sobotka wrote:
Thanks for quick answer. I am feeling a little embarrassed that I did 
not find this before... The thread definitely helped me to understand more.


When I execute query with foreign tables, the connection is created 
(according pg_stat_activity). The question is what I should do for 
ending the connection. I can get PID of connection and force to end it 


Not sure. In the original thread the OP was calling conn.close() from 
inside a Python script and still seeing the message. The only way they 
could prevent it was to disable SSL. From what I gather this is down in 
the internals of the code and needs a patch to fix. Someone with more 
knowledge of this will need to comment further.




by pg_terminate_backend, but that is not "clean way".

Unfortunately I can not disable SSL encryption as it is mentioned in the 
thread. But it is interesting for me, that the log message (resetting by 
peer) is created only in encrypted connection, because in both way the 
connection stayed defined in pg_stat_activity after query on foreign table.


so 23. 2. 2019 v 22:48 odesílatel Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> napsal:




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

Re: Future Non-server Windows support???

[Jeff Janes]

On Sun, Feb 24, 2019 at 7:06 AM Bill Haught  wrote:

> I noticed that for 11.2, non-server versions of Windows are not listed
> as supported platforms.


Listed where?  The only thing I see is "Windows (Win2000 SP4 and later)",
and that isn't a list and surely there are non-server versions of Windows
which are later than that.

https://www.postgresql.org/docs/11/supported-platforms.html

I've run 11.2 on "Windows 10 home" without apparent problem.

Cheers,

Jeff

Re: Future Non-server Windows support???

[Adrian Klaver]

On 2/24/19 10:38 AM, Jeff Janes wrote:
On Sun, Feb 24, 2019 at 7:06 AM Bill Haught > wrote:


I noticed that for 11.2, non-server versions of Windows are not listed
as supported platforms. 



Listed where?  The only thing I see is "Windows (Win2000 SP4 and 


I believe the OP is referring to:

https://www.postgresql.org/download/windows/

Interactive installer by EnterpriseDB

...

Platform support

later)", and that isn't a list and surely there are non-server versions 
of Windows which are later than that.


https://www.postgresql.org/docs/11/supported-platforms.html

I've run 11.2 on "Windows 10 home" without apparent problem.

Cheers,

Jeff



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

Re: Future Non-server Windows support???

[Jeff Janes]

On Sun, Feb 24, 2019 at 3:10 PM Adrian Klaver 
wrote:

> On 2/24/19 10:38 AM, Jeff Janes wrote:
> > On Sun, Feb 24, 2019 at 7:06 AM Bill Haught  > > wrote:
> >
> > I noticed that for 11.2, non-server versions of Windows are not
> listed
> > as supported platforms.
> >
> >
> > Listed where?  The only thing I see is "Windows (Win2000 SP4 and
>
> I believe the OP is referring to:
>
> https://www.postgresql.org/download/windows/
>
> Interactive installer by EnterpriseDB
>

OK, thanks.  I don't know how that list is maintained, but on EnterpriseDB
itself, they list those two versions of Windows for one of their paid
products.  For the free community version, they list: "Windows x86-64:
Windows 2012 R2 & R1, Windows 2016, Windows 7, 8, 10".

https://www.enterprisedb.com/services-support/edb-supported-products-and-platforms

But, this EnterpriseDB page itself is out of date, as it lists 11.1 but not
11.2.  I wouldn't think they would have desupported between 11.1 and 11.2
(and I can install 11.2 using their installer), so I think this is a bug on
https://www.postgresql.org/download/windows/ and a stale page on edb.

Cheers,

Jeff

Re: Future Non-server Windows support???

[Adrian Klaver]

On 2/24/19 1:04 PM, Jeff Janes wrote:
On Sun, Feb 24, 2019 at 3:10 PM Adrian Klaver > wrote:


On 2/24/19 10:38 AM, Jeff Janes wrote:
 > On Sun, Feb 24, 2019 at 7:06 AM Bill Haught
mailto:wlhaught4754...@att.net>
 > >> wrote:
 >
 >     I noticed that for 11.2, non-server versions of Windows are
not listed
 >     as supported platforms.
 >
 >
 > Listed where?  The only thing I see is "Windows (Win2000 SP4 and

I believe the OP is referring to:

https://www.postgresql.org/download/windows/

Interactive installer by EnterpriseDB


OK, thanks.  I don't know how that list is maintained, but on 
EnterpriseDB itself, they list those two versions of Windows for one of 
their paid products.  For the free community version, they list: 
"Windows x86-64: Windows 2012 R2 & R1, Windows 2016, Windows 7, 8, 10".


https://www.enterprisedb.com/services-support/edb-supported-products-and-platforms

But, this EnterpriseDB page itself is out of date, as it lists 11.1 but 
not 11.2.  I wouldn't think they would have desupported between 11.1 and 
11.2 (and I can install 11.2 using their installer), so I think this is 
a bug on https://www.postgresql.org/download/windows/ and a stale page 
on edb.




Well something is out of sync. Still I have to go with what is available 
from the Download page:


https://www.postgresql.org/download/windows/ -->

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads -->

https://www.enterprisedb.com/docs/en/11.0/PG_Inst_Guide_v11/PostgreSQL_Installation_Guide.1.04.html#pID0E0XH0HA


2 Requirements Overview
2.1 Supported Platforms
PostgreSQL 11 is certified on the following platforms:
64 bit Windows:
Windows 2012R2
Windows 2016
MAC OS X:
OS X Server 10.12, 10.13, and 10.14





Cheers,

Jeff



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

Recommended way to enable data-checksums on Centos 7?

[Ken Tanzer]

Hi.  I recently installed PG 11.2 on Centos 7, following the excellent
directions at https://www.postgresql.org/download/linux/redhat/.

I wanted to enable data-checksums.  I at first tried appending
--data-checksums to the doc-specified command:

/usr/pgsql-11/bin/postgresql-11-setup initdb --data-checksums

but that did not work.  After a glance at that script, I was able to do it
with:

export PGSETUP_INITDB_OPTIONS="--data-checksums"
/usr/pgsql-11/bin/postgresql-11-setup initdb

I'm just wondering if there is a more preferred way to do this, and/or any
particular reason you can't pass options to initdb?  Also, since the
checksums are good for data integrity, and can only be done at init time, I
wonder if it's worth adding a note about it to that documentation page?

Thanks.

Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Property Graph Query Language proposed for SQL:2020

[Stefan Keller]

Hi,

Anyone aware and following this standardization activities?
Forthcoming SQL:2020 seems to contain "Property Graph Query Extensions".
See:
* GQL: a standard for property graph querying
https://www.gqlstandards.org/
* Property Graph Query Language (PGQL), an SQL-like query language for
graphs, including an Open-sourced parser and static query validator on
GitHub by Oracle.
http://pgql-lang.org/

:Stefan

Re: Recommended way to enable data-checksums on Centos 7?

[Adrian Klaver]

On 2/24/19 2:39 PM, Ken Tanzer wrote:
Hi.  I recently installed PG 11.2 on Centos 7, following the excellent 
directions at 


.


I wanted to enable data-checksums.  I at first tried appending 
--data-checksums to the doc-specified command:


/usr/pgsql-11/bin/postgresql-11-setup initdb --data-checksums

but that did not work.  After a glance at that script, I was able to do 
it with:


export PGSETUP_INITDB_OPTIONS="--data-checksums"
/usr/pgsql-11/bin/postgresql-11-setup initdb

I'm just wondering if there is a more preferred way to do this, and/or 
any particular reason you can't pass options to initdb?  Also, since the 


You can pass options to initdb:

https://www.postgresql.org/docs/11/app-initdb.html

It seems you cannot pass them to the script postgresql-11-setup. That 
would be a question for the packagers:


https://yum.postgresql.org/contact.php

Or you could just use the initdb program directly instead of going 
through the script. The caveat being whether that would interfere with 
what the script does?



checksums are good for data integrity, and can only be done at init 
time, I wonder if it's worth adding a note about it to that 
documentation page?


Thanks.

Ken


--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list 
 to

learn more about AGENCY or
follow the discussion.



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

Logical replication very slow

[Boris Sagadin]

Doing an initial replica and trying to find a bottleneck, Ubuntu 16.04,
NVMe disks, PgSQL v10.7, AWS. With binary replication, DB is replicated at
good speed, around 500MB/s. Trying LR now for a big table (about 1.4TB with
2 indexes) and the speed is only about 2MB/s.

Checked disk util with iostat and only about 20% utilized on master, 15% on
target, CPU load on master is low. On slave I can see the "logical
replication worker" process is taking about 70% CPU time on a single core,
machine has 16 cores.

Is there a setting I am missing here? Any ideas appreciated.

Boris

Re: Logical replication very slow

[Achilleas Mantzios]

On 25/2/19 8:52 π.μ., Boris Sagadin wrote:
Doing an initial replica and trying to find a bottleneck, Ubuntu 16.04, NVMe disks, PgSQL v10.7, AWS. With binary replication, DB is replicated at good speed, around 500MB/s. Trying LR now for a big 
table (about 1.4TB with 2 indexes) and the speed is only about 2MB/s.



Is logical replication subscriber in "streaming" state or in initial snapshot? 
What's the behavior after the initial snapshot, when it gets into streaming state?
Checked disk util with iostat and only about 20% utilized on master, 15% on target, CPU load on master is low. On slave I can see the "logical replication worker" process is taking about 70% CPU 
time on a single core, machine has 16 cores.


Is there a setting I am missing here? Any ideas appreciated.

Boris






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

Re: LDAP authenticated session terminated by signal 11: Segmentation fault, PostgresSQL server terminates other active server processes

[Mike Yeap]

Hi Tom, when I run "ldd /usr/pgsql-10/bin/postmaster" I got this output:

# ldd /usr/pgsql-10/bin/postmaster
linux-vdso.so.1 =>  (0x7ffd4ec65000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x7eff8b5d3000)
libxml2.so.2 => /lib64/libxml2.so.2 (0x7eff8b268000)
libpam.so.0 => /lib64/libpam.so.0 (0x7eff8b059000)
libssl.so.10 => /lib64/libssl.so.10 (0x7eff8ade7000)
libcrypto.so.10 => /lib64/libcrypto.so.10 (0x7eff8a985000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x7eff8a738000)
librt.so.1 => /lib64/librt.so.1 (0x7eff8a53)
libdl.so.2 => /lib64/libdl.so.2 (0x7eff8a32b000)
libm.so.6 => /lib64/libm.so.6 (0x7eff8a029000)
libldap-2.4.so.2 => /lib64/libldap-2.4.so.2 (0x7eff89dd4000)
libicui18n.so.50 => /lib64/libicui18n.so.50 (0x7eff899d4000)
libicuuc.so.50 => /lib64/libicuuc.so.50 (0x7eff8965b000)
libsystemd.so.0 => /lib64/libsystemd.so.0 (0x7eff89633000)
libc.so.6 => /lib64/libc.so.6 (0x7eff89271000)
/lib64/ld-linux-x86-64.so.2 (0x7eff8b7f9000)
libz.so.1 => /lib64/libz.so.1 (0x7eff8905b000)
liblzma.so.5 => /lib64/liblzma.so.5 (0x7eff88e35000)
libaudit.so.1 => /lib64/libaudit.so.1 (0x7eff88c0c000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x7eff88924000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x7eff8872)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x7eff884ec000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x7eff882de000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x7eff880da000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x7eff87ebf000)
liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x7eff87cb)
libsasl2.so.3 => /lib64/libsasl2.so.3 (0x7eff87a93000)
libssl3.so => /lib64/libssl3.so (0x7eff8784f000)
libsmime3.so => /lib64/libsmime3.so (0x7eff87628000)
libnss3.so => /lib64/libnss3.so (0x7eff87302000)
libnssutil3.so => /lib64/libnssutil3.so (0x7eff870d5000)
libplds4.so => /lib64/libplds4.so (0x7eff86ed1000)
libplc4.so => /lib64/libplc4.so (0x7eff86ccc000)
libnspr4.so => /lib64/libnspr4.so (0x7eff86a8d000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x7eff86785000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x7eff8656f000)
libicudata.so.50 => /lib64/libicudata.so.50 (0x7eff84f9a000)
libcap.so.2 => /lib64/libcap.so.2 (0x7eff84d95000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x7eff84b6e000)
libgcrypt.so.11 => /lib64/libgcrypt.so.11 (0x7eff848ec000)
libgpg-error.so.0 => /lib64/libgpg-error.so.0 (0x7eff846e7000)
libdw.so.1 => /lib64/libdw.so.1 (0x7eff844a)
libcap-ng.so.0 => /lib64/libcap-ng.so.0 (0x7eff84299000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x7eff84062000)
libattr.so.1 => /lib64/libattr.so.1 (0x7eff83e5c000)
libpcre.so.1 => /lib64/libpcre.so.1 (0x7eff83bfa000)
libelf.so.1 => /lib64/libelf.so.1 (0x7eff839e2000)
libbz2.so.1 => /lib64/libbz2.so.1 (0x7eff837d1000)
libfreebl3.so => /lib64/libfreebl3.so (0x7eff835ce000)

On the line that has ldap in it:

libldap-2.4.so.2 => /lib64/libldap-2.4.so.2 (0x7eff89dd4000)

Sorry but in this case what is my libpq?

Regards,
Mike Yeap

On Thu, Feb 21, 2019 at 10:03 AM Tom Lane  wrote:

> Mike Yeap  writes:
> >> Are the "postgres" executable and libpq linked with the same version of
> >> OpenLDAP?
>
> > How should I check whether they are linked?
>
> "ldd" should show the dependencies of whatever executable or library
> you point it at.
>
> regards, tom lane
>