Re: PITR using pg_basebackup ERROR

Error is not resolved I tried again the same process but what I have noticed

Date on Master Server is in IST but the backup of master server on second
server is showing in MSK why ?

"Sun Nov 1 10:12:29 IST 2015 "

##########################

[root(at)abc-Testing data]# cat backup_label.old
START WAL LOCATION: 0/88000024 (file 000000010000000000000088)
CHECKPOINT LOCATION: 0/8800008C
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2015-11-01 07:30:25 MSK
LABEL: pg_basebackup base backup

###################
< 2015-11-01 07:38:19.454 MSK >LOG: database system was interrupted; last
known up at 2015-11-01 07:30:25 MSK
< 2015-11-01 07:38:19.495 MSK >LOG: starting archive recovery
< 2015-11-01 07:38:19.513 MSK >LOG: restored log file
"000000010000000000000088" from archive
< 2015-11-01 07:38:19.758 MSK >LOG: redo starts at 0/88000024
< 2015-11-01 07:38:19.758 MSK >LOG: consistent recovery state reached at
0/88000110
< 2015-11-01 07:38:19.775 MSK >LOG: restored log file
"000000010000000000000089" from archive
< 2015-11-01 07:38:19.934 MSK >LOG: restored log file
"00000001000000000000008A" from archive
< 2015-11-01 07:38:20.065 MSK >LOG: restored log file
"00000001000000000000008B" from archive
< 2015-11-01 07:38:20.195 MSK >LOG: restored log file
"00000001000000000000008C" from archive
< 2015-11-01 07:38:20.353 MSK >LOG: restored log file
"00000001000000000000008D" from archive
< 2015-11-01 07:38:20.490 MSK >LOG: restored log file
"00000001000000000000008E" from archive
< 2015-11-01 07:38:20.627 MSK >LOG: restored log file
"00000001000000000000008F" from archive
< 2015-11-01 07:38:20.764 MSK >LOG: restored log file
"000000010000000000000090" from archive
< 2015-11-01 07:38:20.911 MSK >LOG: restored log file
"000000010000000000000091" from archive
< 2015-11-01 07:38:24.110 MSK >LOG: restored log file
"000000010000000000000092" from archive
< 2015-11-01 07:38:24.269 MSK >LOG: restored log file
"000000010000000000000093" from archive
< 2015-11-01 07:38:24.427 MSK >LOG: restored log file
"000000010000000000000094" from archive
< 2015-11-01 07:38:24.560 MSK >LOG: restored log file
"000000010000000000000095" from archive
< 2015-11-01 07:38:24.722 MSK >LOG: restored log file
"000000010000000000000096" from archive
< 2015-11-01 07:38:24.872 MSK >LOG: restored log file
"000000010000000000000097" from archive
< 2015-11-01 07:38:25.030 MSK >LOG: restored log file
"000000010000000000000098" from archive
< 2015-11-01 07:38:25.186 MSK >LOG: restored log file
"000000010000000000000099" from archive
< 2015-11-01 07:38:25.337 MSK >LOG: restored log file
"00000001000000000000009A" from archive
< 2015-11-01 07:38:29.902 MSK >LOG: restored log file
"00000001000000000000009B" from archive
< 2015-11-01 07:38:30.044 MSK >LOG: restored log file
"00000001000000000000009C" from archive
< 2015-11-01 07:38:30.177 MSK >LOG: restored log file
"00000001000000000000009D" from archive
cp: cannot stat `/var/lib/pgsql/pg_log_archive/00000001000000000000009E':
No such file or directory
< 2015-11-01 07:38:30.304 MSK >LOG: redo done at 0/9DFFFE88
< 2015-11-01 07:38:32.043 MSK >LOG: restored log file
"00000001000000000000009D" from archive
cp: cannot stat `/var/lib/pgsql/pg_log_archive/00000002.history': No such
file or directory
< 2015-11-01 07:38:32.047 MSK >LOG: selected new timeline ID: 2
cp: cannot stat `/var/lib/pgsql/pg_log_archive/00000001.history': No such
file or directory
< 2015-11-01 07:38:32.470 MSK >LOG: archive recovery complete
< 2015-11-01 07:38:34.774 MSK >LOG: MultiXact member wraparound
protections are now enabled
< 2015-11-01 07:38:34.786 MSK >LOG: autovacuum launcher started
< 2015-11-01 07:38:34.786 MSK >LOG: database system is ready to accept
connections

On Thu, Oct 29, 2015 at 11:58 PM, Jerry Sievers <gsievers19(at)comcast(dot)net>
wrote:

> Ankur Kaushik <ankurkaushik(at)gmail(dot)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(dot)consulting(at)comcast(dot)net
> p: 312.241.7800
>