Re: [GENERAL] testcase failing on git master / how to progress

2017-03-17 Thread Tom Lane 
Martin F  writes:
> I did build with

> make clean distclean
> ./configure --prefix=/deploys/postgresql/inst/  --with-pgport=5433 
> --enable-debug CFLAGS="-ggdb -O0 -g3 -fno-omit-frame-pointer"
> make
> make install

That looks like it ought to work.  Did you get any compile warnings?
(Personally I tend to do the make steps as "make -s" so that you can
spot the warnings if there are any.)

> And  I  got a few tests failing.
>   macaddr8 ... FAILED
>   opr_sanity   ... FAILED
>   object_address   ... FAILED

Hard to say much about that.  Looking at the actual regression.diffs
might be more informative.

A general tip when playing around with development HEAD is to look
at the buildfarm:
https://buildfarm.postgresql.org/cgi-bin/show_status.pl

If critters are falling over left and right on the commit you pulled,
then you got a bad version.  If all is green on the buildfarm but
it's not working for you, then we'd like to hear details.

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] testcase failing on git master / how to progress

2017-03-17 Thread Martin F 

On 18/03/2017 01:22, Adrian Klaver wrote:

On 03/17/2017 06:07 PM, Martin F wrote:

Hi,
I just started last week to build postgresql from source. (So this may
well be something I did wrong on my side)


Do you really want to build the latest dev version?


Yes, I want the latest, and I understand, that it may be unstable.

this is not using the postgres in any production/development. Rather 
that I want to start exploring the postgres sources themself.




--
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] testcase failing on git master / how to progress

2017-03-17 Thread Adrian Klaver 

On 03/17/2017 06:07 PM, Martin F wrote:

Hi,
I just started last week to build postgresql from source. (So this may
well be something I did wrong on my side)


Do you really want to build the latest dev version?

If not and you want a specific version you should look here:

https://www.postgresql.org/ftp/source/




I am on branch master
  Revision: f7819baa618c528f60e266874051563ecfe08207
  Date: 17/03/2017 18:58:06

I did build with

make clean distclean
./configure --prefix=/deploys/postgresql/inst/  --with-pgport=5433
--enable-debug CFLAGS="-ggdb -O0 -g3 -fno-omit-frame-pointer"
make
make install

And  I  got a few tests failing.
 macaddr8 ... FAILED
 opr_sanity   ... FAILED
 object_address   ... FAILED

If I build with
./configure --prefix=/deploys/postgresql/inst/ --with-pgport=5433
then all tests pass.

So questions:
- Can anyone else reproduce this?
- How to establish if this is a problem with my build, or a bug in pg?
  (And if the latter, what next)


one more note:
  make check
has a problem on my system, but I (believe I) have worked around.
I can see that "make check" sets
PATH="/deploys/postgresql/postgresql/tmp_install/deploys/postgresql/inst/bin:$PATH"
LD_LIBRARY_PATH="/deploys/postgresql/postgresql/tmp_install/deploys/postgresql/inst/lib"

but this path does not exist, and therefore binaries are not found.
I did install the build, and set the path (and ld-path) to include the
install location, then run the tests.
I have not found what causes this issue, but maybe it is the --prefix?

 uname -a
FreeBSD bsd1 10.0-RELEASE FreeBSD 10.0-RELEASE #0 r260789: Fri Jan 17
01:46:25 UTC 2014 r...@snap.freebsd.org:/usr/obj/usr/src/sys/GENERIC  i386

  gmake --version
GNU Make 4.1
Built for i386-portbld-freebsd10.0

   cc --version
FreeBSD clang version 3.3 (tags/RELEASE_33/final 183502) 20130610
Target: i386-unknown-freebsd10.0
Thread model: posix







--
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

[GENERAL] testcase failing on git master / how to progress

2017-03-17 Thread Martin F 

Hi,
I just started last week to build postgresql from source. (So this may 
well be something I did wrong on my side)


I am on branch master
  Revision: f7819baa618c528f60e266874051563ecfe08207
  Date: 17/03/2017 18:58:06

I did build with

