EMZETT.pg_dump and psql
In short: Two official command-line tools of PostgreSQL — pg_dump exports a database’s data/structure into a file, psql loads SQL files (including such exports) back into a database.
In more detail: Important: the installed pg_dump version must be the same as or newer than the server version, otherwise the export fails. Newer versions add \restrict/\unrestrict security commands at the beginning/end of the export file — this is pure psql syntax, not SQL, and therefore doesn’t work if you paste the content into a pure web SQL editor (which only understands SQL).
Our context: This was used to export exactly four tables (challenges, custom_roles, rewards, site_settings) from the old Bellator database and import them into the new Emzett Neon database.
In Depth
pg_dump can export in different ways, depending on what you want to do with the result later:
# Plain SQL, human-readable, can be loaded again with psql
pg_dump --table=challenges --data-only "$SOURCE_DB_URL" > challenges.sql
# Custom format: compressed, allows selective restore of individual tables
pg_dump --format=custom "$SOURCE_DB_URL" > backup.dump
psql "$TARGET_DB_URL" < challenges.sql--schema-only exports only the table structure (CREATE TABLE, indexes, constraints) without data, --data-only only the data (useful if the target schema already exists, e.g. because Drizzle has already created it via a migration — creating it twice with CREATE TABLE would otherwise fail). A common stumbling block with cloud databases like Neon: by default the export also contains role/owner information (ALTER TABLE ... OWNER TO ...), which can fail when importing into another database with different role names — --no-owner and --no-privileges avoid this.
psql as an interactive console
Besides simply “loading an SQL file”, psql is also a full-fledged interactive console with its own meta-commands (they start with \ and are NOT SQL):
psql "$DATABASE_URL"\dt -- list all tables in the current schema
\d challenges -- show the structure of a single table (columns, types, indexes)
\timing on -- show the execution time of every query
\q -- leave the console
These meta-commands are pure psql extensions — they only work in psql itself, not if you send the same line to a program that only understands real SQL (e.g. a database library in an application).
Common causes of restore failures
A pg_dump export typically fails on restore for one of three reasons: (1) version incompatibility between the pg_dump version that exported and the psql/server version that imports (so always keep both up to date), (2) missing extensions on the target database if the source database used PostgreSQL extensions such as pgcrypto, or (3) foreign-key constraints that, when loading individual tables (instead of the complete database), point to target tables that don’t exist yet — here --data-only combined with a target schema created beforehand via a migration helps, since then all referenced tables already exist.