Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eF0pa-0001lD-Rr for pgsql-admin@arkaria.postgresql.org; Wed, 15 Nov 2017 16:46:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1eF0pa-0000W3-6M for pgsql-admin@arkaria.postgresql.org; Wed, 15 Nov 2017 16:46:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eF0nf-0005ap-Ch for pgsql-admin@postgresql.org; Wed, 15 Nov 2017 16:44:31 +0000 Received: from mr28p48im-tydg10101201.me.com ([17.110.88.44]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eF0na-0001RX-UP for pgsql-admin@postgresql.org; Wed, 15 Nov 2017 16:44:30 +0000 Received: from process-dkim-sign-daemon.mr28p48im-tydg10101201.me.com by mr28p48im-tydg10101201.me.com (Oracle Communications Messaging Server 8.0.1.2.20170607 64bit (built Jun 7 2017)) id <0OZG00600WW0HG00@mr28p48im-tydg10101201.me.com> for pgsql-admin@postgresql.org; Wed, 15 Nov 2017 16:44:23 +0000 (GMT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=04042017; t=1510764263; bh=4AFi8JXL4920jF2NEbZ7vsMGfVMBbFlo6Uks2ac0J5c=; h=Content-type:MIME-version:Subject:From:Date:Message-id:To; b=LRRB+T0G0orvbYt9I1UVlzhdmAAI8dDkGgnXEMU/qtPhBy9EWEkP1QmRCBp3w3LVr fJnf9SYcPcqhHffXtqBBaz3fp5FdjvZ3qEvYijNrEXq4zx5+2sOqId7/u5Jzy6FV7M hjRogxqMxClb+RuCBwrj6gMpSp2hmNEJMYGokxBp6LAB8zzAwT7XoYbHgLRsQk3hlW HsK/vNvieBHBU3rO9SYR+Dcr5gQ8JWLrQa9xGBd+p1Lz81SEe26fZMQrm++V4nrRWa 0sNabRAI92DFrf3JIom+H2ZU22MFwxVQDsZ+J/FbtKwyPIHhmdKfG1FZRZdIPpLp2H VA1Ra6MvH4dvg== Received: from icloud.com ([127.0.0.1]) by mr28p48im-tydg10101201.me.com (Oracle Communications Messaging Server 8.0.1.2.20170607 64bit (built Jun 7 2017)) with ESMTPSA id <0OZG003S9X5XR430@mr28p48im-tydg10101201.me.com>; Wed, 15 Nov 2017 16:44:23 +0000 (GMT) X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:,, definitions=2017-11-15_08:,, signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 clxscore=1011 suspectscore=0 malwarescore=0 phishscore=0 adultscore=0 bulkscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1707230000 definitions=main-1711150225 Content-type: text/plain; charset=us-ascii MIME-version: 1.0 (Mac OS X Mail 11.1 \(3445.4.7\)) Subject: Re: Vacuum not removing dead tuples From: Rui DeSousa In-reply-to: <1510740365.2424.22.camel@cybertec.at> Date: Wed, 15 Nov 2017 11:44:20 -0500 Cc: pgsql-admin@postgresql.org Content-transfer-encoding: quoted-printable Message-id: References: <7D8ED7AF-E189-4FA2-BB6D-FC9E8A127E28@icloud.com> <1510740365.2424.22.camel@cybertec.at> To: Laurenz Albe X-Mailer: Apple Mail (2.3445.4.7) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org Thanks Laurenz,=20 A replication slot was my issue. Much appreciated for the insight. Thanks, Rui > On Nov 15, 2017, at 5:06 AM, Laurenz Albe wrot= e: >=20 > Rui DeSousa wrote: >> Is there a reason vacuum will not remove dead tuples other than the usua= l suspects? >> There are no two phase transactions and even after a restoring the datab= ase >> from binary backup with nothing connect to the restored instance; >> vacuum still does not remove the dead tuples. >=20 > There are three possibilities: >=20 > 1) Active transactions: >=20 > SELECT backend_xmin, xact_start, datname, pid, query > FROM pg_stat_activity > WHERE state <> 'idle' > ORDER BY xact_start; >=20 > 2) Prepared transactions: >=20 > SELECT xid, prepared > FROM pg_prepared_xacts; >=20 > 3) Lagging replication slots: >=20 > SELECT xmin, slot_name, active, active_pid > FROM pg_replication_slots; >=20 > The "xid" columns will tell you which tuples VACUUM can > remove: Any tuple with "xmax" less than this are fair game. >=20 > Yours, > Laurenz Albe --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin