Re: error messages (autovaccum canceled and syntax errors) whileloading a DUMP - Mailing list pgsql-general

On 6/27/20 1:37 AM, Matthias Apitz wrote:
> El día Donnerstag, Juni 25, 2020 a las 04:25:00 -0400, Tom Lane escribió:
> 
>> Matthias Apitz <guru@unixarea.de> writes:
>>> On Thursday, 25 June 2020 21:41:54 CEST, Tom Lane <tgl@sss.pgh.pa.us>
>>> wrote:
>>>> regardless of the exact details, it seems like the most likely theory
>>>> about what is happening is that the dump file is corrupt and the
>>>> corruption is causing the de-gzipped output to be missing or
>>>> duplicating chunks of text.
>>
>>> I think, that a
>>> gzip -dc foo
>>> will work, regardless of the file names extensiom not beeing .gz
>>
>> Agreed, that gzip command won't care.  I was concerned about what
>> might've happened in between.
>>
>> ...
> 
> I restarted the server, created a new database with:
> 
> $ /usr/local/sisis-pap/pgsql/bin/createdb -U sisis -T template0 testdump
> 
> and loaded the DUMP while no other processes were connected to the
> server with:
> 
> $ gzip -dc newanna_export.dmp | /usr/local/sisis-pap/pgsql/bin/psql -U sisis -dtestdump
> 
> all went fine (table creations, COPYs into them, creating indexs)
> without any error message in the terminal. In the serverlog only these
> messages came out:
> 
> 2020-06-27 10:16:38.778 CEST [18094] LOG:  database system is ready to accept connections
> 
> 2020-06-27 10:21:26.062 CEST [18096] LOG:  checkpoints are occurring too frequently (25 seconds apart)
> 2020-06-27 10:21:26.062 CEST [18096] HINT:  Consider increasing the configuration parameter "max_wal_size".
> 2020-06-27 10:21:52.436 CEST [18096] LOG:  checkpoints are occurring too frequently (26 seconds apart)
> 2020-06-27 10:21:52.436 CEST [18096] HINT:  Consider increasing the configuration parameter "max_wal_size".
> 2020-06-27 10:22:07.424 CEST [18302] ERROR:  canceling autovacuum task
> 2020-06-27 10:22:07.424 CEST [18302] CONTEXT:  automatic analyze of table "testdump.public.d01buch"
> 2020-06-27 10:22:14.545 CEST [18302] ERROR:  canceling autovacuum task
> 2020-06-27 10:22:14.545 CEST [18302] CONTEXT:  automatic analyze of table "testdump.public.d02zus"
> 2020-06-27 10:22:16.051 CEST [18249] ERROR:  canceling autovacuum task
> 2020-06-27 10:22:16.051 CEST [18249] CONTEXT:  automatic analyze of table "testdump.public.d03geb"
> 2020-06-27 10:22:18.331 CEST [18249] ERROR:  canceling autovacuum task
> 2020-06-27 10:22:18.331 CEST [18249] CONTEXT:  automatic analyze of table "testdump.public.d11loesch"
> 2020-06-27 10:22:19.718 CEST [18302] ERROR:  canceling autovacuum task
> 2020-06-27 10:22:19.718 CEST [18302] CONTEXT:  automatic analyze of table "testdump.public.d11rueck"
> 2020-06-27 10:22:23.034 CEST [18249] ERROR:  canceling autovacuum task
> 2020-06-27 10:22:23.034 CEST [18249] CONTEXT:  automatic analyze of table "testdump.public.d35buchnotiz"
> 2020-06-27 10:22:38.902 CEST [18302] ERROR:  canceling autovacuum task
> 2020-06-27 10:22:38.902 CEST [18302] CONTEXT:  automatic analyze of table "testdump.public.idm_statistic"
> 2020-06-27 10:22:59.332 CEST [18302] ERROR:  canceling autovacuum task
> 2020-06-27 10:22:59.332 CEST [18302] CONTEXT:  automatic analyze of table "testdump.public.idm_targetlog"
> 2020-06-27 10:23:01.493 CEST [18302] ERROR:  canceling autovacuum task
> 2020-06-27 10:23:01.493 CEST [18302] CONTEXT:  automatic analyze of table "testdump.public.idm_targettab"
> 2020-06-27 10:23:02.631 CEST [18302] ERROR:  canceling autovacuum task
> 2020-06-27 10:23:02.631 CEST [18302] CONTEXT:  automatic analyze of table "testdump.public.idm_tasktab"
> 
> The messages about 'ERROR:  canceling autovacuum task' appeared while
> the indexes were created. The database has around 400 tables and the
> uncompressed dump is around 3.6 GByte:
> 
> $ gzip -dc newanna_export.dmp | wc -c
> 3643850597
> 
> So, the syntax error messages were caused by some other process (and we
> have already an idea which cron job it was). What must be done re/ the
> messages about 'ERROR:  canceling autovacuum task', or can we just
> ignore them?

 From what I understand they are occurring because the machine is to 
busy doing the restore to get to the autovacuum task in a timely manner. 
So I would say ignore and check back later to see that the autovacuum is 
working. Given that it is ANALYZE that is being cancelled I would run a 
manual ANALYZE after the restore is done to update the database statistics.

> 
> Thanks
> 
>     matthias
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com