[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

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 gary.stainb...@ringways.co.uk 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

[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

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,

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

[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

[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

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;

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 gwilliamso...@yahoo.comwrote: 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

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

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 scrawf...@pinpointresearch.com To: Greg Williamson gwilliamso...@yahoo.com Cc: pgsql-admin@postgresql.org pgsql-admin@postgresql.org Sent: Thursday, September 27, 2012 3:48 PM Subject: Re: [ADMIN] Database

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

2012-09-27 Thread Tom Lane
Greg Williamson gwilliamso...@yahoo.com 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

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 t...@sss.pgh.pa.us To: Greg Williamson gwilliamso...@yahoo.com Cc: pgsql-admin@postgresql.org pgsql-admin@postgresql.org Sent: Thursday, September 27, 2012 7:14 PM Subject: Re: [ADMIN] Database size stays constant but disk space keeps

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

2012-09-27 Thread Tom Lane
Greg Williamson gwilliamso...@yahoo.com 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

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 t...@sss.pgh.pa.us To: Greg Williamson gwilliamso...@yahoo.com Cc: pgsql-admin@postgresql.org pgsql-admin@postgresql.org Sent: Thursday, September 27, 2012 7:55 PM Subject: Re: [ADMIN] Database size stays constant but disk space keeps