Thread: Hot standby server does not start
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
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
************************
The procedure for making a base backup is relatively simple:
Ensure that WAL archiving is enabled and working.
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.
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.
Again connect to the database as a superuser, and issue the command
SELECT pg_stop_backup();
This should return successfully.
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
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
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
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 :) > >