Hi, While testing “[763aaa06f] Add non-text output formats to pg_dumpall” and “[3c19983cc] pg_restore: add --no-globals option to skip globals”, I found that 3c19983cc might have introduced a regression in 763aaa06f.
Here is simple repro: 1. Run a non-text dump-all ``` % pg_dumpall --format=directory --file=/tmp/dumpall_1 ``` 2. Restoring to a SQL script fails without -C ``` % pg_restore --format=directory --file=/tmp/no_c.sql /tmp/dumpall_1 pg_restore: error: option -C/--create must be specified when restoring an archive created by pg_dumpall pg_restore: hint: Try "pg_restore --help" for more information. pg_restore: hint: Individual databases can be restored using their specific archives. ``` 3. However, the same restore command succeeds by adding “—no-global” ``` % pg_restore --format=directory --no-global --file=/tmp/no_global_no_c.sql /tmp/dumpall_1 ``` Here, --no-globals only indicates that global objects should not be restored. It should not relax the -C requirement when pg_restore is writing a SQL script. As a result, step 3 creates /tmp/no_globals_no_c.sql without CREATE DATABASE commands. If this script is later run against a single target database, objects from multiple source databases can be restored into that database, which is wrong. 4. For comparison, if we add “-C” to the command: ``` % pg_restore --format=directory --no-global -C --file=/tmp/no_global.sql /tmp/dumpall_1 ``` Then comparing /tmp/no_globals_no_c.sql with /tmp/no_globals.sql shows that the latter has CREATE DATABASE, ALTER DATABASE, etc. statements: ``` % diff /tmp/no_global_no_c.sql /tmp/no_global.sql 5c5 < \restrict 3TgTUH1Zl28vBD1kEc8akSnRCAZgDK2jL8CiDKNCUad8n99v25VzfCEwgkGUKsl --- > \restrict P0fvYohAdc92de300rBp7dxCMsvQ0LgDK2ialIwhPBrUPu6TL4IbMngAO1zgoI7 22a23,85 > -- Name: template1; Type: DATABASE; Schema: -; Owner: chaol > -- > > CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8' > LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8'; > > > ALTER DATABASE template1 OWNER TO chaol; > … omit the rest ... ``` I think “-C” should be required unless: * --globals-only is used, or * --no-globals is used in direct-restore mode See the attached patch for the fix details. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/
v1-0001-Fix-pg_restore-no-globals-SQL-output-for-pg_dumpa.patch
Description: Binary data
