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
So you are talking about DBs on the same PgSQL cluster (RDS instance) or you are trying to clone a DB from 1st RDS instance to the 2nd? In each case, you cannot avoid copying. But if we're talking
about the whole cluster ....
This procedure though is very time consuming (although it could be scripted). Is there any better / faster / safer way to do this?
you could design smth based on replication, have a warm/hot standby applying changes from the primary, and then implement smth like :
- promote (i.e. recover and start in a new timeline as a new primary)
- run a script to change ownership to user2.
So the idea is to have pre-copied the data, so that the whole final procedure is very fast. Note, however, that after you do that, you would have to re-setup replication again, and that would be
costly (you can't avoid ultimately copying data). Maybe it could help if you tell us the whole use case.
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
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
