Re: postgresql11-devel RPM is missing from "Direct RPM downloads"

[Jakub Olczyk]

Hi Dimiry,

On 04/06/2019 23:49, Dmitry O Litvintsev wrote:
> Where can I find postgresq11-devel RPM ?
> [...]
> https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/

have you looked under the letter "P" in your first link?
There you can find the -devel package.

https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/letter_p.group.html

Cheers!
Jakub

RE: Query very different speeds on seemingly similar data

[Rob Northcott]

>-Original Message-
>From: David Rowley  
>Sent: 05 June 2019 01:48
>To: Rob Northcott 
>Cc: pgsql-general@lists.postgresql.org
>Subject: Re: Query very different speeds on seemingly similar data

>On Wed, 5 Jun 2019 at 04:55, Rob Northcott  
>wrote:
>> Explain files attached (assuming attachments will get through to the 
>> group – otherwise what’s the best way to post it?)

>You're best to post the EXPLAIN ANALYZE output to https://explain.depesz.com  
>what you attached is pretty horrible to read and details are only gained by 
>hovering the >mouse cursor over the node. I for one struggle to work out 
>what's the inner and outer sides of the join with the output you've given and 
>can only work it out by looking >at the nested loop plan to see which side the 
>parameter is on.

>It appears that the sub-query plan has changed from a Nested Loop plan to a 
>Merge Join. The Merge Join is a pretty bad plan since the index that provides 
>the pre-sorted input must filter out many non-matching rows. It's not quite 
>clear to me why the planner chooses that index, mostly because I'm too lazy to 
>learn the output you've shown the plans in, but if you did have an index on 
>sales_invoicedetails (std_stk_key, std_unique), then the Merge Join plan would 
>likely produce a better plan, or at least better than the current Merge Join 
>plan.

You'll likely want to ensure that random_page_cost has not been set to 
something insane on the Merge Join instance.  I'd also check seq_page_cost too 
and also effective_cache_size.

More recommendations might be easier to give if you show the plans in a better 
format.

Apart from that, you could consider also instead of performing a sub-query, 
LEFT JOIN to a query similar to your subquery but after removing the 
std_stk_key = stock.stk_key condition and adding a GROUP BY std_stk_key. 
However, that may perform worse if there are many more std_stk_key groups than 
there are matching rows in stock.stk_key.
Experimentation might be required there.


-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services





Thanks for the reply David,

Those output files came from PGAdmin, but I agree they're not the easiest to 
look at.  I've tried writing the query in different ways (which is easy enough 
with the minimal query here but not so easy with the original massive query), 
but it's still way slower on one set of data than on the other.

I'll try changing those settings on the test server and see if it makes any 
difference... We've got almost 100 databases running the same system (on the 
same server) and this is the only one that's being a problem so far, but it 
could of course just be that we've been lucky.

If I can't get anywhere with that I'll try to post the analyse output in a 
better format.

Thanks again for your reply

Rob

Re: granting right to create and delete just one database

[Laurenz Albe]

Chris Withers wrote:
> Is there any way to grant rights to a user such that they can drop and 
> re-create only a single database?

No; what I'd do if I needed that is to create a SECURITY DEFINER function
that is owned by a user with the CREATEDB privilege.
This function can be called by a normal user that has the EXECUTE privilege
on the function.

Don't forget to "SET search_path" on such a function (as mentioned in the
documentation).  It might also be a good idea to REVOKE EXECUTE on the
function from PUBLIC.

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

Re: granting right to create and delete just one database

[Chris Withers]

On 05/06/2019 09:52, Laurenz Albe wrote:

Chris Withers wrote:

Is there any way to grant rights to a user such that they can drop and
re-create only a single database?

No; what I'd do if I needed that is to create a SECURITY DEFINER function
that is owned by a user with the CREATEDB privilege.
This function can be called by a normal user that has the EXECUTE privilege
on the function.

Don't forget to "SET search_path" on such a function (as mentioned in the
documentation).  It might also be a good idea to REVOKE EXECUTE on the
function from PUBLIC.
Thanks, that's a great idea! Is this pattern documented anywhere as a 
complete finished thing?


cheers,

Chris

Re: Long running query - connection and keepalives enabled but query not canceled

[Tomasz Ostrowski]

On 6/4/19 6:24 PM, Laurenz Albe wrote:


Tomasz Ostrowski wrote:


The TCP keepalives functionality on the database server's operating
system would figure out that the connection is not working anymore and
close it.

You'd assume that the database would get the info and cancel the query.
Except - it does not.

The database would get an error on the connection socket only after it
tries to read or write to it next time. But it does not try to do this -
it's busy counting those quarks until statement_timeout is reached.


By default "tcp_keepalives_idle" is quite large: 2 hours.

Are you sure that the queries keep running for longer than that?

Try to experiment with lower settings.  It will cause marginally more
network traffic, but dead connections will be detected more quickly.


The keepalive settings we use is idle/interval/count=60/10/60. It means 
that the the dead connections should be cleared after 60s+10*60s=11m. I 
started to investigate when I found a query running for over 11 days 
(there was no query_timeout set on this server).


But the problem is not that the dead connections aren't detected - they 
are, and the operating system clears them. They disappear from "netstat 
--tcp" output after expected time.


The problem is that the database does not get the info and does not kill 
the query.


I've reproduced this with running the SQL below on a local Postgresql 11 
connected with TCP, configured with idle/interval/count=15/15/15:


  create or replace function pg_temp.fib(n int) returns int language 
plpgsql as $$
begin if n<=1 then return n; end if; return 
pg_temp.fib(n-1)+pg_temp.fib(n-2); end;

  $$;
  select pg_temp.fib(50);

And then filtering out the connection with iptables.

--
Tomasz "Tometzky" Ostrowski

pg_repack issue

[Prakash Ramakrishnan]

Hi Team,

While during the pg_repack installation getting below error please someone
help

postg...@brurhen001.enterprisenet.org:
/home/postgres/software/pg_repack-1.4.4
==> ll
total 16
drwx--. 3 postgres postgres   71 Oct 18  2018 bin
-rw---. 1 postgres postgres 1662 Oct 18  2018 COPYRIGHT
drwx--. 2 postgres postgres  121 Oct 18  2018 doc
drwx--. 3 postgres postgres  139 Oct 18  2018 lib
-rw---. 1 postgres postgres 1616 Oct 18  2018 Makefile
-rw---. 1 postgres postgres 1285 Oct 18  2018 META.json
drwx--. 2 postgres postgres  219 Oct 18  2018 msvc
-rw---. 1 postgres postgres 2201 Oct 18  2018 README.rst
drwx--. 4 postgres postgres   96 Oct 18  2018 regress
drwx--. 2 postgres postgres   54 Oct 18  2018 SPECS
postg...@brurhen001.enterprisenet.org:
/home/postgres/software/pg_repack-1.4.4
==> make
make[1]: Entering directory `/home/postgres/software/pg_repack-1.4.4/bin'
gcc -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions
-fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches
-m64 -mtune=generic -I/usr/pgsql-11/include -DREPACK_VERSION=1.4.4 -I. -I./
-I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal
 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o pg_repack.o
pg_repack.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions
-fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches
-m64 -mtune=generic -I/usr/pgsql-11/include -DREPACK_VERSION=1.4.4 -I. -I./
-I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal
 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o pgut/pgut.o
pgut/pgut.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions
-fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches
-m64 -mtune=generic -I/usr/pgsql-11/include -DREPACK_VERSION=1.4.4 -I. -I./
-I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal
 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o pgut/pgut-fe.o
pgut/pgut-fe.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions
-fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches
-m64 -mtune=generic pg_repack.o pgut/pgut.o pgut/pgut-fe.o
 -L/usr/pgsql-11/lib -Wl,--as-needed -L/usr/lib64/llvm5.0/lib  -L/usr/lib64
-Wl,--as-needed -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags
 -L/usr/pgsql-11/lib -lpq -L/usr/pgsql-11/lib -lpgcommon -lpgport -lpthread
-lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_repack




*/usr/bin/ld: cannot find -lreadlinecollect2: error: ld returned 1 exit
statusmake[1]: *** [pg_repack] Error 1make[1]: Leaving directory
`/home/postgres/software/pg_repack-1.4.4/bin'make: *** [all] Error 2*

-

Re: Long running query - connection and keepalives enabled but query not canceled

[Dmitry Vasiliev]

Tomasz Ostrowski wrote:

> The database would get an error on the connection socket only after it tries 
> to read or write to it next time. But it does not try to do this - it's busy 
> counting those quarks until statement_timeout is reached.
> 


It is unfortunate that PostgreSQL does not take into account that the client 
has disconnected and does not need to process query.
I know only one non-stable utility that can solve your problem: 
https://github.com/yandex/odyssey  
(unfortunately pgbouncer can't do that).

-- 
Dmitry Vasiliev

Re: pg_repack issue

[Peter Eisentraut]

On 2019-06-05 12:48, Prakash Ramakrishnan wrote:
> gcc -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
> -Wformat-security -fno-strict-aliasing -fwrapv
> -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2
> -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4
> -grecord-gcc-switches -m64 -mtune=generic pg_repack.o pgut/pgut.o
> pgut/pgut-fe.o  -L/usr/pgsql-11/lib -Wl,--as-needed
> -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed
> -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags  -L/usr/pgsql-11/lib
> -lpq -L/usr/pgsql-11/lib -lpgcommon -lpgport -lpthread -lssl -lcrypto
> -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_repack
> */usr/bin/ld: cannot find -lreadline
> collect2: error: ld returned 1 exit status
> make[1]: *** [pg_repack] Error 1
> make[1]: Leaving directory `/home/postgres/software/pg_repack-1.4.4/bin'
> make: *** [all] Error 2*

It's a bit bogus that pg_repack would require this, but perhaps
installing the readline-devel (or similar) package would get you past this.

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

Re: pg_repack issue

[Prakash Ramakrishnan]

Hi Peter,

Thanks i have successfully created the extension how to use full vacuum
using pg_repack.

Regards,
Prakash.R

On Wed, Jun 5, 2019 at 5:23 PM Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 2019-06-05 12:48, Prakash Ramakrishnan wrote:
> > gcc -Wall -Wmissing-prototypes -Wpointer-arith
> > -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
> > -Wformat-security -fno-strict-aliasing -fwrapv
> > -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2
> > -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4
> > -grecord-gcc-switches -m64 -mtune=generic pg_repack.o pgut/pgut.o
> > pgut/pgut-fe.o  -L/usr/pgsql-11/lib -Wl,--as-needed
> > -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed
> > -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags  -L/usr/pgsql-11/lib
> > -lpq -L/usr/pgsql-11/lib -lpgcommon -lpgport -lpthread -lssl -lcrypto
> > -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_repack
> > */usr/bin/ld: cannot find -lreadline
> > collect2: error: ld returned 1 exit status
> > make[1]: *** [pg_repack] Error 1
> > make[1]: Leaving directory `/home/postgres/software/pg_repack-1.4.4/bin'
> > make: *** [all] Error 2*
>
> It's a bit bogus that pg_repack would require this, but perhaps
> installing the readline-devel (or similar) package would get you past this.
>
> --
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>


-- 
Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

Re: Two small questions re/ COPY CSV data into table

[Matthias Apitz]

El día Tuesday, June 04, 2019 a las 07:20:54PM +0200, Matthias Apitz escribió:

> El día Tuesday, June 04, 2019 a las 05:56:49PM +0100, Andrew Gierth escribió:
> 
> > If you use COPY ... WITH DELIMITER '|' (i.e. text mode, not CSV mode)
> > then the \| is accepted as being a literal | and the unescaped | is
> > treated as a delimiter. What is the point of the substitutions?
> 
> ...

> I will provide tomorrow the exact input line, the exact COPY command and
> the error.

It works exactly as Andrew states. My error was not caused by treating
the '|' of '\|' as DELIMITER, but by not having WITH (NULL '' ...) in the COPY 
cmd.
I misinterpreted the error message as not matching number of columns.
Sorry for the noise.

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

Postgresql-11 confusing situtation

[Domen Šetar]

Hi,

I’m using Postgresql 11.3 on Centos 7.6.18.10 server.
I have very unusual issue which I don’t know if it is OK:

I have running postgresql server with default instance:

[root@pgpmaster ~]# systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; 
vendor preset: disabled)
   Active: active (running) since Wed 2019-06-05 14:37:21 CEST; 49s ago
 Docs: https://www.postgresql.org/docs/11/static/
  Process: 3552 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir 
${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 3576 (postmaster)
Tasks: 9
   CGroup: /system.slice/postgresql-11.service
   ├─3576 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
   ├─3609 postgres: logger
   ├─3621 postgres: checkpointer
   ├─3622 postgres: background writer
   ├─3623 postgres: walwriter
   ├─3624 postgres: autovacuum launcher
   ├─3625 postgres: archiver
   ├─3626 postgres: stats collector
   └─3627 postgres: logical replication launcher

Jun 05 14:37:20 pgpmaster.localhost systemd[1]: Starting PostgreSQL 11 database 
server...
Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.923 
CEST [3576] LOG:  listening on IPv4 address "0.0.0.0", port 5432
Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.924 
CEST [3576] LOG:  listening on IPv6 address "::", port 5432
Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.928 
CEST [3576] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.026 
CEST [3576] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.123 
CEST [3576] LOG:  redirecting log output to logging collector process
Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.123 
CEST [3576] HINT:  Future log output will appear in directory "log".
Jun 05 14:37:21 pgpmaster.localhost systemd[1]: Started PostgreSQL 11 database 
server.

If I restart it using systemctl restart postgresql-11 command, everything is 
OK, but if I use commands:
# pg_ctl -D /var/lib/pgsql/11/data/ -m immediate stop
# pg_ctl -D /var/lib/pgsql/11/data/ -m immediate start

Then I get following situtation:

# systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; 
vendor preset: disabled)
   Active: inactive (dead) since Wed 2019-06-05 14:41:32 CEST; 1min 40s ago
 Docs: https://www.postgresql.org/docs/11/static/
  Process: 3576 ExecStart=/usr/pgsql-11/bin/postmaster -D ${PGDATA} 
(code=exited, status=0/SUCCESS)
  Process: 3552 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir 