make clean distclean
./configure --prefix=/deploys/postgresql/inst/  --with-pgport=5433 
--enable-debug CFLAGS="-ggdb -O0 -g3 -fno-omit-frame-pointer"

make
make install

And  I  got a few tests failing.
 macaddr8 ... FAILED
 opr_sanity   ... FAILED
 object_address   ... FAILED

If I build with
./configure --prefix=/deploys/postgresql/inst/ --with-pgport=5433
then all tests pass.

So questions:
- Can anyone else reproduce this?
- How to establish if this is a problem with my build, or a bug in pg?
  (And if the latter, what next)


one more note:
  make check
has a problem on my system, but I (believe I) have worked around.
I can see that "make check" sets
PATH="/deploys/postgresql/postgresql/tmp_install/deploys/postgresql/inst/bin:$PATH" 
LD_LIBRARY_PATH="/deploys/postgresql/postgresql/tmp_install/deploys/postgresql/inst/lib"

but this path does not exist, and therefore binaries are not found.
I did install the build, and set the path (and ld-path) to include the 
install location, then run the tests.

I have not found what causes this issue, but maybe it is the --prefix?

 uname -a
FreeBSD bsd1 10.0-RELEASE FreeBSD 10.0-RELEASE #0 r260789: Fri Jan 17 
01:46:25 UTC 2014 r...@snap.freebsd.org:/usr/obj/usr/src/sys/GENERIC  i386


  gmake --version
GNU Make 4.1
Built for i386-portbld-freebsd10.0

   cc --version
FreeBSD clang version 3.3 (tags/RELEASE_33/final 183502) 20130610
Target: i386-unknown-freebsd10.0
Thread model: posix




--
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 - looking in wrong place for socket

2017-03-17 Thread George Neuner 
On Fri, 17 Mar 2017 10:31:16 -0400, Steve Clark
 wrote:


>Hmm... maybe you missed the fact I am running CentOS 6. It appears
>8.20 is the latest official release.


I'm running 9.5.5 on Centos 6.8 - no problems at all. 


Go into /etc/yum.repos.d/CentOS-Base.repo, and add the line

  exclude=postgresql*

to both the [base] and [updates] section.  This will prevent yum from
looking at the Centos repositories for anything postgresql.


Then grab a repo file for a more recent version from
https://yum.postgresql.org/ and place the file in /etc/yum.repos.d.

>From that point, yum will see the new version.


I don't know what issues you may face in upgrading from 8.2 - I have
never tried leaping so many [major] versions at once.

George



-- 
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] CenOS 5/Postgresql 9.6

2017-03-17 Thread John R Pierce 

On 3/17/2017 1:56 PM, Thomas Kellerer wrote:
The question remains - does anyone know where I might find packages 
so I don't have to compile them myself?


You can download the binaries (no RPM) from here:

https://www.enterprisedb.com/products-services-training/pgbindownload


how are those built so they avoid all the usual Linux library 
compatibility issues?   they have everything statically linked,, 
including glibc and openssl and whatever?



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] CenOS 5/Postgresql 9.6

2017-03-17 Thread Thomas Kellerer 

Steve Crawford schrieb am 17.03.2017 um 20:15:

I'm aware of all those dates. Also that 9.6 has been out for
six-months, and that RHEL/CentOS 5 are listed among the currently
supported versions at https://yum.postgresql.org/.

The question remains - does anyone know where I might find packages so I don't 
have to compile them myself?


You can download the binaries (no RPM) from here:

https://www.enterprisedb.com/products-services-training/pgbindownload

Or an installer:

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





--
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] CenOS 5/Postgresql 9.6

2017-03-17 Thread Steve Crawford 
On Fri, Mar 17, 2017 at 11:35 AM, John R Pierce  wrote:

