Re: [GENERAL] PostgrSQL server : CPU and Memory

[John R Pierce]

On 5/17/2017 8:50 PM, kaustubh kelkar wrote:
I am a developer and I want to monitor metrics related to CPU and 
Memory of PostgreSQL server only using SQL queries.


postgres itself doesn't track that level of OS specific stuff.



Do we have any system tables which can give us the data? I have found 
this one
https://aaronparecki.com/2015/02/19/8/monitoring-cpu-memory-usage-from-postgres 


but dont know whether it is exactly the same what I need.



thats reading the /proc/ stuff available on Linux systems via SQL, 
its the same stuff OS level tools like ps, top, free use... it won't 
work on non-Linux systems, even other Unix systems like BSD don't have 
the same /proc stuff, and other OS's like Windows don't have /proc at all.


if you don't know whether that's what you need, I'm not sure how we 
could know what you need.


--
john r pierce, recycling bits in santa cruz



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

[GENERAL] PostgrSQL server : CPU and Memory

[kaustubh kelkar]

Hi ,

I am a developer and I want to monitor metrics related to CPU and Memory of
PostgreSQL server only using SQL queries.

Do we have any system tables which can give us the data? I have found this
one
https://aaronparecki.com/2015/02/19/8/monitoring-cpu-memory-usage-from-postgres

but dont know whether it is exactly the same what I need.

I am able to fetch the same using scripts (batch/shell) but getting data
from SQL will be my main task.

-- 

Kaus2bh Kelkar

Re: [GENERAL] column names and dollar sign

[Gavin Flower]

On 18/05/17 11:59, John R Pierce wrote:

On 5/17/2017 4:51 PM, Gavin Flower wrote:
Variables ending in '$' date back to at least the early days of BASIC 
- long before the spectre of Microsoft loomed large, let alone 
'Visual Basic'! 



I note even INT fields have $ names there...   IBM used to like to use 
$ in names for system stuff, SYS$BLAHBLAH or whatever.




Names ending in % were integers.

The BASIC I'm most familiar with was BBC BASIC as on the BBC MIcro and 
the Acorn Archimedes.


Visual Basic is the only language I've ever written a program in that I 
could not get to run - it should NEVER have had BASIC as part of its names.



Cheers,
Gavin



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

Re: [GENERAL] column names and dollar sign

[John R Pierce]

On 5/17/2017 4:51 PM, Gavin Flower wrote:
Variables ending in '$' date back to at least the early days of BASIC 
- long before the spectre of Microsoft loomed large, let alone 'Visual 
Basic'! 



I note even INT fields have $ names there...   IBM used to like to use $ 
in names for system stuff, SYS$BLAHBLAH or whatever.



--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] column names and dollar sign

[Gavin Flower]

On 18/05/17 11:32, Neil Anderson wrote:

"Armand Pirvu (home)"  writes:

Ran into the following statement
CREATE TABLE test(
  Date$ date,
  Month_Number$ int,
  Month$ varchar(10),
  Year$ int
);

A strange naming convention. It has a whiff of Visual Basic Type
Characters about it,
https://docs.microsoft.com/en-us/dotnet/articles/visual-basic/programming-guide/language-features/data-types/type-characters.

Any chance there is a connection there?

Variables ending in '$' date back to at least the early days of BASIC - 
long before the spectre of Microsoft loomed large, let alone 'Visual Basic'!



Cheers,
Gavin



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

Re: [GENERAL] column names and dollar sign

[Neil Anderson]

>> "Armand Pirvu (home)"  writes:
>>> Ran into the following statement
>>
>>> CREATE TABLE test(
>>>  Date$ date,
>>>  Month_Number$ int,
>>>  Month$ varchar(10),
>>>  Year$ int
>>> );

A strange naming convention. It has a whiff of Visual Basic Type
Characters about it,
https://docs.microsoft.com/en-us/dotnet/articles/visual-basic/programming-guide/language-features/data-types/type-characters.

Any chance there is a connection there?

-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.com



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

Re: [GENERAL] column names and dollar sign

[Armand Pirvu (home)]

Thanks  Tom


Armand

On May 17, 2017, at 4:14 PM, Tom Lane  wrote:

> "Armand Pirvu (home)"  writes:
>> Ran into the following statement
> 
>> CREATE TABLE test(
>>  Date$ date,
>>  Month_Number$ int,
>>  Month$ varchar(10),
>>  Year$ int
>> );
> 
>> While it does execute, I wonder if the $ has any special meaning ?
> 
> Postgres thinks it's a valid identifier character, as long as it's
> not the first character.  I don't believe it's standard SQL, but
> we hacked it in a long time ago for compatibility with Oracle or
> somebody like that.
> 
>   regards, tom lane



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

Re: [GENERAL] column names and dollar sign

[Tom Lane]

"Armand Pirvu (home)"  writes:
> Ran into the following statement

> CREATE TABLE test(
>   Date$ date,
>   Month_Number$ int,
>   Month$ varchar(10),
>   Year$ int
> );

> While it does execute, I wonder if the $ has any special meaning ?

