public inbox for [email protected]
help / color / mirror / Atom feedFrom: Erik Rijkers <[email protected]>
To: Daniel Gustafsson <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Justin Pryzby <[email protected]>
Cc: Dean Rasheed <[email protected]>
Cc: Stephen Frost <[email protected]>
Cc: Surafel Temesgen <[email protected]>
Cc: vignesh C <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: proposal: possibility to read dumped table's name from file
Date: Fri, 1 Oct 2021 18:19:56 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAFj8pRBq9X=oSNbo+v93EwznkQ3JczEW6UsfQK37vx1ktW-2ZA@mail.gmail.com>
<[email protected]>
<CAFj8pRAZbCvM3v8jp5wkPpZHJvAopWpbF=+v+84Pns=qWtvEcg@mail.gmail.com>
<[email protected]>
<CAFj8pRDcdAxSRxqwZ-Aywt-SvdNupOY6rrBUevND=nXaDDtvVg@mail.gmail.com>
<[email protected]>
<CAFj8pRDFwyGmyefihxnk4ZCVedPp=Gs3H=CxbPj2-hBguvmoXw@mail.gmail.com>
<[email protected]>
<CAFj8pRD-p3dHzByfo_ZiGvff10k3Vk4ee2HtHkpH13s0Hf3bhA@mail.gmail.com>
<[email protected]>
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
view thread (181+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: proposal: possibility to read dumped table's name from file
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox