Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VR9Zt-0005a0-TV for pgsql-sql@arkaria.postgresql.org; Tue, 01 Oct 2013 23:42:06 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VR9Zt-0002jW-AK for pgsql-sql@arkaria.postgresql.org; Tue, 01 Oct 2013 23:42:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VR9Zr-0002jJ-Vz for pgsql-sql@postgresql.org; Tue, 01 Oct 2013 23:42:04 +0000 Received: from nm14-vm9.bullet.mail.gq1.yahoo.com ([98.136.218.171]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VR9Zn-0007iS-9v for pgsql-sql@postgresql.org; Tue, 01 Oct 2013 23:42:03 +0000 Received: from [98.137.12.59] by nm14.bullet.mail.gq1.yahoo.com with NNFMP; 01 Oct 2013 23:41:56 -0000 Received: from [98.137.12.194] by tm4.bullet.mail.gq1.yahoo.com with NNFMP; 01 Oct 2013 23:41:56 -0000 Received: from [127.0.0.1] by omp1002.mail.gq1.yahoo.com with NNFMP; 01 Oct 2013 23:41:56 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 777642.50838.bm@omp1002.mail.gq1.yahoo.com Received: (qmail 21437 invoked by uid 60001); 1 Oct 2013 23:41:56 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1380670916; bh=NprPCvsDFcLDeMbgsurduKW0NwANxmLmASnUTBMLwpE=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=YhcG09VxYuLfxJtT8cWl184oR/A/MMRk8KkNEeE8wbAOVNxuLI3RfAXM9q1Kdg3Vmlftrib78AAj8w2Ix3J+lYqTZQpQ/cF8b2zUsnSdxFg1lOwyud2vo2xGkJ+QVrGXF9In64f/5Up/7RWfArkYLSzrK7nrHU1Y8ugkXKHFHVc= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=5mZaqA8Qmfj/DRkxwwpHNKBK6uf6pDm3GkjwkGDXPd1ssSgbMTuQ6E6hdFRsfpCZ+5BDmDMfcXc1IHBqWzRLXP+Mjw9jsqld5FnC+2t6ZxK5UWRBI3EMOkpgKa3v30EE82t4x1IsAONqAcyWK4KNJzwveZaRDVMe+WvoP1U/LAY=; X-YMail-OSG: qwh4I7MVM1l8KHie.onbRRXa.JCYAdRis8eh3iRG5zN_3sp mJtObeIQJ5a0Pqi.MXoRvF7xXFm_I3mmmeG49bYDNDGzkAjQN7cRwhEY37SM ystPMAqE.U9LIaB5D.u6_vosjP7mbvOBOn91c1oG9ONYOBsJ0PQxaS_9SG01 xk3oFeWs5J42ICTTHGcKWMW3o74ggGS8MRQQ2kNJ2pdheG9.YmDjHvjEPc_c QGFIkmjFc5rQkxFgFN7ZR485sgwxHdne8Ac50JUn2_M2ZMc73RJ0cdplzN8O blbmknAO7a84yoZN13djzd8YPK3dgQGYL2jxl6AbcFc5JIqCK.JH0jYqTq.A oVze7MqDVJu2ARf7Ab9ZGEjOR1fLezkMDIVZO3.lvYnnlKLZF4Ia0CvTRe8F jC2ajxdGcQyshtWrPC82Iy0AJE.7.1mGEmFEm2gyIhq8EfcBdSVd5_HEkdad kdQs1vJapuzIhNSOnIMU7F0CTKATH2a252nz6D8elc1kN9ge7RePf.cC7U0Y Nn2ErLmdttVbmQTGGSOF.WmegK9NU7Kewvgrba0TSjlf5jOrsLGQ6E0BXNTz ERwW7rnKanqsgtkrtyNBE9mFkm7jngsMUVy5t4w-- Received: from [64.6.164.162] by web164504.mail.gq1.yahoo.com via HTTP; Tue, 01 Oct 2013 16:41:56 PDT X-Rocket-MIMEInfo: 002.001, SGkgYWxsLCBncmVldGluZ3MhCsKgClVzaW5nIE9yYWNsZSBIZXRlcm9nZW5lb3VzIFNlcnZpY2VzIChPcmFjbGUgSFMpwqBJIGhhdmXCoGNvbmZpZ3VyZWQvY3JlYXRlZCBhIERCIGxpbmsgZnJvbSBQb3N0Z3JlcyA5LjMgZGF0YWJhc2UgaW50byBPcmFjbGUgMTFnUjMgZGF0YWJhc2XCoCh3aXRoIHBvc3RncmVzIERCIHVzZXIgY3JlZGVudGlhbHMpLgrCoFNRTD7CoCBjcmVhdGUgcHVibGljIGRhdGFiYXNlIGxpbmsgcGdfbGluayBjb25uZWN0IHRvICJwb3N0Z3JlcyIgaWRlbnRpZmllZCBieSAiYmxhaGJsYWgBMAEBAQE- X-Mailer: YahooMailWebService/0.8.160.587 References: <524A90D1.5000109@iqbuzz.ru> <524B22C7.2010300@pasteleros.org.ar> Message-ID: <1380670916.20112.YahooMailNeo@web164504.mail.gq1.yahoo.com> Date: Tue, 1 Oct 2013 16:41:56 -0700 (PDT) From: Bhanu Murthy Reply-To: Bhanu Murthy Subject: DB link from postgres to Oracle; how to query Dbname.tablename? To: "pgsql-admin@postgresql.org" , "pgsql-sql@postgresql.org" In-Reply-To: <524B22C7.2010300@pasteleros.org.ar> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="11193365-1848993133-1380670916=:20112" X-Pg-Spam-Score: -1.0 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --11193365-1848993133-1380670916=:20112 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi all, greetings!=0A=A0=0AUsing Oracle Heterogeneous Services (Oracle HS)= =A0I have=A0configured/created a DB link from Postgres 9.3 database into Or= acle 11gR3 database=A0(with postgres DB user credentials).=0A=A0SQL>=A0 cre= ate public database link pg_link connect to "postgres" identified by "blahb= lah"=0Ausing 'postgresql';=0ASince Postgres does not support public synonym= s across databases in a cluster, how do I connect to a specific database an= d query a specific table in this Postgres cluster using the HS DB link?=0A= =A0=0ALet's say, if=A0I have 2 Postgres databases named pgdb01 and pgdb02 i= n the Postgres cluster, using this DB link that I have created in Oracle, h= ow can I query a specific table called table01 from pgdb01 database? =0A=A0= =0AEven though the table user_account exists in pgdb01 database, I cannot s= elect from it using the DB link.=0A=A0=0ASQL> select count(*) from mailto:%= 22user_account%22@pg_link;=0Aselect count(*) from "user_account"@pg_link;= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 *=0AERROR at l= ine 1:=0AORA-28500: connection from ORACLE to a non-Oracle system returned = this message:=0AERROR:=A0 relation "user_account" does not exist at charact= er 21;=0ANo query has been executed with that handle {HY000,NativeErr =3D 1= }=0AORA-02063: preceding 3 lines from PG_LINK;=0A=A0=0AI tried dbname.table= name syntax, but it didn't work!=A0 BTW, all my tables belong to public sch= ema.=0A=A0=0ADoes anyone with DB link expertise try to answer my question?= =0A=0AThanks, =0ABhanu M. Gandikota=0AMobile: (415) 420-7740=0A =0A=0A_____= ___________________________=0A From: Alejandro Brust =0ATo: pgsql-admin@postgresql.org =0ASent: Tuesday, October 1, 2013= 12:30 PM=0ASubject: Re: [ADMIN] PostgreSQL 9.2 - pg_dump out of memory whe= n backuping a database with 300000000 large objects=0A =0A=0ADid U perform= =A0 any vacuumdb / reindexdb before the Pg_dump?=0A=0A=0AEl 01/10/2013 09:4= 9, Magnus Hagander escribi=F3:=0A> On Tue, Oct 1, 2013 at 11:07 AM, Sergey = Klochkov wrote:=0A>> Hello All,=0A>>=0A>> While trying= to backup a database of relatively modest size (160 Gb) I ran=0A>> into th= e following issue:=0A>>=0A>> When I run=0A>> $ pg_dump -f /path/to/mydb.dmp= -C -Z 9 mydb=0A>>=0A>> File /path/to/mydb.dmp does not appear (yes, I've c= hecked permissions and so=0A>> on). pg_dump just begins to consume memory u= ntil it eats up all avaliable=0A>> RAM (96 Gb total on server, >64 Gb avail= able) and is killed by the oom=0A>> killer.=0A>>=0A>> According to pg_stat_= activity, pg_dump runs the following query=0A>>=0A>> SELECT oid, (SELECT ro= lname FROM pg_catalog.pg_roles WHERE oid =3D lomowner)=0A>> AS rolname, lom= acl FROM pg_largeobject_metadata=0A>>=0A>> until it is killed.=0A>>=0A>> st= race shows that pg_dump is constantly reading a large amount of data from= =0A>> a UNIX socket. I suspect that it is the result of the above query.=0A= >>=0A>> There are >300000000 large objects in the database. Please don't as= k me why.=0A>>=0A>> I tried googling on this, and found mentions of pg_dump= being killed by oom=0A>> killer, but I failed to find anything related to = the huge large objects=0A>> number.=0A>>=0A>> Is there any method of workin= g around this issue?=0A> I think this problem comes from the fact that pg_d= ump treats each=0A> large object as it's own item. See getBlobs() which all= ocates a=0A> BlobInfo struct for each LO (and a DumpableObject if there are= any,=0A> but that's just one).=0A>=0A> I assume the query (from that file)= :=0A> SELECT oid, lomacl FROM pg_largeobject_metadata=0A>=0A> returns 30000= 0000 rows, which are then looped over?=0A>=0A> I ran into a similar issue a= few years ago with a client using a=0A> 32-bit version of pg_dump, and got= it worked around by moving to=0A> 64-bit. Did unfortunately not have time = to look at the underlying=0A> issue.=0A>=0A>=0A=0A=0A=0A-- =0ASent via pgsq= l-admin mailing list (pgsql-admin@postgresql.org)=0ATo make changes to your= subscription:=0Ahttp://www.postgresql.org/mailpref/pgsql-admin --11193365-1848993133-1380670916=:20112 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Hi all, greetings!<= /span>
 
Using Oracle Heterogen= eous Services (Oracle HS) I have configured/created a DB link fro= m Postgres 9.3 database into Oracle 11gR3 database (with postgres DB u= ser credentials).
 
SQL>  create public database link=0Apg_link connect to "postgres" identified by "blahblah"=0Ausing = 'postgresql';
Since Postgres does not suppo= rt public synonyms across databases in a cluster, how do I connect to a spe= cific database and query a specific table in this Postgres cluster using th= e HS DB link?
 
Let's sa= y, if I have 2 Postgres databases named pgdb01 and pgdb02 in the Postg= res cluster, using this DB link that I have created in Oracle, how can I qu= ery a specific table called table01 from pgdb01 database?
 
Even though the table user_account exists in pgdb01 database, I cannot select from= it using the DB link.
 
SQL> select count(*) from mailto:%22user_account%22@pg_link;
select count(*) from "user_account"@pg_l= ink;         &nbs= p;           *
ERROR a= t line 1:
ORA-28500: connection from ORACLE to a non-Oracle system retur= ned this message:
ERROR:  relation "user_account" does not exist at= character 21;
No query has been executed with that handle {HY000,Native= Err =3D 1}
ORA-02063: preceding 3 lines from PG_LINK;
<= div> 
I tried dbname.tablename syntax, bu= t it didn't work!  BTW, all my tables belong to public schema.<= /div>
 
Does anyone with DB link expe= rtise try to answer my question?
 
Thanks,
Bhanu M. Gandikota=
Mobile: (415) 420-7740

From: Alejandro Brust <alejandrob@pas= teleros.org.ar>
To:= pgsql-admin@postgresql.org
Sent= : Tuesday, October 1, 2013 12:30 PM
Subject: Re: [ADMIN] PostgreSQL 9.2 - pg_dump out = of memory when backuping a database with 300000000 large objects

Did U perform  any vacuumdb / reindexdb = before the Pg_dump?


El 01/10/2013 09:49, Magnus Hagander escribi= =F3:
> On Tue, Oct 1, 2013 at 11:07 AM, Sergey Klochkov <klochk= ov@iqbuzz.ru> wrote:
>> Hello All,
>>
>> = While trying to backup a database of relatively modest size (160 Gb) I ran<= br>>> into the following issue:
>>
>> When I run>> $ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb
>>
>&g= t; File /path/to/mydb.dmp does not appear (yes, I've checked permissions an= d so
>> on). pg_dump just begins to consume memory until it eats u= p all avaliable
>> RAM (96 Gb total on server, >64 Gb available= ) and is killed by the oom
>> killer.
>>
>> According to pg_stat_activity, pg_dump runs the following query
>>= ;
>> SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oi= d =3D lomowner)
>> AS rolname, lomacl FROM pg_largeobject_metadata=
>>
>> until it is killed.
>>
>> strace= shows that pg_dump is constantly reading a large amount of data from
&g= t;> a UNIX socket. I suspect that it is the result of the above query.>>
>> There are >300000000 large objects in the databas= e. Please don't ask me why.
>>
>> I tried googling on thi= s, and found mentions of pg_dump being killed by oom
>> killer, bu= t I failed to find anything related to the huge large objects
>> n= umber.
>>
>> Is there any method of working around this i= ssue?
> I think this problem comes from the fact that pg_dump treats = each
> large object as it's own item. See getBlobs() which allocates a
> BlobInfo struct for each LO (and a DumpableObject if t= here are any,
> but that's just one).
>
> I assume the qu= ery (from that file):
> SELECT oid, lomacl FROM pg_largeobject_metada= ta
>
> returns 300000000 rows, which are then looped over?
&= gt;
> I ran into a similar issue a few years ago with a client using = a
> 32-bit version of pg_dump, and got it worked around by moving to<= br>> 64-bit. Did unfortunately not have time to look at the underlying> issue.
>
>



--
Sent via pgsql-admin ma= iling list (pgsql-admin@postgresql.org)
To make cha= nges to your subscription:
http://www.postgresql.org/mailpref/pgsql-ad= min


--11193365-1848993133-1380670916=:20112--