Postgres thinks it's a valid identifier character, as long as it's
not the first character.  I don't believe it's standard SQL, but
we hacked it in a long time ago for compatibility with Oracle or
somebody like that.

regards, tom lane


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

Re: [GENERAL] column names and dollar sign

[Armand Pirvu (home)]

Thank you Steve

— Armand

On May 17, 2017, at 4:10 PM, Steve Atkins  wrote:

> 
>> On May 17, 2017, at 2:02 PM, Armand Pirvu (home)  
>> wrote:
>> 
>> Hi 
>> 
>> Ran into the following statement
>> 
>> CREATE TABLE test(
>>  Date$ date,
>>  Month_Number$ int,
>>  Month$ varchar(10),
>>  Year$ int
>> );
>> 
>> 
>> While it does execute, I wonder if the $ has any special meaning ?
>> 
>> Can anyone shed some light please ?
> 
> No special meaning to postgresql - in postgresql a dollar sign is a valid 
> character in an identifier.
> 
> It might have some special meaning to the app that was using it, perhaps.
> 
> Cheers,
>  Steve
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



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

Re: [GENERAL] column names and dollar sign

[Steve Atkins]

> On May 17, 2017, at 2:02 PM, Armand Pirvu (home)  
> wrote:
> 
> Hi 
> 
> Ran into the following statement
> 
> CREATE TABLE test(
>   Date$ date,
>   Month_Number$ int,
>   Month$ varchar(10),
>   Year$ int
> );
> 
> 
> While it does execute, I wonder if the $ has any special meaning ?
> 
> Can anyone shed some light please ?

No special meaning to postgresql - in postgresql a dollar sign is a valid 
character in an identifier.

It might have some special meaning to the app that was using it, perhaps.

Cheers,
  Steve



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

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Tom Lane]

Adrian Klaver  writes:
> I could build repmgr against Postgres source and on Ubuntu install of 
> EDB Postgres. The issue seems to be a combination of RH and EDB Postgres 
> installation. To me it looks like ld is finding 
> /lib64/libldap_r-2.4.so.2 library before the /opt/PostgreSQL/9.6/lib/ 
> one.

IIRC this is determined ultimately by /etc/ld.so.conf, but there's
a layer of caching that might be giving trouble.  See "man 8 ld.so"
and also "man 8 ldconfig".  Looking at the output of "ldconfig -p"
might be informative.

regards, tom lane


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

[GENERAL] column names and dollar sign

[Armand Pirvu (home)]

Hi 

Ran into the following statement

CREATE TABLE test(
Date$ date,
Month_Number$ int,
Month$ varchar(10),
Year$ int
);


While it does execute, I wonder if the $ has any special meaning ?

Can anyone shed some light please ?


Thanks
Armand




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

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Adrian Klaver]

On 05/17/2017 01:28 PM, Martin Goodson wrote:

On 17/05/2017 20:11, Adrian Klaver wrote:


I thought you where working on VM you had access/rights to.



That is not the case?


I have sudo access on the redhat box we're working on so technically I 
could do this, yup. But whilst our Unix team are happy for us to do 
whatever the heck we like within our own domain (so to speak - all the 
databases, tools we use, etc), they prefer to retain control over 'top 
level' system-admin stuff like disks, the contents of /lib, etc.


I like to keep our sysadmins happy, so I'm going to leave /lib to them :)


Got it.




Whoever does it needs to unlink:

/lib64/libldap_r-2.4.so.2


Got it. We'll see what happens tomorrow. Thank you for all the help so 
far, it's been *invaluable*. Hopefully tomorrow I'll have some good news 
to report!


btw, if this is a reproducible thing is it worth raising with 
enterprisedb or 2nd Quadrant? Is this a 'bug' of some kind, or just a 
really weird edge case? :)


I could build repmgr against Postgres source and on Ubuntu install of 
EDB Postgres. The issue seems to be a combination of RH and EDB Postgres 
installation. To me it looks like ld is finding 
/lib64/libldap_r-2.4.so.2 library before the /opt/PostgreSQL/9.6/lib/ 
one. Removing the link in /lib64/ seems to force it to use the EDB 
installed library. I do not know enough about ld to figure out how to 
force it to only look at the EDB installed library without removing the 
link.





Regards,

Martin.



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


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

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Martin Goodson]

On 17/05/2017 20:11, Adrian Klaver wrote:


I thought you where working on VM you had access/rights to.



That is not the case?


I have sudo access on the redhat box we're working on so technically I 
could do this, yup. But whilst our Unix team are happy for us to do 
whatever the heck we like within our own domain (so to speak - all the 
databases, tools we use, etc), they prefer to retain control over 'top 
level' system-admin stuff like disks, the contents of /lib, etc.


I like to keep our sysadmins happy, so I'm going to leave /lib to them :)


Whoever does it needs to unlink:

/lib64/libldap_r-2.4.so.2


Got it. We'll see what happens tomorrow. Thank you for all the help so 
far, it's been *invaluable*. Hopefully tomorrow I'll have some good news 
to report!


