Re: proposal: possibility to read dumped table's name from file

On 10/1/21 3:19 PM, Daniel Gustafsson wrote:
>
> As has been discussed upthread, this format strikes a compromise wrt simplicity
> and doesn't preclude adding a more structured config file in the future should
> we want that. I think this takes care of most comments and opinions made in
> this thread.
>
> --
> Daniel Gustafsson https://vmware.com/
>

Hi,

If you try to dump/restore a foreign file from a file_fdw server, the
restore step will complain and thus leave the returnvalue nonzero. The
foreign table will be there, with complete 'data'.

A complete runnable exampe is a lot of work; I hope the below bits of
input and output makes the problem clear. Main thing: the pg_restore
contains 2 ERROR lines like:

pg_restore: error: COPY failed for table "ireise1": ERROR: cannot
insert into foreign table "ireise1"

----------------------
From the test bash:

echo "
include table table0 # ok public
include table test.table1 #
include foreign_data goethe # foreign server 'goethe' (file_fdw)
include table gutenberg.ireise1 # foreign table
include table gutenberg.ireise2 # foreign table
" > inputfile1.txt

pg_dump --create -Fc -c -p $port -d $db1 -f dump1 --filter=inputfile1.txt
echo

# prepare for restore
server_name=goethe
echo "create schema if not exists test;" | psql -qaXd $db2
echo "create schema if not exists gutenberg;" | psql -qaXd $db2
echo "create server if not exists $server_name foreign data wrapper
file_fdw " \
| psql -qaXd $db2

echo "-- pg_restore --if-exists -cvd $db2 dump1 "
pg_restore --if-exists -cvd $db2 dump1
rc=$?
echo "-- rc [$rc]" -
echo

----------------------

from the output:

-- pg_dump --create -Fc -c -p 6969 -d testdb1 -f dump1
--filter=inputfile1.txt

-- pg_restore --if-exists -cvd testdb2 dump1
pg_restore: connecting to database for restore
pg_restore: dropping TABLE table1
pg_restore: dropping TABLE table0
pg_restore: dropping FOREIGN TABLE ireise2
pg_restore: dropping FOREIGN TABLE ireise1
pg_restore: creating FOREIGN TABLE "gutenberg.ireise1"
pg_restore: creating COMMENT "gutenberg.FOREIGN TABLE ireise1"
pg_restore: creating FOREIGN TABLE "gutenberg.ireise2"
pg_restore: creating COMMENT "gutenberg.FOREIGN TABLE ireise2"
pg_restore: creating TABLE "public.table0"
pg_restore: creating TABLE "test.table1"
pg_restore: processing data for table "gutenberg.ireise1"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 5570; 0 23625 TABLE DATA ireise1 aardvark
pg_restore: error: COPY failed for table "ireise1": ERROR: cannot
insert into foreign table "ireise1"
pg_restore: processing data for table "gutenberg.ireise2"
pg_restore: from TOC entry 5571; 0 23628 TABLE DATA ireise2 aardvark
pg_restore: error: COPY failed for table "ireise2": ERROR: cannot
insert into foreign table "ireise2"
pg_restore: processing data for table "public.table0"
pg_restore: processing data for table "test.table1"
pg_restore: warning: errors ignored on restore: 2
-- rc [1]

---------

A second, separate practical hickup is that schema's are not restored
from the dumped $schema.$table includes -- but this can be worked
around; for my inputfile1.txt I had to run separately (as seen above,
before running the pg_restore):

create schema if not exists test;
create schema if not exists gutenberg;
create server if not exists goethe foreign data wrapper file_fdw;

A bit annoying but still maybe all right.

Thanks,

Erik Rijkers