While moving database to another instance I'm using pg_dump
and pg_restore
with intermediate file.
Dump file was created like this:
pg_dump --host $SRC_DB_HOST --port 5432 "dbname=$SRC_DB user=$SRC_DB_USER sslrootcert=rds-combined-ca-bundle.pem sslmode=verify-full" --no-owner --verbose -Fc --file db.bak
I definitely see that there are casts in the dump
$strings db.bak | grep "CREATE \(TYPE\|CAST\)"
CREATE TYPE public.score_group_name AS ENUM (
CREATE TYPE public.score_group_type AS ENUM (
CREATE CAST (character varying AS public.score_group_name) WITH INOUT AS IMPLICIT;
CREATE CAST (character varying AS public.score_group_type) WITH INOUT AS IMPLICIT;
But when I restore database running pg_restore
with params:
pg_restore --verbose -Fc --no-acl --no-owner -n public --single-transaction db.bak > migration.log
there are no queries produced for creating it:
$ grep -e "\(CREATE\|DROP\) \(TYPE\|CAST\)" migration.log
CREATE TYPE public.score_group_name AS ENUM (
CREATE TYPE public.score_group_type AS ENUM (
Source PostgreSQL version:
PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
Target PostgreSQL version:
PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
PostgreSQL Utils versions:
$ pg_dump --version
pg_dump (PostgreSQL) 9.6.14
$ pg_restore --version
pg_restore (PostgreSQL) 9.6.14
UPDATE
It seems that the root of the problem is -n public
parameter, without schema filtering — CREATE CAST
statement is produced, but which schema should I add to the restore? Because -n public
and -n pg_catalog
filter out such statements.
Best Answer
The reason is that type casts do not live in a schema.
If you specify
-n public
, you will only get objects from that schema, and that does not include the type cast.