Re: [GENERAL] Where does vacuum FULL write temp-files?

[Andreas Joseph Krogh]

På onsdag 15. april 2015 kl. 04:34:31, skrev Venkata Balaji N mailto:nag1...@gmail.com>>:   I'm planning to vacuum FULL a pg_largeobject 
relation (after vacuumlo'ing it). The relation is 300GB large so I'm concerned 
the operation will write full my pg_xlog directory which is on a 200GB (net) 
RAID1 SSD.   Where does vacuum FULL rewrite to, does it use pg_xlog or some 
other directory?   Which version of PostgreSQL is this ?
   If i got your question correctly, VACUUM FULL would rewrite the data to a 
new data file associated with that particular relation (Table) in the 
"$PGDATA/base" directory. This needs an extra disk space at the OS level (this 
is not related to pg_xlog directory).

 As VACUUMING is a data change operation, "pg_xlog" will also have only the 
WAL data (modifications) written at the time of VACUUMING.

http://www.postgresql.org/docs/9.4/static/sql-vacuum.html 
   This is PG-9.3   
So I understand that VACUUM FULL writes the new table to the same tablespace as 
the original table (also for system-catalogs like pg_largeobject), and doesn't 
use any temp-space outside the location of that tablespace?   Thanks.   -- 
Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 
andr...@visena.com  www.visena.com 
    

Re: [GENERAL] How to keep pg_largeobject from growing endlessly

[Andreas Joseph Krogh]

På onsdag 15. april 2015 kl. 04:43:47, skrev Venkata Balaji N mailto:nag1...@gmail.com>>:   I'm routinely vacuumlo'ing to reap orphan OIDs. 
Is it necessary to manually vacuum pg_largobject or is it handled by autovacuum?
    It is handled by autovacuum. What we do is, we schedule a manual VACUUM 
ANALYZE nightly job on bigger tables to avoid burden on the autovacuum during 
the business time.     In a system where large objects are constantly added 
(and *some* rarely deleted, so it grows every day), would I gain space (freed 
to the OS) by VACUUM FULL it?      The amount of reclaimed space will depend on 
the volume of deletions happening. If the DELETES are rare and are not deleting 
much, then frequent VACUUM FULL is not ideal.   In other words: Does vacuumlo 
cause diskspace used by pg_largeobject to be freed to the OS (after eventually 
vacuumed by autovacuum)?   Thanks.   -- Andreas Joseph Krogh CTO / Partner - 
Visena AS Mobile: +47 909 56 963 andr...@visena.com  
www.visena.com     

Re: [GENERAL] How to keep pg_largeobject from growing endlessly

[Adam Hooper]

On Wed, Apr 15, 2015 at 4:49 AM, Andreas Joseph Krogh
 wrote:
>
>
> In other words: Does vacuumlo cause diskspace used by pg_largeobject to be 
> freed to the OS (after eventually vacuumed by autovacuum)?

No.

But that shouldn't matter in your scenario: if you create more large
objects than you delete, you aren't wasting space anyway.

A longer-term problem that may apply in your scenario: pg_largeobject
can't grow beyond your tablespace's disk size. Unlike other tables,
it's very hard to move pg_largeobject to a new database/tablespace
without downtime. If your table is constantly growing and you're
worrying about how much space it's taking, other storage strategies
(bytea, S3, NFS, etc) might inspire more confidence. I had this
problem a few months ago; since then, I only use pg_largeobject in
prototyping and low-growth situations.
http://www.postgresql.org/message-id/camwjz6gf9tm+vwm_0ymqypi4xk_bv2nyaremwr1ecsqbs40...@mail.gmail.com

Enjoy life,
Adam

-- 
Adam Hooper
+1-613-986-3339
http://adamhooper.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] How to keep pg_largeobject from growing endlessly

[Andreas Joseph Krogh]

På onsdag 15. april 2015 kl. 15:50:36, skrev Adam Hooper mailto:a...@adamhooper.com>>: On Wed, Apr 15, 2015 at 4:49 AM, Andreas Joseph 
Krogh
  wrote:
 >
 >
 > In other words: Does vacuumlo cause diskspace used by pg_largeobject to be 
freed to the OS (after eventually vacuumed by autovacuum)?

 No.

 But that shouldn't matter in your scenario: if you create more large
 objects than you delete, you aren't wasting space anyway.   Ok. Out of 
curiousity; When does it get freed, when VACUUM FULL'ed?   A longer-term 
problem that may apply in your scenario: pg_largeobject
 can't grow beyond your tablespace's disk size. Unlike other tables,
 it's very hard to move pg_largeobject to a new database/tablespace
 without downtime. If your table is constantly growing and you're
 worrying about how much space it's taking, other storage strategies
 (bytea, S3, NFS, etc) might inspire more confidence. I had this
 problem a few months ago; since then, I only use pg_largeobject in
 prototyping and low-growth situations.
 
http://www.postgresql.org/message-id/camwjz6gf9tm+vwm_0ymqypi4xk_bv2nyaremwr1ecsqbs40...@mail.gmail.com
  I'm aware of this but I haven't found an alternate solution which provides 
streaming of large BLOBs and TX-safety.   -- Andreas Joseph Krogh CTO / Partner 
- Visena AS Mobile: +47 909 56 963 andr...@visena.com 
 www.visena.com   
  

Re: [GENERAL] How to keep pg_largeobject from growing endlessly

[Adam Hooper]

On Wed, Apr 15, 2015 at 9:57 AM, Andreas Joseph Krogh
 wrote:
>
> På onsdag 15. april 2015 kl. 15:50:36, skrev Adam Hooper 
> :
>
> On Wed, Apr 15, 2015 at 4:49 AM, Andreas Joseph Krogh
>  wrote:
> >
> > In other words: Does vacuumlo cause diskspace used by pg_largeobject to be 
> > freed to the OS (after eventually vacuumed by autovacuum)?
>
> No.
>
> Ok. Out of curiousity; When does it get freed, when VACUUM FULL'ed?

Yes. VACUUM FULL or CLUSTER will free the space. (Of course, you need
a lot of free disk space to perform those operations.)

Enjoy life,
Adam

-- 
Adam Hooper
+1-613-986-3339
http://adamhooper.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] Help with slow table update

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Pawel Veselov
Sent: Tuesday, April 14, 2015 8:01 PM
To: Jim Nasby
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Help with slow table update

[skipped]

This is where using sets becomes really tedious, as Postgres severely lacks an 
upsert-like statement.
I don't think there are joins allowed in UPDATE statement, so I will need to 
use WITH query, right?
Also, I'm not sure how LEFT JOIN will help me isolate and insert "missed" 
entries...

Would it be OK to replace upsert part with merging into a temp table, then 
deleting and inserting from temp table? Is there any penalty for insert/delete 
comparing to update?

[skipped]

Yes, you can do UPDATE with joins 
(http://www.postgresql.org/docs/9.4/static/sql-update.html) like this:

UPDATE table1 A SET col1 = B.col2
  FROM table2 B
  WHERE A.col3 = B.col4;

Regards,
Igor Neyman

Re: [GENERAL] How to keep pg_largeobject from growing endlessly

[Andreas Joseph Krogh]

På onsdag 15. april 2015 kl. 16:05:22, skrev Adam Hooper mailto:a...@adamhooper.com>>: On Wed, Apr 15, 2015 at 9:57 AM, Andreas Joseph 
Krogh
  wrote:
 >
 > På onsdag 15. april 2015 kl. 15:50:36, skrev Adam Hooper 
:
 >
 > On Wed, Apr 15, 2015 at 4:49 AM, Andreas Joseph Krogh
 >  wrote:
 > >
 > > In other words: Does vacuumlo cause diskspace used by pg_largeobject to 
be freed to the OS (after eventually vacuumed by autovacuum)?
 >
 > No.
 >
 > Ok. Out of curiousity; When does it get freed, when VACUUM FULL'ed?

 Yes. VACUUM FULL or CLUSTER will free the space. (Of course, you need
 a lot of free disk space to perform those operations.)   I'm sure there's a 
good reason for why VACUUM FULL needs to rewreite the whole table and cannot 
"just free the unused space to the OS".   -- Andreas Joseph Krogh CTO / Partner 
- Visena AS Mobile: +47 909 56 963 andr...@visena.com 
 www.visena.com   
  

Re: [GENERAL] Help with slow table update

[Pawel Veselov]

>
> [skipped]
>
>
>
> This is where using sets becomes really tedious, as Postgres severely
> lacks an upsert-like statement.
>
> I don't think there are joins allowed in UPDATE statement, so I will need
> to use WITH query, right?
>
> Also, I'm not sure how LEFT JOIN will help me isolate and insert "missed"
> entries...
>
>
>
>   [skipped]
>
>
>
> Yes, you can do UPDATE with joins (
> http://www.postgresql.org/docs/9.4/static/sql-update.html) like this:
>
>
>
> UPDATE table1 A SET col1 = B.col2
>
>   FROM table2 B
>
>   WHERE A.col3 = B.col4;
>


I meant using JOIN operator in the update. But it's still possible, though
through WITH query.

[GENERAL] Best way to migrate a 200 GB database from PG 2.7 to 3.6

[Filip Lyncker]

Dear List,

I need to migrate my database from a 2.x to 3.x. Usually Im using 
pg_basebackup , but this is not possible with different versions.
Pg_dump seems to be an option but it is slow like hell and I dont want 
to stay offline all the time.

Is there another possibility to migrate  a database with 200 GB ?

Thanks a lot,

cheers

Filip


--
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] Best way to migrate a 200 GB database from PG 2.7 to 3.6

[Raymond O'Donnell]

On 15/04/2015 20:03, Filip Lyncker wrote:
> Dear List,
> 
> I need to migrate my database from a 2.x to 3.x. Usually Im using
> pg_basebackup , but this is not possible with different versions.
> Pg_dump seems to be an option but it is slow like hell and I dont want
> to stay offline all the time.
> Is there another possibility to migrate  a database with 200 GB ?

Those version numbers aren't correct... current PG major versions run
from 9.0 to 9.4. I don't think there was ever any version less than 6
(though I could be wrong).

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


-- 
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] Best way to migrate a 200 GB database from PG 2.7 to 3.6

[Igor Neyman]

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Filip Lyncker
Sent: Wednesday, April 15, 2015 3:03 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Best way to migrate a 200 GB database from PG 2.7 to 3.6

Dear List,

I need to migrate my database from a 2.x to 3.x. Usually Im using pg_basebackup 
, but this is not possible with different versions.
Pg_dump seems to be an option but it is slow like hell and I dont want to stay 
offline all the time.
Is there another possibility to migrate  a database with 200 GB ?

Thanks a lot,

cheers

Filip

---

PG 2.7 to 3.6???

Anyway, did you look at pg_upgrade?

Regards,
Igor Neyman






-- 
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] Best way to migrate a 200 GB database from PG 2.7 to 3.6

[Andy Colson]

On 4/15/2015 2:03 PM, Filip Lyncker wrote:

Dear List,

I need to migrate my database from a 2.x to 3.x. Usually Im using
pg_basebackup , but this is not possible with different versions.
Pg_dump seems to be an option but it is slow like hell and I dont want
to stay offline all the time.
Is there another possibility to migrate  a database with 200 GB ?

Thanks a lot,

cheers

Filip




Postgresql is on version 9.  What do you mean version 2 or 3?

Maybe use replication?

http://www.slony.info/documentation/1.2/versionupgrade.html

Or something similar?

-Andy


--
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] Best way to migrate a 200 GB database from PG 2.7 to 3.6