btw, if this is a reproducible thing is it worth raising with 
enterprisedb or 2nd Quadrant? Is this a 'bug' of some kind, or just a 
really weird edge case? :)


Regards,

Martin.
--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."


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

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Adrian Klaver]

On 05/17/2017 12:01 PM, Martin Goodson wrote:

On 17/05/2017 17:19, Adrian Klaver wrote:

Spun up a RH 7.3 instance and tried the compile and got the same error.

Good to know it's reproducible, and not just me  :)



I'll have a word with our UNIX team about unlinking this (and relinking 
afterward) tomorrow (they're gone for the night ... :) ) and report 
back, but it certainly is looking promising again! :)


I thought you where working on VM you had access/rights to.

That is not the case?




Here's what I currently see in /lib64 for libldap files ...

$ ls -l /lib64/libldap*

lrwxrwxrwx 1 root root   21 May 11 12:42 /lib64/libldap-2.4.so.2 -> 
libldap-2.4.so.2.10.3

-rwxr-xr-x 1 root root 337K Feb 23  2016 /lib64/libldap-2.4.so.2.10.3
lrwxrwxrwx 1 root root   23 May 11 12:42 /lib64/libldap_r-2.4.so.2 -> 
libldap_r-2.4.so.2.10.3

-rwxr-xr-x 1 root root 365K Feb 23  2016 /lib64/libldap_r-2.4.so.2.10.3
lrwxrwxrwx 1 root root   23 May 16 17:06 /lib64/libldap_r.so -> 
libldap_r-2.4.so.2.10.3
lrwxrwxrwx 1 root root   21 May 16 17:06 /lib64/libldap.so -> 
libldap-2.4.so.2.10.3



Whoever does it needs to unlink:

/lib64/libldap_r-2.4.so.2



Regards,




--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."




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


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

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Martin Goodson]

On 17/05/2017 17:19, Adrian Klaver wrote:

Spun up a RH 7.3 instance and tried the compile and got the same error.

Good to know it's reproducible, and not just me  :)


To fix, find the checking library in /lib64/libldap_r-2.4.so.2.

[root@localhost repmgr-2.0]# cd /lib64/
[root@localhost lib64]# ls -l libldap*
lrwxrwxrwx. 1 root root 20 Dec  8 09:23 libldap-2.4.so.2 -> 
libldap-2.4.so.2.5.6

-rwxr-xr-x. 1 root root 317200 Apr 29  2013 libldap-2.4.so.2.5.6
lrwxrwxrwx. 1 root root 22 Dec  8 09:23 libldap_r-2.4.so.2 -> 
libldap_r-2.4.so.2.5.6

-rwxr-xr-x. 1 root root 335264 Apr 29  2013 libldap_r-2.4.so.2.5.6

Ok, there are two copies, retain one and unlink other.

[root@localhost lib64]# unlink libldap_r-2.4.so.2
"


I did the above and success:

PATH=/opt/PostgreSQL/9.6/bin:$PATH make USE_PGXS=1 clean all
...
I am going to agree with John's comment upstream that unlinking a file 
seems to be dubious. You might want to try linking the file back up 
again after you have installed repmgr and see if it causes any issues.


I'll have a word with our UNIX team about unlinking this (and relinking 
afterward) tomorrow (they're gone for the night ... :) ) and report 
back, but it certainly is looking promising again! :)


Here's what I currently see in /lib64 for libldap files ...

$ ls -l /lib64/libldap*

lrwxrwxrwx 1 root root   21 May 11 12:42 /lib64/libldap-2.4.so.2 -> 
libldap-2.4.so.2.10.3

-rwxr-xr-x 1 root root 337K Feb 23  2016 /lib64/libldap-2.4.so.2.10.3
lrwxrwxrwx 1 root root   23 May 11 12:42 /lib64/libldap_r-2.4.so.2 -> 
libldap_r-2.4.so.2.10.3

-rwxr-xr-x 1 root root 365K Feb 23  2016 /lib64/libldap_r-2.4.so.2.10.3
lrwxrwxrwx 1 root root   23 May 16 17:06 /lib64/libldap_r.so -> 
libldap_r-2.4.so.2.10.3
lrwxrwxrwx 1 root root   21 May 16 17:06 /lib64/libldap.so -> 
libldap-2.4.so.2.10.3


Regards,

Martin.
--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Martin Goodson]

On 17/05/2017 16:15, Adrian Klaver wrote:


On 05/17/2017 07:26 AM, Martin Goodson wrote:

On 16/05/2017 18:46, Adrian Klaver wrote:

Per Tom's suggestion try

ldd /db_demo/app/postgres/9.6.2-3/lib/libldap_r-2.4.so.2


Hello. Apologies for the delay in replying - medical appointment this 
morning :) Anyway, executed that command as suggested and this is the 
output I got.


pginst@testdemo00:/db_demo/app/postgres/9.6.2-3/lib$ ls -lrt *ldap*

-rwxr-xr-x 1 pginst gpginst 354K Mar 13 11:25 libldap-2.4.so.2
-rwxr-xr-x 1 pginst gpginst 383K Mar 13 11:25 libldap_r-2.4.so.2