> On 3/17/2017 11:07 AM, Steve Crawford wrote:
>
>> Where might I find yum repos PostgreSQL 9.6 on CentOS 5 (i386 & x86_64)?
>>
>> RHEL/CentOS 5 is still in production with extended support through 2020
>> but seems to be dropped from the 9.6 PGDG repos.
>>
>
> CentOS 5 will be completely dropped in 2-3 weeks, I believe. The last
> CentOS 5 Update 11 release was in 2014.  Extended support applies only
> to RHEL, and costs significant money per server. You might contact the
> yum.postgresql.org repository manager's employer, EnterpriseDB, and see
> if they'd be willing to extend support for RHEL 6 and PG 9.6 under contract.
>
>
I'm aware of all those dates. Also that 9.6 has been out for six-months,
and that RHEL/CentOS 5 are listed among the currently supported versions at
https://yum.postgresql.org/.

The question remains - does anyone know where I might find packages so I
don't have to compile them myself?

Cheers,
Steve

Re: [GENERAL] CenOS 5/Postgresql 9.6

2017-03-17 Thread John R Pierce 

On 3/17/2017 11:07 AM, Steve Crawford wrote:

Where might I find yum repos PostgreSQL 9.6 on CentOS 5 (i386 & x86_64)?

RHEL/CentOS 5 is still in production with extended support through 
2020 but seems to be dropped from the 9.6 PGDG repos.


CentOS 5 will be completely dropped in 2-3 weeks, I believe. The last 
CentOS 5 Update 11 release was in 2014.  Extended support applies 
only to RHEL, and costs significant money per server. You might 
contact the yum.postgresql.org repository manager's employer, 
EnterpriseDB, and see if they'd be willing to extend support for RHEL 6 
and PG 9.6 under contract.



--
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] CenOS 5/Postgresql 9.6

2017-03-17 Thread Steve Crawford 
Where might I find yum repos PostgreSQL 9.6 on CentOS 5 (i386 & x86_64)?

RHEL/CentOS 5 is still in production with extended support through 2020 but
seems to be dropped from the 9.6 PGDG repos.

Cheers,
Steve

Re: [GENERAL] psql - looking in wrong place for socket

2017-03-17 Thread Adrian Klaver 

On 03/17/2017 07:31 AM, Steve Clark wrote:

On 03/17/2017 10:14 AM, Adrian Klaver wrote:

On 03/17/2017 06:58 AM, Steve Clark wrote:

On 03/17/2017 09:49 AM, Adrian Klaver wrote:

On 03/17/2017 06:42 AM, Steve Clark wrote:

Hi List,

I am running postgresql 8.4.20 on CentOS 6. Things have been running fine for a 
long time
then I rebooted. Postgres came up but when I tried to connect with psql on the 
local machine
I got

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The socket is actually being created (and always has been) in /tmp

So you built Postgres from source?

No - it is installed using yum.

It looks like the problem is someone loaded postgresql-9.5 and psql was
using:
psql14971 postgres  memREG8,4   193296   950807
/usr/pgsql-9.5/lib/libpq.so.5.8

the libpq from 9.5.

By removing the ln and using