[Joshua D. Drake]

On 04/15/2015 12:14 PM, Andy Colson wrote:


Postgresql is on version 9.  What do you mean version 2 or 3?


He probably means 9.2.7 to 9.3.6. Remember to a lot of people 9 means 9.

That said, pg_upgrade is the way to do this as long as you can have an 
outage.


JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


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

[GENERAL] Error in the connection to the server

[Ravi Kiran]

Hi,

I have installed postgresql-9.4.0 version.

I have started the server from eclipse indigo version using Run
configurations.

There is a table in my database whose name is "b". whenever I give a query
which is related to this table I get the error *"The connection to the
server was lost. Attempting reset: Failed." *Even for the drop command it
is giving the same error.
But it is working fine for the rest of the queries which are not related to
the table b.
Please help me in resolving this error.

Thank you.

-- 
Regards,

K.Ravikiran

Re: [GENERAL] Error in the connection to the server

[Adrian Klaver]

On 04/15/2015 01:35 PM, Ravi Kiran wrote:

Hi,

I have installed postgresql-9.4.0 version.

I have started the server from eclipse indigo version using Run
configurations.


Have no idea what that means.

Some detail on exactly how you are making the connection would be 
helpful including:


1) Is the client on the same machine as the server?

2) What OS or OSes are we talking about?