pginst@testdemo00:/db_demo/app/postgres/9.6.2-3/lib$ ldd 
libldap_r-2.4.so.2

 linux-vdso.so.1 =>  (0x7ffcd12e4000)
 liblber-2.4.so.2 => 
/db_demo/app/postgres/9.6.2-3/lib/./liblber-2.4.so.2 
(0x7f20ff9a1000)

 libresolv.so.2 => /lib64/libresolv.so.2 (0x7f20ff77d000)
 libsasl2.so.3 => 
/db_demo/app/postgres/9.6.2-3/lib/./libsasl2.so.3 (0x7f20ff55b000)
 libssl.so.1.0.0 => 
/db_demo/app/postgres/9.6.2-3/lib/./libssl.so.1.0.0 (0x7f20ff2ec000)
 libcrypto.so.1.0.0 => 
/db_demo/app/postgres/9.6.2-3/lib/./libcrypto.so.1.0.0 
(0x7f20feeb5000)

 libpthread.so.0 => /lib64/libpthread.so.0 (0x7f20fec99000)
 libc.so.6 => /lib64/libc.so.6 (0x7f20fe8d7000)
 libdl.so.2 => /lib64/libdl.so.2 (0x7f20fe6d2000)
 /lib64/ld-linux-x86-64.so.2 (0x7f20ffe14000)


So the EDB install is using its own library for liblber

Per Tom's suggestion:

nm -D /db_demo/app/postgres/9.6.2-3/lib/liblber-2.4.so.2



Output from that is:

$ nm -D /db_demo/app/postgres/9.6.2-3/lib/liblber-2.4.so.2

 U __assert_fail
7dd9 T ber_alloc
7d48 T ber_alloc_t
90b2 T ber_bprint
a76c T ber_bvarray_add
a611 T ber_bvarray_add_x
a4df T ber_bvarray_dup_x
a4c0 T ber_bvarray_free
a441 T ber_bvarray_free_x
9ea6 T ber_bvdup
9d1f T ber_bvecadd
9bdc T ber_bvecadd_x
9bbd T ber_bvecfree
9b3d T ber_bvecfree_x
9b1e T ber_bvfree
9acb T ber_bvfree_x
a417 T ber_bvreplace
a32a T ber_bvreplace_x
34ac T ber_decode_oid
937a T ber_dump
7df9 T ber_dup
9e7c T ber_dupbv
9d49 T ber_dupbv_x
5e06 T ber_encode_oid
8cb0 T ber_errno_addr
8cda T ber_error_print
4621 T ber_first_element
8244 T ber_flatten
80e1 T ber_flatten2
7b01 T ber_flush
7b33 T ber_flush2
7abd T ber_free
7a3a T ber_free_buf
4455 T ber_get_bitstringa
45fc T ber_get_boolean
3b4b T ber_get_enum
3a65 T ber_get_int
83a0 T ber_get_next
45c1 T ber_get_null
a798 T ber_get_option
42ae T ber_get_stringa
4374 T ber_get_stringal
4311 T ber_get_stringa_null
3b70 T ber_get_stringb
404b T ber_get_stringbv
4178 T ber_get_stringbv_null
3800 T ber_get_tag
801e T ber_init
7f06 T ber_init2
7ff9 T ber_init_w_nullc
0020fb28 B ber_int_errno_fn
0020fb18 B ber_int_log_proc
0020fb40 B ber_int_memory_fns
0020fb60 B ber_int_options
b8a6 T ber_int_sb_close
b93c T ber_int_sb_destroy
b7ea T ber_int_sb_init
b9f9 T ber_int_sb_read
bb03 T ber_int_sb_write
8c07 T ber_len
9049 T ber_log_bprint
92e8 T ber_log_dump
94c5 T ber_log_sos_dump
a15a T ber_mem2bv
a03c T ber_mem2bv_x
98ed T ber_memalloc
986e T ber_memalloc_x
99a8 T ber_memcalloc
990c T ber_memcalloc_x
97c0 T ber_memfree
9730 T ber_memfree_x
9aa1 T ber_memrealloc
99d2 T ber_memrealloc_x
984f T ber_memvfree
97df T ber_memvfree_x
46cb T ber_next_element
3835 T ber_peek_element
39d3 T ber_peek_tag
6aa0 T ber_printf
8c29 T ber_ptrlen
636a T ber_put_berval
6444 T ber_put_bitstring
65d1 T ber_put_boolean
6218 T ber_put_enum
6251 T ber_put_int
6558 T ber_put_null
628a T ber_put_ostring
6a6c T ber_put_seq
6a86 T ber_put_set
63dc T ber_put_string
0020fb20 B ber_pvt_err_file
8da3 T ber_pvt_log_output
0020f9e0 D ber_pvt_log_print
8f14 T ber_pvt_log_printf
0020fba0 B ber_pvt_opt_on
b41c T ber_pvt_sb_buf_destroy
b3e3 T ber_pvt_sb_buf_init
b52c T ber_pvt_sb_copy_out
b636 T 