# rm /var/run/postgresql/.s.PGSQL.5432
rm: remove symbolic link `/var/run/postgresql/.s.PGSQL.5432'? y

$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?

$ LD_LIBRARY_PATH=/usr/lib64 psql
psql (8.4.20)

You probably already know, but 8.4 is ~2 1/2 past EOL. Also the last
release in that series was 8.4.22.



Hmm... maybe you missed the fact I am running CentOS 6. It appears 8.20
is the latest official release.


Yeah, that would be the CentOS repo's.

The Postgres community repo's are more current:

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

My guess, that is where the 9.5 version you found came from.



$ rpm -qa|grep postgres
postgresql-server-8.4.20-6.el6.x86_64

$ sudo yum update postgresql-server
Loaded plugins: fastestmirror, refresh-packagekit
Setting up Update Process
Loading mirror speeds from cached hostfile
 * atomic: www6.atomicorp.com
 * base: repo1.dal.innoscale.net
 * elrepo: iad.mirror.rackspace.com
 * epel: mirror.nodesdirect.com
 * extras: mirror.sjc02.svwh.net
 * updates: mirror.millry.co
No Packages marked for Update






--
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 - looking in wrong place for socket

2017-03-17 Thread Steve Clark 
On 03/17/2017 10:14 AM, Adrian Klaver wrote:
> On 03/17/2017 06:58 AM, Steve Clark wrote:
>> On 03/17/2017 09:49 AM, Adrian Klaver wrote:
>>> On 03/17/2017 06:42 AM, Steve Clark wrote:
 Hi List,

 I am running postgresql 8.4.20 on CentOS 6. Things have been running fine 
 for a long time
 then I rebooted. Postgres came up but when I tried to connect with psql on 
 the local machine
 I got

 psql: could not connect to server: No such file or directory
 Is the server running locally and accepting
 connections on Unix domain socket 
 "/var/run/postgresql/.s.PGSQL.5432"?

 The socket is actually being created (and always has been) in /tmp
>>> So you built Postgres from source?
>> No - it is installed using yum.
>>
>> It looks like the problem is someone loaded postgresql-9.5 and psql was
>> using:
>> psql14971 postgres  memREG8,4   193296   950807
>> /usr/pgsql-9.5/lib/libpq.so.5.8
>>
>> the libpq from 9.5.
>>
>> By removing the ln and using
>>
>> # rm /var/run/postgresql/.s.PGSQL.5432
>> rm: remove symbolic link `/var/run/postgresql/.s.PGSQL.5432'? y
>>
>> $ psql
>> psql: could not connect to server: No such file or directory
>> Is the server running locally and accepting
>> connections on Unix domain socket
>> "/var/run/postgresql/.s.PGSQL.5432"?
>>
>> $ LD_LIBRARY_PATH=/usr/lib64 psql
>> psql (8.4.20)
> You probably already know, but 8.4 is ~2 1/2 past EOL. Also the last 
> release in that series was 8.4.22.
>
>
Hmm... maybe you missed the fact I am running CentOS 6. It appears 8.20 is the 
latest official release.

$ rpm -qa|grep postgres
postgresql-server-8.4.20-6.el6.x86_64

$ sudo yum update postgresql-server
Loaded plugins: fastestmirror, refresh-packagekit
Setting up Update Process
Loading mirror speeds from cached hostfile
 * atomic: www6.atomicorp.com
 * base: repo1.dal.innoscale.net
 * elrepo: iad.mirror.rackspace.com
 * epel: mirror.nodesdirect.com
 * extras: mirror.sjc02.svwh.net
 * updates: mirror.millry.co
No Packages marked for Update

Re: [GENERAL] psql - looking in wrong place for socket

2017-03-17 Thread Adrian Klaver 

On 03/17/2017 06:58 AM, Steve Clark wrote:

On 03/17/2017 09:49 AM, Adrian Klaver wrote:

On 03/17/2017 06:42 AM, Steve Clark wrote:

Hi List,

I am running postgresql 8.4.20 on CentOS 6. Things have been running fine for a 
long time
then I rebooted. Postgres came up but when I tried to connect with psql on the 
local machine
I got

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The socket is actually being created (and always has been) in /tmp

So you built Postgres from source?

No - it is installed using yum.

It looks like the problem is someone loaded postgresql-9.5 and psql was
using:
psql14971 postgres  memREG8,4   193296   950807
/usr/pgsql-9.5/lib/libpq.so.5.8

the libpq from 9.5.

By removing the ln and using

