Wrong Encoding on Restoring?

1

Hi, i begin to use this tool and i have one problem restoring backups, maybe is a misconfiguration of my side but i can’t find where change the configuration.
I have one database on PostgreSQL with LATIN1 encoding.
In the backup task i don’t add extra commands on Output SQL Options and the generated SQL have:

–
– PostgreSQL database dump

– Dumped from database version 9.2.23
– Dumped by pg_dump version 11.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = ‘LATIN1’;

but when i restore the database the encoding changes to UTF8.
I need that the restored database have the same encoding (LATIN1) that the original one.
How i may achieve that?
Regards.

2

Hi SB1,

Thank you for your reply.

Yes, this is a known issue, and we’ll fix it in the future. Currently, if you need to restore with another encoding, then please do it manually.

Sorry for the inconvenience.

3

Hi, is this issue already fixed?
I ask the question because some days ago i restore one database with no errors but one table don’t populate. One table with columns that have characters like Ñ and the length of data was the max length of column. If a try to add manually the data with insert queries from pgadmin appears a warning indicating that the length of data is larger then the length definded for the column.
Regards.

4

Hi SB1,

Some improvements have been added to the Alpha edition https://sqlbackupandftp.com/download/alpha Could you please install it and check if it works for you?

Thank you and sorry for the inconvenience.

5

Hi, I updated the software to the latest version and I can’t get the restoration with the correct encoding.
I add as extra parameter --encoding=LATIN1 and make the backup.

captura1
captura1479×519 84.5 KB

The created backup file has correct encoding LATIN1:

captura2

Then I restore but the encoding remains as UTF8.

captura3
captura3339×714 66.7 KB

I try restore overwriting an existing database with encoding LATIN1 and drop the existing database to create new one; with both options I get encoding UTF8 after restore.
I don’t know if I’m missing something, but I can’t get it to work.
Am I skipping any steps?
I also try create a db from scratch with:

– Database: test_encoding
– DROP DATABASE test_encoding;
CREATE DATABASE test_encoding
WITH OWNER = postgres
ENCODING = ‘LATIN1’
TABLESPACE = pg_default
LC_COLLATE = ‘C’
LC_CTYPE = ‘C’
CONNECTION LIMIT = -1;

And then backup (plain and custom settings) and the restore also change ENCODING, LC_COLLATE, and LC_CTYPE:

– Database: test_encoding2
– DROP DATABASE test_encoding2;
CREATE DATABASE test_encoding2
WITH OWNER = postgres
ENCODING = ‘UTF8’
TABLESPACE = pg_default
LC_COLLATE = ‘Spanish_Spain.1252’
LC_CTYPE = ‘Spanish_Spain.1252’
CONNECTION LIMIT = -1;

With plain no errores.
With custom:

Failed to restore “test_encoding2” database. pg_restore error: pg_restore: error: could not execute query: ERROR: parámetro de configuración no reconocido: «idle_in_transaction_session_timeout»
Command was: SET idle_in_transaction_session_timeout = 0;
pg_restore: error: could not execute query: ERROR: parámetro de configuración no reconocido: «transaction_timeout»
Command was: SET transaction_timeout = 0;
pg_restore: warning: errors ignored on restore: 2

This error can be result of PostgreSQL version older than expected?

Regards.

6

I try also restore the backup created by SqlBackuoAndFtp using psql and in thta way works, the econding of database is not changed.
Maybe I’m missing some configuration on restore job?
Regards.

7

Hello,

Thank you for your detailed message.

We’ve recently released an alpha version of SQLBackupAndFTP that includes updated handling for PostgreSQL.

Could you please download and try the alpha version from the link below to see if it resolves the issue?

:link: https://sqlbackupandftp.com/download/alpha

You can install it over your current version — your settings and jobs will remain intact.

If the issue persists even with the alpha version, please let us know.