Re: [GENERAL] PSQL command line print speed

[Adrian Klaver]

On 05/17/2017 09:46 AM, Adrian Myers wrote:

Ah I should have mentioned, the pager is off.


Is that by choice and if so why?

With the pager off you have to wait for the entire output to write to 
the screen. For anything but a small dataset that is going to take time.




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


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

Re: [GENERAL] database is not accepting commands

[Jeff Janes]

On Tue, May 16, 2017 at 1:28 AM, reem  wrote:

> We have 1.5 TB database that's shown an error and block all commands.
> The error is :
> "ERROR:  database is not accepting commands to avoid wraparound data loss
> in
> database "dbname"
> HINT:  Stop the postmaster and use a standalone backend to vacuum that
> database.
> You might also need to commit or roll back old prepared transactions."
>
> I tried to do vacuum in the backend mode. Also I tried to set
> zero_damaged_pages = on then do the vacuum again but same error appeared.
> The error appeared after two hours of vacuuming where verbose shows passing
> tables.
>

I don't see any way that error message can be generated while in standalone
mode.

Are you sure you sure you don't have multiple instances running, and you
are mixing up the logs between them?

Cheers,

Jeff

Re: [GENERAL] PSQL command line print speed

[Francisco Olarte]

Adrian:

On Wed, May 17, 2017 at 6:03 PM, Adrian Myers  wrote:
> Queries which perform in a few milliseconds through a driver (psycopg2 in
> this case) can take several seconds or minutes to complete in the console,
> seemingly just due to it printing lines at a very leisurely pace (in other
> words, this is not the delay caused by the query itself, often it starts
> printing almost instantly but spends a very long time just writing output).
> While overall application performance is unaffected, simple admin tasks and
> poking around the data take longer than I would like as a result. Is there
> any way to improve just the display/write performance in the console?

Are you sure the culprit is psql and not you terminal emulator ?


Francisco Olarte.-


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

Re: [GENERAL] PSQL command line print speed

[Adrian Myers]

Ah I should have mentioned, the pager is off.

On Wed, May 17, 2017 at 12:34 PM, Adrian Klaver 
wrote:

> On 05/17/2017 09:22 AM, Adrian Myers wrote:
>
> Please reply to list also
> Ccing list
>
> Hi Adrian, it is running locally.
>>
>
> In addition to Pavel's suggestions:
>
> In psql what does \pset show for the pager setting?
>
>
>
>
>> On Wed, May 17, 2017 at 12:21 PM, Adrian Klaver <
>> adrian.kla...@aklaver.com > wrote:
>>
>> On 05/17/2017 09:03 AM, Adrian Myers wrote:
>>
>> Is there a way to speed up the actual line-by-line display of
>> the psql console in version 8.4?
>>
>> Queries which perform in a few milliseconds through a driver
>> (psycopg2 in this case) can take several seconds or minutes to
>> complete in the console, seemingly just due to it printing lines
>> at a very leisurely pace (in other words, this is not the delay
>> caused by the query itself, often it starts printing almost
>> instantly but spends a very long time just writing output).
>> While overall application performance is unaffected, simple
>> admin tasks and poking around the data take longer than I would
>> like as a result. Is there any way to improve just the
>> display/write performance in the console?
>>
>>
>> Is this a psql client local to the server machine or across a network?
>>
>>
>>
>> Thanks for any insight,
>> Adrian
>>
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] PSQL command line print speed

[Adrian Klaver]

On 05/17/2017 09:22 AM, Adrian Myers wrote:

Please reply to list also
Ccing list


Hi Adrian, it is running locally.


In addition to Pavel's suggestions:

In psql what does \pset show for the pager setting?





On Wed, May 17, 2017 at 12:21 PM, Adrian Klaver 
> wrote:


On 05/17/2017 09:03 AM, Adrian Myers wrote:

Is there a way to speed up the actual line-by-line display of
the psql console in version 8.4?

Queries which perform in a few milliseconds through a driver
(psycopg2 in this case) can take several seconds or minutes to
complete in the console, seemingly just due to it printing lines
at a very leisurely pace (in other words, this is not the delay
caused by the query itself, often it starts printing almost
instantly but spends a very long time just writing output).
While overall application performance is unaffected, simple
admin tasks and poking around the data take longer than I would
like as a result. Is there any way to improve just the
display/write performance in the console?


Is this a psql client local to the server machine or across a network?



Thanks for any insight,
Adrian



-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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


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

Re: [GENERAL] PSQL command line print speed

[Adrian Klaver]

On 05/17/2017 09:03 AM, Adrian Myers wrote:
Is there a way to speed up the actual line-by-line display of the psql 
console in version 8.4?


Queries which perform in a few milliseconds through a driver (psycopg2 
in this case) can take several seconds or minutes to complete in the 
console, seemingly just due to it printing lines at a very leisurely 
pace (in other words, this is not the delay caused by the query itself, 
often it starts printing almost instantly but spends a very long time 
just writing output). While overall application performance is 
unaffected, simple admin tasks and poking around the data take longer 
than I would like as a result. Is there any way to improve just the 
display/write performance in the console?


