Thread: device full ---> postgres will not start

Duffy House wrote:

> Hello:
>
> It looks like I got caught sleeping.
>
> I am running Postgresql 7.2 under Red Hat 7.3.
> Postgresql failed today.  When I tried to restart it, I got a message
> stating that the device is full.
> I used df to confirm that /var where postgres reside is full.  /var is
> on a 4 Gig device.
> Postgresql is the only application on this machine.
>
> Please note, the backup of the database from the prior day, is 45 Meg
> zipped.
> I do not know how the database could be soaking up a 4 Gig device.
>
> Questions:
>
> How can I recover from this situation?

Var contain also all log file of your box I guess.
Go on your directory data  ( on RH is on /var/lib/pgsql/data )
and do :

du -sh

if is really 4GB move your data directory in another big partition,
make a simbolink link with your old location, start postgres and
do a vacuum full of your database.

if is not really 4GB  do a log rotate of your logs.




Regards
Gaetano Mendola

Thanks for your reply to my posting.
Hope you would be willing to provide some additional assistance.
What can I says, I really do not have enough experience to bail, myself
out of this one.

i did cd /var/lib/pgsql/data then du-sh   this reported    3.6gig
This means I really have a 3.6 gig postgesql database?

**********
Var contain also all log file of your box
where would they be? can I dump old ones?  well how do I dump them?
*********
 we run vacume everyday against the database.

 thanks in advance

kd




mendola@bigfoot.com (Gaetano Mendola) wrote in message news:<3F9C5B92.4060800@bigfoot.com>...
> Duffy House wrote:
>
> > Hello:
> >
> > It looks like I got caught sleeping.
> >
> > I am running Postgresql 7.2 under Red Hat 7.3.
> > Postgresql failed today.  When I tried to restart it, I got a message
> > stating that the device is full.
> > I used df to confirm that /var where postgres reside is full.  /var is
> > on a 4 Gig device.
> > Postgresql is the only application on this machine.
> >
> > Please note, the backup of the database from the prior day, is 45 Meg
> > zipped.
> > I do not know how the database could be soaking up a 4 Gig device.
> >
> > Questions:
> >
> > How can I recover from this situation?
>
> Var contain also all log file of your box I guess.
> Go on your directory data  ( on RH is on /var/lib/pgsql/data )
> and do :
>
> du -sh
>
> if is really 4GB move your data directory in another big partition,
> make a simbolink link with your old location, start postgres and
> do a vacuum full of your database.
>
> if is not really 4GB  do a log rotate of your logs.
>
>
>
>
> Regards
> Gaetano Mendola
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

On Fri, 24 Oct 2003, Duffy House wrote:

> Hello:
>
> It looks like I got caught sleeping.
>
> I am running Postgresql 7.2 under Red Hat 7.3.
> Postgresql failed today.  When I tried to restart it, I got a message
> stating that the device is full.
> I used df to confirm that /var where postgres reside is full.  /var is on a
> 4 Gig device.
> Postgresql is the only application on this machine.
>
> Please note, the backup of the database from the prior day, is 45 Meg
> zipped.
> I do not know how the database could be soaking up a 4 Gig device.
>
> Questions:
>
> How can I recover from this situation?
>
> If postgres will not start how can I restore the database from the prior
> day?
>
> When I run a backup does postgres truncate its logs?
>
> please keep it simple, I have about 1 hours admin experience with postgres.
>
> thanks in advance.

It sounds like either you've never run vacuum, or you've had a few threads
holding transactions open for a very long time.

Easiest way to recover:  Make a bigger partition, cp everything there, and
point the postmaster at that partition to startup.  Then vacuum etc... and
copy it all back over the original partition.  Then make sure you're
running the latest and greatest (at least 7.2.4, prefereable 7.3.4) and
look up the autovacuum daemon to make sure this never happens again.  If
you installed from source, it's in the contrib/pg_autovacuum directory,
just cd in there as root, and 'make;make install' and it's installed.

i will take the advice and upgrade to 7.2.4 ASAP as in now.

this what rpm reports on the server currently:
[root@localhost root]# rpm -qa | grep postgres
postgresql-jdbc-7.2.1-5
postgresql-libs-7.2.1-5
postgresql-7.2.1-5
postgresql-server-7.2.1-5
postgresql-contrib-7.2.1-5
postgresql-devel-7.2.1-5
[root@localhost root]#

i downloaded the following:
[root@localhost download]# ls
postgresql-7.2.4-1PGDG.i386.rpm          postgresql-libs-7.2.4-1PGDG.i386.rpm
postgresql-contrib-7.2.4-1PGDG.i386.rpm  postgresql-perl-7.2.4-1PGDG.i386.rpm
postgresql-devel-7.2.4-1PGDG.i386.rpm    postgresql-server-7.2.4-1PGDG.i386.rpm
postgresql-docs-7.2.4-1PGDG.i386.rpm     postgresql-tcl-7.2.4-1PGDG.i386.rpm
postgresql-jdbc-7.2.4-1PGDG.i386.rpm
[root@localhost download]#


