Re: Non-text mode for pg_dumpall

On Mon, 3 Feb 2025 at 14:23, Srinath Reddy <srinath2133(at)gmail(dot)com> wrote:
>
> Hi,
> I found a bug ,while using "./pg_restore pdd -f -" actually it has to copy everything(global sql commands + remaining dump ) into stdout as per the "-f, --file=FILENAME output file name (- for stdout)" but it is copying global sql commands to a file literally naming it as "-" and remaining dump is written to stdout without those global sql commands."-" is not a output file it signifies stdout in terminal cmds.so we have to handle this case.
> because of above reason "./pg_restore pdd -g -f -" also does the same creates a file "-" and writes globals to that file instead of stdout.

I also tested this but in my testing, I can see that all globals are
printed into the console also. Patch was creating a "-" file that was
wrong.
Yes, we should consider "-" as a stdout. In the latest patch, I have
fixed this issue.

On Mon, 3 Feb 2025 at 14:44, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> hi.
>
> git clean -fdx && $BIN10/pg_dumpall --format=directory --file=dir10
> $BIN10/pg_restore --format=directory --file=1.sql --verbose dir10 >
> dir_format 2>&1
>
> there is no "\connect dbname" command.
> pipe 1.sql to psql will execute all the database dump into a single
> database, which is not good.
> we need "\connect dbname" in file 1.sql

We can't add this command directly to the dump file. We need to add
some TOC entry for this command. I will try to make a TOC entry for
this command.

>
>
> --------<<<<<<<>>>>>>>>>>>>>>>------------------
> $BIN10/pg_dumpall --format=directory --exclude-database=src10 --file=dir12_temp
> drop table t from database x
> $BIN10/pg_restore --format=directory --dbname=x --verbose dir12_temp >
> dir_format 2>&1
> --------log info------------------
> pg_restore: found database "template1" (OID: 1) in map.dat file while restoring.
> pg_restore: found database "x" (OID: 19554) in map.dat file while restoring.
> pg_restore: found total 2 database names in map.dat file
> pg_restore: needs to restore 2 databases out of 2 databases
> pg_restore: restoring dump of pg_dumpall without -C option, there
> might be multiple databases in directory.
> pg_restore: restoring database "template1"
> pg_restore: connecting to database for restore
> pg_restore: implied data-only restore
> pg_restore: restoring database "x"
> pg_restore: connecting to database for restore
> pg_restore: processing data for table "public.t"
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 3374; 0 19555 TABLE DATA t jian
> pg_restore: error: could not execute query: ERROR: relation
> "public.t" does not exist
> Command was: COPY public.t (a) FROM stdin;
> pg_restore: warning: errors ignored on restore: 1
> pg_restore: number of restored databases are 2
> ________________________
> $BIN10/pg_restore --format=directory --list dir12_temp
> selected output:
>
> ; Selected TOC Entries:
> ;
> 217; 1259 19555 TABLE public t jian
> 3374; 0 19555 TABLE DATA public t jian
> 3228; 2606 19560 CONSTRAINT public t t_pkey jian
>
> As you can see, dir12_temp has TABLE and TABLE DATA.
> so the above log message: "pg_restore: implied data-only restore" is
> not what we expected.

I will do some tests with pg_dump and -t option.

>
> BTW, add --create option, it works as i expected.
> like
> $BIN10/pg_restore --format=directory --create --dbname=x --verbose
> dir12_temp > dir_format 2>&1
> output is what i expected.
>
> --------<<<<<<<>>>>>>>>>>>>>>>------------------
> with the changes in filter_dbnames_for_restore.
> so <option>--exclude-database=<replaceable
> class="parameter">pattern</replaceable></option>
> will behave differently when you specify the --file option or not.
>
> * --file option specified
> -exclude-database=pattern not allow any special wildcard character.
> it does not behave the same as the doc mentioned.
> * --file option not specified, it behaves the same as the doc mentioned.
>
> That's kind of tricky, either more words in the doc explain the
> scarenio where --file option is specified
> or disallow --file option when --exclude-database is specified.

We will do some more doc changes for this in next versions.

>
> we need to update pg_restore.sgml about MAX_ON_EXIT_NICELY 100?

Temporary, we increased this size. Based on other opinions, we will do
more changes for this.

>
> there is some corner like num_db_restore == 0, num_db_restore >= 100
> in that scarenio, the execute_global_sql_commands already executed,
> which is not ideal, since you have pg_fatal and some sql commands
> already executed.
> maybe we can be if 0 < num_db_restore < 100 then
> call execute_global_sql_commands and restoreAllDatabases.

Got it. Fixed it as per delta patch and added some extra condition to
the IF clause.

Here, I am attaching an updated patch for review and testing.

--
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com