Thread: BUG #18287: pg_restore with -C and -c options does not do what is said in the documentation

The following bug has been logged on the website:

Bug reference:      18287
Logged by:          Loic Seguin
Email address:      loic.seguin@hotmail.fr
PostgreSQL version: 14.0
Operating system:   Ubuntu
Description:

Hello,

I created two databases on localhost on my PC. I called them like this : 
- source
- target

There are empty databases.

I want to pg_dump the source and pg_restore into the target.

I do this command :

"pg_dump -h localhost -U my_user -F c -d source -v -f dump.bin"

pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path = 
pg_dump: saving database definition

Then I do this command with -C and -c options to restore the dump to the
target database : 

"pg_restore -h localhost -U my_user -C -c -d target -v dump.bin"

pg_restore: connecting to database for restore
pg_restore: dropping DATABASE source
pg_restore: creating DATABASE "source"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 2975; 1262 106504 DATABASE source my_user
pg_restore: error: could not execute query: ERROR:  option "locale" not
recognized
LINE 1: ...ource WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = '...
                                                             ^
Command was: CREATE DATABASE source WITH TEMPLATE = template0 ENCODING =
'UTF8' LOCALE = 'en_US.utf8';


pg_restore: error: could not execute query: ERROR:  database "source" does
not exist
Command was: ALTER DATABASE source OWNER TO my_user;

pg_restore: connecting to new database "source"
pg_restore: error: reconnection failed: connection to server at "localhost"
(127.0.0.1), port 5432 failed: FATAL:  database "source" does not exist

The error log is not what I am interested in.

The problem is that I just dropped the database source but I thought the
target database will be dropped because of the documentation.

Indeed in the documentation stated here
https://www.postgresql.org/docs/current/app-pgrestore.html. The -C option of
pg_restore says : 

"Create the database before restoring into it. If --clean is also specified,
drop and recreate the target database before connecting to it.

With --create, pg_restore also restores the database's comment if any, and
any configuration variable settings that are specific to this database, that
is, any ALTER DATABASE ... SET ... and ALTER ROLE ... IN DATABASE ... SET
... commands that mention this database. Access privileges for the database
itself are also restored, unless --no-acl is specified.

When this option is used, the database named with -d is used only to issue
the initial DROP DATABASE and CREATE DATABASE commands. All data is restored
into the database name that appears in the archive."

If you analyze the log above you can see that the database that is dropped
is not the one used after the -d option but the one in the dump file.

So I think it is an error in the description of the option -C no ?

Or maybe I don't understand maybe the description but it also says above
"If --clean is also specified, drop and recreate the target database before
connecting to it."

It is clearly stated "target" here not the source database.