How to wait for PostgreSQL to be startable / restorable?

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:

• Start the pg service

• Vacuum all DBs (not sure if this is needed)

• Backup with pg_dumpall

• Stop the pg service

• Move away the directory where the data is stored (/var/pg; it's a simple, single-server setup)

• Install PostgreSQL 9.2

• initdb

• Start the server

• Restore the dumped data

• reindexdb all DBs

• Recreate the referential_constraints view

• 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:

• sleeping for some generous amount of time before either operation,

• looping until it works or until a generous timeout is reached, or

• 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?

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.

StackExchange Database Administrators Q#27033, answer score: 6