${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 3576 (code=exited, status=0/SUCCESS)

Jun 05 14:37:20 pgpmaster.localhost systemd[1]: Starting PostgreSQL 11 database 
server...
Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.923 
CEST [3576] LOG:  listening on IPv4 address "0.0.0.0", port 5432
Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.924 
CEST [3576] LOG:  listening on IPv6 address "::", port 5432
Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.928 
CEST [3576] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.026 
CEST [3576] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.123 
CEST [3576] LOG:  redirecting log output to logging collector process
Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.123 
CEST [3576] HINT:  Future log output will appear in directory "log".
Jun 05 14:37:21 pgpmaster.localhost systemd[1]: Started PostgreSQL 11 database 
server.

# ps agx | grep postgre
4592 pts/0S  0:00 /usr/pgsql-11/bin/postgres -D /var/lib/pgsql/11/data
4593 ?Ss 0:00 postgres: logger
4595 ?Ss 0:00 postgres: checkpointer
4596 ?Ss 0:00 postgres: background writer
4597 ?Ss 0:00 postgres: walwriter
4598 ?Ss 0:00 postgres: autovacuum launcher
4599 ?Ss 0:00 postgres: archiver
4600 ?Ss 0:00 postgres: stats collector
4601 ?Ss 0:00 postgres: logical replication launcher
4630 pts/0S+ 0:00 grep --color=auto postgre

$ pg_ctl -D /var/lib/pgsql/11/data/ status
pg_ctl: server is running (PID: 4592)
/usr/pgsql-11/bin/postgres "-D" "/var/lib/pgsql/11/data"

Systemctl shows that database is dead, but it is actually running.
Can someone explain this behavioure?

Best regards!
[izum]

Domen Šetar
Computer Systems Support
IZUM – Institute of Information Scienc

Re: pg_repack issue

[Sathish Kumar]

Hi Prakash,

You can run below command.

pg_repack -d dbname -E DEBUG


On Wed, Jun 5, 2019, 7:55 PM Prakash Ramakrishnan <
prakash.ramakrishnan...@nielsen.com> wrote:

> Hi Peter,
>
> Thanks i have successfully created the extension how to use full vacuum
> using pg_repack.
>
> Regards,
> Prakash.R
>
> On Wed, Jun 5, 2019 at 5:23 PM Peter Eisentraut <
> peter.eisentr...@2ndquadrant.com> wrote:
>
>> On 2019-06-05 12:48, Prakash Ramakrishnan wrote:
>> > gcc -Wall -Wmissing-prototypes -Wpointer-arith
>> > -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
>> > -Wformat-security -fno-strict-aliasing -fwrapv
>> > -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2
>> > -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4
>> > -grecord-gcc-switches -m64 -mtune=generic pg_repack.o pgut/pgut.o
>> > pgut/pgut-fe.o  -L/usr/pgsql-11/lib -Wl,--as-needed
>> > -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed
>> > -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags  -L/usr/pgsql-11/lib
>> > -lpq -L/usr/pgsql-11/lib -lpgcommon -lpgport -lpthread -lssl -lcrypto
>> > -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_repack
>> > */usr/bin/ld: cannot find -lreadline
>> > collect2: error: ld returned 1 exit status
>> > make[1]: *** [pg_repack] Error 1
>> > make[1]: Leaving directory `/home/postgres/software/pg_repack-1.4.4/bin'
>> > make: *** [all] Error 2*
>>
>> It's a bit bogus that pg_repack would require this, but perhaps
>> installing the readline-devel (or similar) package would get you past
>> this.
>>
>> --
>> Peter Eisentraut  http://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
>
> --
> Thanks,
> Prakash.R
> PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On
> call : +91-8939599426
>

Re: pg_repack issue

[Prakash Ramakrishnan]

Hi Sathish,

Thanks for quick response and the database using separate table-space and
we need to clear table and index bloat values and using jobs also how can i
achieve this need exact command ?

Regards,
Prakash.R

On Wed, Jun 5, 2019 at 6:16 PM Sathish Kumar  wrote:

> Hi Prakash,
>
> You can run below command.
>
> pg_repack -d dbname -E DEBUG
>
>
> On Wed, Jun 5, 2019, 7:55 PM Prakash Ramakrishnan <
> prakash.ramakrishnan...@nielsen.com> wrote:
>
>> Hi Peter,
>>
>> Thanks i have successfully created the extension how to use full vacuum
>> using pg_repack.
>>
>> Regards,
>> Prakash.R
>>
>> On Wed, Jun 5, 2019 at 5:23 PM Peter Eisentraut <
>> peter.eisentr...@2ndquadrant.com> wrote:
>>
>>> On 2019-06-05 12:48, Prakash Ramakrishnan wrote:
>>> > gcc -Wall -Wmissing-prototypes -Wpointer-arith
>>> > -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
>>> > -Wformat-security -fno-strict-aliasing -fwrapv
>>> > -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2
>>> > -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4
>>> > -grecord-gcc-switches -m64 -mtune=generic pg_repack.o pgut/pgut.o
>>> > pgut/pgut-fe.o  -L/usr/pgsql-11/lib -Wl,--as-needed
>>> > -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed
>>> > -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags  -L/usr/pgsql-11/lib
>>> > -lpq -L/usr/pgsql-11/lib -lpgcommon -lpgport -lpthread -lssl -lcrypto
>>> > -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_repack
>>> > */usr/bin/ld: cannot find -lreadline
>>> > collect2: error: ld returned 1 exit status
>>> > make[1]: *** [pg_repack] Error 1
>>> > make[1]: Leaving directory
>>> `/home/postgres/software/pg_repack-1.4.4/bin'
>>> > make: *** [all] Error 2*
>>>
>>> It's a bit bogus that pg_repack would require this, but perhaps
>>> installing the readline-devel (or similar) package would get you past
>>> this.
>>>
>>> --
>>> Peter Eisentraut  http://www.2ndQuadrant.com/
>>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>>
>>
>>
>> --
>> Thanks,
>> Prakash.R
>> PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On
>> call : +91-8939599426
>>
>

-- 
Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

Re: pg_repack issue

[Sathish Kumar]

Hi Prakash,

You can see all the available options from here.

http://reorg.github.io/pg_repack/#installation

On Wed, Jun 5, 2019, 8:48 PM Prakash Ramakrishnan <
prakash.ramakrishnan...@nielsen.com> wrote:

> Hi Sathish,
>
> Thanks for quick response and the database using separate table-space and
> we need to clear table and index bloat values and using jobs also how can i
> achieve this need exact command ?
>
> Regards,
> Prakash.R
>
> On Wed, Jun 5, 2019 at 6:16 PM Sathish Kumar  wrote:
>
>> Hi Prakash,
>>
>> You can run below command.
>>
>> pg_repack -d dbname -E DEBUG
>>
>>
>> On Wed, Jun 5, 2019, 7:55 PM Prakash Ramakrishnan <
>> prakash.ramakrishnan...@nielsen.com> wrote:
>>
>>> Hi Peter,
>>>
>>> Thanks i have successfully created the extension how to use full vacuum
>>> using pg_repack.
>>>
>>> Regards,
>>> Prakash.R
>>>
>>> On Wed, Jun 5, 2019 at 5:23 PM Peter Eisentraut <
>>> peter.eisentr...@2ndquadrant.com> wrote:
>>>
 On 2019-06-05 12:48, Prakash Ramakrishnan wrote:
 > gcc -Wall -Wmissing-prototypes -Wpointer-arith
 > -Wdeclaration-after-statement -Wendif-labels
 -Wmissing-format-attribute
 > -Wformat-security -fno-strict-aliasing -fwrapv
 > -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2
 > -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4
 > -grecord-gcc-switches -m64 -mtune=generic pg_repack.o pgut/pgut.o
 > pgut/pgut-fe.o  -L/usr/pgsql-11/lib -Wl,--as-needed
 > -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed
 > -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags  -L/usr/pgsql-11/lib
 > -lpq -L/usr/pgsql-11/lib -lpgcommon -lpgport -lpthread -lssl -lcrypto
 > -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_repack
 > */usr/bin/ld: cannot find -lreadline
 > collect2: error: ld returned 1 exit status
 > make[1]: *** [pg_repack] Error 1
 > make[1]: Leaving directory
 `/home/postgres/software/pg_repack-1.4.4/bin'
 > make: *** [all] Error 2*

 It's a bit bogus that pg_repack would require this, but perhaps
 installing the readline-devel (or similar) package would get you past
 this.

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

>>>
>>>
>>> --
>>> Thanks,
>>> Prakash.R
>>> PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On
>>> call : +91-8939599426
>>>
>>
>
> --
> Thanks,
> Prakash.R
> PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On
> call : +91-8939599426
>

Re: Long running query - connection and keepalives enabled but query not canceled

[Dmitry Vasiliev]

> Tomasz Ostrowski wrote:
> 
>> The database would get an error on the connection socket only after it tries 
>> to read or write to it next time. But it does not try to do this - it's busy 
>> counting those quarks until statement_timeout is reached.
>> 
> 

I found relevant patch from postgrespro on commitfest: 
https://commitfest.postgresql.org/21/1882/

--
Dmitry Vasiliev 

Re: Postgresql-11 confusing situtation

[Adrian Klaver]

On 6/5/19 5:46 AM, Domen Šetar wrote:

Hi,

I’m using Postgresql 11.3 on Centos 7.6.18.10 server.

I have very unusual issue which I don’t know if it is OK:

I have running postgresql server with default instance:

[root@pgpmaster ~]# systemctl status postgresql-11

● postgresql-11.service - PostgreSQL 11 database server

    Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; 
enabled; vendor preset: disabled)


    Active: active (running) since Wed 2019-06-05 14:37:21 CEST; 49s ago

  Docs: https://www.postgresql.org/docs/11/static/

   Process: 3552 
ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} 
(code=exited, status=0/SUCCESS)


Main PID: 3576 (postmaster)

     Tasks: 9

    CGroup: /system.slice/postgresql-11.service

├─3576 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/

├─3609 postgres: logger

├─3621 postgres: checkpointer

├─3622 postgres: background writer

├─3623 postgres: walwriter

├─3624 postgres: autovacuum launcher

├─3625 postgres: archiver

├─3626 postgres: stats collector

    └─3627 postgres: logical replication launcher

Jun 05 14:37:20 pgpmaster.localhost systemd[1]: Starting PostgreSQL 11 
database server...


Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:20.923 CEST [3576] LOG:  listening on IPv4 address "0.0.0.0", port 
5432


Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:20.924 CEST [3576] LOG:  listening on IPv6 address "::", port 5432


Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:20.928 CEST [3576] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"


Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:21.026 CEST [3576] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"


Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:21.123 CEST [3576] LOG:  redirecting log output to logging 
collector process


Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:21.123 CEST [3576] HINT:  Future log output will appear in 
directory "log".


Jun 05 14:37:21 pgpmaster.localhost systemd[1]: Started PostgreSQL 11 
database server.


If I restart it using systemctl restart postgresql-11 command, 
everything is OK, but if I use commands:


# pg_ctl -D /var/lib/pgsql/11/data/ -m immediate stop

# pg_ctl -D /var/lib/pgsql/11/data/ -m immediate start


Don't do that. You are mixing starting procedures. pg_ctl is directly 
starting the server without going through systemd. Since systemd did not 
start the server it sees it as dead.




Then I get following situtation:

# systemctl status postgresql-11

● postgresql-11.service - PostgreSQL 11 database server

    Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; 
enabled; vendor preset: disabled)


*Active: inactive (dead) since Wed 2019-06-05 14:41:32 CEST; 1min 40s ago*

  Docs: https://www.postgresql.org/docs/11/static/

   Process: 3576 ExecStart=/usr/pgsql-11/bin/postmaster -D ${PGDATA} 
(code=exited, status=0/SUCCESS)


   Process: 3552 
ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} 
(code=exited, status=0/SUCCESS)


Main PID: 3576 (code=exited, status=0/SUCCESS)

Jun 05 14:37:20 pgpmaster.localhost systemd[1]: Starting PostgreSQL 11 
database server...


Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:20.923 CEST [3576] LOG:  listening on IPv4 address "0.0.0.0", port 
5432


Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:20.924 CEST [3576] LOG:  listening on IPv6 address "::", port 5432


Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:20.928 CEST [3576] LOG:  listening on Unix socket 
"/var/run/postgresql/.s.PGSQL.5432"


Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:21.026 CEST [3576] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"


Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:21.123 CEST [3576] LOG:  redirecting log output to logging 
collector process


Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 
14:37:21.123 CEST [3576] HINT:  Future log output will appear in 
directory "log".


Jun 05 14:37:21 pgpmaster.localhost systemd[1]: Started PostgreSQL 11 
database server.


# ps agx | grep postgre

4592 pts/0    S  0:00 /usr/pgsql-11/bin/postgres -D 
/var/lib/pgsql/11/data


4593 ?    Ss 0:00 postgres: logger

4595 ?    Ss 0:00 postgres: checkpointer

4596 ?    Ss 0:00 postgres: background writer

4597 ?    Ss 0:00 postgres: walwriter

4598 ?    Ss 0:00 postgres: autovacuum launcher

4599 ?    Ss 0:00 postgres: archiver

4600 ?    Ss 0:00 postgres: stats collector

4601 ?    Ss 0:00 postgres: logical replication launcher

4630 pts/0    S+ 0:00 grep --color=auto postgre

$ pg_ctl -D /var/lib/pgsql/11/data/ status

pg_ctl: server is running (PID: 4592)

/usr/pgsql-11/bin/postgr

Re: pg_repack issue

[Prakash Ramakrishnan]

Hi Sathish,

Thanks i ran the pg_repack and its working fine but still the wasted bytes
not removing why?

 current_database |  schemaname   |tablename
  | tbloat | wastedbytes |  i
name  | ibloat | wastedibytes
--+---+--++-+---
--++--
 AIBE01PR | lbmdigital_prod   | digital_display_execution
 |1.1 |   637059072 | digital_display_exe_idx_fk14
  |0.1 |0
 AIBE01PR | lbmdigital_prod   | digital_display_execution
 |1.1 |   637059072 | digital_display_exe_idx_fk22
  |0.1 |0
 AIBE01PR | lbmdigital_prod   | digital_display_execution
 |1.1 |   637059072 | digital_display_exe_index4
  |0.1 |0
 AIBE01PR | lbmdigital_prod   | digital_display_execution
 |1.1 |   637059072 | digital_display_exe_index3
  |0.2 |0
 AIBE01PR | lbmdigital_prod   | digital_display_execution
 |1.1 |   637059072 | digital_display_exe_index1
  |0.1 |0
 AIBE01PR | lbmdigital_prod   | digital_display_execution
 |1.1 |   637059072 | digital_display_execution_pkey
  |0.1 |0
 AIBE01PR | lbmdigital_prod   | digital_display_execution
 |1.1 |   637059072 | digital_display_exe_idx_fk9
  |0.1 |0
 AIBE01PR | cmt_prod  | cmt_creative_transfer
 |1.1 |   157155328 | cmt_creative_transfer_idx3


Regards,
Praaksh.R

On Wed, Jun 5, 2019 at 6:54 PM Sathish Kumar  wrote:

> Hi Prakash,
>
> You can see all the available options from here.
>
> http://reorg.github.io/pg_repack/#installation
>
> On Wed, Jun 5, 2019, 8:48 PM Prakash Ramakrishnan <
> prakash.ramakrishnan...@nielsen.com> wrote:
>
>> Hi Sathish,
>>
>> Thanks for quick response and the database using separate table-space and
>> we need to clear table and index bloat values and using jobs also how can i
>> achieve this need exact command ?
>>
>> Regards,
>> Prakash.R
>>
>> On Wed, Jun 5, 2019 at 6:16 PM Sathish Kumar  wrote:
>>
>>> Hi Prakash,
>>>
>>> You can run below command.
>>>
>>> pg_repack -d dbname -E DEBUG
>>>
>>>
>>> On Wed, Jun 5, 2019, 7:55 PM Prakash Ramakrishnan <
>>> prakash.ramakrishnan...@nielsen.com> wrote:
>>>
 Hi Peter,

 Thanks i have successfully created the extension how to use full vacuum
 using pg_repack.

 Regards,
 Prakash.R

 On Wed, Jun 5, 2019 at 5:23 PM Peter Eisentraut <
 peter.eisentr...@2ndquadrant.com> wrote:

> On 2019-06-05 12:48, Prakash Ramakrishnan wrote:
> > gcc -Wall -Wmissing-prototypes -Wpointer-arith
> > -Wdeclaration-after-statement -Wendif-labels
> -Wmissing-format-attribute
> > -Wformat-security -fno-strict-aliasing -fwrapv
> > -fexcess-precision=standard -O2 -g -pipe -Wall
> -Wp,-D_FORTIFY_SOURCE=2
> > -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4
> > -grecord-gcc-switches -m64 -mtune=generic pg_repack.o pgut/pgut.o
> > pgut/pgut-fe.o  -L/usr/pgsql-11/lib -Wl,--as-needed
> > -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed
> > -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags
>  -L/usr/pgsql-11/lib
> > -lpq -L/usr/pgsql-11/lib -lpgcommon -lpgport -lpthread -lssl -lcrypto
> > -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_repack
> > */usr/bin/ld: cannot find -lreadline
> > collect2: error: ld returned 1 exit status
> > make[1]: *** [pg_repack] Error 1
> > make[1]: Leaving directory
> `/home/postgres/software/pg_repack-1.4.4/bin'
> > make: *** [all] Error 2*
>
> It's a bit bogus that pg_repack would require this, but perhaps
> installing the readline-devel (or similar) package would get you past
> this.
>
> --
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>


 --
 Thanks,
 Prakash.R
 PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On
 call : +91-8939599426

>>>
>>
>> --
>> Thanks,
>> Prakash.R
>> PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On
>> call : +91-8939599426
>>
>

-- 
Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

Re: Converting yes or no to one letter strings.

[Adrian Klaver]

On 6/4/19 7:27 PM, Ron wrote:



On 6/4/19 7:19 PM, Adrian Klaver wrote:

On 6/4/19 3:29 PM, Lou wrote:

Hi everyone,

Is it possible to convert a boolean yes or no field to hold a one 
letter string? For example, the strings: 's' 'f' 'p' 'e'


To start off, I just need to convert true to 's'. false will have to 
be manually changed to 'f' or 'p' or 'e' as appropriate.


Why not an enum field?:

https://www.postgresql.org/docs/11/datatype-enum.html


I thought enum was Considered Harmful because of the difficulty in 
altering the enums?




The difficulty is removing a value:

https://www.postgresql.org/docs/11/sql-altertype.html

Otherwise I see equal difficulty in pretending that s, f, p and e are a 
boolean choice.


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

Re: Postgresql-11 confusing situtation

[Achilleas Mantzios]

On 5/6/19 3:46 μ.μ., Domen Šetar wrote:


Hi,

I’m using Postgresql 11.3 on Centos 7.6.18.10 server.

I have very unusual issue which I don’t know if it is OK:

I have running postgresql server with default instance:

[root@pgpmaster ~]# systemctl status postgresql-11

● postgresql-11.service - PostgreSQL 11 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; 
vendor preset: disabled)

   Active: active (running) since Wed 2019-06-05 14:37:21 CEST; 49s ago

 Docs: https://www.postgresql.org/docs/11/static/

  Process: 3552 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir 
