Why warm-standby doesn't work using file-based log shipping method?

Started by sunpengabout 14 years ago4 messagesgeneral
Jump to latest
#1sunpeng
bluevaley@gmail.com

I try to setup warm-standby using file-based log shipping method:
Master: 5432 port
Standby:6432 port at same machine
Master's :

- wal_level = archive
- archive_mode = on
- archive_command = 'cp %p /home/postgres/archive/%f'

Standby's restore_command = 'cp /home/postgres/archive/%f %p'

1. Firstly do a base backup for Standby ( successfuly )
[postgres@localhost ~]$ /home/postgres/db/standby/pgsql/bin/postmaster -D
/home/postgres/db/standby/pgsql/data --port=6432
LOG: database system was interrupted; last known up at 2012-02-21 17:24:33
CST
LOG: starting archive recovery
LOG: restored log file "000000010000000000000006" from archive
LOG: redo starts at 0/6000070
LOG: consistent recovery state reached at 0/7000000
cp: cannot stat `/home/postgres/archive/000000010000000000000007': No such
file or directory
LOG: could not open file "pg_xlog/000000010000000000000007" (log file 0,
segment 7): No such file or directory
LOG: redo done at 0/6000094
LOG: restored log file "000000010000000000000006" from archive
cp: cannot stat `/home/postgres/archive/00000002.history': No such file or
directory
LOG: selected new timeline ID: 2
cp: cannot stat `/home/postgres/archive/00000001.history': No such file or
directory
LOG: archive recovery complete
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
2. Then, let standby running as warm-standby mode with the following
parameters in recovery.conf to enable it continously track the Master
server:

- standby_mode = on
- restore_command = 'cp /home/postgres/archive/%f %p'

[postgres@localhost ~]$ /home/postgres/db/standby/pgsql/bin/postmaster -D
/home/postgres/db/standby/pgsql/data --port=6432
LOG: database system was shut down at 2012-02-21 17:29:38 CST
cp: cannot stat `/home/postgres/archive/00000003.history': No such file or
directory
cp: cannot stat `/home/postgres/archive/00000002.history': No such file or
directory
LOG: entering standby mode
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory
WARNING: WAL was generated with wal_level=minimal, data may be missing
HINT: This happens if you temporarily set wal_level=minimal without taking
a new base backup.
LOG: consistent recovery state reached at 0/700013C
LOG: record with zero length at 0/700013C
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory
cp: cannot stat `/home/postgres/archive/00000003.history': No such file or
directory
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory
cp: cannot stat `/home/postgres/archive/00000003.history': No such file or
directory
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory

3. Then let us do some DML queries on Master, for example, I insert new
tuples(large number, 1000000) to a table.
4. Theoreticaly new tuples could also be visible on Standby server some
time later, yet after I promote the Standby to check, it reveals nothing
has been down.

My question is:
1. Are there anything I have missed ?
2. I still can't understand why Standby(when running as standby mode)
always try to find `/home/postgres/archive/000000020000000000000007' file,
yet Master only copy the following files to archive directory:
[postgres@localhost archive]$ cd /home/postgres/archive
[postgres@localhost archive]$ ls
000000010000000000000004
000000010000000000000005
000000010000000000000006
000000010000000000000006.00000020.backup
000000010000000000000007
000000010000000000000008
000000010000000000000009
obviously there is no 000000020000000000000007.

Thanks!

