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

Op 12-09-2022 om 09:58 schreef Daniel Gustafsson:
>> On 9 Sep 2022, at 11:00, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>
>>> On Sep 9, 2022, at 5:53 PM, John Naylor <john(dot)naylor(at)enterprisedb(dot)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