${PGDATA} (code=exited, status=0/SUCCESS)

Main PID: 3576 (postmaster)

    Tasks: 9

   CGroup: /system.slice/postgresql-11.service

├─3576 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/

├─3609 postgres: logger

├─3621 postgres: checkpointer

├─3622 postgres: background writer

├─3623 postgres: walwriter

├─3624 postgres: autovacuum launcher

├─3625 postgres: archiver

├─3626 postgres: stats collector

   └─3627 postgres: logical replication launcher

Jun 05 14:37:20 pgpmaster.localhost systemd[1]: Starting PostgreSQL 11 database 
server...

Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.923 CEST [3576] 
LOG:  listening on IPv4 address "0.0.0.0", port 5432

Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.924 CEST [3576] 
LOG:  listening on IPv6 address "::", port 5432

Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.928 CEST [3576] 
LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.026 CEST [3576] 
LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"

Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.123 
CEST [3576] LOG:  redirecting log output to logging collector process

Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.123 CEST [3576] 
HINT:  Future log output will appear in directory "log".

Jun 05 14:37:21 pgpmaster.localhost systemd[1]: Started PostgreSQL 11 database 
server.

If I restart it using systemctl restart postgresql-11 command, everything is 
OK, but if I use commands:

# pg_ctl -D /var/lib/pgsql/11/data/ -m immediate stop

