Running a backup while Continua CI is running

Continua CI Discussion
1

Dear Sir/Madam

We are running a daily database backup on our Continua CI database by issuing the following command: pg_dump.exe -h localhost -p 9001 -U postgres -F p -f C:\continua_backup.bak ContinuaCI
http://wiki.finalbuilder.com/displa…a+database

Sometimes the backup fails and sometimes the database is corrupted. We are wondering if it is save to dump the database while the Continua Service is running?

Best regards,
Philippe


2

Hi Phillipe,

Yes, it is safe to run the backup while the Continua CI and the PostgreSQL database service are running. See postgresql - Does running pg_dump on live db produce consistent backups? 

If you write the output of each backup to a file using > outfile then you should see error messages giving the reason for the failure.

3

Hi Dave

Over the weekend the daily backup failed again. When executing: pg_dump.exe -h localhost -p 9001 -U postgres -F p -f C:\continua_backup.bak ContinuaCI we encountered following error:

pg_dump: Dumping the contents of table “builds_repositorydata” failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: missing chunk number 0 for toast value 5842186 in pg_toast_3595984
pg_dump: The command was: COPY public.builds_repositorydata (id, plugin, latestcachedupdate, lastchecked, lastchangesetdatetime, lastchangesetid, repositorystate, lastchangesetrevision, lastchangesetb
ranch) TO stdout;

Do you have any explanation?

Best regards,
Philippe

4

Hi Phillipe,

There is a corrupt record in the public.builds_repositorydata table. This could have happened due to an immediate service shutdown e.g. a server crash or powercut, or could be related to an issue with disk or memory. 

If your previous backup is up-to-date than you can restore that and get back up an running. 

Otherwise, to fix this issue you will need to run some SQL commands.

First shutdown the Continua CI Server service as you will be making updates to the database. Then run the pgAdmin3.exe tool, which is in the %PROGRAM_FILES%\VSoft Technologies\ContinuaCI\Server\PostgreSQL\bin folder. You can find the database connection details in %PROGRAM_FILES%\VSoft Technologies\ContinuaCI\Server\Continua.Server.Service.exe.config under hibernate-configuration -> session-factory -> property name=“connection.connection_string”.

Once you are connected, select the Continua database in the Object browser, then choose the Query Tool from the Tools menu. 

Run the following command:

REINDEX table pg_toast.pg_toast_359584;
REINDEX table public.builds_repositorydata;

then run the following command:

VACUUM ANALYZE public.builds_repositorydata;

Next run 

SELECT * FROM public.builds_repositorydata;

If the error is still reported we will need to find the corrupted record.

Run the following command to get the number of records in the table:

SELECT COUNT() FROM public.builds_repositorydata;

Then use a binary search with the following command to narrow down to the corrupted record:

SELECT FROM public.builds_repositorydata LIMIT N1 OFFSET N2;

This blog post describes how to do this.  

Once you have found the affected row and field, note down the id of the row (which we will refer to as N) and check the corresponding row of the public.builds_repository table. 

SELECT * FROM public.builds_repository WHERE repodataid = N;

This will allow you to determine whether this is an important repository. If the Archived flag is set the true (t) then the data in the corrupted field can be safely discarded. You can run relevant update statement below to reset the field e.g.

UPDATE public.builds_repositorydata SET latestcachedupdate = ‘2017-01-10 00:00:00’ WHERE Id = N;
UPDATE public.builds_repositorydata SET lastchecked = ‘2017-01-10 00:00:00’ WHERE Id = N;
UPDATE public.builds_repositorydata SET lastchangesetdatetime = NULL WHERE Id = N;
UPDATE public.builds_repositorydata SET lastchangesetid = -1 WHERE Id = N;
UPDATE public.builds_repositorydata SET repositorystate = ‘{}’ WHERE Id = N;
UPDATE public.builds_repositorydata SET lastchangesetrevision = NULL WHERE Id = N;
UPDATE public.builds_repositorydata SET lastchangesetbranch = NULL WHERE Id = N;
UPDATE public.builds_repositorydata SET plugin = null WHERE Id = N;

If the repository is not archived then it will be OK to reset all fields except for the plugin field. You will just need to reset the repository in the Administration section of Continua CI after starting up the service.
If you need to reset the plugin field then the repository data is lost and you will need to archive the repository using the following command and then recreate it manually in Continua CI.

UPDATE public.builds_repository SET archived = true WHERE repodataid = N

Please let us know how you get on with this.
5

Hi Dave

I have narrowed down the corrupted field and updated/reset it. If everything is fine is not yet clear however the original error is resolved, I can run now pg_dump again. However I am puzzled why there has been a db-error at the first place.

Best regards,
Philippe



6

Hi Dave

As mentioned in my previous post db-dump worked however db-restore now failed. There was some error message about double entries.

I removed all double entries in the public.builds_repositorydata and retried it. At the moment it seems to work.

BR Philippe

7

Hi Dave

Again we have issues with the database:

[code]Message: More than one row with the given identifier was found: 31, for class: Continua.Modules.Builds.Repositories.RepositoryData[/code]

Every time the error is somehow related to double entries in the RepositoryData database.

I would like to send you the screenshot, database export and event log. Can you please let me know an e-mail where I can send the confidential data to?

Best regards,
Philippe

8

Hi Philippe,

If you send these to support@finalbuilder.com, it will go directly to me. If the files are large then you can also request ftp upload details via this email address.