pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHot standby server does not start
6+ messages / 3 participants
[nested] [flat]
* Hot standby server does not start
@ 2010-09-29 07:52 Michal Glowacki <mg.scandic@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Michal Glowacki @ 2010-09-29 07:52 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Hot standby server does not start
@ 2010-09-29 08:06 Fujii Masao <masao.fujii@gmail.com>
parent: Michal Glowacki <mg.scandic@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Fujii Masao @ 2010-09-29 08:06 UTC (permalink / raw)
To: Michal Glowacki <mg.scandic@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Hot standby server does not start
@ 2010-09-29 08:16 Michal Glowacki <mg.scandic@gmail.com>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Michal Glowacki @ 2010-09-29 08:16 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: pgsql-admin
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
> <mailto: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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Hot standby server does not start
@ 2010-09-29 08:56 Guillaume Lelarge <guillaume@lelarge.info>
parent: Michal Glowacki <mg.scandic@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Guillaume Lelarge @ 2010-09-29 08:56 UTC (permalink / raw)
To: Michal Glowacki <mg.scandic@gmail.com>; +Cc: Fujii Masao <masao.fujii@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Hot standby server does not start
@ 2010-09-29 13:01 Michal Glowacki <mg.scandic@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 0 replies; 6+ messages in thread
From: Michal Glowacki @ 2010-09-29 13:01 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Fujii Masao <masao.fujii@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Hot standby server does not start
@ 2010-10-01 11:07 Michal Glowacki <mg.scandic@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 0 replies; 6+ messages in thread
From: Michal Glowacki @ 2010-10-01 11:07 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Fujii Masao <masao.fujii@gmail.com>; pgsql-admin
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 :)
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-10-01 11:07 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-29 07:52 Hot standby server does not start Michal Glowacki <mg.scandic@gmail.com>
2010-09-29 08:06 ` Re: Hot standby server does not start Fujii Masao <masao.fujii@gmail.com>
2010-09-29 08:16 ` Re: Hot standby server does not start Michal Glowacki <mg.scandic@gmail.com>
2010-09-29 08:56 ` Re: Hot standby server does not start Guillaume Lelarge <guillaume@lelarge.info>
2010-09-29 13:01 ` Re: Hot standby server does not start Michal Glowacki <mg.scandic@gmail.com>
2010-10-01 11:07 ` Re: Hot standby server does not start Michal Glowacki <mg.scandic@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