#2Scott Mead
scottm@openscg.com
In reply to: sunpeng (#1)
Re: Why warm-standby doesn't work using file-based log shipping method?

On Tue, Feb 21, 2012 at 5:09 AM, sunpeng <bluevaley@gmail.com> wrote:

I try to setup warm-standby using file-based log shipping method:
Master: 5432 port
Standby:6432 port at same machine
Master's :

- wal_level = archive
- archive_mode = on
- archive_command = 'cp %p /home/postgres/archive/%f'

Standby's restore_command = 'cp /home/postgres/archive/%f %p'

The slave's restore_command needs to be pg_standby (or similar) instead of
'cp' , cp will just kick and error and stop restoring on the first file it
doesn't find.

http://www.postgresql.org/docs/9.1/static/pgstandby.html

--Scott

Show quoted text

1. Firstly do a base backup for Standby ( successfuly )
[postgres@localhost ~]$ /home/postgres/db/standby/pgsql/bin/postmaster -D
/home/postgres/db/standby/pgsql/data --port=6432
LOG: database system was interrupted; last known up at 2012-02-21
17:24:33 CST
LOG: starting archive recovery
LOG: restored log file "000000010000000000000006" from archive
LOG: redo starts at 0/6000070
LOG: consistent recovery state reached at 0/7000000
cp: cannot stat `/home/postgres/archive/000000010000000000000007': No such
file or directory
LOG: could not open file "pg_xlog/000000010000000000000007" (log file 0,
segment 7): No such file or directory
LOG: redo done at 0/6000094
LOG: restored log file "000000010000000000000006" from archive
cp: cannot stat `/home/postgres/archive/00000002.history': No such file or
directory
LOG: selected new timeline ID: 2
cp: cannot stat `/home/postgres/archive/00000001.history': No such file or
directory
LOG: archive recovery complete
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
2. Then, let standby running as warm-standby mode with the following
parameters in recovery.conf to enable it continously track the Master
server:

- standby_mode = on
- restore_command = 'cp /home/postgres/archive/%f %p'

[postgres@localhost ~]$ /home/postgres/db/standby/pgsql/bin/postmaster -D
/home/postgres/db/standby/pgsql/data --port=6432
LOG: database system was shut down at 2012-02-21 17:29:38 CST
cp: cannot stat `/home/postgres/archive/00000003.history': No such file or
directory
cp: cannot stat `/home/postgres/archive/00000002.history': No such file or
directory
LOG: entering standby mode
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory
WARNING: WAL was generated with wal_level=minimal, data may be missing
HINT: This happens if you temporarily set wal_level=minimal without
taking a new base backup.
LOG: consistent recovery state reached at 0/700013C
LOG: record with zero length at 0/700013C
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory
cp: cannot stat `/home/postgres/archive/00000003.history': No such file or
directory
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory
cp: cannot stat `/home/postgres/archive/00000003.history': No such file or
directory
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory
cp: cannot stat `/home/postgres/archive/000000020000000000000007': No such
file or directory

3. Then let us do some DML queries on Master, for example, I insert new
tuples(large number, 1000000) to a table.
4. Theoreticaly new tuples could also be visible on Standby server some
time later, yet after I promote the Standby to check, it reveals nothing
has been down.

My question is:
1. Are there anything I have missed ?
2. I still can't understand why Standby(when running as standby mode)
always try to find `/home/postgres/archive/000000020000000000000007' file,
yet Master only copy the following files to archive directory:
[postgres@localhost archive]$ cd /home/postgres/archive
[postgres@localhost archive]$ ls
000000010000000000000004
000000010000000000000005
000000010000000000000006
000000010000000000000006.00000020.backup
000000010000000000000007
000000010000000000000008
000000010000000000000009
obviously there is no 000000020000000000000007.

Thanks!

#3Magnus Hagander
magnus@hagander.net
In reply to: Scott Mead (#2)
Re: Why warm-standby doesn't work using file-based log shipping method?

On Tue, Feb 21, 2012 at 23:59, Scott Mead <scottm@openscg.com> wrote:

On Tue, Feb 21, 2012 at 5:09 AM, sunpeng <bluevaley@gmail.com> wrote:

I try to setup warm-standby using file-based log shipping method:
Master: 5432 port
Standby:6432 port at same machine
Master's :

wal_level = archive
archive_mode = on
archive_command = 'cp %p /home/postgres/archive/%f'

Standby's restore_command = 'cp /home/postgres/archive/%f %p'

The slave's restore_command needs to be pg_standby (or similar) instead of
'cp' , cp will just kick and error and stop restoring on the first file it
doesn't find.

Not in 9.1 (and later). Regular restore_command and set
standby_mode=on in recovery.conf is the easier way there.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

#4Jens Wilke
jens@wilke.org
In reply to: sunpeng (#1)
Re: Why warm-standby doesn't work using file-based log shipping method?

On Dienstag, 21. Februar 2012, sunpeng wrote:

000000010000000000000009
obviously there is no 000000020000000000000007.

The standby is on timeline 2, but the WAL Files are on timeline 1.
I guess, you should make a new base backup.

Regards, Jens