Обсуждение: [GENERAL] Copy database to another host without data from specific tables

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
outthe 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
todo this? 

Thanks.

--
Panagiotis (atmosx) Atmatzidis

email:    atma@convalesco.org
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

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@convalesco.org
> 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

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@convalesco.org
> 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

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
figureout 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
instanceto 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
wayto do this? 
you could design smth based on replication, have a warm/hot standby applying changes from the primary, and then
implementsmth 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
youdo 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@convalesco.org
> 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