index name “ix_builds_changeset_repositoryid”
Wanted to check before I attempt reindex or other remediation.
When I attempt to backup with pg_dump I’m getting:
pg_dump: Dumping the contents of table “core_variable” failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: invalid page in block 2619 of relation base/16384/17044
pg_dump: The command was: COPY public.core_variable (id, name, value, elementtype, environment, description, variablenamespaceid, maskvalue, expandexpressions) TO stdout;
I’ve checked all project variables, nothing seems amiss. We’ve been using CI for a long time and had weird DB issues in the past that were related to upgrade. Perhaps this is another one?
Hi Brenden,
These errors imply that your PostgreSQL database has become corrupt. The first thing to do is shut down the Continua CI Server service to prevent any further damage.
Database corruption usually happens as a result of hardware issues or a system crash (possibly due to running out of disk space). It’s worth checking the Windows event log for system errors - particularly disk failure, so that the cause can be rectified.
If you have a recent enough backup, then the easiest solution is to restore the backup. Each time you upgrade, the server installer automatically creates a full backup of all the bundled Continua CI PostgreSQL databases and saves it to the folder C:\ProgramData\VSoft\ContinuaCI. This can be restored by running psql.
e.g.
SET PGPASSWORD=your_db_password
%ProgramFiles%\VSoft Technologies\ContinuaCI\Server\PostgreSQL\bin\psql.exe --dbname postgres --no-password --username postgres --host localhost --port 9001 --file "C:\ProgramData\VSoft\ContinuaCIContinua_PG_0_0_20181211-1851.sql"
Note that the database connection details can be found in %ProgramFiles%\VSoft Technologies\ContinuaCI\Server\Continua.Server.Service.exe.config under configuration -> hibernate-configuration -> session-factory -> property name="connection.connection_string"_
Often database backups are not recent enough to contain the latest data. In this case, we can generally repair a corrupt database by reindexing the tables. This may not recover all the corrupted data but will at least get the database back into a usable state.
Before continuing, we recommend that you make a copy of the folder: C:\ProgramData\VSoft\ContinuaCI\PostgreSQLDB. You will need to shutdown the ContinuaCIPostgreSQL service to do this. Restart the service once the copy is complete.
The error message from pg_dump implies that there is corruption in the table core_variable, so we’ll start with that table.
Start up pgAdmin (%ProgramFiles%\VSoft Technologies\ContinuaCI\Server\PostgreSQL\bin\pgAdmin3.exe), open the Query Tool and run the following SQL commands, one at a time.
SET zero_damaged_pages = on;
VACUUM FULL core_variable;
REINDEX TABLE core_variable;
SET zero_damaged_pages = off;
Run a database backup, either from pgAdmin3 or using pg_dump again. If there are any further errors, note the name of the table, and rerun the above commands for that table. Continue until there are no further errors.
Next run a full database reindex.
REINDEX database ContinuaCI;
Then start up the Continua CI server service and check that your builds run correctly, and without error.
Let us know if there are any further errors.
Reindexing has corrected the problem. Thanks!
I’ve also implemented redundant backup via pg_dump, rotating 5 days worth. That should help with any future problems.
1 Like