Re: proposal: possibility to read dumped table's name from file - Mailing list pgsql-hackers

Op 12-09-2022 om 09:58 schreef Daniel Gustafsson:
>> On 9 Sep 2022, at 11:00, Andrew Dunstan <andrew@dunslane.net> wrote:
>>
>>> On Sep 9, 2022, at 5:53 PM, John Naylor <john.naylor@enterprisedb.com> wrote:

>>> [v4-0001-Add-include-exclude-filtering-via-file-in-pg_dump.patch]

I noticed that pg_restore --filter cannot, or at last not always, be 
used with the same filter-file that was used to produce a dump with 
pg_dump --filter.

Is that as designed?  It seems a bit counterintuitive.  It'd be nice if 
that could be fixed.  Admittedly, the 'same' problem in pg_restore -t, 
also less than ideal.

(A messy bashdemo below)

thanks,

Erik Rijkers


#! /bin/bash
db2='testdb2' db3='testdb3'
db2='testdb_source' db3='testdb_target'
sql_dropdb="drop database if exists $db2; drop database if exists $db3;"
sql_createdb="create database $db2; create database $db3;"
schema1=s1  table1=table1  t1=$schema1.$table1
schema2=s2  table2=table2  t2=$schema2.$table2
sql_schema_init="create schema if not exists $schema1; create schema if 
not exists $schema2;"
sql_test="select '$t1', n from $t1 order by n; select '$t2', n from $t2 
order by n;"

function sqltest()
{
   for database_name in $db2 $db3 ;do
     port_used=$( echo "show port" |psql -qtAX -d $database_name )
     echo -n "-- $database_name ($port_used):  "
     echo "$sql_test" | psql -qtAX -a -d $database_name | md5sum
   done
   echo
}

echo "setting up orig db $db2, target db $db3"
echo "$sql_dropdb"    | psql -qtAX
echo "$sql_createdb"  | psql -qtAX

psql -X -d $db2 << SQL
$sql_schema_init
create table $t1 as select n from generate_series(1, (10^1)::int) as f(n);
create table $t2 as select n from generate_series(2, (10^2)::int) as f(n);
SQL
echo "
include table $t1
include table $t2
# include schema $s1
# include schema $s2
" > inputfile1.txt

# in filter; out plain
echo "-- pg_dump -F p -f plainfile1 --filter=inputfile1.txt -d $db2"
          pg_dump -F p -f plainfile1 --filter=inputfile1.txt -d $db2

echo "$sql_schema_init" | psql -qX -d $db3
echo  "-- pg_restore -d $db3 dumpfile1"
           pg_restore -d $db3 dumpfile1
           rc=$?
echo "-- pg_restore returned [$rc]  -- pg_restore without --filter"
sqltest

# enable this to see it fail
if [[ 1 -eq 1 ]]
then

# clean out
echo "drop schema $schema1 cascade; drop schema $schema2 cascade; " | 
psql -qtAXad $db3

--filter=inputfile1.txt"
echo "$sql_schema_init" | psql -qX -d $db3
echo "-- pg_restore -d $db3 --filter=inputfile1.txt dumpfile1"
          pg_restore -d $db3 --filter=inputfile1.txt dumpfile1
          rc=$?
echo "-- pg_restore returned [$rc]  -- pg_restore without --filter"
sqltest

fi