What should I pay attention to when migrating from MySQL to PostgreSQL?

Question

MySQL

We are developing our project in the company with a bunch of internal services, CRM, Task manager, etc. The whole thing interacts quite closely with 1c, which is based on PosgreSQL, but at the same time, the main MySQL database (because I have more experience working with it, I know along and across)
At this stage, due to the expansion of functionality, deeper integration was needed, and accordingly, either once again complicate the synchronization mechanisms, or transfer everything that is directly to Posgre, especially since the database itself has much more capabilities. Then there is no point in synchronization, it will be possible to work in a scheme adjacent to 1s and use foreign keys and tricky views.
Accordingly, the question is for people who imagine the intricacies of such a transition: what should I pay attention to during tests, what can be pitfalls?
It is also confusing that posgre is patched to work with 1s, and the most serious consequence is the absence of managed locks, only table locks.
The requests themselves only need to be slightly edited and the class for working with the database is rewritten (a small add-on to make it more convenient to work with). Users: about 40 people, the load (for 1s) is about 3 requests per second (now I measure it more accurately at active work with more active synchronization).

Answer 1

eeeh, he asked himself answered)
So, what actually needs to be paid attention to:
- change date_format(now() '%d.%m.%Y'); to_char( now(), 'DD.MM.YYYY' );
- if you need to use unix_timestamp() (I use it to pass the dateDiff function to PHP), then we write:

CREATE OR REPLACE FUNCTION unix_timestamp(timestamp without time zone)
  RETURNS integer AS
$BODY$
SELECT date_part('epoch', $1::timestamp)::INTEGER AS RESULT
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

- remove all performance hacks in queries. Without the deepest knowledge of PostgreSQL, this is all useless. No matter how much I experimented, the postgre optimizer turned out to be smarter, although with MySQl I have no such problems.
- remove all sorts of indications of the use of indexes, etc.
- study postgre data types, because all tables will have to be transferred very carefully. The same some_id int(10) unsigned not null auto_increment turns into some_id serial
- well, flip through the documentation at your leisure, because MySQL is terribly underdeveloped compared to Posgre.
- here you should write your own wrapper around pg_prepare and other functions, I made myself like the one I had for MySQL, but I haven’t licked all the nuances yet.
- Take into account that postgre stores prepared requests per connection, this must be taken into account, otherwise pg_prepare throws an error. I made myself something like:

private static function getPrepared()
    {
    $result=pg_query(self::$pgConn, 'SELECT name FROM pg_prepared_statements');
    while ($data=pg_fetch_object($result))
      {
      self::$prepareds[(string)$data->name]=1;
      }
    }

- as soon as you load the database 1s, it is better to perform analyze on all tables
- tune the database by editing the config
- If you use View on 1c tables (I create them in batches, because there is no desire to work with document132._field1439_rref), then create them in a separate schema and drop it before making major changes to the configuration (and don't forget about backups). When making serious changes, say adding a prop, 1c will try to drop the table (yes, they don’t know what alter is), but if there are dependencies on this table, it will give a critical error. With such an error, in no case should you close the configurator, you can simply remove the dependencies and click update again. If you closed the configurator, then you will have a very exciting quest (although there are 2.5 options for solving the problem).
- Similarly, you need to remember about triggers and your own indexes on tables, they will not prevent 1s from dropping a table, but they themselves will naturally die along with it.
Starting with version 8.2, 1s has a mechanism for connecting to external data sources. In 8.3, you can even work with them to write and modify quite natively, as well as use functions. A huge plus is that by picking up a table with the required list of fields, you can use the whole thing as a prop in all configuration elements, as well as use it in queries. The only bug that I see in both 8.2 and 8.3: the connection to the database, that is, is permanent, then it displays the connection window, then it hangs on it. Decision:

//mydb - имя источника данных заданное в 1с
Параметры = ВнешниеИсточникиДанных.mydb.ПолучитьОбщиеПараметрыСоединения();
Параметры.АутентификацияСтандартная = Истина;
Параметры.ИмяПользователя = "user";
Параметры.Пароль = "password";
Параметры.СтрокаСоединения = "DRIVER={PostgreSQL Unicode(x64)};SERVER=127.0.0.1;port=5432;UID=user;PWD=password;DATABASE=somedb";
Параметры.СУБД = "PostgreSQL";

ВнешниеИсточникиДанных.mydb.УстановитьОбщиеПараметрыСоединения(Параметры);
ВнешниеИсточникиДанных.mydb.УстановитьПараметрыСоединенияПользователя(ИмяПользователя(), Параметры);
ВнешниеИсточникиДанных.mydb.УстановитьПараметрыСоединенияСеанса(Параметры);

ВнешниеИсточникиДанных.mydb.УстановитьСоединение();

Well, that seems to be all that I remembered so far 8)