Een PostgreSQL-dump verhuizen naar een oudere of nieuwere PostgreSQL

Sleep een gewone dump van pg_dump of pg_dumpall, als .sql of .sql.gz, hierheen en download één script dat laadt op PostgreSQL 13 tot en met 18, welke versie de dump ook schreef. Identities, enums, arrays en jsonb blijven zoals ze zijn, typen van een extensie worden text, en elke wijziging staat in het rapport.

Migratiehandleiding PostgreSQL naar PostgreSQL Datatypen van PostgreSQL naar PostgreSQL

Je bestand

sqlshop_2024_postgres.sqlVoorbeeldbestand, 6,2 kB

18 rijen geconverteerd, geen correcties, geen afgewezen

TabellenSQLRapport

Tabellen in shop_2024_postgres.sqlPostgreSQL 18.6-dump · geschreven door pg_dump · utf8mb4

customers8 rijen
8 kolommen
id integer not null
name text not null
email text not null
country varchar(2) not null
is_active boolean not null
tags text[] not null
signed_up date
created_at timestamptz(6) not null

orders10 rijen

10 kolommen
id	integer not null
customer_id	integer not null
status	order_status not null
total	numeric(10,2) not null
items	smallint not null
ordered_at	timestamp(6) not null
shipped_at	timestamp(6)
note	text
details	jsonb
delivery	interval

Niet geconverteerd

Weergave paid_orders

Elke database schrijft deze in een eigen taal. Herschrijf ze voor PostgreSQL.

-- Written by IntoSQL from a PostgreSQL dump. Load it with: psql -v ON_ERROR_STOP=1 -f <file>
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET synchronous_commit = off;
SET maintenance_work_mem = '512MB';

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');
CREATE TABLE customers (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    name text NOT NULL,
    email text NOT NULL,
    country varchar(2) DEFAULT 'US' NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    tags text[] DEFAULT '{}' NOT NULL,
    signed_up date,
    created_at timestamptz(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE orders (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    customer_id integer NOT NULL,
    status order_status DEFAULT 'pending' NOT NULL,
    total numeric(10,2) NOT NULL,
    items smallint DEFAULT 1 NOT NULL,
    ordered_at timestamp(6) NOT NULL,
    shipped_at timestamp(6),
    note text,
    details jsonb,
    delivery interval,
    CONSTRAINT orders_total_check CHECK (total >= (0))
);

COPY customers (id, name, email, country, is_active, tags, signed_up, created_at) FROM stdin;
1	Ana Martínez	ana@example.com	MX	t	{vip}	2023-01-15	2023-01-15 10:24:00+00
2	Zoë Müller	zoe@example.de	DE	t	{}	2023-02-03	2023-02-03 08:11:42+00
3	José Peña	jose@example.es	ES	f	{wholesale,"late payer"}	2023-03-21	2023-03-21 19:05:17+00
4	李雷	lilei@example.cn	CN	t	{}	2023-05-09	2023-05-09 02:40:00+00
5	Noah O'Brien	noah@example.ie	IE	t	{}	\N	2023-06-30 14:00:00+00
6	Amélie Laurent	amelie@example.fr	FR	t	{vip}	2024-01-02	2024-01-02 09:30:00+00
7	Ravi Patel	ravi@example.in	IN	f	{}	2024-02-29	2024-02-29 23:59:59+00
8	Sofia Rossi 🌻	sofia@example.it	IT	t	{}	2024-04-12	2024-04-12 12:12:12+00
\.

COPY orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, delivery) FROM stdin;
1	1	shipped	129.90	3	2024-03-01 10:00:00	2024-03-02 16:30:00	\N	{"gift": false}	1 day 06:30:00
2	2	paid	54.00	1	2024-03-02 11:15:00	\N	Gift wrap 🎁	{"gift": true, "wrap": "red"}	\N
3	1	cancelled	12.50	1	2024-03-03 09:45:00	\N	Customer changed their mind	\N	\N
4	4	shipped	980.00	12	2024-03-05 02:10:00	2024-03-07 08:00:00	请小心轻放	{"fragile": true}	2 days 05:50:00
5	5	pending	45.75	2	2024-03-08 17:20:00	\N	Leave at the door; ring twice	\N	\N
6	6	shipped	310.40	5	2024-03-10 13:00:00	2024-03-11 09:00:00	Fragile: "glass"	{"fragile": true}	20:00:00
7	8	paid	76.20	2	2024-03-11 18:45:00	\N	\N	\N	\N
8	2	shipped	19.99	1	2024-03-12 08:00:00	infinity	Shipped date set to infinity by an old import	\N	\N
9	3	shipped	64.00	2	2024-03-14 15:30:00	2024-03-15 10:00:00	Two parcels	{"parcels": [1, 2]}	18:30:00
10	7	paid	1499.00	1	2024-03-15 21:05:00	\N	C:\\Users\\ravi\\invoice.pdf	\N	\N
\.


-- Keys, indexes and constraints, after the rows so each builds once.
CREATE UNIQUE INDEX customers_email_key ON customers (email);
CREATE UNIQUE INDEX customers_pkey ON customers (id);
CREATE UNIQUE INDEX orders_pkey ON orders (id);
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status_date ON orders (status, ordered_at);
ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE USING INDEX customers_email_key;
ALTER TABLE customers ADD CONSTRAINT customers_pkey PRIMARY KEY USING INDEX customers_pkey;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey;
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
DO $f$ BEGIN
    ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;
EXCEPTION WHEN foreign_key_violation THEN
    RAISE WARNING 'foreign key % has rows without a parent: it stays NOT VALID, and new rows are checked', 'orders_customer_id_fkey';
END $f$;
SELECT setval(pg_get_serial_sequence('customers', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM customers)::numeric, 0) + 1, 1001), 2147483647)::bigint, false);
COMMENT ON TABLE customers IS 'People who bought at least once';
SELECT setval(pg_get_serial_sequence('orders', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM orders)::numeric, 0) + 1, 5001), 2147483647)::bigint, false);