# rm /var/run/postgresql/.s.PGSQL.5432
rm: remove symbolic link `/var/run/postgresql/.s.PGSQL.5432'? y

$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?

$ LD_LIBRARY_PATH=/usr/lib64 psql
psql (8.4.20)


You probably already know, but 8.4 is ~2 1/2 past EOL. Also the last 
release in that series was 8.4.22.



Type "help" for help.

postgres=#

It works OK again.

So now I know what caused the problem.

Thanks,
Steve

I worked around the problem temporarily by
ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

What controls where psql looks for the socket?

https://www.postgresql.org/message-id/23876.1488949292%40sss.pgh.pa.us

"With the default configure options you used, the postmaster would have
put its Unix socket file into /tmp, not /var/run.  I wonder whether your
problem is that you're trying to connect to it with distro-supplied
psql+libpq that expects to find the Unix socket in /var/run."




Thanks,
Steve









--
Stephen Clark
*NetWolves Managed Services, LLC.*
Director of Technology
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.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] array of bytea;

2017-03-17 Thread Tom Lane 
Tom DalPozzo  writes:
> I tried without success INSERT INTO MYTABLE VALUES ('{'\x0011','\x2233'}');
> What's wrong?

You've ignored the rules about how to write element values within an
array literal:
https://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-IO

Personally I tend to write an ARRAY[] construct in preference to
manually adding extra quotes and backslashes to such values.

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] psql - looking in wrong place for socket

2017-03-17 Thread Steve Clark 
On 03/17/2017 09:49 AM, Adrian Klaver wrote:
> On 03/17/2017 06:42 AM, Steve Clark wrote:
>> Hi List,
>>
>> I am running postgresql 8.4.20 on CentOS 6. Things have been running fine 
>> for a long time
>> then I rebooted. Postgres came up but when I tried to connect with psql on 
>> the local machine
>> I got
>>
>> psql: could not connect to server: No such file or directory
>> Is the server running locally and accepting
>> connections on Unix domain socket 
>> "/var/run/postgresql/.s.PGSQL.5432"?
>>
>> The socket is actually being created (and always has been) in /tmp
> So you built Postgres from source?
No - it is installed using yum.

It looks like the problem is someone loaded postgresql-9.5 and psql was using:
psql14971 postgres  memREG8,4   193296   950807 
/usr/pgsql-9.5/lib/libpq.so.5.8

the libpq from 9.5.

By removing the ln and using

# rm /var/run/postgresql/.s.PGSQL.5432
rm: remove symbolic link `/var/run/postgresql/.s.PGSQL.5432'? y

$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

$ LD_LIBRARY_PATH=/usr/lib64 psql
psql (8.4.20)
Type "help" for help.

postgres=#

It works OK again.

So now I know what caused the problem.

Thanks,
Steve
>> I worked around the problem temporarily by
>> ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432
>>
>> What controls where psql looks for the socket?
> https://www.postgresql.org/message-id/23876.1488949292%40sss.pgh.pa.us
>
> "With the default configure options you used, the postmaster would have 
> put its Unix socket file into /tmp, not /var/run.  I wonder whether your
> problem is that you're trying to connect to it with distro-supplied
> psql+libpq that expects to find the Unix socket in /var/run."
>
>
>
>> Thanks,
>> Steve
>>
>>
>>
>>
>


-- 
Stephen Clark
*NetWolves Managed Services, LLC.*
Director of Technology
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com

[GENERAL] array of bytea;

2017-03-17 Thread Tom DalPozzo 
Hi,
I created a table:
CREATE TABLE MYTABLE (DATA BYTEA[]);

Now, I'd like to insert some values (a '\x0011' and a '\x2233') but I can't
find a right way.
I tried without success INSERT INTO MYTABLE VALUES ('{'\x0011','\x2233'}');
What's wrong?

Thanks
Pupillo

Re: [GENERAL] psql - looking in wrong place for socket

2017-03-17 Thread Adrian Klaver 

On 03/17/2017 06:42 AM, Steve Clark wrote:

Hi List,

I am running postgresql 8.4.20 on CentOS 6. Things have been running fine for a 
long time
then I rebooted. Postgres came up but when I tried to connect with psql on the 
local machine
I got

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The socket is actually being created (and always has been) in /tmp


So you built Postgres from source?



I worked around the problem temporarily by
ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

What controls where psql looks for the socket?


https://www.postgresql.org/message-id/23876.1488949292%40sss.pgh.pa.us

"With the default configure options you used, the postmaster would have 
put its Unix socket file into /tmp, not /var/run.  I wonder whether your

problem is that you're trying to connect to it with distro-supplied
psql+libpq that expects to find the Unix socket in /var/run."





Thanks,
Steve







--
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

[GENERAL] psql - looking in wrong place for socket

2017-03-17 Thread Steve Clark 
Hi List,

