Re: erro na gravação dos archives e também para finalizar o pg_basebackup

Em qua, 24 de jul de 2019 às 17:05, Flavio Henrique Araque Gurgel <
fhagur(at)gmail(dot)com> escreveu:

> Seu archive_command está falhando. Olhe qual o comando que está lá, por
> exemplo, executando "show archive_command;" e veja o motivo da falha, pode
> ser erro de digitação, disco cheio no destino, comando não instalado,
> mudança de nome de diretório, etc.
>
>
O archive command está configurado da seguinte forma:
archive_command = 'cp /var/lib/pgsql/9.4/data/%p /var/lib/pgsql/9.4/data/
backup/pitr/wall/%f'

> Desculpe o top post, respondendo do celular.
> []s
> Flavio Gurgel
>
> Em qua, 24 de jul de 2019 21:33, Mauricio Tavares <mfx1975(at)gmail(dot)com>
> escreveu:
>
>> Pessoal, saudações
>>
>> Está acontecendo um erro na geração dos archives que já estou a um bom
>> tempo tentando resolver, e não encontrei até o momento na internet algum
>> artigo que solucione tal problema.
>>
>> Bem, vamos lá....
>>
>> No ser servidor, começou a aparecer este erro no serviço do postgres:
>> postgres 11473 0.0 0.0 194936 1804 ? Ss Jul18 0:13
>> postgres: archiver process failed on
>> 0000000100000148000000A8.00000028.backup
>>
>> No log é apresentada a seguinte informação:
>>
>> 2019-07-24 16:17:08 -03 [11469]: [3601] user=,db=,app=,host=,
>> SQL=00000LOG: checkpoint starting: time
>> 2019-07-24 16:17:12 -03 [11469]: [3602] user=,db=,app=,host=,
>> SQL=00000LOG: checkpoint complete: wrote 45 buffers (0.1%); 0 transaction
>> log file(s) added, 0 removed, 0 recycled; write=4.433 s, sync=0.096 s,
>> total=4.536 s; sync files=29, longest=0.016 s, average=0.003 s
>> cp: cannot stat
>> ‘/var/lib/pgsql/9.4/data/pg_xlog/0000000100000148000000A8.00000028.backup’:
>> No such file or directory
>> 2019-07-24 16:17:48 -03 [11473]: [60880] user=,db=,app=,host=,
>> SQL=00000LOG: archive command failed with exit code 1
>> 2019-07-24 16:17:48 -03 [11473]: [60881] user=,db=,app=,host=,
>> SQL=00000DETAIL: The failed archive command was: cp
>> /var/lib/pgsql/9.4/data/pg_xlog/0000000100000148000000A8.00000028.backup
>> /var/lib/pgsql/9.4/data/backup/pitr/wall/0000000100000148000000A8.00000028.backup
>> cp: cannot stat
>> ‘/var/lib/pgsql/9.4/data/pg_xlog/0000000100000148000000A8.00000028.backup’:
>> No such file or directory
>> 2019-07-24 16:17:49 -03 [11473]: [60882] user=,db=,app=,host=,
>> SQL=00000LOG: archive command failed with exit code 1
>> 2019-07-24 16:17:49 -03 [11473]: [60883] user=,db=,app=,host=,
>> SQL=00000DETAIL: The failed archive command was: cp
>> /var/lib/pgsql/9.4/data/pg_xlog/0000000100000148000000A8.00000028.backup
>> /var/lib/pgsql/9.4/data/backup/pitr/wall/0000000100000148000000A8.00000028.backup
>> cp: cannot stat
>> ‘/var/lib/pgsql/9.4/data/pg_xlog/0000000100000148000000A8.00000028.backup’:
>> No such file or directory
>> 2019-07-24 16:17:50 -03 [11473]: [60884] user=,db=,app=,host=,
>> SQL=00000LOG: archive command failed with exit code 1
>> 2019-07-24 16:17:50 -03 [11473]: [60885] user=,db=,app=,host=,
>> SQL=00000DETAIL: The failed archive command was: cp
>> /var/lib/pgsql/9.4/data/pg_xlog/0000000100000148000000A8.00000028.backup
>> /var/lib/pgsql/9.4/data/backup/pitr/wall/0000000100000148000000A8.00000028.backup
>> 2019-07-24 16:17:50 -03 [11473]: [60886] user=,db=,app=,host=,
>> SQL=01000WARNING: archiving transaction log file
>> "0000000100000148000000A8.00000028.backup" failed too many times, will try
>> again later
>> -bash-4.2$
>>
>>
>>
>> E quanto a rotina de geração do basebackup é startada, ela roda
>> perfeitamente, mas não é encerrada devido a um erro na execução do
>> pg_stop_backup.
>>
>> 2019-07-24 10:42:32 -03 [31936]: [24]
>> user=pgbackup,db=[unknown],app=pg_basebackup,host=localhost,
>> SQL=01000WARNING: pg_stop_backup still waiting for all required WAL
>> segments to be archived (122880 seconds elapsed)
>> 2019-07-24 10:42:32 -03 [31936]: [25]
>> user=pgbackup,db=[unknown],app=pg_basebackup,host=localhost, SQL=01000HINT:
>> Check that your archive_command is executing properly. pg_stop_backup can
>> be canceled safely, but the database backup will not be usable without all
>> the WAL segments.
>>
>>
>> Verifiquei a pasta pg_xlog, e até então o
>> arquivo 000000010000014B000000AB.00000028.backup existe na pasta...
>>
>> -rw------- 1 postgres postgres 16M Jul 22 15:25 000000010000014B000000A4
>> -rw------- 1 postgres postgres 311 Jul 22 15:25
>> 000000010000014B000000A4.00000028.backup
>> -rw------- 1 postgres postgres 16M Jul 22 15:34 000000010000014B000000A5
>> -rw------- 1 postgres postgres 16M Jul 22 15:35 000000010000014B000000A6
>> -rw------- 1 postgres postgres 311 Jul 22 15:35
>> 000000010000014B000000A6.00000028.backup
>> -rw------- 1 postgres postgres 16M Jul 23 00:31 000000010000014B000000A7
>> -rw------- 1 postgres postgres 16M Jul 23 00:32 000000010000014B000000A8
>> -rw------- 1 postgres postgres 311 Jul 23 00:32
>> 000000010000014B000000A8.00000090.backup
>> -rw------- 1 postgres postgres 16M Jul 23 19:31 000000010000014B000000A9
>> -rw------- 1 postgres postgres 16M Jul 24 00:31 000000010000014B000000AA
>> -rw------- 1 postgres postgres 16M Jul 24 00:34 000000010000014B000000AB
>> -rw------- 1 postgres postgres 311 Jul 24 00:34
>> 000000010000014B000000AB.00000028.backup
>> -rw------- 1 postgres postgres 16M Jul 24 15:26 000000010000014B000000AC
>> -rw------- 1 postgres postgres 16M Jul 24 16:23 000000010000014B000000AD
>> drwx------ 1 postgres postgres 6.6K Jul 24 15:26 archive_status
>>
>>
>>
>> Uma outra coisa que me chamou a atenção, foi que na pasta
>> ./archive_status, possuem muitos arquivos com o final .backup.ready..
>>
>> -rw------- 1 postgres postgres 0 Jul 17 00:36
>> 000000010000014B00000098.00005748.backup.ready
>> -rw------- 1 postgres postgres 0 Jul 17 00:36
>> 000000010000014B00000098.ready
>> -rw------- 1 postgres postgres 0 Jul 17 19:14
>> 000000010000014B00000099.ready
>> -rw------- 1 postgres postgres 0 Jul 18 00:31
>> 000000010000014B0000009A.ready
>> -rw------- 1 postgres postgres 0 Jul 18 00:36
>> 000000010000014B0000009B.00000028.backup.ready
>> -rw------- 1 postgres postgres 0 Jul 18 00:36
>> 000000010000014B0000009B.ready
>> -rw------- 1 postgres postgres 0 Jul 18 10:30
>> 000000010000014B0000009C.ready
>> -rw------- 1 postgres postgres 0 Jul 19 10:00
>> 000000010000014B0000009D.ready
>> -rw------- 1 postgres postgres 0 Jul 20 10:31
>> 000000010000014B0000009E.ready
>> -rw------- 1 postgres postgres 0 Jul 21 14:00
>> 000000010000014B0000009F.ready
>> -rw------- 1 postgres postgres 0 Jul 22 11:14
>> 000000010000014B000000A0.ready
>> -rw------- 1 postgres postgres 0 Jul 22 11:14
>> 000000010000014B000000A1.ready
>> -rw------- 1 postgres postgres 0 Jul 22 11:14
>> 000000010000014B000000A2.ready
>> -rw------- 1 postgres postgres 0 Jul 22 15:23
>> 000000010000014B000000A3.ready
>> -rw------- 1 postgres postgres 0 Jul 22 15:25
>> 000000010000014B000000A4.00000028.backup.ready
>> -rw------- 1 postgres postgres 0 Jul 22 15:25
>> 000000010000014B000000A4.ready
>> -rw------- 1 postgres postgres 0 Jul 22 15:34
>> 000000010000014B000000A5.ready
>> -rw------- 1 postgres postgres 0 Jul 22 15:35
>> 000000010000014B000000A6.00000028.backup.ready
>> -rw------- 1 postgres postgres 0 Jul 22 15:35
>> 000000010000014B000000A6.ready
>> -rw------- 1 postgres postgres 0 Jul 23 00:31
>> 000000010000014B000000A7.ready
>> -rw------- 1 postgres postgres 0 Jul 23 00:32
>> 000000010000014B000000A8.00000090.backup.ready
>> -rw------- 1 postgres postgres 0 Jul 23 00:32
>> 000000010000014B000000A8.ready
>> -rw------- 1 postgres postgres 0 Jul 23 19:31
>> 000000010000014B000000A9.ready
>> -rw------- 1 postgres postgres 0 Jul 24 00:31
>> 000000010000014B000000AA.ready
>> -rw------- 1 postgres postgres 0 Jul 24 00:34
>> 000000010000014B000000AB.00000028.backup.ready
>> -rw------- 1 postgres postgres 0 Jul 24 00:34
>> 000000010000014B000000AB.ready
>> -rw------- 1 postgres postgres 0 Jul 24 15:26
>> 000000010000014B000000AC.ready
>> -bash-4.2$
>>
>>
>>
>> Bem, o cenário é este.....
>>
>> Será que alguém poderia me ajudar?????
>>
>>
>> Grato pela atenção
>>
>> Mauricio
>>
>>