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

Boa tarde Thiago...

Em qui, 25 de jul de 2019 às 15:01, Thiago Lopes <aslthiago(at)gmail(dot)com>
escreveu:

> Conseguiu Maurício?
>
Ainda não... os ajustes propostos não surtiram efeito

> Se ainda estiver com problemas, tente filtrar os resultados do Google.
> Pesquise assim:
> "MEU TEXTO"site:severalnines.com
> Ou DZone... Enfim use o Google assim!
>

Já tentei refinar a pesquisa, mas os resultados não foram tão acertivos
quanto ao erro e possível solução

> Se puder me explicar a solução depois agradeço.
>
Assim que eu tiver a solução eu passo aqui sim.....

> Valeu.
>
Abraços

>
> Em quarta-feira, 24 de julho de 2019, Mauricio Tavares <mfx1975(at)gmail(dot)com>
> escreveu:
> >
> >
> > 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
> >