Обсуждение: Hot standby server does not start

Поиск
Список
Период
Сортировка

Hot standby server does not start

От
Michal Glowacki
Дата:
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

Re: Hot standby server does not start

От
Fujii Masao
Дата:

On Wed, Sep 29, 2010 at 4:52 PM, Michal Glowacki <mg.scandic@gmail.com> wrote:

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.

Instead of pg_dump, you need to take a base backup by using pg_start_backup
and pg_stop_backup, and load it in the standby.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Re: Hot standby server does not start

От
Michal Glowacki
Дата:
Fujii, I believe I should follow this (from documentation):

************************

The procedure for making a base backup is relatively simple:

  1. Ensure that WAL archiving is enabled and working.

  2. Connect to the database as a superuser, and issue the command

    SELECT pg_start_backup('label');

    where label is any string you want to use to uniquely identify this backup operation. (One good practice is to use the full path where you intend to put the backup dump file.) pg_start_backup creates a backup label file, called backup_label, in the cluster directory with information about your backup.

    It does not matter which database within the cluster you connect to to issue this command. You can ignore the result returned by the function; but if it reports an error, deal with that before proceeding.

  3. Perform the backup, using any convenient file-system-backup tool such as tar or cpio. It is neither necessary nor desirable to stop normal operation of the database while you do this.

  4. Again connect to the database as a superuser, and issue the command

    SELECT pg_stop_backup();

    This should return successfully.

  5. Once the WAL segment files used during the backup are archived as part of normal database activity, you are done.


**********************************

BUT I have some questions:

1. I connect to database using psql and issue command SELECT pg_start_backup('my_backup'); The result (in less than second) is:

 pg_start_backup
-----------------
 4/DA000020
(1 row)

Is it correct it goes that fast?

2. Now I proceed to step 3 - perform the backup using file system backup. Could you assist me on this one? I should archive what?The  '/data' folder (please note I use Windows)? Should I just compress it into rar file and transfer to standby server and overwrite it's '/data' folder?

Regards,
Michal

On Wed, Sep 29, 2010 at 4:52 PM, Michal Glowacki <mg.scandic@gmail.com> wrote:

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.

Instead of pg_dump, you need to take a base backup by using pg_start_backup
and pg_stop_backup, and load it in the standby.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Re: Hot standby server does not start

От
Guillaume Lelarge
Дата:
Le 29/09/2010 10:16, Michal Glowacki a écrit :
>  Fujii, I believe I should follow this (from documentation):
>
> ************************
>
> The procedure for making a base backup is relatively simple:
>
>   1.
>
>      Ensure that WAL archiving is enabled and working.
>
>   2.
>
>      Connect to the database as a superuser, and issue the command
>
>      SELECT pg_start_backup('label');
>
>      where label is any string you want to use to uniquely identify
>      this backup operation. (One good practice is to use the full path
>      where you intend to put the backup dump file.) |pg_start_backup|
>      creates a /backup label/ file, called backup_label, in the cluster
>      directory with information about your backup.
>
>      It does not matter which database within the cluster you connect
>      to to issue this command. You can ignore the result returned by
>      the function; but if it reports an error, deal with that before
>      proceeding.
>
>   3.
>
>      Perform the backup, using any convenient file-system-backup tool
>      such as tar or cpio. It is neither necessary nor desirable to stop
>      normal operation of the database while you do this.
>
>   4.
>
>      Again connect to the database as a superuser, and issue the command
>
>      SELECT pg_stop_backup();
>
>      This should return successfully.
>
>   5.
>
>      Once the WAL segment files used during the backup are archived as
>      part of normal database activity, you are done.
>
>
> **********************************
>
> BUT I have some questions:
>
> 1. I connect to database using psql and issue command SELECT
> pg_start_backup('my_backup'); The result (in less than second) is:
>
>  pg_start_backup
> -----------------
>  4/DA000020
> (1 row)
>
> Is it correct it goes that fast?
>

Yes.

> 2. Now I proceed to step 3 - perform the backup using file system
> backup. Could you assist me on this one? I should archive what?The
> '/data' folder (please note I use Windows)? Should I just compress it
> into rar file and transfer to standby server and overwrite it's '/data'
> folder?
>

Using rar on the data folder should work if you don't have tablespaces.
If you have some, add them to your data rar archive.

And instead of overwriting the data folder on the slave, wipe out your
old data folder and recreate it by restoring your rar archive.

