8

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?

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

2 Answers 2

6

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.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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