Correcties

Er hoefde niets gecorrigeerd te worden.

Na te kijken

1 weergave, trigger, routine of gebeurtenis is niet geconverteerd; het rapport noemt ze allemaal. Maak ze met de hand opnieuw aan in PostgreSQL.
De index idx_orders_details op orders gebruikt een expressie die niet voor PostgreSQL kan worden geschreven (a gin index), dus hij wordt niet aangemaakt. Maak de index met de hand aan als query's hem nodig hebben.

Je bestand wordt op deze computer omgezet en nooit geüpload.

Andere voorbeelden:

Wat er verandert tussen het bestand en het script

Elke rij is een regel die de converter toepast, met de rapportregel die hij schrijft als de regel iets in je data verandert.

varchar(n), char(n), text
dezelfde typen
Dezelfde typen, met dezelfde lengtes.
numeric(p,s), numeric without a precision, NaN and Infinity
dezelfde typen en waarden
Hetzelfde type: elk cijfer wordt gekopieerd zoals de dump het schreef.
money
numeric(19,2)
Hoe money wordt geschreven, hangt af van lc_monetary van de server, dus het bedrag blijft een getal zonder valutasymbool en laadt ongeacht de locale van het doel.
Rapportregel: type_replaced
enum types
dezelfde enum-typen, aangemaakt vóór de tabellen
De labels behouden hun volgorde.
enum columns holding values outside their labels
text zonder check
Het type zou die waarden weigeren, dus de kolom is text en behoudt ze allemaal.
Rapportregel: enum_check_skipped
composite and range types the dump defines, extension types (citext, hstore, ltree, PostGIS) and multiranges
text met de tekst van PostgreSQL
Het script maakt geen extensies of samengestelde typen aan, dus elke waarde behoudt de tekst die PostgreSQL schreef; maak het type aan en wijzig de kolom na het laden.
Rapportregel: type_replaced
citext
text, exact vergeleken
Het script maakt de extensie citext niet aan, dus deze kolommen vergelijken tekst exact; maak hem aan en zet de kolommen na het laden terug.
Rapportregel: case_insensitive_collation
json, jsonb
dezelfde typen
json behoudt de tekst zoals geschreven; jsonb behoudt de waarden zoals de server ze normaliseerde.
timestamp with time zone
timestamptz, dezelfde momenten
Elke waarde behoudt de offset die de dump schreef, dus het moment is hetzelfde in elke tijdzone.
time, time with time zone
dezelfde typen
Dezelfde typen, 24:00:00 inbegrepen.
infinity, -infinity and BC dates
behouden
PostgreSQL bewaart ze ongewijzigd.
NaN and Infinity in numeric, real and double precision
behouden
PostgreSQL bewaart ze ongewijzigd.
generated columns
dezelfde gegenereerde kolommen
Dezelfde formule, berekend door het doel.
generated columns with functions IntoSQL doesn't write
een gewone kolom met de waarden
Een formule die IntoSQL niet kan schrijven, levert een gewone kolom met de waarden uit de dump en een regel in het rapport op.
Rapportregel: generated_not_translated
defaults with functions IntoSQL doesn't write
geen standaardwaarde
Een standaardwaarde die IntoSQL niet kan schrijven, wordt weggelaten en gemeld.
Rapportregel: default_not_translated
CHECK constraints with functions IntoSQL doesn't write
niet aangemaakt
Een check die IntoSQL niet kan schrijven, wordt weggelaten en gemeld.
Rapportregel: check_skipped
CHECK ... NOT VALID
na de rijen toegevoegd als NOT VALID
De rijen die er al waren, zijn nooit gecontroleerd, dus de check wordt erna toegevoegd, nog steeds niet gevalideerd.
gin, gist, brin and hash indexes, partial and expression indexes
niet aangemaakt
IntoSQL schrijft b-tree-indexen op kolommen; maak de andere na het laden opnieuw aan.
Rapportregel: index_skipped
foreign keys to tables the dump doesn't create
niet aangemaakt
Een sleutel kan niet verwijzen naar een tabel die er niet is.
Rapportregel: foreign_key_skipped
NOT NULL columns with values that become NULL
NOT NULL weggelaten
Een kolom waarvan waarden NULL moesten worden, kan NOT NULL niet houden, anders zou het laden stoppen.
Rapportregel: not_null_relaxed
partitioned tables
één tabel met de rijen van elke partitie
De rijen zijn dezelfde; partitioneer de tabel opnieuw als dat nodig is.
Rapportregel: partitions_dropped
views, functions, procedures, triggers, rules, policies, extensions and types other than enums
vermeld in het rapport
IntoSQL converteert tabellen en hun rijen; de rest wordt vermeld zodat je die opnieuw kunt maken.
Rapportregel: not_converted_objects
dumps without CREATE TABLE (--data-only)
typen afgeleid uit de waarden
Een dump met alleen data bevat alleen rijen, dus de typen komen uit de waarden.
Rapportregel: no_schema
expressions in INSERT values
NULL
Een converter die geen database draait, kan ze niet evalueren.
Rapportregel: expression_value
types IntoSQL doesn't know
text
Tekst kan elke waarde bevatten; wijzig het type na het laden.
Rapportregel: unknown_type
text that isn't valid in its encoding
de foute bytes als het vervangingsteken
Tekst in PostgreSQL moet geldige UTF-8 zijn.
Rapportregel: invalid_utf8_replaced
text decoded twice (Ã±)
ongewijzigd gekopieerd
De dump bevat precies deze tekens, dus ze worden gekopieerd; het rapport wijst ze aan.
Rapportregel: mojibake_suspected

