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

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

> Não consigo entender o que está fazendo, um backup ou uma restauração?
>
Estou apenas montando uma estrutura pra o PITR...
Estes arquivos serão armazenados em uma outra partição.....
#archive_command = 'cp %p /mnt/bkp/archives/%f' # command to use to
archive a logfile segment

O caminho foi alterado durante as tentativas de resolver este problema....

E como estava dando erro de escrita, eu até pensei que pudesse ser algum
erro de permissão de escrita na partição (mas que já foi constatado que não
é o caso)

> Você está usando um diretório interno ao PostgreSQL para colocar seus
> arquivamentos, isso me parece estranho.
>
>
> Em qua, 24 de jul de 2019 23:28, Mauricio Tavares <mfx1975(at)gmail(dot)com>
> escreveu:
>
>>
>>
>> Em qua, 24 de jul de 2019 às 18:20, Flavio Henrique Araque Gurgel <
>> fhagur(at)gmail(dot)com> escreveu:
>>
>>> Seu archive_command está errado.
>>> A origem é simplesmente %p sem o caminho. Exemplo:
>>> cp %p /diretório/destino/%f
>>>
>>
>> Fiz o ajuste...
>> archive_command = 'cp %p /var/lib/pgsql/9.4/data/backup/pitr/wall/%f'
>>
>>
>>>
>>> O %p inclui o caminho de onde o PostgreSQL tem de copiar.
>>>
>>
>>
>> Flávio, fiz a correção, e realmente agora está lendo do pg_xlog...
>> Mas o erro ainda persiste....
>>
>>
>> 2019-07-24 18:24:21 -03 [9380]: [1] user=,db=,app=,host=, SQL=00000LOG:
>> database system was shut down at 2019-07-24 18:24:17 -03
>> 2019-07-24 18:24:21 -03 [9380]: [2] user=,db=,app=,host=, SQL=00000LOG:
>> MultiXact member wraparound protections are now enabled
>> 2019-07-24 18:24:21 -03 [9384]: [1] user=,db=,app=,host=, SQL=00000LOG:
>> autovacuum launcher started
>> 2019-07-24 18:24:21 -03 [9378]: [3] user=,db=,app=,host=, SQL=00000LOG:
>> database system is ready to accept connections
>> cp: cannot stat ‘pg_xlog/0000000100000148000000A8.00000028.backup’: No
>> such file or directory
>> 2019-07-24 18:24:21 -03 [9385]: [1] user=,db=,app=,host=, SQL=00000LOG:
>> archive command failed with exit code 1
>> 2019-07-24 18:24:21 -03 [9385]: [2] user=,db=,app=,host=,
>> SQL=00000DETAIL: The failed archive command was: cp
>> pg_xlog/0000000100000148000000A8.00000028.backup
>> /var/lib/pgsql/9.4/data/backup/pitr/wall/0000000100000148000000A8.00000028.backup
>> cp: cannot stat ‘pg_xlog/0000000100000148000000A8.00000028.backup’: No
>> such file or directory
>> 2019-07-24 18:24:22 -03 [9385]: [3] user=,db=,app=,host=, SQL=00000LOG:
>> archive command failed with exit code 1
>> 2019-07-24 18:24:22 -03 [9385]: [4] user=,db=,app=,host=,
>> SQL=00000DETAIL: The failed archive command was: cp
>> pg_xlog/0000000100000148000000A8.00000028.backup
>> /var/lib/pgsql/9.4/data/backup/pitr/wall/0000000100000148000000A8.00000028.backup
>> cp: cannot stat ‘pg_xlog/0000000100000148000000A8.00000028.backup’: No
>> such file or directory
>> 2019-07-24 18:24:23 -03 [9385]: [5] user=,db=,app=,host=, SQL=00000LOG:
>> archive command failed with exit code 1
>> 2019-07-24 18:24:23 -03 [9385]: [6] user=,db=,app=,host=,
>> SQL=00000DETAIL: The failed archive command was: cp
>> pg_xlog/0000000100000148000000A8.00000028.backup
>> /var/lib/pgsql/9.4/data/backup/pitr/wall/0000000100000148000000A8.00000028.backup
>> 2019-07-24 18:24:23 -03 [9385]: [7] user=,db=,app=,host=,
>> SQL=01000WARNING: archiving transaction log file
>> "0000000100000148000000A8.00000028.backup" failed too many times, will try
>> again later
>>
>>
>>>
>>> Em qua, 24 de jul de 2019 22:50, Mauricio Tavares <mfx1975(at)gmail(dot)com>
>>> escreveu:
>>>
>>>>
>>>>
>>>> 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
>>>>>>
>>>>>>