3) What is the client using for connecting, though I going to assume 
JDBC, correct?


4) What is the connection string?





There is a table in my database whose name is "b". whenever I give a
query which is related to this table I get the error *"The connection to
the server was lost. Attempting reset: Failed." *Even for the drop
command it is giving the same error.


So what happens if you psql to connect to the database and run the query?

Is there anything in the Postgres logs around the connection drop that 
relates?




But it is working fine for the rest of the queries which are not related
to the table b.
Please help me in resolving this error.

Thank you.

--
Regards,

K.Ravikiran




--
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] Error in the connection to the server

[PT]

On Thu, 16 Apr 2015 02:05:34 +0530
Ravi Kiran  wrote:
> 
> I have installed postgresql-9.4.0 version.
> 
> I have started the server from eclipse indigo version using Run
> configurations.
> 
> There is a table in my database whose name is "b". whenever I give a query
> which is related to this table I get the error *"The connection to the
> server was lost. Attempting reset: Failed." *Even for the drop command it
> is giving the same error.
> But it is working fine for the rest of the queries which are not related to
> the table b.
> Please help me in resolving this error.

Find the postgresql.conf, read through it to understand where
PostgreSQL is logging, then go look at the PostgreSQL logs.
There's a 90% chance that there will be a detailed message in
the logs that will help you quickly resolve the problem. If not,
you can increase the log verbosity.


-- 
PT 


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