BTW, pgAdmin backup and restore facilities use pg_dump/pg_restore. So
it's not really surprising that you get the same results :)


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

Re: Hot standby server does not start

От
Michal Glowacki
Дата:
  W dniu 2010-09-29 10:56, Guillaume Lelarge pisze:
>
> Using rar on the data folder should work if you don't have tablespaces.
> If you have some, add them to your data rar archive.
>
> And instead of overwriting the data folder on the slave, wipe out your
> old data folder and recreate it by restoring your rar archive.
>
> BTW, pgAdmin backup and restore facilities use pg_dump/pg_restore. So
> it's not really surprising that you get the same results :)
>
>

Alright, now it starts - thanks:

2010-09-29 14:54:33 CEST LOG:  database system was interrupted; last
known up at 2010-09-29 13:02:11 CEST
2010-09-29 14:54:33 CEST LOG:  entering standby mode
2010-09-29 14:54:34 CEST LOG:  streaming replication successfully
connected to primary
2010-09-29 14:54:34 CEST LOG:  redo starts at 6/D2000020
2010-09-29 14:54:34 CEST LOG:  consistent recovery state reached at
6/D3000000
2010-09-29 14:54:34 CEST LOG:  database system is ready to accept read
only connections

, but still some questions on my side :)

1. I wiped out the data dir on standby and modified configuration in
files backed up from master - I believe I had to it, to configure it as
standby.

2. Now I would like to connect to standby to see myself if it
replicates, but when I run pgadminIII and try to connect to standby I got:

2010-09-29 14:55:05 CEST FATAL:  could not open file "global/11601": No
such file or directory

And I can't connect to standby database. I know it's because I wiped out
/data folder - how can I connect to standby then? Even if I can believe
it's replicating at the moment, when the master is down then I need
access to standby.

Regards,
Michal

Re: Hot standby server does not start

От
Michal Glowacki
Дата:
  hi,

     it's fine now - the problem was WinRAR which reported that he can't
access some files and I ignored that - big mistake :)

     Thanks for help - working fine now.

Michal
> Le 29/09/2010 10:16, Michal Glowacki a écrit :
>>   Fujii, I believe I should follow this (from documentation):
>>
>> ************************
>>
>> The procedure for making a base backup is relatively simple:
>>
>>    1.
>>
>>       Ensure that WAL archiving is enabled and working.
>>
>>    2.
>>
>>       Connect to the database as a superuser, and issue the command
>>
>>       SELECT pg_start_backup('label');
>>
>>       where label is any string you want to use to uniquely identify
>>       this backup operation. (One good practice is to use the full path
>>       where you intend to put the backup dump file.) |pg_start_backup|
>>       creates a /backup label/ file, called backup_label, in the cluster
>>       directory with information about your backup.
>>
>>       It does not matter which database within the cluster you connect
>>       to to issue this command. You can ignore the result returned by
>>       the function; but if it reports an error, deal with that before
>>       proceeding.
>>
>>    3.
>>
>>       Perform the backup, using any convenient file-system-backup tool
>>       such as tar or cpio. It is neither necessary nor desirable to stop
>>       normal operation of the database while you do this.
>>
>>    4.
>>
>>       Again connect to the database as a superuser, and issue the command
>>
>>       SELECT pg_stop_backup();
>>
>>       This should return successfully.
>>
>>    5.
>>
>>       Once the WAL segment files used during the backup are archived as
>>       part of normal database activity, you are done.
>>
>>
>> **********************************
>>
>> BUT I have some questions:
>>
>> 1. I connect to database using psql and issue command SELECT
>> pg_start_backup('my_backup'); The result (in less than second) is:
>>
>>   pg_start_backup
>> -----------------
>>   4/DA000020
>> (1 row)
>>
>> Is it correct it goes that fast?
>>
> Yes.
>
>> 2. Now I proceed to step 3 - perform the backup using file system
>> backup. Could you assist me on this one? I should archive what?The
>> '/data' folder (please note I use Windows)? Should I just compress it
>> into rar file and transfer to standby server and overwrite it's '/data'
>> folder?
>>
> Using rar on the data folder should work if you don't have tablespaces.
> If you have some, add them to your data rar archive.
>
> And instead of overwriting the data folder on the slave, wipe out your
> old data folder and recreate it by restoring your rar archive.
>
> BTW, pgAdmin backup and restore facilities use pg_dump/pg_restore. So
> it's not really surprising that you get the same results :)
>
>