pg_dump -h host -U postgres -d shop -Fc -f shop.dump takes a compressed dump that pg_restore -d shop_copy shop.dump puts back. Before any of that works, the client has to be at least as new as the server: an older pg_dump refuses outright, and that single error is the most common reason a backup never happens.
1. The error that stops everything
$ clai dump the shop database from the pg17 server→ pg_dump -h pg17 -U postgres -d shoppg_dump: error: aborting because of server version mismatchpg_dump: detail: server version: 17.10; pg_dump version: 16.14 (Debian 16.14-1.pgdg13+1)No flag fixes this. You need a client of the right version — the postgresql-client-17 package, or simply the same container image as the server. The other direction is fine: a newer client can dump an older server.
2. A custom-format dump, not a .sql file
$ clai dump the shop database into a binary file→ pg_dump -h pg17 -U postgres -d shop -Fc -f shop.dump-Fc writes a compressed archive that pg_restore can open selectively and load in parallel with -j. A plain .sql file can do neither — it only goes back in through psql, all of it, in order.
3. Look inside before restoring
$ clai show what is inside the dump→ pg_restore -l shop.dump218; 1259 16386 TABLE public orders postgres217; 1259 16385 SEQUENCE public orders_id_seq postgres3427; 0 0 SEQUENCE OWNED BY public orders_id_seq postgres3271; 2604 16389 DEFAULT public orders id postgres3420; 0 16386 TABLE DATA public orders postgresThat's the table of contents. Save it to a file, delete the lines you don't want, and feed it back with -L — that is how selective restores are done properly.
4. Restore into another database
$ clai restore the dump into the shop_copy database→ pg_restore -h pg17 -U postgres -d shop_copy shop.dumpThe database has to exist first — createdb shop_copy — because pg_restore won't create it without -C. On success the command says nothing at all, so verify with a select count(*).
5. One table, data only
$ clai export only the orders table without the schema→ pg_dump -h pg17 -U postgres -d shop -t orders --data-only -f orders-data.sql-t narrows to one object, --data-only drops the CREATE TABLE. Useful when the target table already exists with the same shape and you just need the rows.
6. Restoring over a database that isn't empty
$ clai restore the dump over the existing database→ pg_restore -h pg17 -U postgres -d shop_copy --clean --if-exists shop.dumpWithout --clean the same dump fails the second time with pg_restore: error: could not execute query: ERROR: relation "orders" already exists. --clean drops objects before recreating them, and --if-exists silences the drops for objects that aren't there yet.
Gotchas
- An older client can't dump a newer server at all. Verified: pg_dump 16.14 against a 17.10 server stops immediately. Keep the client at least as new as the server; with containers the easy answer is to run pg_dump from the same image the server uses.
pg_restore -tappends, it doesn't replace. Restoring one table into a database where it already has rows givesERROR: duplicate key value violates unique constraint "orders_pkey". Truncate first, restore second.pg_restoreexits 0 even after errors. Verified: on a key conflict it printed the error and still returned exit code 0. In scripts add--exit-on-error, otherwise a broken restore looks like a successful one.
Related questions
How do I dump only the schema? pg_dump --schema-only gives structure with no rows. --data-only is the mirror image.
What restores a .sql dump? psql -d dbname -f dump.sql. pg_restore only understands the -Fc, -Fd and -Ft formats — plain text is not one of them.
How do I speed up a large restore? pg_restore -j 4 loads data and builds indexes in parallel. It works only with the custom and directory formats.
See also
CliAI writes the dump and restore lines with the right flags for the job, and shows them before either one touches your database. Install it in one line.