Subject: Re: pg_upgrade 8.3 to 9.0, shutdown is to slow
From: Bernhard Schrader <bernhard.schrader@innogames.de>
To: Bruce Momjian <bruce@momjian.us>
Cc: pgsql-admin@postgresql.org
In-Reply-To: <201101270241.p0R2f4B25076@momjian.us>
References: <201101270241.p0R2f4B25076@momjian.us>
Content-Type: text/plain; charset="UTF-8"
Organization: Innogames GmbH
Date: Thu, 27 Jan 2011 09:17:32 +0100
Message-ID: <1296116252.2220.6.camel@schrader-laptop>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

it's the same behaviour, it doesnt stop.

what maybe is also interesting, if i start the database manually i have
a huge waiting IO of 23% for a time of maybe 2-5 minutes, i think it is
because of the fragmentation of the tables. 

is the shutdown so slow because of the waiting io? but why can i stop it
as user root without problems...

i will test the command later today, and wait for the waiting io to get
finished...

Am Mittwoch, den 26.01.2011, 21:41 -0500 schrieb Bruce Momjian:
> Wow, that is odd.  Good thing you were only running in check mode.  What
> happens if you run that pg_ctl command manually?  Is
> /etc/init.d/postgresql-8.3 stop running pg_ctl or something different?
> 
> ---------------------------------------------------------------------------
> 
> Bernhard Schrader wrote:
> > Hi all,
> > 
> > I try to inplace migrate our postgresql databases from 8.3 to 9.0.
> > So far, it worked by the testdbs I set up, it was really quick and I
> > looked forward to migrate the live dbs. 
> > But here were some issues I didn't had in the test environment. 
> > 
> > If I start this command:
> > 
> > """
> > su postgres -c
> > "cd /usr/lib/postgresql/9.0/bin/; /usr/lib/postgresql/9.0/bin/pg_upgrade
> > --link --check -g -v -d /var/lib/postgresql/8.3/main/
> > -D /var/lib/postgresql/9.0/main/ -b /usr/lib/postgresql/8.3/bin/
> > -B /usr/lib/postgresql/9.0/bin/ -l /tmp/migration.log"
> > """
> > 
> > It starts the checking and all the things, at the end he trys to stop
> > the db via pg_ctl, but here seems to be the error nested:
> > 
> > """"/usr/lib/postgresql/8.3/bin/pg_ctl" -l "/tmp/migration.log" -D
> > "/var/lib/postgresql/8.3/main"  stop >> "/tmp/migration.log" 2>&1
> > waiting for server to shut down...2011-01-26 14:21:59 CET LOG:  received
> > smart shutdown request
> > .......2011-01-26 14:22:05 CET FATAL:  the database system is shutting
> > down
> > ..................................................... failed
> > pg_ctl: server does not shut down
> > 
> > There were problems executing "/usr/lib/postgresql/8.3/bin/pg_ctl" -l
> > "/tmp/migration.log" -D "/var/lib/postgresql/8.3/main"  stop >>
> > "/tmp/migration.log" 2>&1
> > "/usr/lib/postgresql/8.3/bin/pg_ctl" -l "/tmp/migration.log" -D
> > "/var/lib/postgresql/8.3/main" -m fast stop >> "/tmp/migration.log" 2>&1
> > 2011-01-26 14:22:59 CET LOG:  received fast shutdown request
> > 2011-01-26 14:22:59 CET LOG:  aborting any active transactions
> > 2011-01-26 14:22:59 CET FATAL:  terminating connection due to
> > administrator command
> > waiting for server to shut down....2011-01-26 14:22:59 CET LOG:
> > shutting down
> > 2011-01-26 14:22:59 CET LOG:  database system is shut down
> >  done
> > server stopped
> > 
> > """
> > 
> > At the end it stopped, but with returncode 1. So it is broken here... :(
> > 
> > Well, as I saw this first I thought I might forgot some db connections
> > to terminate, but all services were down. A "ps -axuf" gives me the
> > following output:
> > 
> > """
> > postgres 26253 2.8 0.3 426264 10684 pts/1 S+ 14:21
> > 0:01 /usr/lib/postgresql/8.3/bin/postgres
> > -D /var/lib/postgresql/8.3/main -
> > postgres 26255 0.0 0.0 426396 2044 ? Ss 14:21 0:00 \_ postgres: writer
> > process 
> > postgres 26257 0.0 0.0 154368 1616 ? Ss 14:21 0:00 \_ postgres: stats
> > collector process 
> > postgres 26258 0.0 0.1 427612 4188 ? Ss 14:21 0:00 \_ postgres: grepo
> > DB_NAME LOCAL_IP(PORT) idle
> > """
> > 
> > It seems that it waits for termination of its own process. With my
> > testsetup i didn't get this error, maybe because it was much faster,
> > because the used data were new and not fragmented or anything. 
> > 
> > If i try a /etc/init.d/postgresql-8.3 stop with user root it needs only
> > 5 seconds and the db is down. 
> > 
> > Well, now I am stucked at this point and can't upgrade my databases, is
> > there any way to increase the timeout? or another way to perform before
> > the upgrade to reduce the stopping and starting time? 
> > 
> > Hope you have some hints for me. 
> > 
> > greetz
> > Bernhard
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > -- 
> > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-admin
>