Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1

2012-09-27 Thread Greg Williamson
Tom -- - Original Message - > From: Tom Lane > To: Greg Williamson > Cc: "pgsql-admin@postgresql.org" > Sent: Thursday, September 27, 2012 7:55 PM > Subject: Re: [ADMIN] Database size stays constant but disk space keeps > shrinking -- postgres 9.1 > >G reg Williamson writes:

Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1

2012-09-27 Thread Tom Lane
Greg Williamson writes: >>> postgres 2540 postgres 50u REG8,3 409600 >>> 93429 /var/lib/postgresql/9.1/main/base/2789200/11816 (deleted) >>> postgres 2540 postgres 51u REG8,3 18112512 >>> 49694570 /var/lib/postgresql/9.1/main/base/278

Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1

2012-09-27 Thread Greg Williamson
Tom -- - Original Message - > From: Tom Lane > To: Greg Williamson > Cc: "pgsql-admin@postgresql.org" > Sent: Thursday, September 27, 2012 7:14 PM > Subject: Re: [ADMIN] Database size stays constant but disk space keeps > shrinking -- postgres 9.1 > >G reg Williamson writes: >>> Hav

Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1

2012-09-27 Thread Tom Lane
Greg Williamson writes: >> Have you checked to see if there are any processes that have open handles to >> deleted files (lsof -X | grep deleted). > lsof -X | grep deleted | wc -l > shows: 835 such files. > A couple: > postgres 2540 postgres 50u REG8,3 409600

Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1

2012-09-27 Thread Greg Williamson
Steve (and others who replied): - Original Message - > From: Steve Crawford > To: Greg Williamson > Cc: "pgsql-admin@postgresql.org" > Sent: Thursday, September 27, 2012 3:48 PM > Subject: Re: [ADMIN] Database size stays constant but disk space keeps > shrinking -- postgres 9.1 > > O

Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1

2012-09-27 Thread Dinesh Bhandary
You might also want to check to see if temp files are growing out of bounds. Thanks. Dinesh On 9/27/2012 3:05 PM, Greg Williamson wrote: Dear list, I have a postgres database, 9.1.3, which shows a fairly constant amount of space used by postgres, but total disk space kees shrinking. If I resta

Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1

2012-09-27 Thread Micky Gough
Hi Greg, On Fri, Sep 28, 2012 at 8:05 AM, Greg Williamson wrote: > > I have a postgres database, 9.1.3, which shows a fairly constant amount of > space used by postgres, but total disk space kees shrinking. > If I restart postgres the space on my file system returns. > It sounds like (maybe?) Po

Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1

2012-09-27 Thread Steve Crawford
On 09/27/2012 03:05 PM, Greg Williamson wrote: Dear list, I have a postgres database, 9.1.3, which shows a fairly constant amount of space used by postgres, but total disk space kees shrinking. If I restart postgres the space on my file system returns. This cluster is replicated to another; th

[ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1

2012-09-27 Thread Greg Williamson
Dear list, I have a postgres database, 9.1.3, which shows a fairly constant amount of space used by postgres, but total disk space kees shrinking. If I restart postgres the space on my file system returns. This cluster is replicated to another; that custer does not show this problem. There are

[ADMIN] replication question - connections on slave

2012-09-27 Thread Aras Targaryen
Hi I have a replicated slave copy of my database. Our 6 year old - non modular web app does not have the connection selector for our PHP scripts, so PHP opens SQL queries to the default (last connection) made by the scripts. So if i open a connection to the slave somewhere, it would become the lat

Re: [ADMIN] error during pg_dump

2012-09-27 Thread Spike Grobstein
SOLVED. So it turns out that you can't access unlogged tables on the replica: exchange_prod=# select count(1) from office_imports; ERROR: cannot access temporary or unlogged relations during recovery The solution was to add the --no-unlogged-table-data option to pg_dump and it

Re: [ADMIN] error during pg_dump

2012-09-27 Thread Spike Grobstein
btw, I just realized... this table that it's failing on is unlogged. I'm beginning to do some googling based around that tidbit of information, but I wanted to get that clarification on here sooner rather than later. Thanks! ...spike On Sep 27, 2012, at 1:29 PM, Spike Grobstein wrote: > Hi,

[ADMIN] error during pg_dump

2012-09-27 Thread Spike Grobstein
Hi, I'm running into an issue with our backup process using pg_dump on our replica that I just noticed. When running pg_dump, I get the following error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: could not open file "base/3273817/4515672": No such f

Re: [ADMIN] alter table alter column to resize a varchar

2012-09-27 Thread Craig James
On Thu, Sep 27, 2012 at 2:23 AM, Gary Stainburn wrote: > Hi folks. > > I'm planning on extending a field in one of my main tables using: > > alter table stock alter column type varchar(255); Why not just do alter table stock alter column type text; That is, do you really need a limit? The

[ADMIN] alter table alter column to resize a varchar

2012-09-27 Thread Gary Stainburn
Hi folks. I'm planning on extending a field in one of my main tables using: alter table stock alter column type varchar(255); to extend it from the current varchar(80). My concern is that this table and this field is used in a number of views, and views of views. Are then any gotchas that I n