i have done new installs using RPMs, but not an upgrade.
silly question time:
are these the correct RPMs?
do I just upgrade them one at a time as follows:
  rpm -Uvh  postgresql.......rpm


many thanks

kd







scott.marlowe@ihs.com ("scott.marlowe") wrote in message
news:<Pine.LNX.4.33.0310270756530.14845-100000@css120.ihs.com>...
> On Fri, 24 Oct 2003, Duffy House wrote:
>
> > Hello:
> >
> > It looks like I got caught sleeping.
> >
> > I am running Postgresql 7.2 under Red Hat 7.3.
> > Postgresql failed today.  When I tried to restart it, I got a message
> > stating that the device is full.
> > I used df to confirm that /var where postgres reside is full.  /var is on a
> > 4 Gig device.
> > Postgresql is the only application on this machine.
> >
> > Please note, the backup of the database from the prior day, is 45 Meg
> > zipped.
> > I do not know how the database could be soaking up a 4 Gig device.
> >
> > Questions:
> >
> > How can I recover from this situation?
> >
> > If postgres will not start how can I restore the database from the prior
> > day?
> >
> > When I run a backup does postgres truncate its logs?
> >
> > please keep it simple, I have about 1 hours admin experience with postgres.
> >
> > thanks in advance.
>
> It sounds like either you've never run vacuum, or you've had a few threads
> holding transactions open for a very long time.
>
> Easiest way to recover:  Make a bigger partition, cp everything there, and
> point the postmaster at that partition to startup.  Then vacuum etc... and
> copy it all back over the original partition.  Then make sure you're
> running the latest and greatest (at least 7.2.4, prefereable 7.3.4) and
> look up the autovacuum daemon to make sure this never happens again.  If
> you installed from source, it's in the contrib/pg_autovacuum directory,
> just cd in there as root, and 'make;make install' and it's installed.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

Assuming you'll be upgrading from 7.2 to 7.2.4, then yes, all you need to
do is upgrage the rpms.  you can do tham all at once, assuming they're in
the same directory and named postgresql-something or other:

rpm -Uvh postgresql*

should do it.

If you want to upgrade to 7.3.4, then you'll need to do a pg_dumpall first
and save the output of that file like so:

pg_dumpall >mydata.sql

so you can restore it.  Then, after installing 7.3.4, you'll need to set
up a data directory and restore there.  If you've got the space, do that
somewhere other than the old 7.2.x data directory, as you may have some
issues and need to go back to 7.2.x and tweak the backup procedure etc...

On 27 Oct 2003, kbd wrote:

> i will take the advice and upgrade to 7.2.4 ASAP as in now.
>
> this what rpm reports on the server currently:
> [root@localhost root]# rpm -qa | grep postgres
> postgresql-jdbc-7.2.1-5
> postgresql-libs-7.2.1-5
> postgresql-7.2.1-5
> postgresql-server-7.2.1-5
> postgresql-contrib-7.2.1-5
> postgresql-devel-7.2.1-5
> [root@localhost root]#
>
> i downloaded the following:
> [root@localhost download]# ls
> postgresql-7.2.4-1PGDG.i386.rpm          postgresql-libs-7.2.4-1PGDG.i386.rpm
> postgresql-contrib-7.2.4-1PGDG.i386.rpm  postgresql-perl-7.2.4-1PGDG.i386.rpm
> postgresql-devel-7.2.4-1PGDG.i386.rpm    postgresql-server-7.2.4-1PGDG.i386.rpm
> postgresql-docs-7.2.4-1PGDG.i386.rpm     postgresql-tcl-7.2.4-1PGDG.i386.rpm
> postgresql-jdbc-7.2.4-1PGDG.i386.rpm
> [root@localhost download]#
>
>
> i have done new installs using RPMs, but not an upgrade.
> silly question time:
> are these the correct RPMs?
> do I just upgrade them one at a time as follows:
>   rpm -Uvh  postgresql.......rpm
>
>
> many thanks
>
> kd
>
>
>
>
>
>
>
> scott.marlowe@ihs.com ("scott.marlowe") wrote in message
news:<Pine.LNX.4.33.0310270756530.14845-100000@css120.ihs.com>...
> > On Fri, 24 Oct 2003, Duffy House wrote:
> >
> > > Hello:
> > >
> > > It looks like I got caught sleeping.
> > >
> > > I am running Postgresql 7.2 under Red Hat 7.3.
> > > Postgresql failed today.  When I tried to restart it, I got a message
> > > stating that the device is full.
> > > I used df to confirm that /var where postgres reside is full.  /var is on a
> > > 4 Gig device.
> > > Postgresql is the only application on this machine.
> > >
> > > Please note, the backup of the database from the prior day, is 45 Meg
> > > zipped.
> > > I do not know how the database could be soaking up a 4 Gig device.
> > >
> > > Questions:
> > >
> > > How can I recover from this situation?
> > >
> > > If postgres will not start how can I restore the database from the prior
> > > day?
> > >
> > > When I run a backup does postgres truncate its logs?
> > >
> > > please keep it simple, I have about 1 hours admin experience with postgres.
> > >
> > > thanks in advance.
> >
> > It sounds like either you've never run vacuum, or you've had a few threads
> > holding transactions open for a very long time.
> >
> > Easiest way to recover:  Make a bigger partition, cp everything there, and
> > point the postmaster at that partition to startup.  Then vacuum etc... and
> > copy it all back over the original partition.  Then make sure you're
> > running the latest and greatest (at least 7.2.4, prefereable 7.3.4) and
> > look up the autovacuum daemon to make sure this never happens again.  If
> > you installed from source, it's in the contrib/pg_autovacuum directory,
> > just cd in there as root, and 'make;make install' and it's installed.
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> >       subscribe-nomail command to majordomo@postgresql.org so that your
> >       message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>