# pg_ctl -D /var/lib/pgsql/11/data/ -m immediate start


Don't run those as root. I guess you didn't run pg_ctl as root (since it worked)


Then I get following situtation:

# systemctl status postgresql-11

● postgresql-11.service - PostgreSQL 11 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; 
vendor preset: disabled)

*Active: inactive (dead) since Wed 2019-06-05 14:41:32 CEST; 1min 40s ago*

 Docs: https://www.postgresql.org/docs/11/static/

  Process: 3576 ExecStart=/usr/pgsql-11/bin/postmaster -D ${PGDATA} 
(code=exited, status=0/SUCCESS)

  Process: 3552 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir 
${PGDATA} (code=exited, status=0/SUCCESS)

Main PID: 3576 (code=exited, status=0/SUCCESS)

Jun 05 14:37:20 pgpmaster.localhost systemd[1]: Starting PostgreSQL 11 database 
server...

Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.923 CEST [3576] 
LOG: listening on IPv4 address "0.0.0.0", port 5432

Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.924 CEST [3576] 
LOG: listening on IPv6 address "::", port 5432

Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:20.928 CEST [3576] 
LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.026 CEST [3576] 
LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"

Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.123 
CEST [3576] LOG: redirecting log output to logging collector process

Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05 14:37:21.123 CEST [3576] 
HINT: Future log output will appear in directory "log".

Jun 05 14:37:21 pgpmaster.localhost systemd[1]: Started PostgreSQL 11 database 
server.

# ps agx | grep postgre

4592 pts/0    S  0:00 /usr/pgsql-11/bin/postgres -D /var/lib/pgsql/11/data

4593 ?    Ss 0:00 postgres: logger

4595 ?    Ss 0:00 postgres: checkpointer

4596 ?    Ss 0:00 postgres: background writer

4597 ?    Ss 0:00 postgres: walwriter

4598 ?    Ss 0:00 postgres: autovacuum launcher

4599 ?    Ss 0:00 postgres: archiver

4600 ?    Ss 0:00 postgres: stats collector

4601 ?    Ss 0:00 postgres: logical replication launcher

4630 pts/0    S+ 0:00 grep --color=auto postgre

$ pg_ctl -D /var/lib/pgsql/11/data/ status

pg_ctl: server is running (PID: 4592)

/usr/pgsql-11/bin/postgres "-D" "/var/lib/pgsql/11/data"

Systemctl shows that database is dead, but it is actually running.

Can someone explain this behavioure?


if you byp

verify checksums / CREATE DATABASE

[Karsten Hilbert]

Dear all,

I cannot find documentation on whether

CREATE DATABASE ... TEMPLATE template_db;

will verify checksums (if enabled) on template_db during
reading.

I would assume it does not, because very likely the copy
process happens at the file level. Is that correct ?

Many thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

configure multiple repository path in pgbackrest

[Pavan Kumar]

Hello Experts,

Is it possible to configure multiple backup repositories in pgbackrest tool
in one server?
I am getting few issues with that.


-- 



*Regards,#!  Pavan Kumar--*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell#  267-799-3182 #  pavan.dba27 (Gtalk)  *
*India   # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you will be
Wise  *

Re: verify checksums / CREATE DATABASE

[Magnus Hagander]

On Wed, Jun 5, 2019 at 4:47 PM Karsten Hilbert 
wrote:

> Dear all,
>
> I cannot find documentation on whether
>
> CREATE DATABASE ... TEMPLATE template_db;
>
> will verify checksums (if enabled) on template_db during
> reading.
>
> I would assume it does not, because very likely the copy
> process happens at the file level. Is that correct ?
>
>
That is correct, it does not verify checksums when copying the template.


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

Re: granting right to create and delete just one database

[Laurenz Albe]

Chris Withers wrote:
> > > Is there any way to grant rights to a user such that they can drop and
> > > re-create only a single database?
> > No; what I'd do if I needed that is to create a SECURITY DEFINER function
> > that is owned by a user with the CREATEDB privilege.
>
> Thanks, that's a great idea! Is this pattern documented anywhere as a 
> complete finished thing?

I'm afraid that is left as an exercise to the reader.

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

Re: configure multiple repository path in pgbackrest

[Ron]

On 6/5/19 9:48 AM, Pavan Kumar wrote:

Hello Experts,

Is it possible to configure multiple backup repositories in pgbackrest 
tool in one server?

I am getting few issues with that.


https://pgbackrest.org/command.html#command-backup

3.4.9
Repository Path Option (--repo-path)
Path where backups and archive are stored.
The repository is wherepgBackReststores backups and archives WAL segments.

It may be difficult to estimate in advance how much space you'll need. The 
best thing to do is take some backups then record the size of different 
types of backups (full/incr/diff) and measure the amount of WAL generated 
per day. This will give you a general idea of how much space you'll need, 
though of course requirements will likely change over time as your database 
evolves.


default: /var/lib/pgbackrest
example: --repo1-path=/backup/db/backrest


Since there's a --repo*1*-path, then presumably there can also be a 
--repo2-path.


You'd presumably associate a database with repo2 by using --pg2-path

https://pgbackrest.org/command.html#command-archive-get

2.5
Stanza Options
2.5.1
PostgreSQL Path Option (--pg-path)
PostgreSQLdata directory.
This should be the same as thedata_directorysetting inpostgresql.conf. Even 
though this value can be read frompostgresql.conforPostgreSQLit is prudent 
to set it in case those resources are not available during a restore or 
offline backup scenario.


Thepg-pathoption is tested against the value reported byPostgreSQLon every 
online backup so it should always be current.


example: --pg1-path=/data/db

Deprecated Name: db-path

Of course, this might not work, so you'll have to try it yourself.

--
Angular momentum makes the world go 'round.

Advice on setting cost for function

[guyren]

The documentation in CREATE FUNCTION is fine as far is it goes regarding the 
COST setting, but that isn’t very far, and I haven’t had any luck finding good 
advice.

How do I determine what value to set as the COST of a function? I understand 
that it’s a relative, abstract, per-row sort of thing, but that’s not quite 
enough to go on to do it well.

If I have a function that looks up a single value from an index, should that be 
lower than 100? 20, say?

If I have a function that calls multiple other functions, each of which does a 
reasonable amount of work, should I set the caller to a higher COST, or will 
Postgres use the costs of the functions it calls?

I can imagine that a SQL function which is just inlined might ignore the COST 
estimate. Is that so? Are there other such considerations?

In general, a section in the CREATE FUNCTION documentation of two or three 
paragraphs with a few examples and general guidelines would be very helpful. I 
would be happy to write it if someone will explain it to me.

Re: postgresql11-devel RPM is missing from "Direct RPM downloads"

[Dmitry O Litvintsev]

D'oh. Thanks!

/--\
| Tel:   (630) 840 5005|
| FAX:   (630) 840 2968|
|(630) 840 2783|
| office:FCC 240   |
| E-mail:litvi...@fnal.gov |
\--/

On Wed, 5 Jun 2019, Jakub Olczyk wrote:

> Hi Dimiry,
>
> On 04/06/2019 23:49, Dmitry O Litvintsev wrote:
>> Where can I find postgresq11-devel RPM ?
>> [...]
>> https://urldefense.proofpoint.com/v2/url?u=https-3A__yum.postgresql.org_11_redhat_rhel-2D7-2Dx86-5F64_repoview_&d=DwICaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=x0FsGjpFkd074Owr0TbLitPaA8Y3iFdjJIgsUZQKx4Q&s=64-qFF0vgct9WdlN14qDUr3Dx58YQv0sWwCFLg2D8M4&e=
>
> have you looked under the letter "P" in your first link?
> There you can find the -devel package.
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__yum.postgresql.org_11_redhat_rhel-2D7-2Dx86-5F64_repoview_letter-5Fp.group.html&d=DwICaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=x0FsGjpFkd074Owr0TbLitPaA8Y3iFdjJIgsUZQKx4Q&s=9BjL2HUlWfddhgpV1vnwDsRjRXNO4d1DNN0USk3J1uE&e=
>
> Cheers!
> Jakub
>
>

Re: Converting yes or no to one letter strings.

[Christopher Browne]

On Tue, 4 Jun 2019 at 18:30, Lou  wrote:

> Hi everyone,
>
> Is it possible to convert a boolean yes or no field to hold a one letter
> string? For example, the strings: 's' 'f' 'p' 'e'
>
> To start off, I just need to convert true to 's'. false will have to be
> manually changed to 'f' or 'p' or 'e' as appropriate.
>
> Lou
>
Certainly you can.

origin=# create table foo (id serial primary key, name text, tfvalue
boolean);
CREATE TABLE
origin=# alter table foo alter column tfvalue set data type character;
ALTER TABLE

Perfectly fine if the table has no values in that column.

But wait, maybe not...
origin=# create table foo (id serial primary key, name text, tfvalue
boolean);
CREATE TABLE
origin=# insert into foo (name, tfvalue) values ('chris', 'true');
INSERT 0 1
origin=# insert into foo (name, tfvalue) values ('dave', 'false');
INSERT 0 1
origin=# insert into foo (name) values ('brad');
INSERT 0 1
origin=# alter table foo alter column tfvalue set data type character;
ERROR:  value too long for type character(1)

Nope, you can't do that if there's data in the table.

This seems like an altogether terrible idea, actually.  If you need a new
column with a totally different interpretation, you should probably create
a totally new column, that way you can do whatever you wish to the new
column.

Might want to consult the trusty documentation, too.  <
https://www.postgresql.org/docs/9.5/datatype-boolean.html>
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Flood Warning message : user=[unknown],db=[unknown],host= WARNING: pg_getnameinfo_all() failed: Temporary failure in name resolution

[Perumal Raj]

Hi All,

We have recently noticed in our development environment pg_log with flooded
message.

[64459]: [1-1] user=[unknown],db=[unknown],host= WARNING:
 pg_getnameinfo_all() failed: Temporary failure in name resolution
[64463]: [1-1] user=[unknown],db=[unknown],host= WARNING:
 pg_getnameinfo_all() failed: Temporary failure in name resolution
[64464]: [1-1] user=[unknown],db=[unknown],host= WARNING:
 pg_getnameinfo_all() failed: Temporary failure in name resolution
[64548]: [1-1] user=[unknown],db=[unknown],host= WARNING:
 pg_getnameinfo_all() failed: Temporary failure in name resolution
[64551]: [1-1] user=[unknown],db=[unknown],host= WARNING:
 pg_getnameinfo_all() failed: Temporary failure in name resolution
[64553]: [1-1] user=[unknown],db=[unknown],host= WARNING:
 pg_getnameinfo_all() failed: Temporary failure in name resolution

Not sure , How to address this message ,

Much appreciated if some one give light on this.

Version . : 9.2.23

Regards,
Raj

Re: Flood Warning message : user=[unknown],db=[unknown],host= WARNING: pg_getnameinfo_all() failed: Temporary failure in name resolution

[Steve Crawford]

On Wed, Jun 5, 2019 at 10:13 AM Perumal Raj  wrote:

> Hi All,
>
> We have recently noticed in our development environment pg_log with
> flooded message.
>
> [64459]: [1-1] user=[unknown],db=[unknown],host= WARNING:
>  pg_getnameinfo_all() failed: Temporary failure in name resolution...
>

First thing I'd check is that DNS is functioning correctly (including local
resolution settings or caching name resolvers).

Cheers,
Steve

Re: configure multiple repository path in pgbackrest

[Pavan Kumar]

Hello Ron,

Thank you so much for quick response.

here is my configuration.

[postgres@oralnx v2demo4448]$cat /etc/pgbackrest/pgbackrest.conf
[v1demo]
pg1-path=/oradbaudit/pg_data_dir
pg1-port=5400
[v2demo]
pg1-path=/oratrace/11_2_data
pg1-port=4448

# Backup repository configuration
[global]
repo1-path=/orabackup/WORM/PGBACKUP/visdemo5400
repo1-retention-full=2
repo2-path=/orabackup/WORM/PGBACKUP/v2demo4448
repo2-retention-full=2

[postgres@oralnx v2demo4448]$

[postgres@oralnx v2demo4448]$ pgbackrest --stanza=v2demo
--log-path=/oratrace/pg_alert_log
--repo-path=/orabackup/WORM/PGBACKUP/v2demo4448 --log-level-console=detail
--log-level-file=detail stanza-create
WARN: configuration file contains invalid option 'repo2-path'   
*
WARN: configuration file contains invalid option 'repo2-retention-full'
>> *
2019-06-05 19:35:05.351 P00   INFO: stanza-create command begin 2.14:
--log-level-console=detail --log-level-file=detail
--log-path=/oratrace/pg_alert_log --pg1-path=/oratrace/11_2_data
--pg1-port=4448 --repo1-path=/orabackup/WORM/PGBACKUP/v2demo4448
--stanza=v2demo  > * for some reason it took repo1- path
2019-06-05 19:35:06.674 P00   INFO: stanza-create command end: completed
successfully (1324ms)
[postgres@oralnx v2demo4448]$

It got errored out when I check stanza

[postgres@oralnx v2demo4448]$pgbackrest --stanza=v2demo
--log-level-console=info check
WARN: configuration file contains invalid option 'repo2-path'
WARN: configuration file contains invalid option 'repo2-retention-full'
2019-06-05 19:36:31.188 P00   INFO: check command begin 2.14:
--log-level-console=info --pg1-path=/oratrace/11_2_data --pg1-port=4448
--repo1-path=/orabackup/WORM/PGBACKUP/visdemo5400 --stanza=v2demo   ***
why it is not taking repo2-path
ERROR [055]: : unable to open
/orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/backup.info or
/orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/backup.info.copy
ERROR [055]: : /orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/
backup.info does not exist and is required to perform a backup.
HINT: has a stanza-create been performed?
ERROR [055]: : /orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/
backup.info does not exist and is required to perform a backup.
HINT: has a stanza-create been performed?
2019-06-05 19:36:32.826 P00   INFO: check command end: aborted with
exception [055]
[postgres@oralnx v2demo4448]$
here is my configuration



On Wed, Jun 5, 2019 at 10:09 AM Ron  wrote:

>
>
> On 6/5/19 9:48 AM, Pavan Kumar wrote:
>
> Hello Experts,
>
> Is it possible to configure multiple backup repositories in pgbackrest
> tool in one server?
> I am getting few issues with that.
>
>
> https://pgbackrest.org/command.html#command-backup
>
> 3.4.9
> Repository Path Option (--repo-path)
> Path where backups and archive are stored.
> The repository is where pgBackRest stores backups and archives WAL
> segments.
>
> It may be difficult to estimate in advance how much space you'll need. The
> best thing to do is take some backups then record the size of different
> types of backups (full/incr/diff) and measure the amount of WAL generated
> per day. This will give you a general idea of how much space you'll need,
> though of course requirements will likely change over time as your database
> evolves.
>
> default: /var/lib/pgbackrest
> example: --repo1-path=/backup/db/backrest
>
>
> Since there's a --repo*1*-path, then presumably there can also be a --repo
> 2-path.
>
> You'd presumably associate a database with repo2 by using --pg2-path
>
> https://pgbackrest.org/command.html#command-archive-get
>
> 2.5
> Stanza Options
> 2.5.1
> PostgreSQL Path Option (--pg-path)
> PostgreSQL data directory.
> This should be the same as the data_directory setting in postgresql.conf.
> Even though this value can be read from postgresql.conf or PostgreSQL it
> is prudent to set it in case those resources are not available during a
> restore or offline backup scenario.
>
> The pg-path option is tested against the value reported by PostgreSQL on
> every online backup so it should always be current.
>
> example: --pg1-path=/data/db
>
> Deprecated Name: db-path
>
> Of course, this might not work, so you'll have to try it yourself.
>
> --
> Angular momentum makes the world go 'round.
>


