On 07-03-2017 09:02, Panagiotis Atmatzidis wrote:
Hello,
I have 2 RDS instances on AWS running PSQL 9.4.7.
I want to make a clone of database1 which belongs to user1, to
database2 which belongs to user2. Database1 has 20+ tables. I want to
avoid copying the DATA sitting on 5 tables on database1 (many Gigs).
I've read one too many posts about how to perform the actions with
"pg_dump" and "pg_restore" but I'm trying to figure out the easiest
way to do this. The process I have in mind is this:
1) pg_dump the schema from DB1 to DB2 using --no-owner and pg_restore
with --role=user2
2) pg_dump -Fc --no-owner --data-only -t 'table1' from DB1 and then
restore with pg_restore -t 'table' --role=user2 <table.sql> to DB2
This procedure though is very time consuming (although it could be
scripted). Is there any better / faster / safer way to do this?
Thanks.
--
Panagiotis (atmosx) Atmatzidis
email: atma@xxxxxxxxxxxxxx
URL: http://www.convalesco.org
GnuPG ID: 0x1A7BFEC5
gpg --keyserver pgp.mit.edu --recv-keys 1A7BFEC5
"Everyone thinks of changing the world, but no one thinks of changing
himself.” - Leo Tolstoy
Hello,
I do it with shell script, here is it:
#!/bin/sh
for table in a_tbl ab_tbl some_other_tbl
do
echo $table
psql -U data -h 192.168.1.152 second_db -c "TRUNCATE $table;"
/usr/bin/pg_dump -U postgres --no-tablespaces --no-owner -a -b
-t $table first_db | psql -U data -h 192.168.1.152 second_db
done
# line bellow removing some data that should not be on backup.
psql -U data -h 192.168.1.152 second_db -c 'UPDATE a_tbl SET upss =
DEFAULT;'
My servers are in local network. Of course for this way you need to
create table structure on second_db that is the same on master.
Because I use it for backup only, I dont have index on second_db and
process is fast.
Regards,
Hristo S
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
