pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPITR using pg_basebackup ERROR
4+ messages / 3 participants
[nested] [flat]
* PITR using pg_basebackup ERROR
@ 2015-10-29 11:24 Ankur Kaushik <ankurkaushik@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Ankur Kaushik @ 2015-10-29 11:24 UTC (permalink / raw)
To: pgsql-admin
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
=========================
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PITR using pg_basebackup ERROR
@ 2015-10-29 12:58 Kevin Grittner <kgrittn@ymail.com>
parent: Ankur Kaushik <ankurkaushik@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2015-10-29 12:58 UTC (permalink / raw)
To: Ankur Kaushik <ankurkaushik@gmail.com>; pgsql-admin
On Thursday, October 29, 2015 6:24 AM, Ankur Kaushik <ankurkaushik@gmail.com> wrote:
> I Have to Take the backup from on server and to restore to other server using PITR
> < 2015-10-29 14:21:13.189 MSK >LOG: database system was interrupted; last known up at 2015-10-29 12:41:45 MSK
It appears that the base backup was somehow damaged -- you should
not get this error unless the backup_label file (a critical and
integral part of the backup) is missing.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PITR using pg_basebackup ERROR
@ 2015-10-29 18:28 Jerry Sievers <gsievers19@comcast.net>
parent: Ankur Kaushik <ankurkaushik@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Jerry Sievers @ 2015-10-29 18:28 UTC (permalink / raw)
To: Ankur Kaushik <ankurkaushik@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PITR using pg_basebackup ERROR
@ 2015-11-01 04:45 Ankur Kaushik <ankurkaushik@gmail.com>
parent: Jerry Sievers <gsievers19@comcast.net>
0 siblings, 0 replies; 4+ messages in thread
From: Ankur Kaushik @ 2015-11-01 04:45 UTC (permalink / raw)
To: Jerry Sievers <gsievers19@comcast.net>; +Cc: pgsql-admin
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@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@comcast.net>
wrote:
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-11-01 04:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-10-29 11:24 PITR using pg_basebackup ERROR Ankur Kaushik <ankurkaushik@gmail.com>
2015-10-29 12:58 ` Kevin Grittner <kgrittn@ymail.com>
2015-10-29 18:28 ` Jerry Sievers <gsievers19@comcast.net>
2015-11-01 04:45 ` Ankur Kaushik <ankurkaushik@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox