2

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.