RE: Slow WAL recovery for DROP TABLE

[Jamison, Kirk]

Hi, I have also reported a similar problem in the hackers mailing list, but 
particularly on TRUNCATE TABLE.
https://www.postgresql.org/message-id/flat/D09B13F772D2274BB348A310EE3027C62FD6E6%40g01jpexmbkw24

The problem lies with the standby server’s replay as it does separate scanning 
of the whole shared buffer for each DROP/TRUNCATE TABLE in order to check if 
the table-to-delete is cached in shared buffer. Therefore, it will take a long 
recovery time and sometimes fail for large tables depending on shared_buffer 
size.

The main problem here is the scanning of shared_buffers, which not only affects 
drop/truncate table, but also drop database and vacuum as well. Other 
developers have contributed ideas and have been thinking of developing a buffer 
mapping implementation for PG12 that will solve all these, but it’s still a 
long shot from now.
But I think any working minor solutions/fixes from developers are also welcome, 
such as the recent committed patch for the multiple dropped tables per 
transaction with large shared_buffers.

Regards,
Kirk Jamison

From: Sherrylyn Branchaw [mailto:sbranc...@gmail.com]
Sent: Tuesday, July 17, 2018 11:41 PM
To: pgsql-gene...@postgresql.org
Subject: Slow WAL recovery for DROP TABLE

We are running Postgres 9.6.9 on RHEL 6.9. We're using built-in streaming 
replication, with a WAL archive for fallback purposes. No logical replication.

We recently had a bug in our code accidentally create several hundred thousand 
tables in a single database. A batch job started cleaning them up one at a time 
(separate transactions), over the course of a couple nights.

But what we noticed the next day was that the 2 standbys to this database were 
falling further and further behind. They were no longer streaming, instead they 
were recovering from the archive, and they were doing so very slowly.

Only a small amount of WAL was generated by the DROP TABLEs, relative to normal 
traffic, but the time to recover a single WAL file sometimes took over a 
minute, compared to 1 second normally. Meanwhile, new WALs were being generated 
by regular user traffic, and the database got 24 hours behind and getting 
worse, before we resynced the standbys, in order to not have to replay the 
WALs. That did the trick. But it seemed like this performance drop was drastic 
enough to be worth reporting.

I was able to reproduce the slow recovery time of WALs generated by DROP TABLE 
on a pair of test servers.

Normal writes (INSERT, UPDATE, DELETE, CREATE TABLE) generated WAL files that 
were recoverable on the order of 1 second each. The standby never fell behind, 
even when hundreds of WAL files were generated quickly. But when I generated 
WALs exclusively from DROP TABLE, it took 20-80 seconds to recover each one.

Some benchmarks:

For 10 minutes, I issued a bunch of rapid-fire INSERT statements to server1, 
the primary. These generated 144 WAL files. At the end of the 10 minutes, 
server2, the standby, had already recovered 143 of them and immediately 
completed the last one.

For 10 minutes, I issued a bunch of rapid-fire DROP TABLE statements to 
server1, the primary. These generated 28 WAL files. At the end of the 10 
minutes, server2, the standby, had only recovered 16 of them.

My expectation is that replaying binary changes on a standby should be at least 
as fast and sometimes faster than generating them locally. This is because when 
a logical command like "DROP TABLE" or "INSERT" is issued on a primary, the 
database has to check for dependencies, satisfy constraints, etc. But once all 
those decisions are made on the primary, the binary changes should be 
replayable much faster on the standby.

For example, server1, the primary in my test pair, has significantly better 
hardware than server2, the standby. If server2 is keeping up with server1 in 
replication, I predict that server2 should be able to get through the same 
amount of writes faster as a replica than as a standalone.

This expectation was borne out in my testing for INSERTs but not DROP TABLEs, 
per the following benchmarks.
I issued 9,000,000 INSERTs to server1, the primary. It took 10 minutes to 
complete these. At the end of the 10 minutes, server2, the standby, was caught 
up. When I made server2 a standalone, it took 15 minutes to get through the 
same 9,000,000 INSERTs. In conclusion, being a standalone was 50% slower for 
server2 when it came to INSERT.

I issued 100,000 DROP TABLEs to server1, the primary. It took 10 minutes to 
complete these on server 1, and 20 minutes for server2, the standby, to catch 
up. When I made server2 a standalone, it took 18 minutes to get through the 
same 100,000 DROP TABLEs. In conclusion, being a standalone was 10% faster for 
server2 when it came to DROP TABLE.

It seems that there is something especially inefficient about WAL files 
generated by DROP TABLE, or the process of recovering them.

I also want to mention that recovering a WAL file generated by DROP TABLE 
commands con

Re: User documentation vs Official Docs

[Tim Cross]

Peter Eisentraut  writes:

> On 17.07.18 02:13, Joshua D. Drake wrote:
>> On 07/16/2018 05:08 PM, Alvaro Herrera wrote:
>>>
>>> Sounds like wiki pages could solve need this pretty conveniently.  If
>>> and when the content is mature enough and migrates to the tutorial main
>>> documentation pages, the wiki pages can be replaced with redirects to
>>> those.
>> 
>> Anyone who writes a lot is going to rebel against using a wiki. They are 
>> one of the worst to write in from a productivity perspective. I would 
>> rather write in Docbook, at least then I can template everything and we 
>> could have a standard xsl sheet etc...
>
> I don't really buy that.  The wiki seems just fine for writing short to
> medium size how-to type articles.  We already have good content of that
> sort in the wiki right now.  It's not like there isn't going to be
> anyone who will rebel against any of the other tool chains that have
> been mentioned.

