Op 12-09-2022 om 09:58 schreef Daniel Gustafsson:
On 9 Sep 2022, at 11:00, Andrew Dunstan <and...@dunslane.net> wrote:
On Sep 9, 2022, at 5:53 PM, John Naylor <john.nay...@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
