From: Jerry Sievers <gsievers19@comcast.net>
To: Ankur Kaushik <ankurkaushik@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: PITR using pg_basebackup ERROR
References: 
 <CALXoLqxFPmKEPv1V+BaiQbkdPuXqX_sKxLTSDnzXU1Dbb3WCrQ@mail.gmail.com>
Date: Thu, 29 Oct 2015 13:28:02 -0500
In-Reply-To: 
 <CALXoLqxFPmKEPv1V+BaiQbkdPuXqX_sKxLTSDnzXU1Dbb3WCrQ@mail.gmail.com>
 (Ankur Kaushik's message of "Thu, 29 Oct 2015 16:54:54 +0530")
Message-ID: <86k2q5fs25.fsf@jerry.enova.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Ankur Kaushik <ankurkaushik@gmail.com> writes:

> I Have to Take the backup from on server and to restore to other server using PITR
>
> I did below steps for PITR 
>
> On Master Server :
>
> wal_level = hot_standby
> archive_mode = on
> archive_command = 'test ! -f /var/lib/pgsql/pg_log_archive/%f && cp %p /var/lib/pgsql/pg_log_archive/%f'
> max_wal_senders = 2
>
> mkdir /var/lib/pgsql/pg_log_archive/
> chown postgres.postgres /var/lib/pgsql/pg_log_archive/
> chmod 700 /var/lib/pgsql/pg_log_archive/
>
> su - postgres
> psql -c "CREATE ROLE base_backup_user REPLICATION LOGIN PASSWORD 'backupuser';"
>
> /etc/init.d/postgresql-9.4 restart
>
> pg_basebackup -h127.0.0.1 -U base_backup_user -D /var/lib/pgsql/backup -Ft -z -P
>
> Now I transfer /var/lib/pgsql/backup/base.tar.gz  to other server location /var/lib/pgsql/9.4/data
>
> also transfer  /var/lib/pgsql/pg_log_archive/ to other server with same location ,
>
> On target  server
>
> in recovery.conf
>
> restore_command = 'cp /var/lib/pgsql/pg_log_archive/%f %p'
>
> ERROR IN ERROR LOG
> ======================
>
> < 2015-10-29 14:21:13.189 MSK >LOG:  database system was interrupted; last known up at 2015-10-29 12:41:45 MSK
> < 2015-10-29 14:21:13.230 MSK >LOG:  starting archive recovery
> < 2015-10-29 14:21:13.247 MSK >LOG:  restored log file "000000010000000000000029" from archive
> < 2015-10-29 14:21:13.434 MSK >LOG:  redo starts at 0/29000024
> < 2015-10-29 14:21:13.434 MSK >LOG:  consistent recovery state reached at 0/29000110
> cp: cannot stat `/var/lib/pgsql/pg_log_archive/00000001000000000000002A': No such file or directory
> < 2015-10-29 14:21:13.436 MSK >LOG:  redo done at 0/29000110
> < 2015-10-29 14:21:13.459 MSK >LOG:  restored log file "000000010000000000000029" from archive
> cp: cannot stat `/var/lib/pgsql/pg_log_archive/00000002.history': No such file or directory
> < 2015-10-29 14:21:13.463 MSK >LOG:  selected new timeline ID: 2
> cp: cannot stat `/var/lib/pgsql/pg_log_archive/00000001.history': No such file or directory
> < 2015-10-29 14:21:13.800 MSK >LOG:  archive recovery complete
> < 2015-10-29 14:21:14.001 MSK >LOG:  MultiXact member wraparound protections are now enabled
> < 2015-10-29 14:21:14.002 MSK >LOG:  database system is ready to accept connections
> < 2015-10-29 14:21:14.003 MSK >LOG:  autovacuum launcher started

What *error*?

Are you referring to the missing 2.history file that is shown below?

I bet your new instance is configured to do archiving into the same
place as the master and that file that is seen below is from the new
instance which took timeline 2 after recovery.

>
> =========================
>
> In Location of second server /var/lib/pgsql/pg_log_archive
>
> -rw------- 1 postgres postgres 16M Oct 29 13:54 000000010000000000000023
> -rw------- 1 postgres postgres 305 Oct 29 13:54 000000010000000000000024.00000024.backup
> -rw------- 1 postgres postgres 16M Oct 29 13:54 000000010000000000000024
> -rw------- 1 postgres postgres 16M Oct 29 14:05 000000010000000000000025
> -rw------- 1 postgres postgres 16M Oct 29 14:09 000000010000000000000026
> -rw------- 1 postgres postgres 16M Oct 29 14:25 000000010000000000000027
> -rw------- 1 postgres postgres 16M Oct 29 15:11 000000010000000000000028
> -rw------- 1 postgres postgres 305 Oct 29 15:11 000000010000000000000029.00000024.backup
> -rw------- 1 postgres postgres 16M Oct 29 15:11 000000010000000000000029
> -rw------- 1 postgres postgres  42 Oct 29 16:51 00000002.history
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


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