If using web widgets to author content on the wiki is the main
impediment for contributing content, maybe we should see if the wiki
provides alternative access methods. I've used wikis in the past which
allowed users to upload content via xmlrpc, api etc. Perhaps something
similar could be made available for those making significant
contributions or to a select few 'curators' who could accept content
from others.

If it is the interface that is the problem, we should try to address
that first rather than simply switching to something new which will have
its own problems. However, I don't know if this is the case or not.

Tim

-- 
Tim Cross

Re: control over database files

[David G. Johnston]

On Tuesday, July 17, 2018, Aaron Gray  wrote:
>
> I am wanting to be able to have control over what files that ables are
> stored in. Basically I am looking to have literally tens of thousands of
> tables all of the same type and to be able to store these as separate files
> in a filing system.
>

At first glance your needs and what PostgreSQL provides are incompatible
but you can learn the details in the documentation.

https://www.postgresql.org/docs/10/static/storage-file-layout.html

That said, you will likely find that the configuration variables that are
provided are sufficient to meet most needs and that actual control over
low-level details such as file and directory structure are something best
left to existing well written software such as PostgreSQL.

You might find that features such as row-level security and/or partitioning
(depending on why you need thousands of identical tables...) can either
reduce the need for them  or make setting them up considerably easier,
respectively.  IOW, starting with a higher level use case will produce more
useful responses.

David J.

Re: control over database files

[Adrian Klaver]

On 07/17/2018 02:50 PM, Aaron Gray wrote:

Hi,

I am wanting to be able to have control over what files that ables are 
stored in. Basically I am looking to have literally tens of thousands of 
tables all of the same type and to be able to store these as separate 
files in a filing system.


Because?

In sense that is what is being done anyway:

https://www.postgresql.org/docs/10/static/storage-file-layout.html



--
Aaron Gray

Independent Open Source Software Engineer, Computer Language Researcher, 
Information Theorist, and amateur computer scientist.



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

control over database files

[Aaron Gray]

Hi,

I am wanting to be able to have control over what files that ables are
stored in. Basically I am looking to have literally tens of thousands of
tables all of the same type and to be able to store these as separate files
in a filing system.

-- 
Aaron Gray

Independent Open Source Software Engineer, Computer Language Researcher,
Information Theorist, and amateur computer scientist.

Re: Can't compile postgresql 11 on FreeBSD 11.1

[Olivier Gautherot]

On Tue, Jul 17, 2018 at 4:30 PM, Christoph Moench-Tegeder <
c...@burggraben.net> wrote:

> ## Olivier Gautherot (oliv...@gautherot.net):
>
> > To: Christoph Moench-Tegeder 
>
> I think you meant to hit List-Reply...
>

Ooops, my bad...



> > I just pulled the .tar.bz2 archive and compiled it with no issue.
>
> In that case I suspect you have CFLAGS, CPPFLAGS and/or LDFLAGS
> set in your environment, or you are using a compiler with non-
> standard include/library paths. Default cc (clang) does not
> search in /usr/local by default (at least on 11.2, but that did
> not change with the update).
> Basically, the port's CONFIGURE_ARGS are there for a reason...
>

I compiled the standard archive and noticed that it used GCC 6.4.0 - and I
think it does include /usr/local/include in the standard header files path.
I checked the environment and there are no custom CFLAGS/CPPFLAGS/LDFLAGS.
My default shell is csh (I doubt it makes any difference).

My test machine was a FreeBSD 11.2, if that matters.

Márcio, do you have GCC installed?

Re: Can't compile postgresql 11 on FreeBSD 11.1

[Tom Lane]

Adrian Klaver  writes:
> On 07/17/2018 12:34 PM, Márcio Antônio Sepp wrote:
>> I'm trying to compile PostgreSQL 11beta2 but this errors occur:
>> checking readline.h usability... no

> Looks like you need whatever is the FreeBSD equivalent of readline-dev(el).

AFAICT FreeBSD doesn't do things that way.  On a nearby machine, I see

$ pkg which /usr/local/include/readline/readline.h
/usr/local/include/readline/readline.h was installed by package readline-6.3.8

What's more likely the problem is that FreeBSD insists on installing
packages under /usr/local, but it does *not* set that up to be part of
gcc's default search paths.  (Security 1, usability 0.)  You need these
configure flags to do much of anything on that platform:

  --with-includes=/usr/local/include --with-libs=/usr/local/lib

regards, tom lane

Re: Can't compile postgresql 11 on FreeBSD 11.1

[Christoph Moench-Tegeder]

## Márcio Antônio Sepp (mar...@zyontecnologia.com.br):

> I’m trying to compile PostgreSQL 11beta2 but this errors occur:
> 
> root@srvbacula:/postgresql/postgresql-11beta2 # ./configure

