hi,
I wanted to take new feature of postgres 9 into action - hot standby.
However for some days I am still struggling with starting hot standby
server. I have set up everything as mentioned in Postgres Tutorial, on
master and standby. All the time I get:
2010-09-27 15:56:22 CEST LOG: database system was shut down at
2010-09-27 15:56:20 CEST
2010-09-27 15:56:22 CEST LOG: entering standby mode
2010-09-27 15:56:22 CEST WARNING: WAL was generated with
wal_level=minimal, data may be missing
2010-09-27 15:56:22 CEST HINT: This happens if you temporarily set
wal_level=minimal without taking a new base backup.
2010-09-27 15:56:22 CEST FATAL: hot standby is not possible because
wal_level was not set to "hot_standby" on the master server
2010-09-27 15:56:22 CEST HINT: Either set wal_level to "hot_standby" on
the master, or turn off hot_standby here.
2010-09-27 15:56:22 CEST LOG: startup process (PID 5100) exited with
exit code 1
2010-09-27 15:56:22 CEST LOG: aborting startup due to startup process
failure
I tried it even on 'empty' server, with only maintanance 'postgres'
database. Here's some info how I do it:
I try to do it on two machines, my computer and local server. My system
is Windows 7 64bit, standby is XP professional sp2 64bit. Both machines
run postgres 9.0.0. I tried dump, dump_all, pgadmin III's backup and
restore - nothing works.
recovery.conf on standby is:
1:
2:
3:
4:
5:
standby_mode = 'on'
primary_conninfo = 'host=192.168.1.40 port=5433 user=postgres password=pass'
restore_command = 'copy "d:\\postgresql\\9.0\\archive\\%f" "%p"'
trigger_file = 'standby.stop'
pga_hba.conf on master is:
1:
2:
3:
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host replication postgres 192.168.1.108/32 trust
and postgresql.conf on master is:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
# - Settings -
wal_level = hot_standby # minimal, archive, or hot_standby
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on # immediate fsync at commit
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = 64kB # min 32kB
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
# - Archiving -
archive_mode = on # allows archiving to be done
# (change requires restart)
#d:\\postgresql\\9.0\\archive\\
archive_command = 'copy "%p" "d:\\postgresql\\9.0\\%f"' # command
to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
# - Streaming Replication -
max_wal_senders = 10 # max number of walsender processes
#wal_sender_delay = 200ms # walsender cycle time, 1-10000 milliseconds
wal_keep_segments = 32 # in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
# - Standby Servers -
hot_standby = on # "on" allows queries during recovery
max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
What more can I do?
Regards,
Michal
