3CX Installation Error with Postgres SQL

[amanjuman]

I'm trying to install 3CX on Debian 10, and when I tried to install it getting this error

System.Exception: Error in CreatingCloudServerManagementDatabase.sql: 22023: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII) ---> Npgsql.PostgresException (0x80004005): 22023: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII) at Npgsql.NpgsqlConnector.g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at Npgsql.NpgsqlDataReader.<>c__DisplayClass41_0.<g__ReadMessageSequential|0>d.MoveNext() --- End of stack trace from previous location --- at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlDataReader.NextResult() at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteScalar(Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteScalar() at PostInstall.DBConnection.ExecuteScriptInternal(NpgsqlConnection connection, String sqlQuery, IDictionary`2 parameters) at PostInstall.DBConnection.ExecuteScript(String sqlQuery, IDictionary`2 parameters) at _3CXCloudDBManager.DbConnectionExtensions.ExecuteScript(IDBConnection connection, Object configurationStructure, String scriptText, Boolean asResourceName) Exception data: Severity: ERROR SqlState: 22023 MessageText: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII) Hint: Use the same encoding as in the template database, or use template0 as template. File: dbcommands.c Line: 396 Routine: createdb --- End of inner exception stack trace --- at _3CXCloudDBManager.DbConnectionExtensions.ExecuteScript(IDBConnection connection, Object configurationStructure, String scriptText, Boolean asResourceName) at _3CXCloudDBManager.CloudDBManager.CreateCloudServerManagementDatabase(IDBConnection superuser, CloudServerManagementDatabaseConfiguration configuration) at PostInstall.SetupExecutor.CreateCloudServerManagementDatabase(CloudDBManager cloudDbManager, DBConnection templateConnection) at PostInstall.SetupExecutor.CreateDatabase(Action`1 stateChanged) at PostInstall.SetupExecutor.ExecuteSetup(SetupSettings setupSettings, Action`1 stateChanged, Action`1 errorNotifier) at PbxWebConfigTool.PbxSetupService.CreatePbxInternal(SetupSettings settings)

this is my host file.

cat /etc/hosts
127.0.0.1       localhost
127.0.1.1       localhost.localdomain fqdn.domain.tld fqdn
X.X.X.X   fqdn.domain.tld fqdn

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

How can I fix this?
Thanks

 

[amanjuman]

Postgres log

root@tunnel:~# tail -F /var/log/postgresql/postgresql-11-main.log
        CREATE DATABASE masterprofiles
          WITH OWNER = phonesystem
               ENCODING = 'UTF8'
2022-04-27 13:19:10.254 +06 [7461] phonesystem@template1 ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
2022-04-27 13:19:10.254 +06 [7461] phonesystem@template1 HINT:  Use the same encoding as in the template database, or use template0 as template.
2022-04-27 13:19:10.254 +06 [7461] phonesystem@template1 STATEMENT:  --Initial setup
        -- master profiles database
        CREATE DATABASE masterprofiles
          WITH OWNER = phonesystem
               ENCODING = 'UTF8'

 

[Saqqara]

Are you using the 3CX Debian ISO image, as this is the only supported way of installing 3cx

 

[amanjuman]

Are you using the 3CX Debian ISO image, as this is the only supported way of installing 3cx

No just on a fresh Debian 10 which I used before and worked fine.

 

[amanjuman]

Just fixed the issue with the following steps.

sudo service postgresql stop

nano /etc/postgresql/11/main/pg_hba.conf

#Comment existing line and add bypass

#local   all             postgres                                peer

local   all             postgres                                trust

sudo service postgresql start

psql -U postgres

First, we need to drop template1. Templates can’t be dropped, so we first modify it so t’s an ordinary database:

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

Now we can drop it:

DROP DATABASE template1;

Now its time to create database from template0, with a new default encoding:

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

Now modify template1 so it’s actually a template:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

Now switch to template1 and VACUUM FREEZE the template:

\c template1

VACUUM FREEZE;

exit

sudo service postgresql stop

Now change back as it was

nano /etc/postgresql/11/main/pg_hba.conf

sudo service postgresql start

Reference: https://stackoverflow.com/questions...coding-utf8-is-incompatible/16737776#16737776