Stop right here and try using the same configure command line
as the port (postgresql10-server, as there's no v11 port yet).
At the very minimum, you will need "--with-libraries=/usr/local/lib"
and "--with-includes=/usr/local/include". That makes configure
pass for me.

Regards,
Christoph

-- 
Spare Space

Re: Can't compile postgresql 11 on FreeBSD 11.1

[Adrian Klaver]

On 07/17/2018 12:34 PM, Márcio Antônio Sepp wrote:

Hi,

I’m trying to compile PostgreSQL 11beta2 but this errors occur:




checking readline.h usability... no

checking readline.h presence... no

checking for readline.h... no

checking readline/readline.h usability... no

checking readline/readline.h presence... no

checking for readline/readline.h... no

configure: error: readline header not found

If you have libedit already installed, see config.log for details on the

failure.  It is possible the compiler isn't looking in the proper directory.

Use --without-readline to disable libedit support.

root@srvbacula:/postgresql/postgresql-11beta2 # pkg info | grep readline

readline-7.0.3_1   Library for editing command lines as they 
are typed


Looks like you need whatever is the FreeBSD equivalent of readline-dev(el).



root@srvbacula:/postgresql/postgresql-11beta2 #

thanks in advance

--

Att

Márcio A. Sepp




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

Can't compile postgresql 11 on FreeBSD 11.1

[Márcio Antônio Sepp]

 

Hi,

 

 

I’m trying to compile PostgreSQL 11beta2 but this errors occur:

 

root@srvbacula:/postgresql/postgresql-11beta2 # ./configure

checking build system type... x86_64-unknown-freebsd11.1

checking host system type... x86_64-unknown-freebsd11.1

checking which template to use... freebsd

checking whether NLS is wanted... no

checking for default port number... 5432

checking for block size... 8kB

checking for segment size... 1GB

checking for WAL block size... 8kB

checking for gcc... no

checking for cc... cc

checking whether the C compiler works... yes

checking for C compiler default output file name... a.out

checking for suffix of executables...

checking whether we are cross compiling... no

checking for suffix of object files... o

checking whether we are using the GNU C compiler... yes

checking whether cc accepts -g... yes

checking for cc option to accept ISO C89... none needed

checking for g++... no

checking for c++... c++

checking whether we are using the GNU C++ compiler... yes

checking whether c++ accepts -g... yes

checking whether cc supports -Wdeclaration-after-statement, for CFLAGS...
yes

checking whether cc supports -Wendif-labels, for CFLAGS... yes

checking whether c++ supports -Wendif-labels, for CXXFLAGS... yes

checking whether cc supports -Wmissing-format-attribute, for CFLAGS... yes

checking whether c++ supports -Wmissing-format-attribute, for CXXFLAGS...
yes

checking whether cc supports -Wformat-security, for CFLAGS... yes

checking whether c++ supports -Wformat-security, for CXXFLAGS... yes

checking whether cc supports -fno-strict-aliasing, for CFLAGS... yes

checking whether c++ supports -fno-strict-aliasing, for CXXFLAGS... yes

checking whether cc supports -fwrapv, for CFLAGS... yes

checking whether c++ supports -fwrapv, for CXXFLAGS... yes

checking whether cc supports -fexcess-precision=standard, for CFLAGS... no

checking whether c++ supports -fexcess-precision=standard, for CXXFLAGS...
no

checking whether cc supports -funroll-loops, for CFLAGS_VECTOR... yes

checking whether cc supports -ftree-vectorize, for CFLAGS_VECTOR... yes

checking whether cc supports -Wunused-command-line-argument, for
NOT_THE_CFLAGS... yes

checking whether cc supports -Wformat-truncation, for NOT_THE_CFLAGS... no

checking whether cc supports -Wstringop-truncation, for NOT_THE_CFLAGS... no

checking whether the C compiler still works... yes

checking how to run the C preprocessor... cc -E

checking allow thread-safe client libraries... yes

checking whether to build with ICU support... no

checking whether to build with Tcl... no

checking whether to build Perl modules... no

checking whether to build Python modules... no

checking whether to build with GSSAPI support... no

checking whether to build with PAM support... no

checking whether to build with BSD Authentication support... no

checking whether to build with LDAP support... no

checking whether to build with Bonjour support... no

checking whether to build with OpenSSL support... no

checking whether to build with SELinux support... no

checking whether to build with systemd support... no

checking for grep that handles long lines and -e... /usr/bin/grep

checking for egrep... /usr/bin/grep -E

checking for ld used by GCC... /usr/bin/ld

checking if the linker (/usr/bin/ld) is GNU ld... yes

checking for ranlib... ranlib

checking for strip... strip

checking whether it is possible to strip libraries... no

checking for ar... ar

checking for a BSD-compatible install... /usr/bin/install -c

checking for tar... /usr/bin/tar

checking whether ln -s works... yes

checking for gawk... no

checking for mawk... no

checking for nawk... nawk

checking for a thread-safe mkdir -p... config/install-sh -c -d

checking for bison... no

configure: WARNING:

*** Without Bison you will not be able to build PostgreSQL from Git nor

*** change any of the parser definition files.  You can obtain Bison from

*** a GNU mirror site.  (If you are using the official distribution of

*** PostgreSQL then you do not need to worry about this, because the Bison

*** output is pre-generated.)

checking for flex... /usr/bin/flex

configure: using flex 2.5.37

checking for perl... /usr/local/bin/perl

configure: using perl 5.26.2

checking for ANSI C header files... yes

checking for sys/types.h... yes

checking for sys/stat.h... yes

checking for stdlib.h... yes

checking for string.h... yes

checking for memory.h... yes

checking for strings.h... yes

checking for inttypes.h... yes

checking for stdint.h... yes

checking for unistd.h... yes

checking if compiler needs certain flags to reject unknown flags... no

checking for the pthreads library -lpthreads... no

checking whether pthreads work without any flags... no

checking whether pthreads work with -Kthread... no

checking whether pthreads work with -kthread... no

checking for the pthreads library -llthread... no

checking whether pthreads work with -pthread... yes

checking for joina

Re: Slow WAL recovery for DROP TABLE

[Jeff Janes]

There was a recent commit for a similar performance problem, which will
appear in 9.6.10.  But that was specifically for cases where there were
multiple dropped tables per transaction, and large shared_buffers.

I can't reproduce your single-drop-per-transaction problem.  The replica
has no problem keeping up with the master.

Can you share the reproduction scripts, and any non-default config
settings?  Especially the setting of shared_buffers (on both master and
replica, if different)

Cheers,

Jeff

Slow WAL recovery for DROP TABLE

[Sherrylyn Branchaw]

We are running Postgres 9.6.9 on RHEL 6.9. We're using built-in streaming
replication, with a WAL archive for fallback purposes. No logical
replication.

We recently had a bug in our code accidentally create several hundred
thousand tables in a single database. A batch job started cleaning them up
one at a time (separate transactions), over the course of a couple nights.

But what we noticed the next day was that the 2 standbys to this database
were falling further and further behind. They were no longer streaming,
instead they were recovering from the archive, and they were doing so very
slowly.

Only a small amount of WAL was generated by the DROP TABLEs, relative to
normal traffic, but the time to recover a single WAL file sometimes took
over a minute, compared to 1 second normally. Meanwhile, new WALs were
being generated by regular user traffic, and the database got 24 hours
behind and getting worse, before we resynced the standbys, in order to not
have to replay the WALs. That did the trick. But it seemed like this
performance drop was drastic enough to be worth reporting.

I was able to reproduce the slow recovery time of WALs generated by DROP
TABLE on a pair of test servers.

Normal writes (INSERT, UPDATE, DELETE, CREATE TABLE) generated WAL files
that were recoverable on the order of 1 second each. The standby never fell
behind, even when hundreds of WAL files were generated quickly. But when I
generated WALs exclusively from DROP TABLE, it took 20-80 seconds to
recover each one.

Some benchmarks:

For 10 minutes, I issued a bunch of rapid-fire INSERT statements to
server1, the primary. These generated 144 WAL files. At the end of the 10
minutes, server2, the standby, had already recovered 143 of them and
immediately completed the last one.

For 10 minutes, I issued a bunch of rapid-fire DROP TABLE statements to
server1, the primary. These generated 28 WAL files. At the end of the 10
minutes, server2, the standby, had only recovered 16 of them.

My expectation is that replaying binary changes on a standby should be at
least as fast and sometimes faster than generating them locally. This is
because when a logical command like "DROP TABLE" or "INSERT" is issued on a
primary, the database has to check for dependencies, satisfy constraints,
etc. But once all those decisions are made on the primary, the binary
changes should be replayable much faster on the standby.

For example, server1, the primary in my test pair, has significantly better
hardware than server2, the standby. If server2 is keeping up with server1
in replication, I predict that server2 should be able to get through the
same amount of writes faster as a replica than as a standalone.

This expectation was borne out in my testing for INSERTs but not DROP
TABLEs, per the following benchmarks.

I issued 9,000,000 INSERTs to server1, the primary. It took 10 minutes to
complete these. At the end of the 10 minutes, server2, the standby, was
caught up. When I made server2 a standalone, it took 15 minutes to get
through the same 9,000,000 INSERTs. In conclusion, being a standalone was
50% slower for server2 when it came to INSERT.

I issued 100,000 DROP TABLEs to server1, the primary. It took 10 minutes to
complete these on server 1, and 20 minutes for server2, the standby, to
catch up. When I made server2 a standalone, it took 18 minutes to get
through the same 100,000 DROP TABLEs. In conclusion, being a standalone was
10% faster for server2 when it came to DROP TABLE.

It seems that there is something especially inefficient about WAL files
generated by DROP TABLE, or the process of recovering them.

I also want to mention that recovering a WAL file generated by DROP TABLE
commands consistently takes approximately 100% CPU on the standby, both in
testing and in prod. In prod, we also saw high memory use for that process.

The standby with 128 GB of RAM showed this:

PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND


23279 postgres  20   0 57.0g  50g 5.1g R 100.0 40.0   1723:15 postgres:
startup process   recovering 000203B1008D

The standby with 32 GB of RAM crashed overnight with an out-of-memory error.

When I restarted the servers and let them resume recovering WALs, I watched
%MEM and RES grow slowly and steadily before I gave up and resynced. No
memory appeared to be freed up after each WAL file was replayed.

I was not able to reproduce memory usage getting quite this high in the
time I was allotted for testing, but I did witness steady climbing up to
almost 20%, especially when I mixed DROP TABLEs with regular writes. I
suspect there is a sweet (or bitter?) spot of write activity we hit in our
prod workload that I was unable to reproduce in testing.

I'm not sure if this is the right mailing list to report this behavior to,
but I thought it was the best place to start. Let me know if there's any
other information I should provide, or if I should send it to another list.

Thanks,
Sherrylyn

Re: watchdog issues

[Adrian Klaver]

On 07/17/2018 07:01 AM, Jean Claude wrote:

Hello Guys'

I have implement a pgpool-II cluster and the standby service want not to 
add on the watchdog info..

Some issues for help?



Jul 17 09:55:59 test01 pgpool[2166]: [223-2] 2018-07-17 09:55:59: pid 
2166: DETAIL:  backend response with kind 'E' when expecting 'R'
Jul 17 09:55:59 test01 pgpool[2166]: [223-3] 2018-07-17 09:55:59: pid 
2166: HINT:  This issue can be caused by version mismatch (current 
version 2)


I don't use pgpool-II, but I would take the above as indicator that 
there may be different versions of pgpool-II trying to communicate with 
each other and failing.


So what are the versions for each pgpool-II node?


[root@test01 pgpool-II-10]#





Thanks in advance,
Jean




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

watchdog issues

[Jean Claude]

Hello Guys'

I have implement a pgpool-II cluster and the standby service want not to
add on the watchdog info..
Some issues for help?

[root@tes01 ~]# /usr/pgpool-10/bin/pcp_watchdog_info -p 9898 -U pgpool
Password:
2 YES test01.adm.test.ch:5432 Linux test01 test01.test.cacc.ch

test01.adm.test.ch:5432 Linux pgpool01 test01.test.cacc.ch 5432 9000 4
MASTER
Not_Set test02.test.cacc.ch  9000 0 DEAD

-
test01 active

├─12725 pgpool: wait for connection request
├─12784 pgpool: wait for connection request
└─14564 pgpool: wait for connection request

Jul 17 09:55:59 test01 pgpool[2925]: [431-1] 2018-07-17 09:55:59: pid 2925:
LOG:  new connection received
Jul 17 09:55:59 test01 pgpool[2925]: [431-2] 2018-07-17 09:55:59: pid 2925:
DETAIL:  connecting host=test01 port=59438
Jul 17 09:55:59 test01 pgpool[2925]: [432-1] 2018-07-17 09:55:59: pid 2925:
ERROR:  backend authentication failed
Jul 17 09:55:59 test01 pgpool[2925]: [432-2] 2018-07-17 09:55:59: pid 2925:
DETAIL:  backend response with kind 'E' when expecting 'R'
Jul 17 09:55:59 test01 pgpool[2925]: [432-3] 2018-07-17 09:55:59: pid 2925:
HINT:  This issue can be caused by version mismatch (current version 3)
Jul 17 09:55:59 test01 pgpool[2166]: [222-1] 2018-07-17 09:55:59: pid 2166:
LOG:  new connection received
Jul 17 09:55:59 test01 pgpool[2166]: [222-2] 2018-07-17 09:55:59: pid 2166:
DETAIL:  connecting host=test01 port=59442
Jul 17 09:55:59 test01 pgpool[2166]: [223-1] 2018-07-17 09:55:59: pid 2166:
ERROR:  backend authentication failed
Jul 17 09:55:59 test01 pgpool[2166]: [223-2] 2018-07-17 09:55:59: pid 2166:
DETAIL:  backend response with kind 'E' when expecting 'R'
Jul 17 09:55:59 test01 pgpool[2166]: [223-3] 2018-07-17 09:55:59: pid 2166:
HINT:  This issue can be caused by version mismatch (current version 2)
[root@test01 pgpool-II-10]#


-
test02 standby

pgpool-log in my standby test02 :

Jul 13 11:38:08 test02 pgpool[824]: [7-1] 2018-07-13 11:38:08: pid 824:
FATAL:  Add to watchdog cluster request is rejected by node "
pgpool01.adm.cacc.ch:9000"
Jul 13 11:38:08 test02 pgpool[824]: [7-2] 2018-07-13 11:38:08: pid 824:
HINT:  check the watchdog configurations.
Jul 13 11:38:08 test02 pgpool[824]: [8-1] 2018-07-13 11:38:08: pid 824:
LOG:  Watchdog is shutting down
Jul 13 11:38:08 test02 pgpool[776]: [2-1] 2018-07-13 11:38:08: pid 776:
LOG:  watchdog child process with pid: 824 exits with status 768
Jul 13 11:38:08 test02 pgpool[776]: [3-1] 2018-07-13 11:38:08: pid 776:
FATAL:  watchdog child process exit with fatal error. exiting pgpool-II
Jul 13 11:38:08 test02 systemd[1]: pgpool-II-10.service: main process
exited, code=exited, status=3/NOTIMPLEMENTED
Jul 13 11:38:08 test02 pgpool[830]: [1-1] 2018-07-13 11:38:08: pid 830:
FATAL:  could not read pid file
Jul 13 11:38:08 test02 systemd[1]: pgpool-II-10.service: control process
exited, code=exited status=3
Jul 13 11:38:08 test02 systemd[1]: Unit pgpool-II-10.service entered failed
state.
Jul 13 11:38:08 test02 systemd[1]: pgpool-II-10.service failed

Thanks in advance,
Jean

Re: User documentation vs Official Docs

[Adrian Klaver]

On 07/16/2018 04:56 PM, Joshua D. Drake wrote:

On 07/16/2018 04:33 PM, Adrian Klaver wrote:




I did it! Want to help? I think if we got together 5-7 people and came 
up with a proposal we could submit to -www/-core and get some buy in.


Given the really discovered existence of the tutorial pages I would say 
that is the way to go, rather then fragment the user documentation on to 
another site. It would seem to meet the need for step by step 
instructions on common tasks. For more specialized cases adding a layer 
of organization to the Wiki could prove useful. I would be willing to 
help as needed. Contact me offline to carry on that conversation.




JD




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

Re: User documentation vs Official Docs

[Magnus Hagander]

On Tue, Jul 17, 2018 at 1:47 PM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 17.07.18 02:13, Joshua D. Drake wrote:
> > On 07/16/2018 05:08 PM, Alvaro Herrera wrote:
> >>
> >> Sounds like wiki pages could solve need this pretty conveniently.  If
> >> and when the content is mature enough and migrates to the tutorial main
> >> documentation pages, the wiki pages can be replaced with redirects to
> >> those.
> >
> > Anyone who writes a lot is going to rebel against using a wiki. They are
> > one of the worst to write in from a productivity perspective. I would
> > rather write in Docbook, at least then I can template everything and we
> > could have a standard xsl sheet etc...
>
> I don't really buy that.  The wiki seems just fine for writing short to
> medium size how-to type articles.  We already have good content of that
> sort in the wiki right now.  It's not like there isn't going to be
> anyone who will rebel against any of the other tool chains that have
> been mentioned.
>

I think the biggest problem with the wiki for that type of content has
nothing to do with the formatting, and everything to do with the structure.
By definition the wiki is unstructured. One could put a structure on top of
it, with proper categories and indexing pages. That's done for some info on
it, but not for all. There's also a lot of outdated information.

Both those things are things that could be solved by somebody with the time
and willingness to trawl through the wiki to update such things, and then
to keep things updated. But keeping it updated is an equal amount of work
regardless of platform. If we find somebody who wants to do that, then at
least *starting out* on the wiki is a good idea. It's usually a Good Enough
(TM) system. And the most common things I see people writing such things in
today are Markdown (hi github!) or RST (hi Sphinx!) anyway, both of which
are pretty similar to the wiki markup. Which means that the project could
*start out* using the wiki, and once there is enough content to prove the
idea other platforms could be looked at and it would be easy enough to
migrate that data out there (even if just by copy/paste) if it becomes a
need.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: User documentation vs Official Docs

[Peter Eisentraut]

On 17.07.18 02:13, Joshua D. Drake wrote:
> On 07/16/2018 05:08 PM, Alvaro Herrera wrote:
>>
>> Sounds like wiki pages could solve need this pretty conveniently.  If
>> and when the content is mature enough and migrates to the tutorial main
>> documentation pages, the wiki pages can be replaced with redirects to
>> those.
> 
> Anyone who writes a lot is going to rebel against using a wiki. They are 
> one of the worst to write in from a productivity perspective. I would 
> rather write in Docbook, at least then I can template everything and we 
> could have a standard xsl sheet etc...

I don't really buy that.  The wiki seems just fine for writing short to
medium size how-to type articles.  We already have good content of that
sort in the wiki right now.  It's not like there isn't going to be
anyone who will rebel against any of the other tool chains that have
been mentioned.

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

Re: How to create logical replication slot with NOEXPORT_SNAPSHOT in jdbc

[Peter Eisentraut]

On 17.07.18 03:41, Igor Polishchuk wrote:
> We are trying to use logical decoding for detecting database changes.
> However, when we create a replication slot, the data processing pauses
> if there are still transactions running from before the slot creation.
> If I understand correctly, the slot is waiting for creating a consistent
> snapshot and is blocked by the long transactions.
> In our application, we don't need it, as we only want to see if some
> tables were modified. Is it possible to create a  logical replication
> slot with  NOEXPORT_SNAPSHOT option using jdbc?

That doesn't do what you want.  You still need to wait for the snapshot
to be created; there is no way around that.  The NOEXPORT_SNAPSHOT
option just means that the snapshot, once created, won't be exported for
use by other sessions.

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

Re: Query to monitor index bloat

[Fabio Pardi]

you have your points, my friend.


On 07/17/2018 11:23 AM, Jehan-Guillaume (ioguix) de Rorthais wrote:
> On Tue, 17 Jul 2018 11:03:08 +0200
> Fabio Pardi  wrote:
> 
>> On 07/17/2018 10:21 AM, Jehan-Guillaume (ioguix) de Rorthais wrote:
>>> On Tue, 17 Jul 2018 10:11:50 +0200  
>>
>>> ...and check this one: https://github.com/OPMDG/check_pgactivity/
>>>
>>> It uses bloat queries for tables and btree indexes Adrien Nayrat was
>>> pointing earlier in this thread.
>>>
>>> In fact, both queries in check_pgactivity were written because the bloat
>>> check in check_postgres was considering **all** fields were in **all***
>>> indexes..  
>>
>> not accurately, since it is excluding a few things.
>>
>> from the docs:
>> 'Tables must have at least 10 pages, and indexes at least 15, before
>> they can be considered by this test.'
> 
> well I agree with this. What the point of computing bloat for small objects? I
> would raise this way higher.
> 
>> + you can include and exclude objects based on your taste, same as in
>> check_pgactivity.
>>
>> The only 'drawback' of check_postgres.pl is that it checks indexes and
>> tables's bloat in one go. (but: if your object's names are normalized,
>> it should not be difficult to include or exclude them)
>> I do not consider it a drawback, but you are free to pick your poison...
> 
> Well, again, the btree approximation is quite large in check_postgres. I would
> not rely on it detect bloat quickly. **If this is still true**, as it 
> considers
> all fields are in the index, the estimated index size might be very
> large compared to the real one.
> 
> But, again, this is a few years I did not digg in this query, I mmight be 
> wrong.
> 
>>> Which is quite a large approximation...I don't know if this is still
>>> the case though.  
>>
>> While i think both tools might fit Alessandro's purpose, please note
>> that check_pgactivity is **only** checking for btree indexes (which are
>> the default ones, and the proven-to-get-bloated-quickly)
>>
>> If I were you (both), I would monitor **all** indexes (and yes! tables
>> too), since one day you might realize it was actually a good idea to do so.
> 
> I agree, we should monitor all indexes. If you have some formula to quickly
> estimate ideal size of a GIN, GiST, hash or sp-gist indexes, please share. 
> But,
> unfortunately, as far as I know, this is way more complex than just summing 
> the
> average size of the fields in the index :/
> 