In het bestand	In het script	Rapportregel
varchar(n), char(n), text	dezelfde typenDezelfde typen, met dezelfde lengtes.
numeric(p,s), numeric without a precision, NaN and Infinity	dezelfde typen en waardenHetzelfde type: elk cijfer wordt gekopieerd zoals de dump het schreef.
money	numeric(19,2)Hoe money wordt geschreven, hangt af van lc_monetary van de server, dus het bedrag blijft een getal zonder valutasymbool en laadt ongeacht de locale van het doel.	type_replaced
enum types	dezelfde enum-typen, aangemaakt vóór de tabellenDe labels behouden hun volgorde.
enum columns holding values outside their labels	text zonder checkHet type zou die waarden weigeren, dus de kolom is text en behoudt ze allemaal.	enum_check_skipped
composite and range types the dump defines, extension types (citext, hstore, ltree, PostGIS) and multiranges	text met de tekst van PostgreSQLHet script maakt geen extensies of samengestelde typen aan, dus elke waarde behoudt de tekst die PostgreSQL schreef; maak het type aan en wijzig de kolom na het laden.	type_replaced
citext	text, exact vergelekenHet script maakt de extensie citext niet aan, dus deze kolommen vergelijken tekst exact; maak hem aan en zet de kolommen na het laden terug.	case_insensitive_collation
json, jsonb	dezelfde typenjson behoudt de tekst zoals geschreven; jsonb behoudt de waarden zoals de server ze normaliseerde.
timestamp with time zone	timestamptz, dezelfde momentenElke waarde behoudt de offset die de dump schreef, dus het moment is hetzelfde in elke tijdzone.
time, time with time zone	dezelfde typenDezelfde typen, 24:00:00 inbegrepen.
infinity, -infinity and BC dates	behoudenPostgreSQL bewaart ze ongewijzigd.
NaN and Infinity in numeric, real and double precision	behoudenPostgreSQL bewaart ze ongewijzigd.
generated columns	dezelfde gegenereerde kolommenDezelfde formule, berekend door het doel.
generated columns with functions IntoSQL doesn't write	een gewone kolom met de waardenEen formule die IntoSQL niet kan schrijven, levert een gewone kolom met de waarden uit de dump en een regel in het rapport op.	generated_not_translated
defaults with functions IntoSQL doesn't write	geen standaardwaardeEen standaardwaarde die IntoSQL niet kan schrijven, wordt weggelaten en gemeld.	default_not_translated
CHECK constraints with functions IntoSQL doesn't write	niet aangemaaktEen check die IntoSQL niet kan schrijven, wordt weggelaten en gemeld.	check_skipped
CHECK ... NOT VALID	na de rijen toegevoegd als NOT VALIDDe rijen die er al waren, zijn nooit gecontroleerd, dus de check wordt erna toegevoegd, nog steeds niet gevalideerd.
gin, gist, brin and hash indexes, partial and expression indexes	niet aangemaaktIntoSQL schrijft b-tree-indexen op kolommen; maak de andere na het laden opnieuw aan.	index_skipped
foreign keys to tables the dump doesn't create	niet aangemaaktEen sleutel kan niet verwijzen naar een tabel die er niet is.	foreign_key_skipped
NOT NULL columns with values that become NULL	NOT NULL weggelatenEen kolom waarvan waarden NULL moesten worden, kan NOT NULL niet houden, anders zou het laden stoppen.	not_null_relaxed
partitioned tables	één tabel met de rijen van elke partitieDe rijen zijn dezelfde; partitioneer de tabel opnieuw als dat nodig is.	partitions_dropped
views, functions, procedures, triggers, rules, policies, extensions and types other than enums	vermeld in het rapportIntoSQL converteert tabellen en hun rijen; de rest wordt vermeld zodat je die opnieuw kunt maken.	not_converted_objects
dumps without CREATE TABLE (--data-only)	typen afgeleid uit de waardenEen dump met alleen data bevat alleen rijen, dus de typen komen uit de waarden.	no_schema
expressions in INSERT values	NULLEen converter die geen database draait, kan ze niet evalueren.	expression_value
types IntoSQL doesn't know	textTekst kan elke waarde bevatten; wijzig het type na het laden.	unknown_type
text that isn't valid in its encoding	de foute bytes als het vervangingstekenTekst in PostgreSQL moet geldige UTF-8 zijn.	invalid_utf8_replaced
text decoded twice (Ã±)	ongewijzigd gekopieerdDe dump bevat precies deze tekens, dus ze worden gekopieerd; het rapport wijst ze aan.	mojibake_suspected

Het script in PostgreSQL laden

Het script maakt de tabellen aan, kopieert de rijen en voegt daarna sleutels, indexen en refererende sleutels toe, zodat het snel laadt en bij de eerste fout stopt.

$psql -v ON_ERROR_STOP=1 -d mydb -f postgresql.postgresql.sql

Refererende sleutels worden toegevoegd als NOT VALID en daarna gevalideerd. Verwijst een rij naar een rij die niet bestaat, dan vertelt het script je welke constraint het is, in plaats van halverwege te mislukken.

Maak voor een grote dump eerst de database aan en voer het script uit met psql vanaf dezelfde machine als de server.

IntoSQL Pro laadt het bestand rechtstreeks in PostgreSQL vanuit de desktopapp of de opdrachtregel, zonder een script te schrijven.

Een dump van PostgreSQL maken voor het beste resultaat

De converter leest wat de dump bevat. Met deze opties blijft er meer van je data in staan:

--format=plain, de standaard van pg_dump, schrijft SQL. Een custom- of directoryarchief (-Fc, -Fd) wordt SQL met pg_restore -f dump.sql, zonder databaseserver.
--no-owner --no-privileges laten rollen en rechten weg, die de converter toch overslaat, dus de dump wordt kleiner.
pg_dumpall zet elke database in één bestand; de tabellen van elke database krijgen de naam ervan, dus niets botst.

Wat niet wordt geconverteerd

Weergaven, triggers, procedures, functies en andere objecten in de eigen taal van PostgreSQL staan op naam in het rapport en worden nooit stilletjes weggelaten, zodat je elk ervan kunt controleren tegen de PostgreSQL-versie waarin je laadt voordat je het opnieuw aanmaakt.

Vragen over PostgreSQL verhuizen tussen versies

Staat je vraag er niet bij? Schrijf ons via de contactpagina en een mens antwoordt. Contactpagina

Wordt mijn dump geüpload?

Nee. De converter draait in je browser en leest de dump in delen van je schijf, dus hij verlaat je computer nooit. De desktopapp en de opdrachtregel converteren ook op je computer.

Hoe groot mag de dump zijn?

De converter houdt nooit de hele dump in het geheugen: hij leest het bestand twee keer, in bereiken, en schrijft het script gaandeweg. Dumps van meerdere gigabytes worden geconverteerd in de browser, in de desktopapp en met de opdrachtregel.

Leest het pg_dump -Fc-archieven?

Niet direct: een custom-archief is een binair formaat. pg_restore -f dump.sql dump.backup zet het om naar de gewone SQL die de converter leest, zonder verbinding met een server. De converter meldt dat als hij een archief krijgt.

Van welke PostgreSQL-versies kan de dump komen?

Dumps geschreven door pg_dump en pg_dumpall vanaf PostgreSQL 10, met rijen als COPY of INSERT. Elke testdump komt van PostgreSQL 13 en 18 en wordt cel voor cel met zijn bron vergeleken.

Blijven serial- en identity-waarden behouden?

Ja. Serial- en identity-kolommen blijven identity-kolommen, elke rij behoudt zijn id, en elke teller gaat verder vanaf de waarde die de sequence in de dump had bereikt.

Wat gebeurt er met arrays, enums en andere PostgreSQL-typen?

Arrays, ranges, json en jsonb en bitstrings blijven zoals ze zijn, money niet. Enum-typen worden opnieuw aangemaakt met hun labels. Typen van een extensie (citext, hstore, PostGIS) en samengestelde typen worden text, en het rapport zegt welke kolommen.

En functies, triggers en weergaven?

Ze staan op naam in het rapport en worden niet geconverteerd, zodat je ze opnieuw kunt maken zodra de tabellen er zijn. NOT VALID-checks worden na de rijen toegevoegd, nog steeds niet gevalideerd.

Op welke PostgreSQL-versies laadt het script?

PostgreSQL 13 tot en met 18, welke versie de dump ook schreef. Hetzelfde script laadt op elk ervan en stopt bij de eerste fout als er iets misgaat.

id	integer not null
name	text not null
email	text not null
country	varchar(2) not null
is_active	boolean not null
tags	text[] not null
signed_up	date
created_at	timestamptz(6) not null