>> -----Original Message----- >> From: Dan Langille [mailto:d...@langille.org] >> Sent: Monday, November 27, 2017 1:29 PM >> To: Michel Figgins >> Cc: bacula-users >> Subject: Re: [Bacula-users] Errors migrating from mysql to postgres >> >>> On Nov 27, 2017, at 2:13 PM, Michel Figgins <mfigg...@atlasallied.com> >>> wrote: >>> >>> Hello, >>> I'm trying to migrate from a mysql backend to a postgresql backend and >>> I'm getting an error importing the RestoreObject table. Specifically I'm >>> getting : >>> ERROR: invalid input syntax for type bytea LINE 1: >>> ...bjectCompression) VALUES (1588,'job_metadata.xml','<B\0A\0C\... >>> Googling around leads me to believe that Postgres isn't liking the >>> format of RestoreObject field, but I'm stumped as to how to change it. >>> So my questions: Can anyone suggest a way to modify the INSERT >>> statement to one Postgres will understand? Do I actually need to import >>> this table, or will the entries get re-populated as successful backups are >>> completed? >>> Additional Info: >>> This is on Fedora 27, Bacula version is 9.0.5, the MySQL database is >>> MariaDB 10.2.9, the PostgreSQL database is 9.6.6. The mysqldump command >>> used to generate the import file is : >>> mysqldump -t -n -c -e --compatible=postgresql --skip-quote-names --skip-opt >>> --disable-keys --lock-tables -u bacula -pXXXXX bacula | grep -v "INSERT >>> INTO Status" | sed -e 's/0000-00-00 00:00:00/1970-01-01 00:00:00/g' | >>> sed -e 's/\\0//' | sed -e "s#\\\'#\'\'#g" > bacula-export.sql >>> The pqsl command used to import the dump is: >>> psql -Ubacula bacula < bacula-export.sql >> >> Can you confirm your PostgreSQL is SQL_ASCII? >> >> $ psql -l >> List of databases >> Name | Owner | Encoding | Collate | Ctype | Access >> privileges >> ------------------+----------+-----------+---------+-------+------------ >> ------------------+----------+-----------+---------+-------+----------- >> bacula | bacula | SQL_ASCII | C | C | >> >> >> > >
> On Nov 27, 2017, at 7:25 PM, Michel Figgins <mfigg...@atlasallied.com> wrote: > > Yes, it is: > postgres=# \l > List of databases > Name | Owner | Encoding | Collate | Ctype | Access > privileges > -----------+----------+-----------+-------------+-------------+----------------------- > bacula | bacula | SQL_ASCII | C | C | =Tc/bacula > + > | | | | | > bacula=C*T*c*/bacula > The only other idea I have is escape notation. That would give you: VALUES (1588,'job_metadata.xml',E'<B\0A\0C\.... To answer your other question, that table is empty in my database. -- Dan Langille - BSDCan / PGCon d...@langille.org ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