Re: 65279 Invisible ASCII Character

[ramsiddu007]

Thanks, I'll look into it.

On Tue, Jul 17, 2018 at 3:42 PM Christoph Moench-Tegeder 
wrote:

> ## ramsiddu007 (ramsiddu...@gmail.com):
>
> >  If i remove first character it's run. That first
> > character is invisible, I have checked that *ascii* value, it is *65279*.
>
> That's not an ASCII-value, ASCII has 8 bits at most.
> What you've got there is a UTF-16 Byte Order Mark: 65279 is 0xfeff
> (one of the well-known constants).
> I'd suggest you get your editor configured to write files without
> BOM. Maybe there's a workaround via locale settings - but I have
> no machine with an UTF-16 locale available. Another approach is using
> recode on your files before concatenating.
>
> This question isn't really for pgsql-hackers - I'm redirecting to -general.
>
> Regards,
> Christoph
>
> --
> Spare Space
>


-- 
*Best Regards:*
Ramanna Gunde

Re: PG backup check

[Magnus Hagander]

On Tue, Jul 17, 2018 at 11:17 AM, Guillaume Lelarge 
wrote:

> 2018-07-17 2:35 GMT+02:00 Ravi Krishna :
>
>> Not sure I am following this.  Did Google release this because PG backups
>> are not 100% reliable or the data corruption can occur due to hardware
>> failure.
>>
>> http://www.eweek.com/cloud/google-releases-open-source-tool-
>> that-checks-postgres-backup-integrity?utm_medium=email&
>> utm_campaign=EWK_NL_EP_20180713_STR5L2&dni=450493554&rni=24844166
>>
>
> From what I understand with this Google tool, it has nothing to do with
> backups. It just allows you to check data blocks in a PostgreSQL cluster.
> Google advice is to run it before taking a backup, but that's about it.
>
>
This appears to basically be the same tool that's already included in
PostgreSQL 11, and has been around in a few different incarnations (but
unpolished) for years.

