Re: pg_ctl stop failure

Thanks for this - to be honest I didn't check but if I look at it now I get some transactions with

| 2013-05-17 15:08:25.973161+00 | <IDLE> in transaction
| 2013-05-17 15:08:25.745154+00 | <IDLE> in transaction
| 2013-05-17 14:58:24.066386+00 | <IDLE> in transaction
| 2013-05-17 14:58:24.224678+00 | <IDLE> in transaction

Which I suppose I could kill using pg_terminate_backend() although if they are rollbacked anyway?

When I do pg_ctl start does PostgreSQL attempt to re-write them (from the WAL logs?) or as I suspect are they gone for good?

Austen

-----Original Message-----
From: Amit Langote [mailto:amitlangote09(at)gmail(dot)com]
Sent: 17 May 2013 16:00
To: Birchall, Austen
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] pg_ctl stop failure

On Fri, May 17, 2013 at 10:47 PM, Birchall, Austen <austen(dot)birchall(at)metoffice(dot)gov(dot)uk> wrote:
> 8.4.13 & PostGIS 2.0 on Red Hat 6
>
> I want to do a offline backup of /var/lib/pgsql/data
>
> But whenever I do
>
> Pg_ctl stop
>
> I get
>
> -bash-4.1$ pg_ctl stop
> waiting for server to shut
> down...............................................................
> failed
> pg_ctl: server does not shut down
>

This means you have clients connected to the database and server won't shut down until all the clients have disconnected.

> 1. If I do
>
> pg_ctl stop -m f
>
> the cluster and databases shutdown as required but is this a clean
> and/or consistent shutdown of the cluster in the sense that I could
> recover from the OS
>

"-m f " mode disconnects those clients for you. pg_ctl Documentation at http://www.postgresql.org/docs/8.4/static/app-pg-ctl.html
says:
"Fast" mode does not wait for clients to disconnect and will terminate an online backup in progress. All active transactions are rolled back and clients are forcibly disconnected, then the server is shut down.

So what you have is a consistent shut down with incomplete transactions rolled back. By the way, could you see any active connections while you tried stopping the server?

--
Amit Langote