From: Márcio M. <mar...@a1...> - 2014-02-18 12:19:59
|
Greetings, I have this Ubuntu 10.04 LTS server running bacula 5.0 over PostgreSQL 8.4. This server has already been upgraded from an old 3.x install, have gone trough a mysql->postgresql migration, and now is time to move ahead again. :) It may be running for almost a decade now! I'd like to describe planned steps and have tips, considerations and advices for this process. I'm mainly worried about: * Database upgrade from version 12 -> 14; * Bacula director config changes. I have the daily bacula.sql from the catalog backup job ("select VersionId from Version" gives 12) and a new 12.04 LTS server with bacula 5.2 and PostgreSQL 9.1 on its repos. In short detail, the planned steps are: 1. Install PostgreSQL server 2. Fix some utf-8 invalid chars: iconv -c -f UTF-8 -t UTF-8 -o /tmp/bacula_new.sql /tmp/bacula.sql 3. sh create_postgresql_database 4. psql create user bacula (needed to restore sql dump) 5. su - postgres; psql -d bacula -f /tmp/bacula_new.sql (7GB sql file) 6. sh update_postgresql_tables_12_to_14 7. sh grant_postgresql_privileges 8. Install bacula 9. Overwrite /etc/bacula with the old server's files and scripts 10. Start the daemons 11. Run a backup and restore 12. Have a beer. I got those shell scripts from bacula source, just had to edit user, password and database name on them. I could not find any specific instructions, release notes, etc on what care should be taken regarding config files or other specifics for this process. Anything I should be aware of or the steps above should do? Thanks and best regards. -- *Marcio Merlone* TI - Administrador de redes *A1 Engenharia - Unidade Corporativa* Fone: +55 41 3616-3797 Cel: +55 41 9689-0036 http://www.a1.ind.br/ <http://www.a1.ind.br> |
From: Radosław K. <rad...@ko...> - 2014-02-24 10:29:43
|
Hello, 2014-02-18 13:00 GMT+01:00 Márcio Merlone <mar...@a1...>: > Greetings, > > I have this Ubuntu 10.04 LTS server running bacula 5.0 over PostgreSQL > 8.4. This server has already been upgraded from an old 3.x install, have > gone trough a mysql->postgresql migration, and now is time to move ahead > again. :) It may be running for almost a decade now! > > I'd like to describe planned steps and have tips, considerations and > advices for this process. I'm mainly worried about: > > - Database upgrade from version 12 -> 14; > - Bacula director config changes. > > I have the daily bacula.sql from the catalog backup job ("select VersionId > from Version" gives 12) and a new 12.04 LTS server with bacula 5.2 and > PostgreSQL 9.1 on its repos. In short detail, the planned steps are: > > 1. Install PostgreSQL server > 2. Fix some utf-8 invalid chars: iconv -c -f UTF-8 -t UTF-8 -o > /tmp/bacula_new.sql /tmp/bacula.sql > > Why do you need this? Bacula (AFAIK) does not use UTF-8 as a database character coding. best regards -- Radosław Korzeniewski rad...@ko... |
From: Márcio M. <mar...@a1...> - 2014-02-24 12:16:56
|
Em 24-02-2014 07:29, Radosław Korzeniewski escreveu: > 2014-02-18 13:00 GMT+01:00 Márcio Merlone <mar...@a1... > <mailto:mar...@a1...>>: > > 2. Fix some utf-8 invalid chars: iconv -c -f UTF-8 -t UTF-8 -o > /tmp/bacula_new.sql /tmp/bacula.sql > > Why do you need this? > Bacula (AFAIK) does not use UTF-8 as a database character coding. Indeed, but my filesystem is. When restoring catalog dump on the new server I got these two messages: psql:/tmp/bacula.sql:67185854: ERROR: invalid byte sequence for encoding "UTF8": 0xa2 CONTEXT: COPY filename, line 2881 psql:/tmp/bacula.sql:69079011: ERROR: invalid byte sequence for encoding "UTF8": 0x87 CONTEXT: COPY path, line 4771 After iconv I was able to restore without warnings. -- *Marcio Merlone* TI - Administrador de redes *A1 Engenharia - Unidade Corporativa* Fone: +55 41 3616-3797 Cel: +55 41 9689-0036 http://www.a1.ind.br/ <http://www.a1.ind.br> |
From: Radosław K. <rad...@ko...> - 2014-02-24 15:56:26
|
Hello, 2014-02-24 13:16 GMT+01:00 Márcio Merlone <mar...@a1...>: > > Em 24-02-2014 07:29, Radosław Korzeniewski escreveu: > > 2014-02-18 13:00 GMT+01:00 Márcio Merlone <mar...@a1...>: > >> 2. Fix some utf-8 invalid chars: iconv -c -f UTF-8 -t UTF-8 -o >> /tmp/bacula_new.sql /tmp/bacula.sql >> > Why do you need this? > Bacula (AFAIK) does not use UTF-8 as a database character coding. > > Indeed, but my filesystem is. When restoring catalog dump on the new > server I got these two messages: > > ??? I do not understand. Your file name is /tmp/bacula.sql, it does not have UTF8 characters in its name. > psql:/tmp/bacula.sql:67185854: ERROR: invalid byte sequence for encoding > "UTF8": 0xa2 > CONTEXT: COPY filename, line 2881 > psql:/tmp/bacula.sql:69079011: ERROR: invalid byte sequence for encoding > "UTF8": 0x87 > CONTEXT: COPY path, line 4771 > > I think your Bacula database has wrong charset encoding. It should be SQL_ASCII not UTF-8, so PostgreSQL should take any 8bit character as an input. > After iconv I was able to restore without warnings. > But you have a corrupted filename table - means you could not restore some files with proper filename. IMO, YMMV. best regards -- Radosław Korzeniewski rad...@ko... |
From: Márcio M. <mar...@a1...> - 2014-02-24 16:39:27
|
Em 24-02-2014 12:56, Radosław Korzeniewski escreveu: > 2014-02-24 13:16 GMT+01:00 Márcio Merlone <mar...@a1... > <mailto:mar...@a1...>>: > > > Em 24-02-2014 07 <tel:24-02-2014%2007>:29, Radosław Korzeniewski > escreveu: >> 2014-02-18 13:00 GMT+01:00 Márcio Merlone >> <mar...@a1... <mailto:mar...@a1...>>: >> >> 2. Fix some utf-8 invalid chars: iconv -c -f UTF-8 -t UTF-8 >> -o /tmp/bacula_new.sql /tmp/bacula.sql >> >> Why do you need this? >> Bacula (AFAIK) does not use UTF-8 as a database character coding. > Indeed, but my filesystem is. When restoring catalog dump on the > new server I got these two messages: > > > ??? > > I do not understand. Your file name is /tmp/bacula.sql, it does not > have UTF8 characters in its name. I was not referring to the dump file name, but the database records inside it. My filesystem has lots of UTF-8 encoded chars on file's names wich gets into catalog records. Some of those (two records) were the problem. See below, first problem was on line 67185854 of bacula.sql: > psql:/tmp/bacula.sql:67185854: ERROR: invalid byte sequence for > encoding "UTF8": 0xa2 > CONTEXT: COPY filename, line 2881 > psql:/tmp/bacula.sql:69079011: ERROR: invalid byte sequence for > encoding "UTF8": 0x87 > CONTEXT: COPY path, line 4771 > > > I think your Bacula database has wrong charset encoding. It should > be SQL_ASCII not UTF-8, so PostgreSQL should take any 8bit character > as an input. root@phobos:~/bin# su - postgres -c "psql bacula -c 'SHOW SERVER_ENCODING'" server_encoding ----------------- SQL_ASCII (1 row) root@phobos:~/bin# > After iconv I was able to restore without warnings. > > But you have a corrupted filename table - means you could not restore > some files with proper filename. IMO, YMMV. I see. This deserves an investigation, but considering I could not take those two records into the database, is no worse than it already was. Will dig into original sql dump and find out what is wrong. Thanks for pointing this out. Best regards. -- *Marcio Merlone* |
From: <lst...@kw...> - 2014-02-24 17:21:07
|
Zitat von Márcio Merlone <mar...@a1...>: > Em 24-02-2014 12:56, Radosław Korzeniewski escreveu: >> 2014-02-24 13:16 GMT+01:00 Márcio Merlone <mar...@a1... >> <mailto:mar...@a1...>>: >> >> >> Em 24-02-2014 07 <tel:24-02-2014%2007>:29, Radosław Korzeniewski >> escreveu: >>> 2014-02-18 13:00 GMT+01:00 Márcio Merlone >>> <mar...@a1... <mailto:mar...@a1...>>: >>> >>> 2. Fix some utf-8 invalid chars: iconv -c -f UTF-8 -t UTF-8 >>> -o /tmp/bacula_new.sql /tmp/bacula.sql >>> >>> Why do you need this? >>> Bacula (AFAIK) does not use UTF-8 as a database character coding. >> Indeed, but my filesystem is. When restoring catalog dump on the >> new server I got these two messages: >> >> >> ??? >> >> I do not understand. Your file name is /tmp/bacula.sql, it does not >> have UTF8 characters in its name. > I was not referring to the dump file name, but the database records > inside it. My filesystem has lots of UTF-8 encoded chars on file's > names wich gets into catalog records. Some of those (two records) > were the problem. See below, first problem was on line 67185854 of > bacula.sql: > >> psql:/tmp/bacula.sql:67185854: ERROR: invalid byte sequence for >> encoding "UTF8": 0xa2 >> CONTEXT: COPY filename, line 2881 >> psql:/tmp/bacula.sql:69079011: ERROR: invalid byte sequence for >> encoding "UTF8": 0x87 >> CONTEXT: COPY path, line 4771 >> >> >> I think your Bacula database has wrong charset encoding. It should >> be SQL_ASCII not UTF-8, so PostgreSQL should take any 8bit >> character as an input. > root@phobos:~/bin# su - postgres -c "psql bacula -c 'SHOW SERVER_ENCODING'" > server_encoding > ----------------- > SQL_ASCII > (1 row) > > root@phobos:~/bin# I guess this is a problem with psql used with stdin/stdout on a UTF-8 OS: If at least one of standard input or standard output are a terminal, then psql sets the client encoding to “auto”, which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesn't work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING. You might try the --file parameter instead of redirecting the dump to psql. The Bacula database should have no other enconding than ASCII, eg. there is no valid UTF-8 in it. On the other hand your dump should include something like "SET client_encoding = 'ASCII'" on top of the file, no? Regards Andreas |
From: Márcio M. <mar...@a1...> - 2014-02-24 19:41:19
|
Em 24-02-2014 14:20, lst...@kw... escreveu: > You might try the --file parameter instead of redirecting the dump to > psql. The Bacula database should have no other enconding than ASCII, > eg. there is no valid UTF-8 in it. Will try to see what happens and post results here. > On the other hand your dump should include something like "SET > client_encoding = 'ASCII'" on top of the file, no? Yes. :) SET client_encoding = 'SQL_ASCII'; -- *Marcio Merlone* |