FWIW, in relation to backups, tools like pgbackrest already did this
transparently during backup, and again PostgreSQL 11 will do it built-in.

It's quite possible Google was running this internally before of course,
and a separate tool from others, but it's not exactly news...  But they do
outline a very definite problem, which is that if you get physical
corruption in your database, it gets included in the backups. And if it's
in a portion of the database you don't use a lot, checksum failures won't
be noticed until you actually try, which is way too late.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Query to monitor index bloat

[Jehan-Guillaume (ioguix) de Rorthais]

On Tue, 17 Jul 2018 11:03:08 +0200
Fabio Pardi  wrote:

> On 07/17/2018 10:21 AM, Jehan-Guillaume (ioguix) de Rorthais wrote:
> > On Tue, 17 Jul 2018 10:11:50 +0200  
> 
> > ...and check this one: https://github.com/OPMDG/check_pgactivity/
> > 
> > It uses bloat queries for tables and btree indexes Adrien Nayrat was
> > pointing earlier in this thread.
> > 
> > In fact, both queries in check_pgactivity were written because the bloat
> > check in check_postgres was considering **all** fields were in **all***
> > indexes..  
> 
> not accurately, since it is excluding a few things.
> 
> from the docs:
> 'Tables must have at least 10 pages, and indexes at least 15, before
> they can be considered by this test.'

