---
title: "PostgreSQL to PostgreSQL migration guide"
description: "Convert a PostgreSQL dump into a PostgreSQL script with the same tables, keys, indexes and data: how to dump, convert, load, and every rule the converter applies."
canonical: https://intosql.com/docs/migrations/postgresql-to-postgresql
last_updated: 2026-10-15
---

# PostgreSQL to PostgreSQL migration guide

IntoSQL reads a plain PostgreSQL dump and writes one PostgreSQL script with the same tables, columns, keys, indexes, foreign keys, defaults, identities, enum types, comments and rows. It reads the dump itself, without a database server, and lists in the report every value PostgreSQL can't hold as it is and every object it doesn't carry. The script loads on PostgreSQL 13 to 18.

**Convert in the browser:** [PostgreSQL to PostgreSQL](/postgresql-to-postgresql) opens the converter set up for this pair.

## Dump for the best result

Any plain dump from `pg_dump` or `pg_dumpall` from PostgreSQL 10 on works, as `.sql` or `.sql.gz`, with `COPY` rows (the default) or `--inserts`:

```shell
pg_dump --no-owner --no-privileges shop | gzip > shop.sql.gz
```

- `--no-owner --no-privileges` leave out roles and grants, which the converter skips anyway.
- A custom or directory archive (`-Fc`, `-Fd`) is binary: `pg_restore -f shop.sql shop.backup` turns it into plain SQL without a server. The converter says so when it gets an archive (`dump_archive`).
- `pg_dumpall` writes every database in one file; each database's tables are named after it, so nothing collides.

A dump made with `--data-only` still converts: the types are read from the values, and the report says the schema was missing (`no_schema`). Partitioned tables arrive as one table with every partition's rows (`partitions_dropped`).

## Convert

In the browser, open [PostgreSQL to PostgreSQL](/postgresql-to-postgresql) or the converter on the home page, drop the dump and choose PostgreSQL. It reads the file from your disk in pieces, twice, and writes the script as it goes, so the dump is never uploaded and its size isn't limited by memory. The [desktop app](/docs/desktop) does the same with the file on disk.

With the [command line](/docs/cli):

```shell
intosql inspect shop.sql.gz
intosql convert shop.sql.gz --to postgresql --output shop.postgresql.sql --report shop.report.json
```

`inspect` lists the tables, their rows and what won't be converted, without writing anything. `convert` writes the script and, with `--report`, the report as JSON.

Through the [API](/docs/api), a dump converts like any other file: open a conversion with its name ending in `.sql` or `.sql.gz` and `"output": {"dialect": "postgresql"}`.

## Load into PostgreSQL

```shell
createdb shop
psql -v ON_ERROR_STOP=1 -d shop -f shop.postgresql.sql
```

The script creates the schemas, enum types and tables without keys, copies the rows with `COPY`, then adds keys, indexes, foreign keys and identities, so it loads fast and stops at the first error. Names, values and types stay as the dump wrote them, so a dump from PostgreSQL 18 loads on 13. Foreign keys are added as `NOT VALID` and then validated, and `CHECK ... NOT VALID` constraints are added after the rows, still not valid. Each identity continues from the value its sequence had reached.

### Or load it straight in

`intosql load` and the [desktop app](/docs/desktop) load the dump into PostgreSQL without a script, with the same tables, keys and indexes the script creates:

```shell
intosql load shop.sql.gz --into postgres://ana@localhost/shop
```

It stops before writing anything when one of the dump's tables already exists. It loads in one transaction, so a failed load leaves nothing. Loading into a database server is part of IntoSQL Pro, with a 14-day trial. See [Loading](/docs/loading#database-dumps).

## What isn't converted

Views, materialized views, functions, procedures, triggers, rules, row security policies, extensions and types are written in PostgreSQL's own language. They are listed in the report by kind and name, never dropped silently, so you can recreate them in PostgreSQL.


## Data types

[PostgreSQL data types between versions](/docs/types/postgresql-to-postgresql) lists each type that changes in the script: what it becomes, why, and the report line.

## Every other rule

Generated from the engine, where each rule is decided.

| In the dump | In the script | Why | Report line |
| --- | --- | --- | --- |
| citext | text, compared exactly | The script doesn't create the citext extension, so these columns compare text exactly; create it and change the columns back after loading. | `case_insensitive_collation` |
| generated columns | the same generated columns | The same formula, computed by the target. | None |
| generated columns with functions IntoSQL doesn't write | a plain column with the values | A formula IntoSQL can't write leaves a plain column with the dump's values and a report line. | `generated_not_translated` |
| defaults with functions IntoSQL doesn't write | no default | A default IntoSQL can't write is left out and reported. | `default_not_translated` |
| CHECK constraints with functions IntoSQL doesn't write | not created | A check IntoSQL can't write is left out and reported. | `check_skipped` |
| CHECK ... NOT VALID | added NOT VALID after the rows | The rows that were there before were never checked, so the check is added after them, still not valid. | None |
| gin, gist, brin and hash indexes, partial and expression indexes | not created | IntoSQL writes b-tree indexes on columns; create the others again after loading. | `index_skipped` |
| foreign keys to tables the dump doesn't create | not created | A key can't name a table that isn't there. | `foreign_key_skipped` |
| NOT NULL columns with values that become NULL | NOT NULL dropped | A column whose values had to become NULL can't keep NOT NULL, or the load would stop. | `not_null_relaxed` |
| partitioned tables | one table with every partition's rows | The rows are the same; partition the table again if it needs it. | `partitions_dropped` |
| views, functions, procedures, triggers, rules, policies, extensions and types other than enums | listed in the report | IntoSQL converts tables and their rows; the rest is listed for you to recreate. | `not_converted_objects` |
| dumps without CREATE TABLE (--data-only) | types read from the values | A data-only dump has only rows, so the types come from the values. | `no_schema` |
| expressions in INSERT values | NULL | A converter that doesn't run a database can't evaluate them. | `expression_value` |
| text decoded twice (ñ) | copied as it is | The dump holds exactly these characters, so they are copied; the report points them out. | `mojibake_suspected` |