Adrian,
All the logs posted were from syslog (that’s were postgres writes its log on our Ubuntu install)
>> 1) I got 7 transactions back in single user mode
>> Aug 7 23:40:57 p2 postgres[30376]: [5-1] 2016-08-07 23:40:57 CEST WARNING: database "public" must be vacuumed within 999893 transactions
> So the above is from when you enter single user mode?
Yes
>> Aug 7 23:40:57 p2 postgres[30376]: [5-2] 2016-08-07 23:40:57 CEST HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
>>
>I am not seeing what you do in single user mode?
I ran
SELECT age,array_agg(table_name) FROM (SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') order by 2 desc limit 1000) tt group by age order by age desc
And then one VACUUM per table listed until I got enough transactions back
I ended up letting VACUUM run as long as needed in single-user mode (that took another 24h, in addition to the first 30h) and got the max age down to 147M which is great.
In the end, I was probably trying to optimize restoring service too much.
Anyway, thanks for the help and suggestions. The responses were super important to deal with the disk usage and making sure interrupting the vacuum wouldn’t cause any problems !
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