A quick note, anytime you're gonna upgrade, you need to first shut down
the postmaster...

On Mon, 27 Oct 2003, scott.marlowe wrote:

> Assuming you'll be upgrading from 7.2 to 7.2.4, then yes, all you need to
> do is upgrage the rpms.  you can do tham all at once, assuming they're in
> the same directory and named postgresql-something or other:
>
> rpm -Uvh postgresql*
>
> should do it.
>
> If you want to upgrade to 7.3.4, then you'll need to do a pg_dumpall first
> and save the output of that file like so:
>
> pg_dumpall >mydata.sql
>
> so you can restore it.  Then, after installing 7.3.4, you'll need to set
> up a data directory and restore there.  If you've got the space, do that
> somewhere other than the old 7.2.x data directory, as you may have some
> issues and need to go back to 7.2.x and tweak the backup procedure etc...
>
> On 27 Oct 2003, kbd wrote:
>
> > i will take the advice and upgrade to 7.2.4 ASAP as in now.
> >
> > this what rpm reports on the server currently:
> > [root@localhost root]# rpm -qa | grep postgres
> > postgresql-jdbc-7.2.1-5
> > postgresql-libs-7.2.1-5
> > postgresql-7.2.1-5
> > postgresql-server-7.2.1-5
> > postgresql-contrib-7.2.1-5
> > postgresql-devel-7.2.1-5
> > [root@localhost root]#
> >
> > i downloaded the following:
> > [root@localhost download]# ls
> > postgresql-7.2.4-1PGDG.i386.rpm          postgresql-libs-7.2.4-1PGDG.i386.rpm
> > postgresql-contrib-7.2.4-1PGDG.i386.rpm  postgresql-perl-7.2.4-1PGDG.i386.rpm
> > postgresql-devel-7.2.4-1PGDG.i386.rpm    postgresql-server-7.2.4-1PGDG.i386.rpm
> > postgresql-docs-7.2.4-1PGDG.i386.rpm     postgresql-tcl-7.2.4-1PGDG.i386.rpm
> > postgresql-jdbc-7.2.4-1PGDG.i386.rpm
> > [root@localhost download]#
> >
> >
> > i have done new installs using RPMs, but not an upgrade.
> > silly question time:
> > are these the correct RPMs?
> > do I just upgrade them one at a time as follows:
> >   rpm -Uvh  postgresql.......rpm
> >
> >
> > many thanks
> >
> > kd
> >
> >
> >
> >
> >
> >
> >
> > scott.marlowe@ihs.com ("scott.marlowe") wrote in message
news:<Pine.LNX.4.33.0310270756530.14845-100000@css120.ihs.com>...
> > > On Fri, 24 Oct 2003, Duffy House wrote:
> > >
> > > > Hello:
> > > >
> > > > It looks like I got caught sleeping.
> > > >
> > > > I am running Postgresql 7.2 under Red Hat 7.3.
> > > > Postgresql failed today.  When I tried to restart it, I got a message
> > > > stating that the device is full.
> > > > I used df to confirm that /var where postgres reside is full.  /var is on a
> > > > 4 Gig device.
> > > > Postgresql is the only application on this machine.
> > > >
> > > > Please note, the backup of the database from the prior day, is 45 Meg
> > > > zipped.
> > > > I do not know how the database could be soaking up a 4 Gig device.
> > > >
> > > > Questions:
> > > >
> > > > How can I recover from this situation?
> > > >
> > > > If postgres will not start how can I restore the database from the prior
> > > > day?
> > > >
> > > > When I run a backup does postgres truncate its logs?
> > > >
> > > > please keep it simple, I have about 1 hours admin experience with postgres.
> > > >
> > > > thanks in advance.
> > >
> > > It sounds like either you've never run vacuum, or you've had a few threads
> > > holding transactions open for a very long time.
> > >
> > > Easiest way to recover:  Make a bigger partition, cp everything there, and
> > > point the postmaster at that partition to startup.  Then vacuum etc... and
> > > copy it all back over the original partition.  Then make sure you're
> > > running the latest and greatest (at least 7.2.4, prefereable 7.3.4) and
> > > look up the autovacuum daemon to make sure this never happens again.  If
> > > you installed from source, it's in the contrib/pg_autovacuum directory,
> > > just cd in there as root, and 'make;make install' and it's installed.
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > >       subscribe-nomail command to majordomo@postgresql.org so that your
> > >       message can get through to the mailing list cleanly
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>