# Migrate from PostgreSQL > Step-by-step guide on migrating from PostgreSQL to GreptimeDB, including creating databases, writing data, exporting and importing data. # Migrate from PostgreSQL This document will guide you through the migration process from PostgreSQL to GreptimeDB. ## Before you start the migration Please be aware that though GreptimeDB supports the wire protocol of PostgreSQL, it does not mean GreptimeDB implements all PostgreSQL's features. You may refer to: * The [ANSI Compatibility](/reference/sql/compatibility.md) to see the constraints regarding using SQL in GreptimeDB. * The [Data Modeling Guide](/user-guide/deployments-administration/performance-tuning/design-table.md) to create proper table schemas. * The [Data Index Guide](/user-guide/manage-data/data-index.md) for index planning. ## Migration steps ### Create the databases and tables in GreptimeDB Before migrating the data from PostgreSQL, you first need to create the corresponding databases and tables in GreptimeDB. GreptimeDB has its own SQL syntax for creating tables, so you cannot directly reuse the table creation SQLs that are produced by PostgreSQL. When you write the table creation SQL for GreptimeDB, it's important to understand its "[data model](/user-guide/concepts/data-model.md)" first. Then, please take the following considerations in your create table SQL: 1. Since the time index column cannot be changed after the table is created, you need to choose the time index column carefully. The time index is best set to the natural timestamp when the data is generated, as it provides the most intuitive way to query the data, and the best query performance. For example, in the IOT scenes, you can use the collection time of sensor data as the time index; or the occurrence time of an event in the observability scenes. 2. In this migration process, it's not recommend to create another synthetic timestamp, such as a new column created with `DEFAULT current_timestamp()` as the time index column. It's not recommend to use the random timestamp as the time index either. 3. It's vital to set the most fit timestamp precision for your time index column, too. Like the chosen of time index column, the precision of it cannot be changed as well. Find the most fit timestamp type for your data set [here](/reference/sql/data-types#data-types-compatible-with-mysql-and-postgresql). 4. Choose a primary key only when it is truly needed. The primary key in GreptimeDB is different from that in PostgreSQL. You should use a primary key only when: * Most queries can benefit from the ordering. * You need to deduplicate (including delete) rows by the primary key and time index. Otherwise, setting a primary key is optional and it may hurt performance. Read [Primary Key](/user-guide/deployments-administration/performance-tuning/design-table.md#primary-key) for details. Finally please refer to "[CREATE](/reference/sql/create.md)" SQL document for more details for choosing the right data types and "ttl" or "compaction" options, etc. 5. Choose proper indexes to speed up queries. * Inverted index: is ideal for filtering by low-cardinality columns and quickly finding rows with specific values. * Skipping index: works well with sparse data. * Fulltext index: enables efficient keyword and pattern search in large text columns. For details and best practices, refer to the [data index](user-guide/manage-data/data-index.md) documentation. ### Write data to both GreptimeDB and PostgreSQL simultaneously Writing data to both GreptimeDB and PostgreSQL simultaneously is a practical strategy to avoid data loss during migration. By utilizing PostgreSQL's client libraries (JDBC + a PostgreSQL driver), you can set up two client instances - one for GreptimeDB and another for PostgreSQL. For guidance on writing data to GreptimeDB using SQL, please refer to the [Ingest Data](/user-guide/ingest-data/for-iot/sql.md) section. If retaining all historical data isn't necessary, you can simultaneously write data to both GreptimeDB and PostgreSQL for a specific period to accumulate the required recent data. Subsequently, cease writing to PostgreSQL and continue exclusively with GreptimeDB. If a complete migration of all historical data is needed, please proceed with the following steps. ### Export data from PostgreSQL [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html) is a commonly used tool to export data from PostgreSQL. Using it, we can export the data that can be later imported into GreptimeDB directly. For example, if we want to export schemas whose names start with `db` in the database `postgres` from PostgreSQL, we can use the following command: ```bash pg_dump -h127.0.0.1 -p5432 -Upostgres -ax --column-inserts --no-comments -n 'db*' postgres | grep -v "^SE" > /path/to/output.sql ``` Replace the `-h`, `-p` and `-U` flags with the appropriate values for your PostgreSQL server. The `-n` flag is used to specify the schemas to be exported. And the database `postgres` is at the end of the `pg_dump` command line. Note that we pipe the `pg_dump` output through a special `grep`, to remove some unnecessary `SET` or `SELECT` lines. The final output will be written to the `/path/to/output.sql` file. The content in the `/path/to/output.sql` file should be like this: ```plaintext ~ ❯ cat /path/to/output.sql -- -- PostgreSQL database dump -- -- Dumped from database version 16.4 (Debian 16.4-1.pgdg120+1) -- Dumped by pg_dump version 16.4 -- -- Data for Name: foo; Type: TABLE DATA; Schema: db1; Owner: postgres -- INSERT INTO db1.foo (ts, a) VALUES ('2024-10-31 00:00:00', 1); INSERT INTO db1.foo (ts, a) VALUES ('2024-10-31 00:00:01', 2); INSERT INTO db1.foo (ts, a) VALUES ('2024-10-31 00:00:01', 3); INSERT INTO ... -- -- Data for Name: foo; Type: TABLE DATA; Schema: db2; Owner: postgres -- INSERT INTO db2.foo (ts, b) VALUES ('2024-10-31 00:00:00', '1'); INSERT INTO db2.foo (ts, b) VALUES ('2024-10-31 00:00:01', '2'); INSERT INTO db2.foo (ts, b) VALUES ('2024-10-31 00:00:01', '3'); INSERT INTO ... -- -- PostgreSQL database dump complete -- ``` ### Import data into GreptimeDB The [psql -- PostgreSQL interactive terminal](https://www.postgresql.org/docs/current/app-psql.html) can be used to we can use the following command to import the data into GreptimeDB: ```bash psql -h127.0.0.1 -p4003 -d public -f /path/to/output.sql ``` Replace the `-h` and `-p` flags with the appropriate values for your GreptimeDB server. The `-d` of the psql command is used to specify the database and cannot be omitted. The value `public` of `-d` is the default database used by GreptimeDB. You can also add `-a` to the end to see every executed line, or `-s` for entering the single-step mode. To summarize, data migration steps can be illustrate as follows: ![migrate postgresql data steps](/migration-postgresql.jpg) After the data migration is completed, you can stop writing data to PostgreSQL and continue using GreptimeDB exclusively! If you need a more detailed migration plan or example scripts, please provide the specific table structure and data volume. The [GreptimeDB official community](https://github.com/orgs/GreptimeTeam/discussions) will offer further support. Welcome to join the [Greptime Slack](http://greptime.com/slack).