Is this a psql client local to the server machine or across a network?



Thanks for any insight,
Adrian



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


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

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Adrian Klaver]

On 05/17/2017 07:26 AM, Martin Goodson wrote:

On 16/05/2017 18:46, Adrian Klaver wrote:

Per Tom's suggestion try

ldd /db_demo/app/postgres/9.6.2-3/lib/libldap_r-2.4.so.2


Hello. Apologies for the delay in replying - medical appointment this 
morning :) Anyway, executed that command as suggested and this is the 
output I got.




(I noticed there was a second file in there with roughly the same name 
... ish. Just in case it might be relevent, I did that one too :) )


Spun up a RH 7.3 instance and tried the compile and got the same error. 
It then rang a bell and I went back to this:


http://raghavt.blogspot.com/2015/01/configuring-automatic-failover-using.html

"/lib64/libldap_r-2.4.so.2: undefined reference to `ber_sockbuf_io_udp'
collect2: ld returned 1 exit status
make: *** [repmgrd] Error 1

To fix, find the checking library in /lib64/libldap_r-2.4.so.2.

[root@localhost repmgr-2.0]# cd /lib64/
[root@localhost lib64]# ls -l libldap*
lrwxrwxrwx. 1 root root 20 Dec  8 09:23 libldap-2.4.so.2 -> 
libldap-2.4.so.2.5.6

-rwxr-xr-x. 1 root root 317200 Apr 29  2013 libldap-2.4.so.2.5.6
lrwxrwxrwx. 1 root root 22 Dec  8 09:23 libldap_r-2.4.so.2 -> 
libldap_r-2.4.so.2.5.6

-rwxr-xr-x. 1 root root 335264 Apr 29  2013 libldap_r-2.4.so.2.5.6

Ok, there are two copies, retain one and unlink other.

[root@localhost lib64]# unlink libldap_r-2.4.so.2
"


I did the above and success:

PATH=/opt/PostgreSQL/9.6/bin:$PATH make USE_PGXS=1 clean all

...


/usr/bin/ld: warning: libssl.so.1.0.0, needed by 
/opt/PostgreSQL/9.6/lib/libpq.so, may conflict with libssl.so.10

make -C sql
make[1]: Entering directory `/home/ec2-user/repmgr-3.3.1/sql'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/home/ec2-user/repmgr-3.3.1/sql'


I am going to agree with John's comment upstream that unlinking a file 
seems to be dubious. You might want to try linking the file back up 
again after you have installed repmgr and see if it causes any issues.




Hope that info's helpful!

Regards,

Martin.




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


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

Re: [GENERAL] PSQL command line print speed

[Pavel Stehule]

Hi

2017-05-17 18:03 GMT+02:00 Adrian Myers :

> Is there a way to speed up the actual line-by-line display of the psql
> console in version 8.4?
>
> Queries which perform in a few milliseconds through a driver (psycopg2 in
> this case) can take several seconds or minutes to complete in the console,
> seemingly just due to it printing lines at a very leisurely pace (in other
> words, this is not the delay caused by the query itself, often it starts
> printing almost instantly but spends a very long time just writing output).
> While overall application performance is unaffected, simple admin tasks and
> poking around the data take longer than I would like as a result. Is there
> any way to improve just the display/write performance in the console?
>

try to play with used format

probably unalign format will be faster

postgres=# \pset format unaligned
Output format is unaligned.

Regards

Pavel


>
> Thanks for any insight,
> Adrian
>

[GENERAL] PSQL command line print speed

[Adrian Myers]

Is there a way to speed up the actual line-by-line display of the psql
console in version 8.4?

Queries which perform in a few milliseconds through a driver (psycopg2 in
this case) can take several seconds or minutes to complete in the console,
seemingly just due to it printing lines at a very leisurely pace (in other
words, this is not the delay caused by the query itself, often it starts
printing almost instantly but spends a very long time just writing output).
While overall application performance is unaffected, simple admin tasks and
poking around the data take longer than I would like as a result. Is there
any way to improve just the display/write performance in the console?

Thanks for any insight,
Adrian

Re: [GENERAL] sorry, too many clients already error

[Sandeep Gupta]

The log_connections helped. I was able
to diagnose the problem. The connections
are getting closed but my setup is actually generating
too many connections.
Thanks for helping with this.

-sandeep


On Tue, May 16, 2017 at 6:53 PM, Tom Lane  wrote:
> Sandeep Gupta  writes:
>> I have set max_connection = 40.
>> The usage is somewhat not typical. It is basically
>> experiment runs that connect to the database
>> and dump results there.
>
>> The experiments connect through JDBC and
>> they close the connection when they are done.
>
>> I can verify that no more than 20 clients/experiments
>> are active at any given time.
>
> It takes nonzero time for a backend process to actually exit after
> the client has closed the connection.  (A client that wants to wait
> for backend exit can do so by waiting for EOF on the socket, but
> I doubt that the JDBC driver does so.)  If you're spinning through
> sessions rapidly enough, you might just have >20 sessions that are
> still in process of exiting when a new one comes in and gets this
> error.
>
> It'd also be a good thing to see if the client code is sending an
> actual Terminate protocol message ('X') or is just closing the
> socket.  If the latter, maybe it isn't really closing because the
> socket is shared with some child process, or something like that?
>
> Turning on log_connections/log_disconnections might help clarify
> what's happening.
>
> regards, tom lane


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

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Adrian Klaver]

On 05/17/2017 07:26 AM, Martin Goodson wrote:

On 16/05/2017 18:46, Adrian Klaver wrote:

Per Tom's suggestion try

ldd /db_demo/app/postgres/9.6.2-3/lib/libldap_r-2.4.so.2


Hello. Apologies for the delay in replying - medical appointment this 
morning :) Anyway, executed that command as suggested and this is the 
output I got.


pginst@testdemo00:/db_demo/app/postgres/9.6.2-3/lib$ ls -lrt *ldap*

-rwxr-xr-x 1 pginst gpginst 354K Mar 13 11:25 libldap-2.4.so.2
-rwxr-xr-x 1 pginst gpginst 383K Mar 13 11:25 libldap_r-2.4.so.2





pginst@testdemo00:/db_demo/app/postgres/9.6.2-3/lib$ ldd libldap_r-2.4.so.2
 linux-vdso.so.1 =>  (0x7ffcd12e4000)
 liblber-2.4.so.2 => 
/db_demo/app/postgres/9.6.2-3/lib/./liblber-2.4.so.2 (0x7f20ff9a1000)

 libresolv.so.2 => /lib64/libresolv.so.2 (0x7f20ff77d000)
 libsasl2.so.3 => 
/db_demo/app/postgres/9.6.2-3/lib/./libsasl2.so.3 (0x7f20ff55b000)
 libssl.so.1.0.0 => 
/db_demo/app/postgres/9.6.2-3/lib/./libssl.so.1.0.0 (0x7f20ff2ec000)
 libcrypto.so.1.0.0 => 
/db_demo/app/postgres/9.6.2-3/lib/./libcrypto.so.1.0.0 (0x7f20feeb5000)

 libpthread.so.0 => /lib64/libpthread.so.0 (0x7f20fec99000)
 libc.so.6 => /lib64/libc.so.6 (0x7f20fe8d7000)
 libdl.so.2 => /lib64/libdl.so.2 (0x7f20fe6d2000)
 /lib64/ld-linux-x86-64.so.2 (0x7f20ffe14000)


So the EDB install is using its own library for liblber

Per Tom's suggestion:

nm -D /db_demo/app/postgres/9.6.2-3/lib/liblber-2.4.so.2


What has me confused is this:

/lib64/libldap_r-2.4.so.2: undefined reference to `ber_sockbuf_io_udp'