I am running postgresql 8.4.20 on CentOS 6. Things have been running fine for a 
long time
then I rebooted. Postgres came up but when I tried to connect with psql on the 
local machine
I got

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The socket is actually being created (and always has been) in /tmp

I worked around the problem temporarily by
ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

What controls where psql looks for the socket?

Thanks,
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] pg_dump pg_restore hanging in CentOS for large data

2017-03-17 Thread Adrian Klaver 

On 03/17/2017 12:27 AM, Sridevi B wrote:
Ccing list.
Please reply to list also, it puts more eyes on the problem.


Hi Adrian,

 Sorry for delay. Please find my answers inline.

Thanks,
Sridevi





On Thu, Mar 16, 2017 at 2:28 AM, Adrian Klaver
> wrote:

On 03/14/2017 09:48 AM, Sridevi B wrote:

Hi ,

   I am facing an issue with backup/Restore for data size more than
*2GB*. Its working fine for *1GB*.



Below are the details for issue:



Description:



The command pg_dump is hanging at saving large objects and
process gets
terminated after some time.



The command pg_restore is hanging at executing BLOB and getting
terminated after some time.






When you refer to BLOB do you mean large objects:

https://www.postgresql.org/docs/9.2/static/largeobjects.html


or something else? *[Sridevi] yes, internally it refers to large
objects*.




***

Expecting: pg_dump/pg_restore should work for minimum large data
size <20GB.



***



What data size are you talking about, the entire dump file or an
object in the file?


***
*[Sridevi] I am talking about entire dump file

size, which of size >3GB*


***






PostgreSQL version number you are running: postgres92-9.2.9-1.x86_64

 How you installed PostgreSQL:

  Linux RHEL(Backup) installed using rpm.

 CentOS7.2(Restore) installed using yum.

 Operating system and version:

  Backup - Red Hat Enterprise Linux Server release 5.4 (Tikanga)

 Restore -centos-release-7-2.1511.el7.centos.2.10.x86_64


What program you're using to connect to PostgreSQL:
pg_dump/pg_restore
using shell script




***

What are the scripts? *[Sridevi]*  - *We are using Linux scripts,
which starts/stops application process during the postgres
backup/restore process. And also scripts takes care of additional
details specific to application. These scripts internally invoke
postgres processes for backup and restore.
*







Is there anything relevant or unusual in the PostgreSQL server
logs?:

  Pg_dump verbose log: stuck after: pg_dump: saving
large objects

Pg_restore verbose log: Stuck after:
pg_restore:
restoring large objects

Some times: pg_restore: pg_restore:
processing item 4376515 BLOB 4993394

pg_restore:
executing BLOB
4993394

For questions about any kind of error:



What you were doing when the error happened / how to cause the
error:
Tried options pg_dump using split and restore. Still same issue
exists.





Explain split and restore?

***



*[Sridevi]* Split option of pg_dump, splits dump file into multiple
files based on size and restore will combine all files and restore the
data.
 I am referring to below link for split and restore.


http://www.postgresql-archive.org/large-database-problems-with-pg-dump-and-pg-restore-td3236910.html

 I tried below commands:
*Backup:* /opt/postgres/9.2/bin/pg_dump -v -c -h localhost -p 5432 -U
${$db_user}-w -Fc ${db_name}- | split -b 1000m -
/opt/backups/${dump_file_name}
*Restore: *cat /opt/backups/${dump_file_name}* |
/opt/postgres/9.2/bin/pg_restore | /opt/postgres/9.2/bin/psql
${db_name}-h localhost -p 5432 -v -U ${$db_user} -w
 The restore is getting stuck at below error message and process gets
terminated.
  could not send data to client: Broken pipe
  connection to client lost



***

So all this happening on the same host, correct?

I do not see anything that is large object specific in the error above.

What is the error message you get at the terminal when you do not use 
the split/cat method?


Have you checked the ulimit settings as suggested by Tom Lane?



The EXACT TEXT of the error message you're getting, if there is one:
(Copy and paste the message to the email, do not send a screenshot)

-  No specific error, pg_dump/pg_restore getting terminated for
data >2GB


