Re: wrong size archive file - Mailing list pgsql-admin

From Ginepro, Raymond C
Subject Re: wrong size archive file
Date
Msg-id 252E847FCD279248AC1165A5878127911015B2@WINPMBXD103.aeth.aetna.com
Whole thread Raw
In response to Re: wrong size archive file  ("Ginepro, Raymond C" <GineproR@aetna.com>)
List pgsql-admin

I decided to start from the top again and have found something curious.  This is what is in my pg_xlog about 20 minutes after my pg_basebackup:

 

 

postgres@xpostgw2d:/postgresql/data/GRIZZ/pg_xlog>ls -ltr

total 32812

-rw------- 1 postgres vfabric       56 Oct  9 15:01 00000002.history

-rw------- 1 postgres vfabric     8192 Oct  9 15:01 xlogtemp.3402

-rw------- 1 postgres vfabric     8192 Oct  9 15:29 RECOVERYXLOG

-rw------- 1 postgres vfabric     8192 Oct 28 09:02 000000010000000000000052

-rw------- 1 postgres vfabric     8192 Oct 28 09:30 000000010000000000000053

-rw------- 1 postgres vfabric 16777216 Oct 28 09:30 000000010000000000000050

-rw------- 1 postgres vfabric      305 Oct 28 09:30 000000010000000000000050.000

00020.backup

-rw-r--r-- 1 postgres vfabric 16777216 Oct 28 09:35 000000010000000000000051

 

I then do a pg_ctl stop, and when I look at the pg_xlog, it is as follows:

postgres@xpostgw2d:/postgresql/data/GRIZZ/pg_xlog>ls -ltr

total 32812

-rw------- 1 postgres vfabric       56 Oct  9 15:01 00000002.history

-rw------- 1 postgres vfabric     8192 Oct  9 15:01 xlogtemp.3402

-rw------- 1 postgres vfabric     8192 Oct  9 15:29 RECOVERYXLOG

-rw------- 1 postgres vfabric     8192 Oct 28 09:30 000000010000000000000053

-rw------- 1 postgres vfabric 16777216 Oct 28 09:30 000000010000000000000054

-rw------- 1 postgres vfabric      305 Oct 28 09:30 000000010000000000000050.000

00020.backup

-rw-r--r-- 1 postgres vfabric 16777216 Oct 28 09:52 000000010000000000000051

-rw------- 1 postgres vfabric     8192 Oct 28 09:52 000000010000000000000052

drwx------ 2 postgres vfabric     4096 Oct 28 09:52 archive_status

 

The question is:  What happened to the 000000010000000000000050 file?

 

It is not in the archive directory.  I was wondering if anything is wrong with my archive_command in postgresql.conf, but I do not see anything wrong with it:

archive_command = 'test -f /postgresql/data/GRIZZ/pg_xlog/backup_in_progress ||cp -n %p /postgresql/archive/GRIZZ/%f'

 

 

 

 

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ginepro, Raymond C
Sent: Wednesday, October 23, 2013 5:22 PM
To: Denish Patel
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] wrong size archive file

 

That is what I have been working from.  There is nothing in my pg_xlog file.  What I showed you is what was in the one that I saved before trying the recovery.  When I did not copy 00000001000000000000004C to /postgresql/archive/GRIZZ, it had told me that the file was missing and recovery had failed.  So I copied it and then it tells me that it is not the right size.

 

From: Denish Patel [mailto:denish@omniti.com]
Sent: Wednesday, October 23, 2013 4:49 PM
To: Ginepro, Raymond C
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] wrong size archive file

 

 

On Wed, Oct 23, 2013 at 4:44 PM, Denish Patel <denish@omniti.com> wrote:

I don't think you need to move anything out of pg_xlog to WAL archive Directory. You should remove all files from pg_xlogs directory on recovery database server too (i.e  Run rm pg_xlog/* ) 

 

Please remove the 00000001000000000000004C from WAL archive directory, remove all files from pg_xlogs on recovery database  and try to startup database ? 

On Wed, Oct 23, 2013 at 4:35 PM, Ginepro, Raymond C <GineproR@aetna.com> wrote:

Quick summary of what I did and the archive file:

I shut down postgres via pg_ctl stop

I renamed the existing data directory

I created a new directory for the restore with the old name

Unzipped and untarred the base.tar.gz file

 

In the pg_xlog, of the renamed data directory, there is the following:

postgres@xpostgw2d:/postgresql/data/GRIZZ??/pg_xlog>ls -ltr

total 16432

-rw------- 1 postgres vfabric      305 Oct  9 14:56 00000001000000000000002B.000

00020.backup

-rw------- 1 postgres vfabric       56 Oct  9 15:01 00000002.history

-rw------- 1 postgres vfabric     8192 Oct  9 15:01 xlogtemp.3402

-rw------- 1 postgres vfabric     8192 Oct  9 15:29 RECOVERYXLOG

-rw------- 1 postgres vfabric     8192 Oct 21 14:22 00000001000000000000004F

-rw------- 1 postgres vfabric      305 Oct 21 14:22 00000001000000000000004C.000

00020.backup

-rw------- 1 postgres vfabric 16777216 Oct 21 14:30 00000001000000000000004D

-rw------- 1 postgres vfabric     8192 Oct 21 14:30 00000001000000000000004E

drwx------ 2 postgres vfabric     4096 Oct 21 14:30 archive_status

 

I copied the 00000001000000000000004C.00000020.backup file to 00000001000000000000004C in the archive directory /postgresql/archive/GRIZZ.

 

The recovery.conf file has the following param:

 

restore_command = 'cp /postgresql/archive/GRIZZ/%f "%p"'

 

 

 

 

 

From: Denish Patel [mailto:denish@omniti.com]
Sent: Wednesday, October 23, 2013 3:30 PM
To: Ginepro, Raymond C
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] wrong size archive file

 

Thanks. Backup looks good. 

 

How's your recovery.conf look like? Can you provide 'ls -l' on archive file "00000001000000000000004C" from your WAL directory?

 

On Wed, Oct 23, 2013 at 3:14 PM, Ginepro, Raymond C <GineproR@aetna.com> wrote:

I have created a script to run the pg_basebackup utility.  The options it uses are:

pg_basebackup -D $BACKUP_DIR/$TIMEINFO -F t -z -Z 9 –x

where $BACKUP_DIR is set to our backup directory and $TIMEINFO is set to TIMEINFO=`date +%Y%m%d_%H%M%S`.

 

The backup looks ok when I unzip it and untar it.  As it is being created, it switches to the next WAL file and the first one is way small.

 

 

 

From: Denish Patel [mailto:denish@omniti.com]
Sent: Wednesday, October 23, 2013 3:07 PM
To: Ginepro, Raymond C
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] wrong size archive file

 

Before going into recovery discussion, I would like to learn about how did you take a backup ?  Have you followed "Making A Base Backup" procedure documented in http://www.postgresql.org/docs/9.2/static/continuous-archiving.html ?

 

On Wed, Oct 23, 2013 at 2:56 PM, Ginepro, Raymond C <GineproR@aetna.com> wrote:

Good afternoon,

I am testing out recovery on 9.2 postgres and I am getting the following message when I try to start up the cluster:



FATAL:  archive file "00000001000000000000004C" has wrong size: 305 instead of 16777216



This archive log was active at the start of the backup and is not 16M in size in the original pg_xlog.  How do I get around this?  Is there a fix for this?





This e-mail may contain confidential or privileged information. If

you think you have received this e-mail in error, please advise the

sender by reply e-mail and then delete this e-mail immediately.

Thank you. Aetna


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



 

--
Denish Patel,
OmniTi Computer Consulting Inc.
Database Architect,
Phone: 
240.646.0770 x 232



 

--

Denish Patel,
OmniTi Computer Consulting Inc.
Database Architect,
http://omniti.com/does/data-management



 

--

Denish Patel,
OmniTi Computer Consulting Inc.
Database Architect,
http://omniti.com/does/data-management



 

--

Denish Patel,
OmniTi Computer Consulting Inc.
Database Architect,
http://omniti.com/does/data-management

pgsql-admin by date:

Previous
From: Patrick Dung
Date:
Subject: Re: ZFS-FreeBSD + postgresql performance
Next
From: Ivailo.TANUSHEFF@raiffeisen.bg
Date:
Subject: Re: ZFS-FreeBSD + postgresql performance