well I agree with this. What the point of computing bloat for small objects? I
would raise this way higher.

> + you can include and exclude objects based on your taste, same as in
> check_pgactivity.
> 
> The only 'drawback' of check_postgres.pl is that it checks indexes and
> tables's bloat in one go. (but: if your object's names are normalized,
> it should not be difficult to include or exclude them)
> I do not consider it a drawback, but you are free to pick your poison...

Well, again, the btree approximation is quite large in check_postgres. I would
not rely on it detect bloat quickly. **If this is still true**, as it considers
all fields are in the index, the estimated index size might be very
large compared to the real one.

But, again, this is a few years I did not digg in this query, I mmight be wrong.

> > Which is quite a large approximation...I don't know if this is still
> > the case though.  
> 
> While i think both tools might fit Alessandro's purpose, please note
> that check_pgactivity is **only** checking for btree indexes (which are
> the default ones, and the proven-to-get-bloated-quickly)
> 
> If I were you (both), I would monitor **all** indexes (and yes! tables
> too), since one day you might realize it was actually a good idea to do so.

I agree, we should monitor all indexes. If you have some formula to quickly
estimate ideal size of a GIN, GiST, hash or sp-gist indexes, please share. But,
unfortunately, as far as I know, this is way more complex than just summing the
average size of the fields in the index :/

