Thanks Laurenz, A replication slot was my issue. Much appreciated for the insight. Thanks, Rui > On Nov 15, 2017, at 5:06 AM, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote: > > Rui DeSousa wrote: >> Is there a reason vacuum will not remove dead tuples other than the usual suspects? >> There are no two phase transactions and even after a restoring the database >> from binary backup with nothing connect to the restored instance; >> vacuum still does not remove the dead tuples. > > There are three possibilities: > > 1) Active transactions: > > SELECT backend_xmin, xact_start, datname, pid, query > FROM pg_stat_activity > WHERE state <> 'idle' > ORDER BY xact_start; > > 2) Prepared transactions: > > SELECT xid, prepared > FROM pg_prepared_xacts; > > 3) Lagging replication slots: > > SELECT xmin, slot_name, active, active_pid > FROM pg_replication_slots; > > The "xid" columns will tell you which tuples VACUUM can > remove: Any tuple with "xmax" less than this are fair game. > > Yours, > Laurenz Albe -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