It is referring to a library path outside the EDB install.

Not sure why it is picking that up?

To get a handle on this:

whereis libldap_r

and then

ls -al whatever files are found above

ldd  whatever files are found above



(I noticed there was a second file in there with roughly the same name 
... ish. Just in case it might be relevent, I did that one too :) )


Hope that info's helpful!

Regards,

Martin.




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


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

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 4 - compilation issues on RHEL 7.2

[Martin Goodson]

On 16/05/2017 18:46, Adrian Klaver wrote:

Per Tom's suggestion try

ldd /db_demo/app/postgres/9.6.2-3/lib/libldap_r-2.4.so.2


Hello. Apologies for the delay in replying - medical appointment this 
morning :) Anyway, executed that command as suggested and this is the 
output I got.


pginst@testdemo00:/db_demo/app/postgres/9.6.2-3/lib$ ls -lrt *ldap*

-rwxr-xr-x 1 pginst gpginst 354K Mar 13 11:25 libldap-2.4.so.2
-rwxr-xr-x 1 pginst gpginst 383K Mar 13 11:25 libldap_r-2.4.so.2

pginst@testdemo00:/db_demo/app/postgres/9.6.2-3/lib$ ldd libldap-2.4.so.2
linux-vdso.so.1 =>  (0x7fff47db7000)
liblber-2.4.so.2 => 
/db_demo/app/postgres/9.6.2-3/lib/./liblber-2.4.so.2 (0x7f88934f)

libresolv.so.2 => /lib64/libresolv.so.2 (0x7f88932cc000)
libsasl2.so.3 => 
/db_demo/app/postgres/9.6.2-3/lib/./libsasl2.so.3 (0x7f88930aa000)
libssl.so.1.0.0 => 
/db_demo/app/postgres/9.6.2-3/lib/./libssl.so.1.0.0 (0x7f8892e3b000)
libcrypto.so.1.0.0 => 
/db_demo/app/postgres/9.6.2-3/lib/./libcrypto.so.1.0.0 (0x7f8892a04000)

libc.so.6 => /lib64/libc.so.6 (0x7f8892642000)
libdl.so.2 => /lib64/libdl.so.2 (0x7f889243e000)
/lib64/ld-linux-x86-64.so.2 (0x7f8893959000)

pginst@testdemo00:/db_demo/app/postgres/9.6.2-3/lib$ ldd libldap_r-2.4.so.2
linux-vdso.so.1 =>  (0x7ffcd12e4000)
liblber-2.4.so.2 => 
/db_demo/app/postgres/9.6.2-3/lib/./liblber-2.4.so.2 (0x7f20ff9a1000)

libresolv.so.2 => /lib64/libresolv.so.2 (0x7f20ff77d000)
libsasl2.so.3 => 
/db_demo/app/postgres/9.6.2-3/lib/./libsasl2.so.3 (0x7f20ff55b000)
libssl.so.1.0.0 => 
/db_demo/app/postgres/9.6.2-3/lib/./libssl.so.1.0.0 (0x7f20ff2ec000)
libcrypto.so.1.0.0 => 
/db_demo/app/postgres/9.6.2-3/lib/./libcrypto.so.1.0.0 (0x7f20feeb5000)

libpthread.so.0 => /lib64/libpthread.so.0 (0x7f20fec99000)
libc.so.6 => /lib64/libc.so.6 (0x7f20fe8d7000)
libdl.so.2 => /lib64/libdl.so.2 (0x7f20fe6d2000)
/lib64/ld-linux-x86-64.so.2 (0x7f20ffe14000)

(I noticed there was a second file in there with roughly the same name 
... ish. Just in case it might be relevent, I did that one too :) )


Hope that info's helpful!

Regards,

Martin.


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

Re: [GENERAL] storing large files in database - performance

[Merlin Moncure]

On Tue, May 16, 2017 at 9:51 AM, Thomas Kellerer  wrote:
> John R Pierce schrieb am 16.05.2017 um 16:44:
>> On 5/16/2017 7:35 AM, Thomas Kellerer wrote:
>>> When my (JDBC based) SQL client and the database server are on the same 
>>> computer...
>>
>> node.js is Javascript, not java w/ jdbc
>
> I know that.
>
> I mentioned JDBC so that it's clear that the timings were done using a 
> different technology
>
> Maybe it's Node.js or the JavaScript "driver" that causes the problems.

When writing large objects to the database, method of transmission
will very much determine performance until you start hitting the
natural boundaries imposed by the database.

via (hastily written):
#include "libpqtypes.h"
#include "stdlib.h"
#include "string.h"

int main()
{
  int s = 1024 * 1024 * 256;

  char *p = malloc(s);
  memset(p, 'x', s);
  p[s-1] = 0;

  PGconn *conn = PQconnectdb("");
  PQinitTypes(conn);

  PGresult *res = PQexecf(conn, "insert into foo values(1,%text)", p);

  if(!res)
fprintf(stderr, "*ERROR: %s\n", PQgeterror());

  PQclear(res);
}

mmoncure@mernix2 09:13 AM /tmp$ gcc -otest test.c -lpq -lpqtypes -I
/home/mmoncure/src/libpqtypes-1.5.1/src -I
/home/mmoncure/pg94/include/ -L
/home/mmoncure/src/libpqtypes-1.5.1/.libs/
mmoncure@mernix2 09:13 AM /tmp$ psql -c "create table foo(i int, f text)"
CREATE TABLE
mmoncure@mernix2 09:13 AM /tmp$ psql -c "alter table foo alter f set
storage external"
ALTER TABLE
mmoncure@mernix2 09:14 AM /tmp$ time
LD_LIBRARY_PATH=/home/mmoncure/src/libpqtypes-1.5.1/.libs ./test

real 0m3.245s
user 0m0.092s
sys 0m0.232s
mmoncure@mernix2 09:15 AM /tmp$ psql -c "select
pg_size_pretty(pg_table_size('foo'))"
 pg_size_pretty

 266 MB
(1 row)


...that's over 76mb/sec (to local server) for 256mb transfer.  That's
pretty good I think.   We have a 1GB barrier on bytea/text and (at
least in C, with certain reasonable precautions) you can work
comfortably under that limit.  There might be other better strategies
but it can be done.

merlin


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

Re: [GENERAL] database is not accepting commands

[Adrian Klaver]

On 05/16/2017 10:44 PM, reem wrote:

postgres 9.3 in ubuntu OS.



Meant to add to previous post.

What is the full version number of Postgres e.g. 9.3.x?


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


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

Re: [GENERAL] database is not accepting commands

[Adrian Klaver]

On 05/16/2017 10:44 PM, reem wrote:

postgres 9.3 in ubuntu OS.

Yes I did this :
1- service postgresql stop
2- /usr/lib/postgresql/9.3/bin/postgres --single -D
/var/lib/postgresql/9.3/main -c
config_file=/etc/postgresql/9.3/main/postgresql.conf dbname
3-vacuum verbose
4- vacumming processed and it shows tables being vacuumed but then suddenly
stopped with the same error above.

Please note we didn't try to VACUUM FULL because it takes a lot of the
resources .


Have you looked at the other part of the original error message:

"You might also need to commit or roll back old prepared transactions."

Might want to take a look at what is in pg_prepared_xacts:

https://www.postgresql.org/docs/9.3/static/view-pg-prepared-xacts.html



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


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