Solved

creating standby Postgresql database server using WAL

Posted on 2009-04-10
4
607 Views
Last Modified: 2012-05-06
I have two identical Cent OS 5.3 computers configured identically with Postgresql 8.3.7.  I'm trying to create a warm standby using WAL following the instructions at:

http://www.postgresql.org/docs/current/static/warm-standby.html

Both servers have access to the directory /path/to/archive

I configured postgresql.conf on the master server with:

archive_mode = on
archive_command = 'cp "%p" /path/to/archive/"%f"'
arvhice_timeout = 300

Then restarted postgresql.  Then I logged into the database "psql db" and created the base backup "SELECT pg_start_backup('label');"  After that was done, I issued the "SELECT pg_stop_backup();" command.  This created the backup file in "/path/to/archive".  

Then on the slave postgresql server, I shut down the database, and created the recovery.conf file with

restore_command = 'cp /path/to/archive/%f %p'


When I try to restart postgresql on the slave, I get an error.  What am I doing wrong?
0
Comment
Question by:philjones85
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 24121655
The restore command is more  complex than a simple copy.
see docs
http://www.postgresql.org/docs/8.3/static/warm-standby.html
24.4.1
"A working example of a waiting restore_command is provided as a contrib module named pg_standby. This example can be extended as needed to support specific configurations or environments."
0
 
LVL 22

Expert Comment

by:earth man2
ID: 24123136
do you know what the error message is ?
0
 
LVL 6

Author Comment

by:philjones85
ID: 24130668
The error message is:
cp cannot stat '/path/to/archive/00000001.history': No such file or directory

This file doesn't exist, but it isn't created in the backup process.

And I realize the restore command is more complex than that, I'm just trying to do a single manual restore first, then I'll add the more complex automation.
0
 
LVL 6

Accepted Solution

by:
philjones85 earned 0 total points
ID: 24229278
I realize the step I missed was copying the data directory from the primary database server to the backup server before trying to "recover".  What I was trying to do was initially load both databases the exact same way and then start reading WAL files from the 1st on the 2nd.  This obviously didn't work...  This question can be closed now.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now