Re: PG backup check

[Guillaume Lelarge]

2018-07-17 2:35 GMT+02:00 Ravi Krishna :

> Not sure I am following this.  Did Google release this because PG backups
> are not 100% reliable or the data corruption can occur due to hardware
> failure.
>
> http://www.eweek.com/cloud/google-releases-open-source-
> tool-that-checks-postgres-backup-integrity?utm_medium=
> email&utm_campaign=EWK_NL_EP_20180713_STR5L2&dni=450493554&rni=24844166
>

>From what I understand with this Google tool, it has nothing to do with
backups. It just allows you to check data blocks in a PostgreSQL cluster.
Google advice is to run it before taking a backup, but that's about it.


-- 
Guillaume.

Re: Query to monitor index bloat

[Fabio Pardi]

On 07/17/2018 10:21 AM, Jehan-Guillaume (ioguix) de Rorthais wrote:
> On Tue, 17 Jul 2018 10:11:50 +0200

> ...and check this one: https://github.com/OPMDG/check_pgactivity/
> 
> It uses bloat queries for tables and btree indexes Adrien Nayrat was pointing
> earlier in this thread.
> 
> In fact, both queries in check_pgactivity were written because the bloat check
> in check_postgres was considering **all** fields were in **all***
> indexes..

not accurately, since it is excluding a few things.