Regards,

Sridevi



--
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

[GENERAL] Generating JSON-encoded list of object out of joined tables

2017-03-17 Thread Alexander Farber 
Good afternoon,

what would be please the best way to generate a list of JSON objects out of
an SQL join?

I am using jQuery dataTables plugin and initially was performing an SQL
join and then in my PHP script was fetching results row by row and finally
encoded them to JSON and feeded to the plugin.

But then I realized that with PostgreSQL that part could be spared and
after reading
https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql I
have rewritten my custom function:

CREATE OR REPLACE FUNCTION words_get_longest2(
in_uid integer
) RETURNS json AS
$func$
SELECT ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(x))) FROM (
SELECT
ROW_NUMBER() OVER () AS row,
s.gid AS gid,
TO_CHAR(g.created, 'DD.MM. HH24:MI') AS created,
TO_CHAR(g.finished, 'DD.MM. HH24:MI') AS
finished,
CASE WHEN g.player1 = in_uid THEN g.player1 ELSE
g.player2 END AS player1,
CASE WHEN g.player1 = in_uid THEN g.player2 ELSE
g.player1 END AS player2,
CASE WHEN g.player1 = in_uid THEN g.score1 ELSE
g.score2 END AS score1,
CASE WHEN g.player1 = in_uid THEN g.score2 ELSE
g.score1 END AS score2,
s1.female AS female1,
s2.female AS female2,
s1.given AS given1,
s2.given AS given2,
s1.photo AS photo1,
s2.photo AS photo2,
s1.place AS place1,
s2.place AS place2,
s.word AS word,
s.score AS score,
m.tiles AS tiles
FROMwords_scores s
LEFT JOIN words_games g USING(gid)
LEFT JOIN words_moves m USING(mid)
LEFT JOIN words_social s1 ON s1.uid = in_uid
-- find social record with the most recent timestamp
AND NOT EXISTS (SELECT 1
FROM words_social s
WHERE s1.uid = s.uid
AND s.stamp > s1.stamp)
LEFT JOIN words_social s2 ON s2.uid = (CASE WHEN g.player1
= in_uid THEN g.player2 ELSE g.player1 END)
-- find social record with the most recent timestamp
AND NOT EXISTS (SELECT 1
FROM words_social s
WHERE s2.uid = s.uid
AND s.stamp > s2.stamp)
WHERE s.uid = in_uid
ORDER BY LENGTH(s.word) DESC, s.mid DESC
LIMIT   10
) x;

$func$ LANGUAGE sql STABLE;

which delivers me results like:

words=> select words_get_longest2(2);



words_get_longest2



---
---
---
---
---
-
 [{"row":2,"gid":1,"created":"17.03.2017
09:06","finished":null,"player1":2,"player2":1,"score1":24,"score2":51,"female1":0,"female2":0,"given1":"Ghijk4","given2":"Abcde1","photo1":null,"
photo2":"https://vk.com/images/camera_200.png","place1":null,"place2":null,"word":"СМЯТИЕ","score":16,"tiles":[{"col":
5, "row": 13, "value": 1, "letter": "Е"}, {"col": 5, "row": 12, "val
ue": 1, "letter": "И"}, {"col": 5, "row": 11, "value": 2, "letter": "Т"},
{"col": 5, "row": 10, "value": 0, "letter": "Я"}, {"col": 5, "row": 8,
"value": 2, "letter": "С"}]},{"row":1,"gid
":1,"created":"17.03.2017
09:06","finished":null,"player1":2,"player2":1,"score1":24,"score2":51,"female1":0,"female2":0,"given1":"Ghijk4","given2":"Abcde1","photo1":null,"photo2":"https:
//vk.com/images/camera_200.png","place1":null,"place2":null,"word":"МЕХ","score":8,"tiles":[{"col":
6, "row": 9, "value": 1, "letter": "Е"}, {"col": 5, "row": 9, "value": 2,
"letter": "М"
}]}]
(1 row)

Is that please a good approach or is there maybe a better way with
PostgreSQL 9.5 or 9.6?

Thank you
Alex