-- 



*Regards,#!  Pavan Kumar--*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell#  267-799-3182 #  pavan.dba27 (Gtalk)  *
*India   # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you will be
Wise  *

Re: configure multiple repository path in pgbackrest

[Stephen Frost]

Greetings,

(dropping -admin, please do NOT send to multiple mailing lists- just
pick one)

* Pavan Kumar (pavan.db...@gmail.com) wrote:
> Is it possible to configure multiple backup repositories in pgbackrest tool
> in one server?

While we have considered adding support for that, it's not there today.
Instead, you can pretty easily just have multiple pgbackrest.conf files
and specify which you want used on the command-line.

Documentation is here:

https://pgbackrest.org/configuration.html#section-repository

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: configure multiple repository path in pgbackrest

[Ron]

You set pg1-path and pg1-port in both v1demo and v2demo. Shouldn't v2demo 
use pg2-path and pg2-port?


On 6/5/19 2:43 PM, Pavan Kumar wrote:

Hello Ron,

Thank you so much for quick response.

here is my configuration.

[postgres@oralnx v2demo4448]$cat /etc/pgbackrest/pgbackrest.conf
[v1demo]
pg1-path=/oradbaudit/pg_data_dir
pg1-port=5400
[v2demo]
pg1-path=/oratrace/11_2_data
pg1-port=4448

# Backup repository configuration
[global]
repo1-path=/orabackup/WORM/PGBACKUP/visdemo5400
repo1-retention-full=2
repo2-path=/orabackup/WORM/PGBACKUP/v2demo4448
repo2-retention-full=2

[postgres@oralnx v2demo4448]$

[postgres@oralnx v2demo4448]$ pgbackrest --stanza=v2demo 
--log-path=/oratrace/pg_alert_log 
--repo-path=/orabackup/WORM/PGBACKUP/v2demo4448 --log-level-console=detail 
--log-level-file=detail stanza-create
WARN: configuration file contains invalid option 'repo2-path'    
*
WARN: configuration file contains invalid option 'repo2-retention-full'  
>> *
2019-06-05 19:35:05.351 P00   INFO: stanza-create command begin 2.14: 
--log-level-console=detail --log-level-file=detail 
--log-path=/oratrace/pg_alert_log --pg1-path=/oratrace/11_2_data 
--pg1-port=4448 --repo1-path=/orabackup/WORM/PGBACKUP/v2demo4448 
--stanza=v2demo  > * for some reason it took repo1- path
2019-06-05 19:35:06.674 P00   INFO: stanza-create command end: completed 
successfully (1324ms)

[postgres@oralnx v2demo4448]$

It got errored out when I check stanza

[postgres@oralnx v2demo4448]$pgbackrest --stanza=v2demo 
--log-level-console=info check

WARN: configuration file contains invalid option 'repo2-path'
WARN: configuration file contains invalid option 'repo2-retention-full'
2019-06-05 19:36:31.188 P00   INFO: check command begin 2.14: 
--log-level-console=info --pg1-path=/oratrace/11_2_data --pg1-port=4448 
--repo1-path=/orabackup/WORM/PGBACKUP/visdemo5400 --stanza=v2demo   
*** why it is not taking repo2-path
ERROR [055]: : unable to open 
/orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/backup.info 
 or 
/orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/backup.info.copy
ERROR [055]: : 
/orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/backup.info 
 does not exist and is required to perform a backup.

HINT: has a stanza-create been performed?
ERROR [055]: : 
/orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/backup.info 
 does not exist and is required to perform a backup.

HINT: has a stanza-create been performed?
2019-06-05 19:36:32.826 P00   INFO: check command end: aborted with 
exception [055]

[postgres@oralnx v2demo4448]$
here is my configuration



On Wed, Jun 5, 2019 at 10:09 AM Ron > wrote:




On 6/5/19 9:48 AM, Pavan Kumar wrote:

Hello Experts,

Is it possible to configure multiple backup repositories in
pgbackrest tool in one server?
I am getting few issues with that.


https://pgbackrest.org/command.html#command-backup

3.4.9
Repository Path Option (--repo-path)
Path where backups and archive are stored.
The repository is wherepgBackReststores backups and archives WAL segments.

It may be difficult to estimate in advance how much space you'll need.
The best thing to do is take some backups then record the size of
different types of backups (full/incr/diff) and measure the amount of
WAL generated per day. This will give you a general idea of how much
space you'll need, though of course requirements will likely change
over time as your database evolves.

default: /var/lib/pgbackrest
example: --repo1-path=/backup/db/backrest


Since there's a --repo*1*-path, then presumably there can also be a
--repo2-path.

You'd presumably associate a database with repo2 by using --pg2-path

https://pgbackrest.org/command.html#command-archive-get

2.5
Stanza Options
2.5.1
PostgreSQL Path Option (--pg-path)
PostgreSQLdata directory.
This should be the same as thedata_directorysetting inpostgresql.conf.
Even though this value can be read frompostgresql.conforPostgreSQLit
is prudent to set it in case those resources are not available during
a restore or offline backup scenario.

Thepg-pathoption is tested against the value reported byPostgreSQLon
every online backup so it should always be current.

example: --pg1-path=/data/db

Deprecated Name: db-path

Of course, this might not work, so you'll have to try it yourself.

-- 
Angular momentum makes the world go 'round.




--
*Regards,

#!  Pavan Kumar
--*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell    #  267-799-3182 #  pavan.dba27 (Gtalk) *
*India   # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you will
be Wise *



--
Angular momentum makes the world go 'round.

Re: Flood Warning message : user=[unknown],db=[unknown],host= WARNING: pg_getnameinfo_all() failed: Temporary failure in name resolution

[bricklen]

On Wed, Jun 5, 2019 at 11:08 AM Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> On Wed, Jun 5, 2019 at 10:13 AM Perumal Raj  wrote:
>
>>
>> [64459]: [1-1] user=[unknown],db=[unknown],host= WARNING:
>>  pg_getnameinfo_all() failed: Temporary failure in name resolution...
>>
>
> First thing I'd check is that DNS is functioning correctly (including
> local resolution settings or caching name resolvers).
>

In addition to what Steve mentioned, I've a vague recollection from running
into this several years ago that this is triggered by having "log_hostname"
enabled in your postgresql.conf.

RE: Requirement PA-DSS 1.1.4

[Jan Bilek]

Hi team,

anyone? Please let me know if this is not a correct group to ask, I'll move it 
somewhere else.

Thank you in advance & Kind Regards,
Jan


On 2019-06-04 08:56:47+10:00 Jan Bilek wrote:

Hi,

We've build a Payments Authorisation system (Box solution) on Postgresql 
database and now we are hitting following issue with our PA:DSS audit - 
requirement PA-DSS 1.1.4:

<>
1.1.4 Securely delete any track data (from the magnetic stripe or equivalent 
data contained on a chip), card verification values or codes, and PINs or PIN 
block data stored by previous versions of the payment application, in 
accordance with industry-accepted standards for secure deletion, as defined, 
for example by the list of approved products maintained by the National 
Security Agency, or by other State or National standards or regulations.


All of these elements of sensitive authentication data are not permitted to be 
stored post-authorization. If older versions of payment applications stored 
this information, the payment application vendor is required to provide 
instructions in the PA-DSS Implementation Guide as well as a secure wipe tool 
or procedure. If not securely deleted, this data could remain hidden on 
customer systems, and malicious individuals who obtain access to this 
information could use it to produce counterfeit payment cards, and/or to 
perform fraudulent transactions.
Unfortunately, description is too ambiguous and our QSA claims that stored is 
stored regardless of form. Tokens he can live with, but encryption not. But we 
do encryption (regardless it is happening with a key stored on HSM).

Actual trouble comes with forensics:

<>
1.1.4.c Verify, through the use of forensic tools and/or methods, that the 
secure wipe tool or procedure provided by vendor securely removes the data, in 
accordance with industry-accepted standards for secure deletion of data.


Similar with:
<>
2.6 Provide a mechanism to render irretrievable any cryptographic key material 
or cryptogram stored by the payment application, in accordance with 
industry-accepted standards.


These are cryptographic keys (Host stored HSM keys) used to encrypt or verify 
cardholder data.

At this stage our QSA was able to identify that data remain on a persistence 
device (DB files) even after deleting those from our application.

