update_bareos_tables progress

[Kai Zimmer]

Hi,

i'm trying to upgrade my bareos installation from 15.2.2.-37.1 to
17.2.4-9.1. Updating the postgres 9.3 tables under Debian 16.04 runs for
about 10 days already. I can see postgres is doing read and write access
to temporary tables in the postgres directory (with fatrace) - and there
is no other postgres database involved. I have plenty of free RAM and
disk space. Swap is slowly growing (about 3 GB now).

Is there any way to see how much progress update_bareos_tables script
has made?

Best,

Kai

[Nikolay]

Is your upgrade process over?

четверг, 10 октября 2019 г., 19:38:44 UTC+3 пользователь Kai Zimmer написал:

[Kai Zimmer]

Hi,

after two weeks i stopped/interrupted the script with Ctrl-Z. According to strace there were only lots of zeroes written in the last time. I'm trying to find out now if i can still restore files - than i'll decide wether to completely stop the process or let it run further...

Best,

Kai

Am 25.10.19 um 11:11 schrieb Nikolay:

--
You received this message because you are subscribed to the Google Groups "bareos-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bareos-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/bareos-users/6a133a51-64f9-428f-b1e1-0f2e5de6490a%40googlegroups.com.

[Nikolay]

What size is your DB ??? Tables "file" and "file name"???

четверг, 10 октября 2019 г., 19:38:44 UTC+3 пользователь Kai Zimmer написал:

Hi,

[Kai Zimmer]

I have no table ' file name'. But for 'file' it says

bareos=# SELECT pg_size_pretty (pg_relation_size('file'));
 pg_size_pretty
----------------
 509 GB

Am 29.10.19 um 08:50 schrieb Nikolay:

--
You received this message because you are subscribed to the Google Groups "bareos-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to bareos-users...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/bareos-users/38bbb5c9-f910-43e6-a439-d3a164df9661%40googlegroups.com.

[Nikolay]

There is no table "file name" ??? This is strange.

Here is part of the database update script:

-- merge Filename in File table

INSERT INTO TmpMergeFilenameIntoFileTable

  (FileId, FileIndex, JobId, PathId, Name, DeltaSeq, MarkId, LStat, Md5)

  SELECT File.FileId, File.FileIndex, File.JobId, File. PathId, Filename.Name, File.DeltaSeq,

         File.MarkId, File.LStat, File.Md5 FROM File, Filename

  WHERE  File.FilenameId = Filename.FilenameId;

This operation takes most of the time.

And she is not optimal. Now I am trying to find a way out of this situation.

I also have a large base ...

The easiest way to solve the problem is to clear tables "file" and "filename".

четверг, 10 октября 2019 г., 19:38:44 UTC+3 пользователь Kai Zimmer написал:

Hi,

[Nikolay]

Added lines:

set enable_hashjoin to 'off';

set enable_mergejoin to 'off';

in the file /usr/lib/bareos/scripts/ddl/updates/postgresql.2004_2171.sql

this greatly reduced the database update time.

Hope this helps someone.

800GB DB I updated 26 hours.

I also advise you to increase in the config postgresql.conf  max_wal_size

 /usr/lib/bareos/scripts/ddl/updates/postgresql.2004_2171.sql

-- update db schema from 2004 to 2171

-- table UnsavedFiles is not used, therefore dropped.

DROP TABLE IF EXISTS UnsavedFiles;

--

-- merge Filenames (from Filename.Name) info File table

--

-- To use lesser disk space,

-- drops indices, that needs to regenerated anyway.

DROP INDEX IF EXISTS file_jobid_idx;

DROP INDEX IF EXISTS file_jpfid_idx;

-- this temporary table, used for the migration,  should not exist, but drop it just in case.

DROP TABLE IF EXISTS TmpMergeFilenameIntoFileTable;

-- start transaction

BEGIN;

-- create temporary table to be used as File table later

CREATE TABLE TmpMergeFilenameIntoFileTable (

   FileId           BIGSERIAL   NOT NULL,

   FileIndex        INTEGER     NOT NULL  DEFAULT 0,

   JobId            INTEGER     NOT NULL,

   PathId           INTEGER     NOT NULL,

   DeltaSeq         SMALLINT    NOT NULL  DEFAULT 0,

   MarkId           INTEGER     NOT NULL  DEFAULT 0,

   Fhinfo           NUMERIC(20) NOT NULL  DEFAULT 0,

   Fhnode           NUMERIC(20) NOT NULL  DEFAULT 0,

   LStat            TEXT        NOT NULL,

   Md5              TEXT        NOT NULL,

   Name             TEXT        NOT NULL,

   PRIMARY KEY (FileId)

);

set enable_hashjoin to 'off';

set enable_mergejoin to 'off';

-- merge Filename in File table

INSERT INTO TmpMergeFilenameIntoFileTable

  (FileId, FileIndex, JobId, PathId, Name, DeltaSeq, MarkId, LStat, Md5)

  SELECT File.FileId, File.FileIndex, File.JobId, File. PathId, Filename.Name, File.DeltaSeq,

         File.MarkId, File.LStat, File.Md5 FROM File, Filename

  WHERE  File.FilenameId = Filename.FilenameId;

DROP TABLE Filename;

DROP TABLE File;

ALTER TABLE TmpMergeFilenameIntoFileTable RENAME TO File;

ALTER INDEX TmpMergeFilenameIntoFileTable_pkey RENAME TO file_pkey;

-- adapt index

CREATE INDEX file_jobid_idx ON File (JobId);

ALTER SEQUENCE TmpMergeFilenameIntoFileTable_fileid_seq RENAME TO file_fileid_seq;

SELECT setval('file_fileid_seq', (SELECT max(fileid) from file));

CREATE INDEX file_jpfid_idx ON File (JobId, PathId, Name);

CREATE INDEX file_pjidpart_idx ON File(PathId,JobId) WHERE FileIndex = 0 AND Name = '';

-- Add JobMedia JobBytes info, also used by NDMP DAR

ALTER TABLE JobMedia ADD COLUMN JobBytes NUMERIC(20) DEFAULT 0;

UPDATE Version SET VersionId = 2171;

COMMIT;

set client_min_messages = fatal;

ANALYSE;