sorted by: recent | see : popular

Content Tagged with hq + postgresql

The built-in HQ database is PostgreSQL. Recently, users have been discovering PostgreSQL has a certain limitation: it will not execute more than 2 billion transactions between vacuums. In rare cases, an HQ built-in database can get into this state.

If this happens, the database will stop accepting connections and HQ, which needs a data store, will obviously cease to operate properly. The immediate symptom will be that users will not be able to log in to HQ and the message displayed on the screen will be The backend datasource is unavailable.

That error is not enough to say for sure that the problem is PostgreSQL avoiding wraparound failure by not accepting connections. A quick look at the hqdb.log can confirm. The telltale log entries look like this:

FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".

Happily, postgres tells you how to solve the problem. It’s not very specific, though so I’ll add some details.

The first thing to do is immediately shut HQ down (including the built-in database). Next, start just the database in single-user mode. Technically, you only really need to run a VACUUM, but since we’re here, we might as well be thorough and run VACUUM FULL ANALYZE.

I’ve created a script to start the built-in HQ database in single-user mode. It’s a slightly modified version of the db-start.sh script that is included in HQ installations. It takes one argument which is the name of the database to start up. The database name to VACUUM is specified in the hqdb.log message (it is “postgres” in the message above). Start the database with the script and you will get dropped into the psql command line. Run the VACUUM and exit. Looks something like this:

$ bin/db-start-single-user-8.1.sh postgres
PostgreSQL stand-alone backend 8.1.2
backend> VACUUM FULL ANALYZE;
VACUUM
backend>

When it’s done, you send an EOF (usually control-D) to exit the shell and shut down the database. At this point, you’re done and you can start HQ normally. Things will work and will usually perform much faster.

For more information on this PostgreSQL, check their documentation on how to avoid running into it to begin with.