Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm testing a PostgreSQL 8.2.1 to 9.2 upgrade on a virtual machine running a custom Linux distro. The upgrade procedure is as follows:

  1. Start the pg service
  2. Vacuum all DBs (not sure if this is needed)
  3. Backup with pg_dumpall
  4. Stop the pg service
  5. Move away the directory where the data is stored (/var/pg; it's a simple, single-server setup)
  6. Install PostgreSQL 9.2
  7. initdb
  8. Start the server
  9. Restore the dumped data
  10. reindexdb all DBs
  11. Recreate the referential_constraints view
  12. Vacuum all DBs (AFAIK required after this upgrade)

This procedure works fine on one host, backing up and restoring without a hitch. On another machine with a different database points 1 through 7 work fine, but the server won't start unless I add a sleep 1 after initdb, and even then the dumped data can't be restored because "the database system is starting up". What are the standard ways to deal with this, except for these terrible hacks:

  1. sleeping for some generous amount of time before either operation,
  2. looping until it works or until a generous timeout is reached, or
  3. looping until it accepts a trivial query or a timeout is reached.

Edit: The "solution" didn't work after all. What does it take to make sure the database is ready to run a restore?

share|improve this question
    
Just an idea: can you test for initdb exit status? I suppose when it's set work is done. –  dezso Oct 16 '12 at 16:32
    
@dezso Nope, initdb is run synchronously, so when the server is started initdb is already finished successfully. –  l0b0 Oct 16 '12 at 20:26
    
Then I have no better idea than to loop a simple test which checks things are ready. –  dezso Oct 16 '12 at 20:42
    
2) is not needed. 10) is also not needed as restoring the dump will re-create all indexes. –  a_horse_with_no_name Oct 23 '12 at 14:06
add comment

2 Answers 2

initdb doesn't return until it's finished, so there shouldn't be any pause needed between it and server startup. There have been bugs in PostgreSQL where it completed without flushing everything to disk first though. I don't know of any left right now, but the nature of bugs is that you don't always know about them.

If you use the pg_ctl command to start the database, use the "-w" parameters for that to wait until startup is finished before returning. It doesn't do anything fancy--it just does the "is it ready yet?" loop for you.

Note that if you get a server crash with a lot of data that needs to be replayed before the server can start, the timeout set by "-t" on the pg_ctl waiting might be too low.

There is no reason to VACUUM the source databases before doing a pg_dump of them. While it might speed the dump up a bit, the vacuum itself will take longer than that improvement.

share|improve this answer
    
Is the 12. step required? I would expect the tables being contiguous (or nearly contiguous after a pg_restore -j {morethan1}). –  dezso Oct 17 '12 at 7:30
    
We're running postmaster to start the daemon, and it doesn't seem to have such an option. –  l0b0 Oct 17 '12 at 10:04
add comment

The working broken solution was to modify the init script to check repeatedly whether the relevant port is in use. If it doesn't appear after a minute, the startup is considered to have failed. Pseudo-code:

start() {
    pg start
    checks=0
    while checks < 30:
        return true if the port is in use
        sleep 2
        checks++
    return false
}

Edit: Turns out this is not sufficient. The restore step:

PGOPTIONS='--client-min-messages=warning' psql \
    --no-psqlrc \
    --variable=ON_ERROR_STOP=1 \
    --quiet \
    --log-file="$restore_log" \
    --single-transaction \
    --username postgres \
    --file="$sql_backup"

Error message:

psql: FATAL:  the database system is starting up
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.