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

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
>>>>
>>>>