2

I am new to postgreSQL, so please talk to me like I'm in kindergarden. By the way, everything in this question is happening on Windows machines.

I pg_dumped a database, creating a backup.dump file. Now, I am trying to import backup.dump to a new installation of postgreSQL, in a different computer. This is the instruction I am using for import:

psql -U postgres -d dbname -1 -f D:\backup.dump

If I create a new database in the same server, the import completes succesfully and I can access all data, which tells me my backup.dump file is okay. But when I try to import it into a different computer, I get all of these errors:

psql:D:/backup.dump:40: ERROR:  no existe el rol «traduality»

psql:D:/backup.dump:59: ERROR:  transacción abortada, las órdenes serán ignoradas hasta el fin de bloque de transacción

psql:D:/backup.dump:62: ERROR:  transacción abortada, las órde... (goes on 1,000 times).

psql:D:/backup.dump:3630: error: orden \. no válida

psql:D:/backup.dump:3637: ERROR:  transacción abortada, las órdenes serán ignoradas hasta el fin de bloque de transacción

psql:D:/backup.dump:3638: error: orden \N no válida

psql:D:/backup.dump:3639: error: orden \. no válida

psql:D:/backup.dump:3646: ERROR:  error de sintaxis en o cerca de «9»
LÍNEA 1: 9a226ab3-f82b-436e-8ada-f3e3a52def7d Default f a5d05e59-ebcc...
         ^

El búfer de consulta ha sido reiniciado (limpiado).

psql:D:/backup.dump:3647: error: orden \n<html no válida

psql:D:/backup.dump:3648: error: orden \N no válida

psql:D:/backup.dump:3649: error: orden \N no válida

psql:D:/backup.dump:3659: error: orden \. no válida

psql:D:/backup.dump:3666: ERROR:  error de sintaxis en o cerca de «8»
LÍNEA 1: 8 AdornerInvoiceDetail 0
         ^

psql:D:/backup.dump:3667: ERROR:  error de sintaxis en o cerca de «11e32986»
LÍNEA 1: 11e32986-e7ac-4a9c-85fb-d11b035d7996 Admin 2019-09-14 02:52:...
         ^

psql:D:/backup.dump:3667: ERROR:  error de sintaxis en o cerca de «Hyun»
LÍNEA 1: Hyun Woo Oh;
         ^

psql:D:/backup.dump:3667: ERROR:  error de sintaxis en o cerca de «N»
LÍNEA 1: N/A;

psql:D:/backup.dump:4233: error: orden \N no válida

psql:D:/backup.dump:4234: ERROR:  error de sintaxis en o cerca de «True»
LÍNEA 1: True 2d111e5d-ad9d-402c-a9a8-d7cf851284ad
         ^

psql:D:/backup.dump:4234: ERROR:  error de sintaxis en o cerca de «David»
LÍNEA 1: David Daleng;
         ^

psql:D:/backup.dump:4234: ERROR:  error de sintaxis en o cerca de «isCloning»
LÍNEA 1: isCloning;

psql:D:/backup.dump:44650: error: orden \N no válida

psql:D:/backup.dump:44664: error: orden \N no válida

psql:D:/backup.dump:65251: ERROR:  carácter con secuencia de bytes 0x81 en codificación «WIN1252» no tiene equivalente en la codificación «UTF8»

The original is actually longer, like 3,000+ error entries.

I also tried encoding it to UTF8 at the exporting step:

pg_dump -U postgres --enconding utf8 -d dbname > D:\backup.dump

But it results in the same errors.

Akiryou
  • 21
  • 2
  • Please do not post links to images. Cut an paste out of the terminal and post as text. Also I would not use -1 in the psql command, that just complicates things. On what OS is the database you are dumping? In that database using psql what does ```show lc_ctype ;``` and ```show lc_collate ;``` return? Add the preceding information to your question. – Adrian Klaver Jul 19 '20 at 21:15

1 Answers1

0

Thanks to Adrian Klaver's input I was able to fix the issue. I can't provide a technical explanation but here's what worked for me:

I removed the -1 from the importing instruction:

psql -U postgres -d dbname -1 -f D:\backup.dump (wrong)
psql -U postgres -d dbname -f D:\backup.dump (good)

Then, it showed me this error:

psql:D:/backup.dump:40: ERROR:  no existe el rol «role_name»

I went to pgAdmin 4 and created a role with the same new as the one shown in the error, then I created a new database using that role as the owner, and tried to import again using:

psql -U postgres -d dbname -f D:\backup.dump

Worked perfectly.

Akiryou
  • 21
  • 2