---
metadata:
  - name: generator
    content: Diplodoc Platform v5.52.0
alternate:
  - https://ydb.tech/docs/en/postgresql/import.md?version=v25.3
  - https://ydb.tech/docs/ru/postgresql/import.md?version=v25.3
  - href: en/postgresql/import.md
    type: text/markdown
    title: Markdown version
  - href: ../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/postgresql/import.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Dump data form PostgreSQL

<!-- source: en/postgresql/_includes/alert_preview.md -->
{% note warning %}

At the moment, YDB's compatibility with PostgreSQL **is under development**, so not all PostgreSQL constructs and [functions](https://ydb.tech/docs/en/postgresql/functions.md?version=v25.3) are supported yet. PostgreSQL compatibility is available for testing in the form of a Docker container, which can be deployed by following these [instructions](https://ydb.tech/docs/en/postgresql/docker-connect.md?version=v25.3).

{% endnote %}
<!-- endsource: en/postgresql/_includes/alert_preview.md -->

Data from PostgreSQL can be migrated to YDB using utilities such as [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html), [psql](https://www.postgresql.org/docs/current/app-psql.html), and [YDB CLI](https://ydb.tech/docs/en/reference/ydb-cli/index.md?version=v25.3). The [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html) and [psql](https://www.postgresql.org/docs/current/app-psql.html) utilities are installed with PostgreSQL. [YDB CLI](https://ydb.tech/docs/en/reference/ydb-cli/index.md?version=v25.3) is YDB's command-line client, which is [installed separately](https://ydb.tech/docs/en/reference/ydb-cli/install.md?version=v25.3).

To do this, you need to:

1. Create a data dump using [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html) with the following parameters:

    * `--inserts` — to add data using the [INSERT](https://ydb.tech/docs/en/postgresql/statements/insert_into.md?version=v25.3) command, instead of the [COPY](https://www.postgresql.org/docs/current/sql-copy.html) protocol.
    * `--column-inserts` — to add data using the [INSERT](https://ydb.tech/docs/en/postgresql/statements/insert_into.md?version=v25.3) command with column names.
    * `--rows-per-insert=1000` — to insert data in batches to speed up the process.
    * `--encoding=utf_8` — YDB only supports string data in [UTF-8](https://en.wikipedia.org/wiki/UTF-8).

2. Convert the dump to a format supported by YDB using the `ydb tools pg-convert` command from [YDB CLI](https://ydb.tech/docs/en/reference/ydb-cli/index.md?version=v25.3).
3. Load the result into YDB in PostgreSQL compatibility mode.


## pg-convert command {#pg-convert}

The `ydb tools pg-convert` command reads a dump file or standard input created by the [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html) utility, performs transformations, and outputs to standard output a dump that can be sent to YDB's PostgreSQL-compatible middleware.

`ydb tools pg-convert` performs the following transformations:

* Moving the creation of the primary key into the body of the [CREATE TABLE](https://ydb.tech/docs/en/postgresql/statements/create_table.md?version=v25.3) command.
* Removing the `public` schema from table names.
* Deleting the `WITH (...)` section in `CREATE TABLE`.
* Commenting out unsupported constructs (optionally):

    * `SELECT pg_catalog.set_config.*`
    * `ALTER TABLE`

If the CLI cannot find a table's primary key, it will automatically create a [BIGSERIAL](https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL) column named `__ydb_stub_id` as the primary key.

The general form of the command:

```bash
ydb [global options...] tools pg-convert [options...]
```

* `global options` — [global parameters](https://ydb.tech/docs/en/reference/ydb-cli/commands/global-options.md?version=v25.3).
* `options` — [subcommand parameters](#options).

### subcommand parameters {#options}

| Name                  | Description |
|-----------------------|-------------|
| `-i`                  | The name of the file containing the original dump. If the option is not specified, the dump is read from standard input. |
| `--ignore-unsupported`| When this option is specified, unsupported constructs will be commented out in the resulting dump and duplicated in standard error. By default, if unsupported constructs are detected, the command returns an error. This does not apply to `ALTER TABLE` expressions that define a table's primary key, as they are commented out in any case. |


{% note warning %}

When loading large dumps, reading from standard input is not recommended because the entire dump will be stored in RAM. It is advised to use the file option, in which case the CLI will only keep a small portion of the dump in memory.

{% endnote %}

## Example of importing a dump into YDB {#examples}

As an example, data generated by [pgbench](https://www.postgresql.org/docs/current/pgbench.html) will be loaded.

1. Start Docker containers with PostgreSQL and YDB:

    ```bash
    docker run --name postgres_container \
        -e POSTGRES_USER=pgroot -e POSTGRES_PASSWORD=1234 \
        -e POSTGRES_DB=local \
        -p 5433:5433 -d postgres:14 -c 'port=5433'
    docker run --name ydb-postgres -d --pull always -p 5432:5432 -p 8765:8765 \
        -e POSTGRES_USER=ydbroot -e POSTGRES_PASSWORD=4321 \
        -e YDB_FEATURE_FLAGS=enable_temp_tables,enable_table_pg_types \
        -e YDB_USE_IN_MEMORY_PDISKS=true \
        ghcr.io/ydb-platform/local-ydb:latest
    ```

    Information about the started Docker containers:

    #|
    || Database | PostgreSQL | YDB ||
    || Container name | postgres_container | ydb-postgres ||
    || Address | postgres://pgroot:1234@localhost:5433/local | postgresql://ydbroot:4321@localhost:5432/local ||
    || Port | 5433 | 5432 ||
    || User name | pgroot | ydbroot ||
    || Password | 1234 | 4321 ||
    |#

2. Generate data through [pgbench](https://www.postgresql.org/docs/current/pgbench.html):

    ```bash
    docker exec postgres_container pgbench postgres://pgroot:1234@localhost:5433/local -i
    ```

3. Create a dump of the database using [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html):

    ```bash
    docker exec postgres_container pg_dump postgres://pgroot:1234@localhost:5433/local --inserts \
        --column-inserts --encoding=utf_8 --rows-per-insert=1000 > dump.sql
    ```

4. Load the dump into YDB:

    ```bash
    ydb tools pg-convert --ignore-unsupported -i dump.sql | psql postgresql://ydbroot:4321@localhost:5432/local
    ```

    This command uses YDB CLI to convert the dump.sql file to the format readable by the YDB PostgreSQL compatibility layer. The converted dump file is then redirected to the `psql` utility for loading the data into YDB via PostgreSQL protocol.