Checking SQLite database, it comes with pragma secure_delete - which is very 
much what we are looking for. 
https://www.sqlite.org/pragma.html#pragma_secure_delete

I would appreciate your input on this. Is there any solution already I haven't 
been able to find in documentation. If not, is there any way we can put this on 
a road map or even contribute to your code?

Thank you in advance & Kind Regards,
Jan

--
EFTlab CTO

Re: Flood Warning message : user=[unknown],db=[unknown],host= WARNING: pg_getnameinfo_all() failed: Temporary failure in name resolution

[Perumal Raj]

Thanks Steve for the response, Yes we had DNS server change.
But conf file updated with right DNS server and its resolving when i
crosscheck with nslookup against clinet_addr

By the way , What is the right syntax for LDAP configuration ,

I am using the one below, and getting LDAP authentication error though i
was able to login to server with same password.

hostall all 0.0.0.0/0   ldap ldapserver=ldap.xxx.com
ldapport=389 ldaptls=1 ldapbasedn="dc=domain,dc=com"
ldapbinddn="cn=auth_user,dc=domain,dc=com"
ldapbindpasswd=encrypted_password ldapsearchattribute=uid

Version :9..2

Regards,
Raju


On Wed, Jun 5, 2019 at 11:08 AM Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> On Wed, Jun 5, 2019 at 10:13 AM Perumal Raj  wrote:
>
>> Hi All,
>>
>> We have recently noticed in our development environment pg_log with
>> flooded message.
>>
>> [64459]: [1-1] user=[unknown],db=[unknown],host= WARNING:
>>  pg_getnameinfo_all() failed: Temporary failure in name resolution...
>>
>
> First thing I'd check is that DNS is functioning correctly (including
> local resolution settings or caching name resolvers).
>
> Cheers,
> Steve
>
>

Re: Requirement PA-DSS 1.1.4

[Pavel Stehule]

Hi

čt 6. 6. 2019 v 1:23 odesílatel Jan Bilek  napsal:

> Hi team,
>
> anyone? Please let me know if this is not a correct group to ask, I'll
> move it somewhere else.
>

this question, proposal is much more related to pgsql-hackers forum.

Currently Postgres doesn't support any feature like this. I think so can be
hard to implement it to be absolutely safe. Modification of VACUUM
statement probably is not problem. Harder work can be index cleaning.

Unfortunately this feature cannot be implemented as extension. It should be
implemented in core.

Postgres has not road map - has only ToDo list - but it is mostly
unimportant. If you need some feature in core, you should to contribute
code.

More informations https://wiki.postgresql.org/wiki/Developer_FAQ

Regards

Pavel


>
> Thank you in advance & Kind Regards,
> Jan
>
>
> On 2019-06-04 08:56:47+10:00 Jan Bilek wrote:
>
> Hi,
>
> We've build a Payments Authorisation system (Box solution) on Postgresql
> database and now we are hitting following issue with our PA:DSS audit -
> requirement PA-DSS 1.1.4:
>
> <>
> 1.1.4 Securely delete any track data (from the magnetic stripe or
> equivalent data contained on a chip), card verification values or codes,
> and PINs or PIN block data stored by previous versions of the payment
> application, in accordance with industry-accepted standards for secure
> deletion, as defined, for example by the list of approved products
> maintained by the National Security Agency, or by other State or National
> standards or regulations.
> 
>
> All of these elements of sensitive authentication data are not permitted
> to be stored post-authorization. If older versions of payment applications
> stored this information, the payment application vendor is required to
> provide instructions in the PA-DSS Implementation Guide as well as a secure
> wipe tool or procedure. If not securely deleted, this data could remain
> hidden on customer systems, and malicious individuals who obtain access to
> this information could use it to produce counterfeit payment cards, and/or
> to perform fraudulent transactions.
> Unfortunately, description is too ambiguous and our QSA claims that stored
> is stored regardless of form. Tokens he can live with, but encryption not.
> But we do encryption (regardless it is happening with a key stored on HSM).
>
> Actual trouble comes with forensics:
>
> <>
> 1.1.4.c Verify, through the use of forensic tools and/or methods, that the
> secure wipe tool or procedure provided by vendor securely removes the data,
> in accordance with industry-accepted standards for secure deletion of data.
> 
>
> Similar with:
> <>
> 2.6 Provide a mechanism to render irretrievable any cryptographic key
> material or cryptogram stored by the payment application, in accordance
> with industry-accepted standards.
> 
>
> These are cryptographic keys (Host stored HSM keys) used to encrypt or
> verify cardholder data.
>
> At this stage our QSA was able to identify that data remain on a
> persistence device (DB files) even after deleting those from our
> application.
>
> Checking SQLite database, it comes with pragma secure_delete - which is
> very much what we are looking for.
> https://www.sqlite.org/pragma.html#pragma_secure_delete
>
> I would appreciate your input on this. Is there any solution already I
> haven't been able to find in documentation. If not, is there any way we can
> put this on a road map or even contribute to your code?
>
> Thank you in advance & Kind Regards,
> Jan
>
> --
>
> EFTlab CTO
>
>

Re: Requirement PA-DSS 1.1.4

[Benjamin Scherrey]

Postgres has spent several decades creating a data store that does its best
to make sure data is accessible and never accidentally lost. The cobc8of a
secure forensic wipe is anathama to the product goals. To have any hope of
implementing such a thing with confidence you'd need to drop most of those
resilience features and write directly to block storage avoiding any file
system that uses journals or copy on write semantics (like btrfs).

I've built some of the early leading forensic discovery and decommissioning
software so I know what's involved. You should never store such information
in a database product unless you plan of decommissioning ALL of the media
that stores the information once you're supposed to lose custody.

If you're allowed to do this on a periodic basis (say monthly or quarterly)
you could, as a policy, make a logical dump of the database and then
restore it on a fresh server periodically and then fully decommission the
data stores on the prior server. If you have a clustered system for
resiliency then round robbin this in an N+1 configuration would work.

Outside of the above - if your custody latency allowences are too short
then never store this information in a database product of any sort or even
a modern file system. I'd write block storage in fixed sizes to a dedicated
partition and then write over the data securely multiple times when you
release custody.

Hope this helps,

  - - Ben Scherrey

On Tue, Jun 4, 2019, 5:57 AM Jan Bilek  wrote:

> Hi,
>
> We've build a Payments Authorisation system (Box solution) on Postgresql
> database and now we are hitting following issue with our PA:DSS audit -
> requirement PA-DSS 1.1.4:
>
> <>
> 1.1.4 Securely delete any track data (from the magnetic stripe or
> equivalent data contained on a chip), card verification values or codes,
> and PINs or PIN block data stored by previous versions of the payment
> application, in accordance with industry-accepted standards for secure
> deletion, as defined, for example by the list of approved products
> maintained by the National Security Agency, or by other State or National
> standards or regulations.
> 
>
> All of these elements of sensitive authentication data are not permitted
> to be stored post-authorization. If older versions of payment applications
> stored this information, the payment application vendor is required to
> provide instructions in the PA-DSS Implementation Guide as well as a secure
> wipe tool or procedure. If not securely deleted, this data could remain
> hidden on customer systems, and malicious individuals who obtain access to
> this information could use it to produce counterfeit payment cards, and/or
> to perform fraudulent transactions.
> Unfortunately, description is too ambiguous and our QSA claims that stored
> is stored regardless of form. Tokens he can live with, but encryption not.
> But we do encryption (regardless it is happening with a key stored on HSM).
>
> Actual trouble comes with forensics:
>
> <>
> 1.1.4.c Verify, through the use of forensic tools and/or methods, that the
> secure wipe tool or procedure provided by vendor securely removes the data,
> in accordance with industry-accepted standards for secure deletion of data.
> 
>
> Similar with:
> <>
> 2.6 Provide a mechanism to render irretrievable any cryptographic key
> material or cryptogram stored by the payment application, in accordance
> with industry-accepted standards.
> 
>
> These are cryptographic keys (Host stored HSM keys) used to encrypt or
> verify cardholder data.
>
> At this stage our QSA was able to identify that data remain on a
> persistence device (DB files) even after deleting those from our
> application.
>
> Checking SQLite database, it comes with pragma secure_delete - which is
> very much what we are looking for.
> https://www.sqlite.org/pragma.html#pragma_secure_delete
>
> I would appreciate your input on this. Is there any solution already I
> haven't been able to find in documentation. If not, is there any way we can
> put this on a road map or even contribute to your code?
>
> Thank you in advance & Kind Regards,
> Jan
>
> --
>
> EFTlab CTO
>