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

From: Mauricio Tavares <mfx1975(at)gmail(dot)com>
To: pgsql-pt-geral(at)lists(dot)postgresql(dot)org
Subject: erro na gravação dos archives e também para finalizar o pg_basebackup
Date: 2019-07-24 19:32:46
Message-ID: CAOQWw8R8Tsffz7i1qYqc4qNFU8dX1YOCQPpEhvUd2YPU-1Bd6Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-pt-geral

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

Responses

Browse pgsql-pt-geral by date

  From Date Subject
Next Message Mauricio Tavares 2019-07-24 20:50:27 Re: erro na gravação dos archives e também para finalizar o pg_basebackup
Previous Message Sebastian Webber 2019-06-02 02:26:26 Re: Atualização de servidor, qual melhor forma?