Migrate data from PostgreSQL to Managed PostgreSQL using Trino

Last update: October 28 2026Open in Markdown

You can migrate tables from one or more PostgreSQL databases to a PostgreSQL Managed Database using Cloud for Trino. This method is suitable if you need to migrate individual tables, filter, or transform data during migration.

The server with the source database and the PostgreSQL cluster with the target database connect to Cloud for Trino as two catalogs using the PostgreSQL connector. A PostgreSQL catalog connects to one database: if you are migrating multiple databases, you need to add a pair of catalogs for each. Trino reads data from the source database and writes it to the target database in a single SQL query, without intermediate files and dumps.

When migrating data using Trino, limitations apply.

Limitations

Data migration guarantee	Cloud for Trino is in public preview: the product is provided without an SLA; service availability is not guaranteed; failures in product operation are possible. Back up the source tables before migrating critical data
Database objects	The CREATE TABLE ... AS SELECT query migrates only table data: the table is created without primary and foreign keys, indexes, constraints, default values, sequences, triggers, or functions. Create them after migration using standard PostgreSQL queries via the system.execute procedure — for example, with the query CALL <target_catalog>.system.execute(query => 'ALTER TABLE <schema>.<table_name> ADD PRIMARY KEY (<column>)'). You can also create the table structure in the target database in advance from the output of pg_dump --schema-only and migrate data using the INSERT INTO ... SELECT query instead of CREATE TABLE ... AS SELECT. If you need to migrate the entire database along with objects, use logical replication or a logical dump. Learn more in the Migrate PostgreSQL databases to PostgreSQL Managed Databases instructions
Data types	Column types in the target database may differ from the source: for example, json is written as jsonb. Arrays and unsupported data types are skipped by default, but you can migrate them; learn more in the Migrate data using Trino subsection. Learn more about such data types in the Type mapping section of the Trino documentation
Sequences	A column of type serial is migrated as integer without a default value. If the application inserts rows without an id, create a sequence and set its value higher than the maximum id
Views	The SHOW TABLES query displays both tables and views. From a view, the CREATE TABLE ... AS SELECT query will create a regular table with data as of the migration time
Data consistency	Each table is read at a specific point in time. If you do not stop writing to the source database during migration, the DBMS will continue write operations to tables, causing some data to become inconsistent. Stop writing to the source database for the duration of the data migration or re-migrate modified data before switching services
Disk space	When executing an INSERT query, Trino first writes data to a temporary table and then moves it to the target table. During loading, there must be enough disk space on the target cluster for the volume of data being added
Large tables	The PostgreSQL connector reads each table with a single query, so the migration speed of one large table is limited by the connection bandwidth to the source database and the database performance. Split a large table into parts by key ranges and execute multiple INSERT INTO ... SELECT ... WHERE id BETWEEN ... queries in parallel
Data migration over the internet	When migrating data over the internet, migration speed depends on the network bandwidth between pools. Traffic during data transfer is encrypted, but the PostgreSQL cluster's public floating IP address is accessible from the internet — if necessary, restrict access with a security group and detach the public IP address after migration if it is no longer needed. If you want to migrate data from Trino to a PostgreSQL Managed Database via a private subnet, the target PostgreSQL cluster and the Trino cluster must be located in the same pool and same private subnet

Migrate data

1. Prepare the server with the source PostgreSQL database.
2. Prepare the target PostgreSQL cluster.
3. Create a Trino cluster with catalogs.
4. Connect to the Trino cluster.
5. Check catalogs.
6. Migrate data using Trino.
7. Check the result.
8. Switch services.

1. Prepare the server with the source PostgreSQL database

To protect data from modification in the source database, Trino will connect to it using a dedicated read-only user.

1. Connect to the source PostgreSQL database as a user with privileges to create roles.

2. Create a new user and grant read-only permissions using the SELECT privilege:

   CREATE USER <user_name> WITH PASSWORD '<password>';
   GRANT CONNECT ON DATABASE <source_database> TO <user_name>;
   GRANT USAGE ON SCHEMA public TO <user_name>;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;

   Specify:

   • <user_name> — username;
   • <password> — user password;
   • <source_database> — name of the database from which to migrate data.

   If you are migrating multiple schemas, grant USAGE and SELECT privileges on each schema.

3. Allow connections to the source database — add the following line to the pg_hba.conf file:

   host <source_database> <user_name> 0.0.0.0/0 <authentication_method>

   Specify:

   • <source_database> — name of the database from which to migrate data;
   • <user_name> — username;
   • <authentication_method> — authentication method used on the server, usually scram-sha-256. If the server uses md5, specify it instead of scram-sha-256.

