Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id C8BF91337B69 for ; Wed, 29 Sep 2010 04:52:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 80896-06 for ; Wed, 29 Sep 2010 07:52:15 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id 95A251336FA7 for ; Wed, 29 Sep 2010 04:52:14 -0300 (ADT) Received: by bwz11 with SMTP id 11so337490bwz.19 for ; Wed, 29 Sep 2010 00:52:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:content-type; bh=LJDIJSvZira3qevWyCpJ0r6EGw6LZSh4yy8S1KgQPHs=; b=HO1TUa1OT2EThzo13Cdc2IayjDnMawaJo6bzV7Z7WcSvK6H3+DkFSZJ+0eXtnoavu1 1b3SHCEWEjhk6oA1BV7WZiAH3FBn2RgCEcvBrpg16S5QJ1fQikRedH4Cpo3mqG5MiNpf KUHTwRYyw6CSwXE+sBPN1FhG2yXyChdw/nKsw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject :content-type; b=Lx918ANRE0cFCqPRnPXDmku2Sjc6frdi0R/g/tmwPJ5HYb4o1cZr3Q5Ag3cnRyi0hS 8HKm4fcTMC7dutpOuz4/8nt3c5V+sgjY7vdM66POrOi3JIY45KDTr8KotOH23iXF4dlU vEgyFT52Jpiuqj3SO5n+kMFmxWXjt313yNeow= Received: by 10.204.179.9 with SMTP id bo9mr987947bkb.80.1285746732427; Wed, 29 Sep 2010 00:52:12 -0700 (PDT) Received: from [127.0.0.1] (ip-94-42-66-74.ibd.gtsenergis.pl [94.42.66.74]) by mx.google.com with ESMTPS id x19sm6389894bkv.9.2010.09.29.00.52.11 (version=SSLv3 cipher=RC4-MD5); Wed, 29 Sep 2010 00:52:12 -0700 (PDT) Message-ID: <4CA2F025.3010508@gmail.com> Date: Wed, 29 Sep 2010 09:52:05 +0200 From: Michal Glowacki User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; pl; rv:1.9.2.9) Gecko/20100915 Thunderbird/3.1.4 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Hot standby server does not start Content-Type: multipart/alternative; boundary="------------010908070208020004060206" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201009/178 X-Sequence-Number: 35722 This is a multi-part message in MIME format. --------------010908070208020004060206 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------010908070208020004060206 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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
 --------------010908070208020004060206--