CODEWITHSUNDEEP
postgresqlutf-8pg-dump
odarriba
odarriba

Reputation: 342

pg_dump - Dump schema with special characters in the name

We are trying to make a backup file per database schema on our multi-tenant application.

We get the schema names list executing something like:

$ psql -d PG_DATABASE -t -c "select schema_name from information_schema.schemata;" | grep 'tenant_'

And then when execute a backup of each schema:

$ pg_dump -d PG_DATABASE --format=plain -n SCHEMA_NAME -f output.sql

But we are having issues with UTF-8 schema names like tenant_eléctrico:

$ pg_dump -d PG_DATABASE --format=plain -n "tenant_eléctrico" -f output.sql
pg_dump: last built-in OID is 16383
pg_dump: [archiver (db)] query failed: ERROR:  invalid byte sequence for encoding "UTF8": 0xe3 0xa9 0x63
pg_dump: [archiver (db)] query was: SELECT oid FROM pg_catalog.pg_namespace n
WHERE n.nspname OPERATOR(pg_catalog.~) '^(tenant_el�ctrico)$'

We already tried with and without quoting (single and double quotes), trying to escape the special character as hexadecimal... without any good result.

How can I call pg_dump with a schema name that includes UTF-8 valid characters? I'm using PostgreSQL 9.6.15 at the moment.

We might remove those special characters in a near future, but if there is a way to do these backups in the meanwhile it would be great.

Thanks in advance!

Upvotes: 2

Views: 2799

Answers (2)

odarriba
odarriba

Reputation: 342

Looks like we found the answer!

We were running this inside a bash script or in the console itself, and the quotes were being interpreted by the shell, and not passed directly to pg_dump, as it expects for special characters.

The solution was escaping the quotes themselves:

$ pg_dump -d PG_DATABASE --format=plain -n \'tenant_eléctrico\' -f output.sql

or:

$ pg_dump -d PG_DATABASE --format=plain -n '"tenant_eléctrico"' -f output.sql

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 247270

The error message is inexplicable.

The UTF-8 encoding for é is C3 A9, and I cannot imagine anything that would turn it into E3 A9. It would be interesting to know your locale.

Anyway, a workaround would be to use a wildcard:

pg_dump -d PG_DATABASE --format=plain -n 'tenant_el*ctrico' -f output.sql

Upvotes: 0

Related Questions