4. Make sure that PostgreSQL accepts connections on the external interface: in the postgresql.conf file, the value of the listen_addresses parameter must contain the public IP address of the server or *. Changes to the listen_addresses parameter take effect only after restarting PostgreSQL.

5. Apply the changes to the pg_hba.conf file:

   SELECT pg_reload_conf();

6. Make sure that the server is accessible from the internet via a public IP address or DNS address.

7. If traffic filtering is used on the server, allow incoming connections to the PostgreSQL port (usually 5432) in the firewall or cloud provider security groups.

2. Prepare the target PostgreSQL cluster

For your information

Cloud for Trino is only available in pool ru-7. If you want to migrate data from Trino to a PostgreSQL Managed Database via a private subnet, create a PostgreSQL cluster in the ru-7 pool and use the same subnet for PostgreSQL and Trino clusters.

If the PostgreSQL and Trino clusters are in different pools, Trino will be able to connect to PostgreSQL only over the internet via a public floating IP address.

1. Create a PostgreSQL cluster. When creating the cluster:

   • if you want to migrate data via a private subnet, select location — pool ru-7;

   • select a PostgreSQL version not lower than the version of the source database;

   • select a node configuration with a disk size sufficient for the amount of data being migrated.

2. Create a user.

3. Create a database.

4. Grant user access to the created database as an owner. The database owner can create schemas and tables in it — this is required for Trino to write data.

5. If you want to migrate data over the internet, attach a public floating IP address to the cluster.

3. Create a Trino cluster with catalogs

A PostgreSQL catalog connects to one database: if you are migrating multiple databases, add a pair of catalogs for each.

Use the Create Trino cluster instructions. When creating the cluster:

• in the Network block:

  • if the target PostgreSQL cluster is created in the ru-7 pool, select the target cluster subnet;

  • to connect to Trino from the internet, select the External access to GUI checkbox;

• in the Catalogs block, add two catalogs with the PostgreSQL connector type for the source and target databases. When adding catalogs, use the connection parameters:

Parameter	Catalog of the server with the source database	Catalog of the target cluster
Host	Public IP address or DNS address of the server	if the PostgreSQL cluster is in the ru-7 pool — DNS address of the cluster master node; you can copy it in the Control panel: in the top menu, click Products → Managed Databases → Active tab → cluster page → Connection tab → Connection addresses block → node row → in the DNS address column, click . The address is bound to the master node role and will not change if a replica becomes the master; if the PostgreSQL cluster is not in the ru-7 pool — public IP address of the cluster; you can copy it in the Control panel: in the top menu, click Products → Managed Databases → Active tab → cluster page → Connection tab → Connection addresses block → node row → in the IP address column, click next to the public IP address
Port	Port of the source database, usually 5432	5432 — direct connection to PostgreSQL. Do not use port 5433 — it goes through a connection pooler, and prepared statements used by the Trino JDBC driver in transaction pooling mode may result in errors
Database	Name of the source database	Name of the database you created when preparing the target PostgreSQL cluster in step 3
Username	Username of the user you created when preparing the server with the source PostgreSQL database in step 2	Username of the user you created when preparing the target PostgreSQL cluster in step 2
Password	Password of the user you created when preparing the server with the source PostgreSQL database in step 2	Password of the user you created when preparing the target PostgreSQL cluster in step 2

4. Connect to the Trino cluster

You can connect to the cluster via the Trino GUI, the Trino CLI console client, and DBeaver.

Trino GUI

1. In the Control panel, in the top menu, click Products and select Trino.

2. In the cluster card, in the GUI column, click .

3. Enter the login — admin.

4. Enter the administrator password — set when creating the cluster. After creating the cluster, the password cannot be viewed in the control panel, but can be changed.

5. Click Log In.

Trino CLI

1. Open the CLI.

2. Connect to the cluster:

   ./trino --server https://<cluster_id>.c.trino.data-platform.selcloud.ru --user admin --password

   Specify <cluster_id> — cluster UUID. You can copy it in the Control panel: in the top menu, click Products → Trino → in the cluster's menu, select Copy UUID.

3. Enter the administrator password — set when creating the cluster. After creating the cluster, the password cannot be viewed in the control panel, but can be changed.

DBeaver

1. Open DBeaver.

2. In the top menu, click Database → New Connection.

3. Select Trino.

4. Click Next.

5. In the Host field, enter <cluster_id>.c.trino.data-platform.selcloud.ru.

   Specify <cluster_id> — cluster UUID. You can copy it in the Control panel: in the top menu, click Products → Trino → in the cluster's menu, select Copy UUID.

6. In the Port field, enter 443.

7. Enter the login — admin.

8. Enter the administrator password — set when creating the cluster. After creating the cluster, the password cannot be viewed in the control panel, but can be changed.

9. Open the Driver properties tab and set the SSL property to true.

10. Click Test Connection.

11. Click Finish.

5. Check catalogs

Display the list of catalogs, schemas, and tables of the source database, and schemas of the target database:

SHOW CATALOGS;
SHOW SCHEMAS FROM <source_catalog>;
SHOW TABLES FROM <source_catalog>.public;
SHOW SCHEMAS FROM <target_catalog>;

Specify:

• <source_catalog> — catalog name of the server with the source database;
• <target_catalog> — catalog name of the target cluster.

The catalog list must contain both created catalogs, and the <source_catalog>.public schema must contain the tables of the source database.

6. Migrate data using Trino

1. Optional: to migrate array columns and columns with unsupported data types, configure data type mapping. By default, the PostgreSQL connector skips such columns — they will not be added to the target table:

   SET SESSION <source_catalog>.array_mapping = 'AS_ARRAY';
   SET SESSION <source_catalog>.unsupported_type_handling = 'CONVERT_TO_VARCHAR';

   <source_catalog> — catalog name of the server with the source database.

   The AS_ARRAY value migrates fixed-dimension arrays; for arrays of arbitrary dimensions, use the AS_JSON value. The CONVERT_TO_VARCHAR value migrates columns with unsupported types as text. Learn more in the Type mapping section of the Trino documentation.

2. If you are migrating a schema other than public, create it in the target database:

   CREATE SCHEMA IF NOT EXISTS <target_catalog>.<schema>;

   Specify:

   • <target_catalog> — catalog name of the target cluster;
   • <schema> — schema name.

3. Migrate the table:

   CREATE TABLE <target_catalog>.public.<target_table> AS
   SELECT * FROM <source_catalog>.public.<source_table>;

   Specify:

   • <target_catalog> — catalog name of the target cluster;
   • <target_table> — target table name;
   • <source_catalog> — catalog name of the server with the source database;
   • <source_table> — source table name.

   To migrate a subset of data or modify it during migration, add filters and expressions to the query — for example, a WHERE condition by date or the lower() function to convert column values to lowercase.

   The output will show the number of rows written.

4. Optional: load additional rows that appeared in the source database after migration:

   INSERT INTO <target_catalog>.public.<target_table>
   SELECT * FROM <source_catalog>.public.<source_table>
   WHERE <column> > (SELECT coalesce(max(<column>), 0) FROM <target_catalog>.public.<target_table>);

   Specify:

   • <target_catalog> — catalog name of the target cluster;
   • <target_table> — target table name;
   • <source_catalog> — catalog name of the server with the source database;
   • <source_table> — source table name;
   • <column> — column with an incrementing row identifier.

   The query is suitable for tables where rows are inserted only with an incrementing identifier. Row modifications and deletions in the source database cannot be migrated this way.

5. Repeat steps 1–4 for each table.

7. Check the result

1. Compare the number of rows in the source and target tables:

   SELECT
     (SELECT count(*) FROM <source_catalog>.public.<source_table>) AS source_rows,
     (SELECT count(*) FROM <target_catalog>.public.<target_table>) AS target_rows;

   Specify:

   • <source_catalog> — catalog name of the server with the source database;
   • <source_table> — source table name;
   • <target_catalog> — catalog name of the target cluster;
   • <target_table> — target table name.

2. Optional: find rows in the source table that are missing from the target table:

   SELECT * FROM <source_catalog>.public.<source_table>
   EXCEPT
   SELECT * FROM <target_catalog>.public.<target_table>;

   Specify:

   • <source_catalog> — catalog name of the server with the source database;
   • <source_table> — source table name;
   • <target_catalog> — catalog name of the target cluster;
   • <target_table> — target table name.

   An empty output means that all rows have been migrated. On large tables, the query creates a significant load on both databases. Columns of type json cannot be compared in EXCEPT — list the columns explicitly or limit yourself to comparing row counts.

3. Display the list of columns of the target table:

   SHOW COLUMNS FROM <target_catalog>.public.<target_table>;

   Specify:

   • <target_catalog> — catalog name of the target cluster;
   • <target_table> — target table name.

4. Compare the target table columns obtained in step 3 with the source table structure — for example, with the output of the \d <source_table> command in psql, where <source_table> is the source table name. Columns with data types skipped by Trino are not visible in the catalog — compare them directly with the source table structure.

8. Switch services

1. Switch services to the target cluster — learn more in the Connect to PostgreSQL cluster instructions. For access to the cluster from the internet, attach a public floating IP address.

2. If you attached a public floating IP address to the target PostgreSQL cluster and no longer need it, detach it.

3. If you no longer need the Trino cluster, delete it.

4. If you no longer need the server with the source MySQL database, delete it.