from the docs:
'Tables must have at least 10 pages, and indexes at least 15, before
they can be considered by this test.'

+ you can include and exclude objects based on your taste, same as in
check_pgactivity.

The only 'drawback' of check_postgres.pl is that it checks indexes and
tables's bloat in one go. (but: if your object's names are normalized,
it should not be difficult to include or exclude them)
I do not consider it a drawback, but you are free to pick your poison...


.Which is quite a large approximation...I don't know if this is still
> the case though.

While i think both tools might fit Alessandro's purpose, please note
that check_pgactivity is **only** checking for btree indexes (which are
the default ones, and the proven-to-get-bloated-quickly)

If I were you (both), I would monitor **all** indexes (and yes! tables
too), since one day you might realize it was actually a good idea to do so.

regards,

fabio pardi

Re: Query to monitor index bloat

[Jehan-Guillaume (ioguix) de Rorthais]

On Tue, 17 Jul 2018 10:11:50 +0200
Fabio Pardi  wrote:

> If you monitor using nagios (or if you want to make a wrapper around it):
> 
> https://bucardo.org/check_postgres/check_postgres.pl.html#bloat

...and check this one: https://github.com/OPMDG/check_pgactivity/

It uses bloat queries for tables and btree indexes Adrien Nayrat was pointing
earlier in this thread.

In fact, both queries in check_pgactivity were written because the bloat check
in check_postgres was considering **all** fields were in **all***
indexes...Which is quite a large approximation...I don't know if this is still
the case though.

> works like a charm.
> 
> regards,
> 
> fabio pardi
> 
> 
> On 07/16/2018 05:16 PM, Alessandro Aste wrote:
> > Hello,  I am trying to put togheter a query to monitor the index bloat
> > for a database I maintain.
> > Is there a "SQL" way to obtain  bloated index ? I googled around but I
> > found nothing working.
> > 
> > I'm currently running 9.6 but I'm looking for something compatible with
> > version 10 too.
> > 
> > Thank you very much in advance,

Re: Query to monitor index bloat

[Fabio Pardi]

If you monitor using nagios (or if you want to make a wrapper around it):

https://bucardo.org/check_postgres/check_postgres.pl.html#bloat

works like a charm.

regards,

fabio pardi


On 07/16/2018 05:16 PM, Alessandro Aste wrote:
> Hello,  I am trying to put togheter a query to monitor the index bloat
> for a database I maintain.
> Is there a "SQL" way to obtain  bloated index ? I googled around but I
> found nothing working.
> 
> I'm currently running 9.6 but I'm looking for something compatible with
> version 10 too.
> 
> Thank you very much in advance,
> 
> 
> Alessandro.