Re: Alter table never finishes

Giuseppe Broccolo <giuseppe.broccolo@xxxxxxxxxxxxxx> · Tue, 30 Jul 2013 17:56:16 +0200



      How can I force disconnect all clients to let me alter that table?.

Regards,

    
    There are two ways: the first is based
        on pg_terminate_backend() function and 'pg_stat_activity'
        catalog to kill idle processes.

    So in a psql session type (tried on PostgreSQL 8.4):

    
    ==# SELECT procpid, (SELECT pg_terminate_backend(procid)) AS killed
    from pg_stat_activity WHERE current_query LIKE '<IDLE>';

    
    A more heavy handed approach then should be used on terminal,
    forcing kill of idle processes using their pid:

    
    :$ for x in `ps -ef | grep -e "postgres.*idle" | awk '{print $2}'`;
    do kill -9 $x; done

    
    Hope it can help.

    
    Giuseppe.
    -- 
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@xxxxxxxxxxxxxx | www.2ndQuadrant.it